No, Not More DMVs
Though I would be cool with new ones, as long as they’re not memes.
When you’re trying to gauge some high level performance metrics on a new server, you probably have your favorite scripts.
They could …
Category: Wait Stats
SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH Redux
Here’s Another One
In the first post, I looked at a relatively large table. 50 million rows is a decent size.
But 50 million row tables might not be the target audience for this wait.
So, we’re gonna go …
What is the PVS_PREALLOCATE wait type?
I was workload testing on SQL Server 2019 RC1 when I ran into a wait type I’d never noticed before: PVS_PREALLOCATE. Wait seconds per second was about 2.5 which was pretty high for this workload. Based on the name it …
SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH
Well Have You Ever
I mean ever really wondered just how long a query of yours waited on stats to automatically update before running?
Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting …
When Are Wait Stats Actually Useful?
The People Who Stare At Plans
I’m going to be honest with you, dear readers. I’ve fallen out of like with wait stats.
Unless there’s a massive resource or blocking problem, they’re a bit bland.
Tuning a single query, I …
Too Much Of A Harmless Wait Can Be Harmful
Absence Of Evidence
There’s a pinky-out saying about wine: don’t hate the grape.
People say it because the same kind of grape can be grown by different people in different places.
Due to many, ahem, local factors, wine made with …
Running Query Confusion
Somewhat Solved
This bit of confusion is largely solved in SQL Server 2019 under compatibility level 150, when FROID (scalar udf inlining) kicks in.
But, you know, we’re a ways off from 2019 dropping, being adopted, and compat level 150 …
What Are Your Weirdest Waits?
Common People
I know, you probably have questions about CXPACKET.
Maybe you look at a wait stats report and nod sagely at the caps lock extravaganza of variably descriptive names.
The unformatted numbers that you tally on your fingers, and …
Hey Query, What Were You Waiting On?
Surreal Plans
In a parallel plan with an eager index spool, we can observe EXECSYNC waits for the duration of the spool being built.
In a serial plan, we’re not so lucky. There’s no obvious wait that indicates we built …
Eager Index Spool Annoyances
Don’t Mask Spools
Certain spools in SQL Server can be counterproductive, though well intentioned.
In this case, I don’t mean that “if the spool weren’t there, the query would be faster”.
I mean that… Well, let’s just go look.