Welcome to T-SQL Tuesday #87 being hosted this month by Matt Gordon (blog|@sqlatspeed). This month’s topic is “Fixing Old Problems with Shiny New Toys”. The old problem I’m going to talk about is seeing what wait types are experienced by a particular query.

If you would like to participate in this month’s blog party, go to Matt’s invitational blog post: Announcing T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toys.

Why is my query waiting?

SQL Server 2005 gave us a new way to view wait statistics for queries, Dynamic Management Views (DMVs). Two of the DMVs in particular give you information about the wait stats for an actively executing query: sys.dm_exec_requests and sys.dm_os_waiting_tasks. Several years ago, I blogged the query I was using to check the waits for currently executing queries: T-SQL Tuesday #49: My Go-To Query For Waiting Tasks.

That showed the current state of all currently executing queries. What if you just want to know what is affecting one query in particular that I’m trying to tune? Paul Randal (blog|@paulrandal) gave us a process to capture wait stats for a query you are executing manually vie Extended Events: Capturing wait stats for a single operation. I have used this process many times to see the waits for a problematic query that I was tuning.

Ooh shiny! And new!

Paul’s process gives you info on every instance of a wait the query experienced and it’s very easy to aggregate those results to see the top waits and their total effect on a query. Quite often though, you don’t need a lot of detail. You don’t need to know every wait, just the top several. If you are already generating the actual query plan to have a in-depth look at the plan, wouldn’t it be nice if the query wait stats were already in there for you?

Now they are. In SQL Server 2016 (I’m told it came in SP1, but I don’t have a non-SP1 instance to verify that), the actual execution plan includes the top waits for the query execution in the plan. You can see them by clicking on the left-most (first) operator in the plan and viewing the Properties (shortcut F4). It will list the top waits right there in the properties dialog for you.

You can also find them in the XML of the execution plan, and I find this easier to read.

Summary

So now we have lots of ways of finding the waits experienced by a query. Most recently, it gives us the top waits for the query right in the actual execution plan without any extra work required of us except to look for it.