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. …
Category: SQL Server 2019
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 …
UDF Inlining And String Aggregations In SQL Server 2019
Quatro Enemigos
This post is really four different posts. Maybe five. The main points are:
- STRING_AGG can’t be inlined if a UDF
- Getting DISTINCT values into STRING_AGG is more obtuse than it should be
- The documentation for UDF inlining is
Rewriting Scalar UDFs For SQL Server 2019 Inlining
You May Find Yourself
SQL Server 2019 has been a bit of a roller coaster ride. In particular, UDF inlining started as I think the most interesting addition to the product. Big brain stuff, for sure.
It has been nerfed …
Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server
Punching
Thanks for watching!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m …
A Silent Bug With Clustered Column Store Indexes And Computed Columns In SQL Server 2019
Oh Boy
This bug has been reported to the proper authorities, and this blog post is not to dunk on the Summer Intern responsible for column store indexes, but to warn you to not do this, because I don’t know …
How SQL Server 2019 Helps You Link Queries To Missing Index Requests
Not Another Upgrade
When dm_db_missing_index_group_stats_query got documented, I was really happy. After all, this has been a peeve of mine for ages.
“Wow look at all these missing index requests. Where’d they come from?”
So this is neat! And it’s …
A Bug With Recursive UDFs When Inlined In SQL Server 2019
Enough Already
I see people do things like this fairly often with UDFs. I don’t know why. It’s almost like they read a list of best practices and decided the opposite was better.
This is a quite simplified function, but …