Looking at wait stats differently with SQL 2016

Wait statistics is a great place to start looking into performance issues. Normally people would issue commands/queries based on  sys.dm_os_wait_stats and/or sys.dm_os_waiting_tasks. Did you know from SQL 2016 you have the ability to work with wait stats at a session level? This is done via sys.dm_exec_session_waits_stats. As stated by Microsoft documentation the “DMV resets the information for a session when the session is opened or when the session is reset” .

This is what is returned. The key bit here is the session_ID.

waitsdmv1

So let’s try and put this to use. Please note I use SQLskills queries written by Paul Randal for my wait stats analysis as shown here http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/, probably because that is where I learnt about the topic.

So I run a specific workload such as

DBCC CHECKDB ('AdventureWorks2016')
GO

Then I use the query from the above link which is based on  sys.dm_os_wait_stats  to check wait stats returning the following snippet (I did not clear anything out beforehand):

waitsmain

This tells me about the waits since my last reboot or since a manual reset of the stats. It’s probably why you should do at least time-based analysis or reset the wait stats before starting, that is if you are interested in something time specific or if you want to understand certain workloads at a given time.

So the other option is that you could go down the session level route. With the session based analysis I took the query and changed it slightly to query sys.dm_exec_session_wait_stats and also pull back the session_id that I am interested in.

First thing is that I used the following to get an ID of interest. This was actually the connection for when I ran the CHECKDB command – SPID 56.


SELECT @@SPID AS [Current SPID]

spid56

I then use this ID within the WHERE clause of the query so I could track it to get session level wait stats.

checkwait

As you would expect it is very specific and quite relevant for the workload (CHECKDB) and for that moment in time only. Its definitely not a replacement technique for the other traditional ways but it could be something that you could use if you want to track a session ID real time.

Here is another example, this time tracking session_id 53 which is a big SELECT statement.

select.JPG

Session level waits:

rbar

Using the DMV in its most simplest form would return the following:

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = 54

simple

 

I guess you could even use sys.dm_exec_session_waits_stats to get some further details when/if you query sys.dm_exec_sessions or anywhere else where you can join onto the session_id, such as:

SELECT
s.program_name,s.login_time,s.database_id, cws.wait_type,cws.wait_time_ms
FROM sys.dm_exec_session_wait_stats AS cws
JOIN sys.dm_exec_sessions AS s
ON cws.session_id = s.session_id
WHERE cws.session_id = 54
ORDER BY wait_time_ms DESC

Snippet:

waits2016_2

SQL Server Bugs & Enhancement Requests

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests hosted by Brent Ozar.

https://www.brentozar.com/archive/2017/01/announcing-t-sql-tuesday-87-sql-server-bugs-enhancement-requests/

tsql

I did not want to miss out on this one because I want to mention a connect item that I logged last year which is still active.

This is an enhancement request for SQL Server where I got the idea from SQL database (Azure).

Wait Stats is my “go-to” thing, when you want to dig into performance issues everyone knows you will probably end up using sys.dm_os_wait_stats. You cannot use this in the Azure world, you have to use a DMV that is scoped to the database level. I think this would be a nice idea to have with the “earth” based SQL Servers – the ability to return information about all the waits encountered by threads that executed at the database level.

The connect item can be found at this link: https://connect.microsoft.com/SQLServer/feedback/details/2647332/sys-dm-db-wait-stats-dmv?tduid=(262281c4c73a682498780643b77e80d1)(256380)(2459594)(TnL5HPStwNw-KjxCoz0pGWobbq7q1MQTIw)()

An example of using the DMV that I would like:

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_db_wait_stats
)
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct <  99

WAITSDMV.JPG

It currently has 8 votes, would be nice to get some more.

Checking your Wait Stats via Power BI

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.

Something like:

select * from dbo.WaitQueues
where waittype ='IO_COMPLETION' order by SampleTime

waits.JPG

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/

waits1.JPG

wait3.JPG

waits4.JPG

Select Get Data to setup a connection to your table.

waits5.JPG

You should then be able to preview your data.

waits6.JPG

 

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.

waits11.JPG

This is the pie chart at a different time point.

waits12.JPG

You can drill into a section of the pie chart to return the underlying details.

waits14.JPG

If you do not like using pie-charts you could switch to a tree map. This was me using CHECKDB heavily.

waits16.JPG

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.

waits18.JPG

waits20.JPG

What about PAGEIOLATCH_EX?

waits23.JPG

What about CXPACKET analysis?

waits24.JPG

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)

waits27.JPG

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.

waits29.JPG

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

waits30.JPG

Or changing the filters you could go by percentage per wait type.

waits31.JPG

Finally I get slightly more creative with signal wait times (red).

signal

I am really enjoying this sort of analysis; it is a nice change from writing queries. Give it ago!