SQL SERVER – Wait Statistics from Query Execution Plan

Performance tuning is an art and I consider myself fortunate that I get to work on it every single day during Comprehensive Database Performance Health Check. Every single day I have a new conversation with my client about how SQL Server works and how we can interpret different aspects of the performance tuning. Recently one of my clients asked if I know a way to find the Wait Statistics from the Query Execution Plan. Yes, there is a way to do it, and let us learn it today.

SQL SERVER - Wait Statistics from Query Execution Plan StatisticsfromQuery-800x455

Statistics from Query

First, open SQL Server Management Studio (SSMS). Now write any sample query. Here is the query which I have written for the sample database [WideWorldImporters].

SELECT *
FROM [WideWorldImporters].[Sales].[Invoices]
WHERE CustomerID != 191
ORDER BY [InvoiceDate]

Again, please note that here there is no importance of the query, so you can write any of your preferred queries.

Now enable the execution plan for the query. You can read how you can do it here: SQL SERVER – 3 Different Ways to Explore Actual Execution Plans. Next, run the query in the SQL Server Management Studio. Right after that, go to the very first operator in the execution plan (in our case that would be the SELECT operator) and click on the properties.

SQL SERVER - Wait Statistics from Query Execution Plan Statistics-from-Query1

Once you open the properties go the property of the WaitStats and expand all the options. Here you will see the wait information about the query. In our case, there were 8 different wait statistics.

SQL SERVER - Wait Statistics from Query Execution Plan Statistics-from-Query2

Well, it is easy to see the wait statistics for any query in SQL Server Management Studio. While this may come in handy when you are looking at the one query and trying to troubleshoot it. You can also do it programmatically for all the queries and here are the instructions for the same: SQL SERVER – Query Specific Wait Statistics and Performance Tuning.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Single Table Scan for Multiple Aggregated Operators
Next Post
SQL SERVER – Reduce Deadlock for Important Transactions With Minimum Code Change

Related Posts

Leave a Reply

Menu