Wait stats is my go to thing, however I do get bored just querying it via a table so I decided to broaden my horizons and see how “analytical” I could get with it via Power BI.
What I usually do is that I create a table and dump the contents of a famous waits stats script (*cough * Paul Randal, his code- NOT mine http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) and all I do is put a timestamp on which I really want for time based analysis.
From there I can usually write some queries to see a trend – that is if one does exist.
select * from dbo.WaitQueues where waittype ='IO_COMPLETION' order by SampleTime
Anyways, I want to see Power BI in use now – first you need to download the Desktop version and install it. From https://powerbi.microsoft.com/en-us/
Select Get Data to setup a connection to your table.
You should then be able to preview your data.
Once the data has been loaded you can get creative – I am not creative at all! So if you are a Power BI person I apologise in advance.
The first pie chart shows the Summary of my waits at a specific time.
This is the pie chart at a different time point.
You can drill into a section of the pie chart to return the underlying details.
If you do not like using pie-charts you could switch to a tree map. This was me using CHECKDB heavily.
I wanted to see some visualization of what has happened to a specific wait over my time intervals.
For the below I selected WRITELOG over my time samples.
What about PAGEIOLATCH_EX?
What about CXPACKET analysis?
So what on earth happened at 930? Yes that was me going crazy with poor performing queries on a poor performing disk!
For the next example I decided to use a stacked bar chart looking at a selection of wait types only at a specific time interval. (9.08am)
That light blue (I think) colour seems to take most of the chart, that being LCK_M_S – you can then drill into it for more details.
(Yes that is a link to the awesome sqlskills waits library, yep I helped gather some data for that)
Or if you prefer a standard bar chart can be used, below concentrates on Wait (S) per wait type encountered.
Or changing the filters you could go by percentage per wait type.
Finally I get slightly more creative with signal wait times (red).
I am really enjoying this sort of analysis; it is a nice change from writing queries. Give it ago!