Used To Love Her
I used to get so excited about looking in the plan cache, and writing all sorts wacky XML queries to parse plans and dig for goodies.
Then I started to ask some tough questions about it, …
Category: Plan Cache
Common SQL Server Consulting Advice: Enabling Forced Parameterization
Not The Bad Kind
I need to start this post off by saying something that may not be obvious to you: Not all parameter sniffing is bad.
Sure, every time you hear someone say “parameter sniffing” they want to teach …
Does My SQL Server Need More Memory?
Work It
Memory is S-Tier crucial for most workloads to run reliably fast. It’s where SQL Server caches data pages, and it’s what it gives to queries to process Sorts and Hashes (among other things, but these are most common).…
Getting Parameter Values From A SQL Server Query Plan For Performance Tuning
Property Management
I’m a really big fan of using operator properties for a lot of things, at least visually. Where things sort of fall down for that is copying and pasting things out.
For some stuff, you still need …
How OPTIMIZE FOR UNKNOWN Makes Troubleshooting SQL Server Performance Problems Harder
Detained
Despite the many metric tons of blog posts warning people about this stuff, I still see many local variables and optimize for unknown hints. As a solution to parameter sniffing, it’s probably the best choice 1/1000th of the time. …
Why You Should Stop Looking At Query Costs In SQL Server
Spare No Expense
Over the years, there have been a lot of requests to get sp_BlitzCache to sort results by query cost. I understand why. It’s assumed that the optimizer is never wrong and that cost is directly associated with …
A Query To Help You Find Plans With Eager Index Spools In SQL Server’s Plan Cache
Yawn And Yawn
I’ve written a bunch about Eager Index Spools, and how to fix them, but I’ve always sort of left the “finding” part up to you, or pointed curious tuners to tools like sp_BlitzCache.
Recently though, …
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 …
Documentation for dm_db_missing_index_group_stats_query
No, It’s New
When I was checking out early builds of SQL Server 2019, I noticed a new DMV called dm_db_missing_index_group_stats_query, that I thought was pretty cool.
It helped you tie missing index requests to the queries that requested …
Troubleshooting RESOURCE_SEMAPHORE_QUERY_COMPILE Waits In SQL Server
Unfortunate
RESOURCE_SEMAPHORE_QUERY_COMPILE happens, in a nutshell, when SQL Server has allocated all the memory it’s willing to give out to compile query plans of a certain size and, throttles itself by making other queries wait to compile. For more details, …