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 that grape can taste miraculously different.
It’s with that in mind that I’m going to say this: don’t ignore the wait.
No matter what script you’re using to look at wait stats, try unquoting the ignoreable list and seeing what shows up.
Get curious. Poke around. You might find something interesting.
Twosifer
While experimenting with FROID, I came up with a function and query that generate some weird waits.
Those waits are EXECSYNC, and CXCONSUMER. Now, under normal circumstances, you might be able to ignore them.
But things are rarely normal when you’re experiencing performance problems, are they? If you ignore too much, you can miss big problems.
Going back to running this query, I can see the wait stats that get generated in sys.dm_exec_session_wait_stats when the query is finished.
SELECT u.DisplayName,
dbo.TotalScore(u.Id) AS TotalScore
FROM dbo.Users AS u
WHERE u.Reputation >= 200000
ORDER BY u.Id;
Here’s what those waits look like:
If one were to follow advice — even advice from Microsoft — one may miss important clues as to what happened.
CXCONSUMER waits being high is fairly tightly correlated to skewed parallelism, and this is no exception.
EXECSYNC represents a serial zone within a parallel plan, in this case building two Eager Index Spools:
When you spend a long time building indexes single threaded, you spend a long time waiting on CXCONSUMER (and not so much time at all waiting on CXPACKET).
Being able to put the waits together with the query plan can help you tune queries more efficiently.
This is especially true if you’re on earlier versions of SQL Server/SSMS where the kind of detail shown in query plans here doesn’t exist.
Thanks for reading!