Have you heard of SQLskills? Have you heard of the waits library? If not then where have you been hiding?
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.
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):
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]
I then use this ID within the WHERE clause of the query so I could track it to get session level wait stats.
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.
Session level waits:
Using the DMV in its most simplest form would return the following:
SELECT * FROM sys.dm_exec_session_wait_stats WHERE session_id = 54
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
T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests hosted by Brent Ozar.
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
It currently has 8 votes, would be nice to get some more.
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!