I have a post up on monitoring SQL Server instances with Telegraf:
Not too long ago, I had the opportunity to put into place a free solution for monitoring SQL Server instances. I saw Tracy’s series on collecting performance metrics InfluxDB + Telegraf + Grafana, and then I saw her talk on the topic (Collecting Performance Metrics), but until I implemented it myself, I couldn’t believe how easy it was. I thought it was going to take two or three days of hard work to get done, but I had everything going within a few hours.
Let’s walk through the process together.
I keep saying this in the post, but it’s much easier than I expected. There are still more steps than a commercial off-the-shelf product but part of what you’re paying for there is convenience, so that had better be easier.
Grant Fritchey loves Entity Framework:
Yes, Entity Framework will improve your job quality and reduce stress in your life.
With one caveat, it gets used correctly.
That’s the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell.
One nit that I’ve always had with Entity Framework is that it’s very difficult to tell what part of the code the call was coming from. You really have no idea. So when my friend, Chris Woodruff, asked me on Twitter what would be the best way to monitor TagWith queries in Entity Framework, well, first, I had to go look up what TagWith was, then I got real excited, because, hey, here’s a solution.
That “I love Entity Framework” is the lead-in to a one-act play of mine with people with pitchforks, tar, and feathers. Nevertheless, Grant shows us how we can tag code in C# and capture that data in extended events. I’d read it but I’m too busy sharpening my pitchfork.
Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.
For this blog post I decided to use Brent Ozar’s famous sp_BlitzWho command (in expert mode) coupled with Azure Logic Apps. At a high level it is simple. At a specific time trigger the execution of sp_BlitzWho stored procedure and query it for later use.
Click through to see how to set this up.
Chris Adkin continues a series on big data clusters in SQL Server 2019:
This post post will focus on creating a big data cluster so that you can get up and running as fast as possible, as such the storage type used will be ephemeral, this perfectly acceptable for “Kicking the tyres”. For production grade installations integration with a production grade storage platform is required via a storage plugin. Before we create our cluster, with the assumption we are doing this with an on premises infrastructure, the following pre-requisites need to be met:
Read the whole thing, but wait until part 4 before putting anything valuable in it.
David Fowler announces the release of Undercover Catalogue 0.2:
dbaTools have announced that a number of functions have been deprecated and will be removed before the release of version 1.
To future proof the Catalogue against these breaking changes, all deprecated functions have been removed from the Interrogation Powershell script.
Click through for the full change log.
Kevin Chant has a list of tools you can use to manage SQL Server on Azure VMs:
From experience I know it’s important to know what applications you can use locally with Azure to manage SQL Server solutions. So you have the right tools for the job.
For instance, I was talking with some people at a client’s site the other day about deciding what application to use to future proof themselves.
In this post I will cover applications for use with Windows, MacOS and Linux distributions.
I don’t think I’m spoiling too much in saying that about 80% of these are the same tools you would use for on-prem work.
The time it takes to rebuild the index can be substantially longer for ONLINE. Many of us has other things to do with the database during night-time and/or weekends. Yes, these are the typical window in time where we try to find things such as index rebuilds. Say that you do it night-time and it currently take 4 hours. Wouldn’t it be nice if you could cut that time down to 1.5 hours? That would leave more time for imports, massaging of data, CHECKDB and other things you want to do. Sure, you can do it ONLINE, but it will slow down access during the rebuild. Also the more data you modify during the rebuild, the more space you need in tempdb.
Betteridge’s Law of Headlines applies too, so that’s two important principles in one post.
As far as the post goes, Tibor makes a fair point: there is a trade-off between availability and efficiency with index rebuilds. But having worked with clustered columnstore indexes in 2014, you’ll pry the online operations in subsequent versions out of my cold, dead hands.
Andrew Pruski writes up some issues he had with creating a LoadBalancer service in Azure Kubernetes:
I logged a case with MS Support and when they came back to me, they advised that the service principal that is spun up in the background had expired. This service principal is required to allow the cluster to interact with the Azure APIs in order to create other Azure resources.
When a service is created within AKS with a type of LoadBalancer, a Load Balancer is created in the background which provides the external IP I was waiting on to allow me to connect to the cluster.
Because this principal had expired, the cluster was unable to create the Load Balancer and the external IP of the service remained in the pending state.
There were a lot of steps here; click through to see just how many.
Shabnam Watson covers a new setting in Analysis Services 2019:
The purpose of this setting is limit the amount of memory any single query can take. This setting is extremely useful when you want to limit the amount of memory consumption per query for queries across the board. Before this setting, it was possible to have an extremely poorly written query eat up all of a server’s memory and bring all other queries down to a halt. You can see an example of a such a query and SSAS memory settings in my previous post here.
Read on for details about what it does and what happens when a query reaches the memory limit.
Jack Vamvas takes us through a reason why you might get error 3041:
An error message has started appearing in the SQL Server Error Logs during a nightly full backup.
Could not clear ‘DIFFERENTIAL’ bitmap in database ‘Database1’ because of error 9002. As a result, the differential or bulk-logged bitmap overstates the amount of change that will occur with the next differential or log backup. This discrepancy might slow down later differential or log backup operations and cause the backup sets to be larger than necessary. Typically, the cause of this error is insufficient resources. Investigate the failure and resolve the cause. If the error occurred on a data backup, consider taking a data backup to create a new base for future differential backups.
Click through for the root cause and solution.
Kevin Feasel
2019-02-06
Administration