Brent recently blogged about ordered columnstore indexes in SQL Server 2022 and had some trouble with them, so I decided to take a look into the mechanics of the feature. I’m testing on SQL Server 2022 CTP 2.0.
Author: Joe Obbish
Unkillable Sessions and Undetected Deadlocks
I recently experienced a blocking issue in a production environment which had been going on for hours. As a responsible DBA, I tried to kill the head of the blocking chain. Unexpectedly, killing the session seemed to have no effect. …
Why Some Types of Parallel Scans can be Slow on Cloud Storage
Upon reading the title, you may be thinking that of course parallel scans will be slow in the cloud. Cloud storage storage simply isn’t very fast. I would argue that there’s a bit more to it.
The Timeout
A query …
Using Batch Mode for the “Matching Supply With Demand” Challenge
Itzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say and getting code formatting right can be tricky in blog post …
Blocking on Columnstore Indexes that RCSI and NOLOCK Don’t Resolve
I recently ran into a production issue where a SELECT query that referenced a NOLOCK-hinted table was hitting a 30 second query timeout. Query store wait stats suggested that the issue was blocking on a table with a nonclustered columnstore …
Why does FAST_FORWARD make some cursors so much faster in SQL Server?
If you’re like me, you started your database journey by defining cursors with the default options. This went on until a senior developer or DBA kindly pointed out that you can get better performance by using the FAST_FORWARD option. Or …
Trying out the new premium-series Azure SQL Managed Instances
At Microsoft Ignite 2021, public preview for new “premium-series” hardware was announced for Azure SQL Managed Instances. There’s even a black friday sort of sale during this month where you can do testing on premium-series VMs without paying for the …
Simple test results for query I/O performance on Azure SQL Managed Instance
Lately I’ve been doing some exploratory performance testing on Azure SQL Managed Instances in preparation for a migration to that platform. This blog post documents some storage testing results and may even have practical advice near the end. All testing …
What’s in your SQL Server error log?
This is my 50th blog post! I’m going to do something a bit special.
The Error Log
I’ve found the SQL Server error log to be slightly underrated as a source of useful information for how your SQL Server instance …