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.
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.
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.
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)
