In our blog post last week: Instance Wide Wait Stats we promised to show You how to visualize the collected wait stats with Microsoft Excel, so this is what this blog post will be about.

The blog post from last week will be the basic setup for this one, so the steps to create the table and to collect the data will not be repeated in this one. If you need them you’ll have to go back and get it.

Today I’ll encapsulate the TSQL logic in a view, this is not necessary, but it makes it much easier from Excel to connect to a predefined view rather than having to write the TSQL. I will call the view for V_WaitStats, and here is the code to create it:

CREATE VIEW V_WaitStats
AS
WITH cte AS
(
SELECT
    id, wait_type, wait_time_ms, SampleTime,
    ROW_NUMBER() OVER(PARTITION BY wait_type ORDER BY SampleTime) as rn
FROM InstanceWideWaitStats
)
SELECT
 t1.wait_type,
 t2.wait_time_ms - t1.wait_time_ms as WaitTimeInMS,
 t2.SampleTime
FROM cte t1
INNER JOIN cte t2 on (t1.wait_type = t2.wait_type) and (t1.rn = t2.rn -1)
WHERE t2.wait_time_ms - t1.wait_time_ms <> 0

What I want to do next is connect to my SQL Server View from Excel, I am no way an Excel Guru, but this is so easy, and such a powerful tool at visualize data like wait stats. Here is some screenshots from the process.

First step is to enter the name of the SQL Server where the data is stored

Then is it time to select database and the View that we created earlier in this blog post. And the in the end it is just a matter of choosing the right option.

And then just hit the OK button. Then it is time to show some Pivot Table magic, Excel gives us so many opportunities to play around with the Wait Stats data, further down you’ll find some examples that shows some of them. Selecting a subset of wait types or periods is easy with Excel, and the Graph changes straight away.

I use this quite often, and the information that You get with this is very useful. This solution that we have described in these two blog posts, should be in every DBA’s toolbox.