Query Hash Values Are Meaningless in SQL Server: They May Be Reset to Be the Same Value as the Query Plan Hash

This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning. This one has taken top slot because it makes my life more difficult on a daily basis.

Background: SQL Server generates a query_hash for each query. This is stored in sys.query_store_query and it’s one of the primary ways you can identify what a query is across different Query Stores, or even the same Query Store over time, as surrogate query_id values get reset if Query Store is cleared or data ages on. The query_hash is a ‘Zobrist hash over the shape of the individual query, based on the bound (input) logical query tree. Query hints aren’t included as part of the hash.’ (Source)

Continue reading

What Is the CPU Wait in Datadog SQL Server Monitoring? How Does It Compare to Waiting on CPU?

I use Datadog on a regular basis, and I’m a pretty huge fan. The monitoring pack for SQL Server (and its PAAS variants) is still pretty rudimentary, but it evolves regularly. That’s NOT what I’m a fan of, though.

What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors. There are always things in SQL Server monitoring packs that I have strong opinions about. Datadog lets me take what I want, build what I need that isn’t contained in that, and ignore the rest. For a team that has the budget to afford Datadog paired with dedicated database staff with the time and resources to do this work, this can be a great fit.

Continue reading

How to Survive Opening a Microsoft Support Ticket for SQL Server or Azure SQL

Asking Microsoft for support for SQL Server or Azure SQL is a lousy experience these days, whether you’re using a cheaper service tier or the more expensive support tier formerly known as “Premiere Support.” You need to know a lot about the root cause of your problem and how to solve it, or your request will be dismissed with misinformation. You’ll need data and metrics to back up your claims to get the ticket escalated, and you’ll need to provide those receipts multiple times. Once escalated to the Product Group, you may get a helpful response, but it takes a while. If the answer is relayed through a lower support tier, it often won’t make much sense.

These issues aren’t due to bad work ethics or personal failings of support workers. These are good humans trying their best. The problem is worse because it’s systemic.

Continue reading

SQL Server Page Compression: Should You Worry About CPU Usage Increasing on Inserts, Updates, and Deletes?

Every time I share a recommendation to use data compression in SQL Server to reduce physical IO and keep frequently accessed data pages in memory, I hear the same concern from multiple people: won’t this increase CPU usage for inserts, updates, and deletes?

DBAs have been trained to ask this question by many trainings and a lot of online content – I used to mention this as a tradeoff to think about, myself– but I’ve found this is simply the wrong question to ask.

Continue reading

Script to Automate Unforcing Failed Forced Plans in Query Store (SQL Server)

tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic ‘failed" forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub.

This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or SQL Server RDS. Since the script is instance-level and loops through all databases, this isn’t really designed for Azure SQL Database – and you don’t get a SQL Agent there anyway, so you probably want to change this around for that use case. The script is shared under the MIT license, feel free to contribute code and/or adapt away for your own uses.

Continue reading

Please Compress Your Indexes and Shrink Your Databases If You Use Azure SQL Managed Instance

Shrinking databases in SQL Server isn’t fun – it’s slow, it causes blocking if you forget to use the WAIT_AT_LOW_PRIORITY option, and sometimes it persistently fails and refuses to budge until you restart the instance. You only want to shrink a SQL Server database when you’ve got a good reason and a lot of patience.

If you’re using Azure SQL Managed Instance and you haven’t already used data compression on your indexes and shrunk your databases, you probably have two good reasons to do both of those things: performance and cost reduction.

Continue reading

Error 1119 When Shrinking Database: Removing IAM Page Failed

At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:

Msg 1119, Level 16, State 1, Line 11
Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using 
the object that this IAM page belongs to. DBCC execution completed. If DBCC printed 
error messages, contact your system administrator.

There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error.

TLDR; I was not able to get past this without restarting the SQL Server service.

Continue reading