SQL Server 2016 Service Pack 1 brought us performance tuners all kinds of shiny goodies – like the ability to see wait stats inside a query’s actual execution plan. I was really excited when this first came out, but I keep seeing some real oddities.
Let’s keep this one really simple: I’m using the Stack Overflow public database, and any version will do. Start by purposely kneecapping your SQL Server, restricting it to just 1GB of memory:
EXEC sys.sp_configure N'show advanced options', N'1';
EXEC sys.sp_configure N'max server memory (MB)', N'1000'
After that finishes, pop open another window, give your SQL Server amnesia, and start sp_Blitz for a 60-second monitoring span:
sp_BlitzFirst @ExpertMode = 1, @Seconds = 60
Then start another window, turn on actual execution plans, and run this simple query which scans the Posts table – which is quite too large to fit into 1GB of RAM:
SELECT COUNT(*) FROM dbo.Posts;
Assuming this is the only query running at the time, you would expect that the server-level wait stats would match the execution plan’s waits. Here’s what the query plan shows:
That sounds amazing! Our server only had 706 milliseconds of wait time altogether, none of which was spent waiting on storage! Our storage must be super-blazing fast, and there’s practically no way to tune it to wait less than 706 milliseconds, right?
Just to show that I’ve got nothing up my sleeve, here’s the waits in the XML plan:
Your first clue that these aren’t quite accurate is the fact that this query went parallel, but no CXPACKET waits are shown.
Your second clue: this query took a full 39 seconds to run, and CPU wasn’t 100% the whole time. Here’s a quick shot of Task Manager just to prove that point:
So what was SQL Server waiting on? Server-level wait stats tell a very different (and more accurate) story:
Ah-ha! We waited a heck of a long time for PAGEIOLATCH_SH, which means reading data pages from a data file. And while we’re looking – that “actual” execution plan didn’t mention anything about CXPACKET waits either.
Can you see wait stats in query plans? Sure. They’re just not accurate – yet, at least – so don’t go making any complex tuning decisions based on those. Keep your eye on this Connect item to know when it’s fixed.
Update 2017/10/05: Bad news.
Microsoft marked the bug as “Won’t Fix” and wrote:
This results in PAGEIOLATCH_* wait type and other IO waits not being tracked per session, because by-design these are SOS waits that are tracked at the server level. Potentially tracking individual IO requests per thread and session also has performance considerations that would impact query execution.
So no, 2016 doesn’t really show wait stats in query plans.
To me, this is worse than not showing any wait stats at all because the waits aren’t anywhere near accurate. Some poor performance tuner who hasn’t read this post is going to start analyzing their query’s wait stats in the plan, do all kinds of work to “fix” it, and not understand why the query’s not getting faster.
Update 2017/10/05 Part II – there might be more hope! Pedro Lopes reopened the Connect item for discussion.