Past Perfect
You know all those pesky developer myths that never go away?
- CTEs and Views can’t use indexes: WRONG
- Subqueries are slower than joins: WRONG
- Temp tables are bad for performance: WRONG
- Table Variables are always in memory: WRONG
Category: SQL Server 2019
How SQL Server 2019 Helps You Find Queries That Have Missing Index Requests
bigtime
In the quest for me trying to get people to upgrade to a not-old-and-busted version of SQL Server, this is one that I talk about a lot because it really helps folks who don’t have all the time in …
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 …
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 …