I have been working a lot on Wait Stats and Wait Types recently. Last Year, I requested blog readers to send me their respective server’s wait stats. I appreciate their kind response as I have received Wait stats from my readers. I took each of the results and carefully analyzed them. I provided necessary feedback to the person who sent me his wait stats and wait types. Based on the feedbacks I got, many of the readers have tuned their server.
After a while I got further feedbacks on my recommendations and again, I collected wait stats. I recorded the wait stats and my recommendations and did further research. At some point at time, there were more than 10 different round trips of the recommendations and suggestions. Finally, after six month of working my hands on performance tuning, I have collected some real world wisdom because of this.
Now I plan to share my findings with all of you over here.
Before anything else, please note that all of these are based on my personal observations and opinions. They may or may not match the theory available at other places. Some of the suggestions may not match your situation. Remember, every server is different and consequently, there is more than one solution to a particular problem. However, this series is written with kept wait stats in mind. While I was working on various performance tuning consultations, I did many more things than just tuning wait stats.
Today we will discuss how to capture the wait stats. I use the script diagnostic script created by my friend and SQL Server Expert Glenn Berry to collect wait stats.
Here is the script to collect the wait stats:
-- Isolate top waits for server instance since last restart or statistics clear
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
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
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 OPTION (RECOMPILE); -- percentage threshold
This script uses Dynamic Management View sys.dm_os_wait_stats to collect the wait stats. It omits the system-related wait stats which are not useful to diagnose performance-related bottleneck. Additionally, note OPTION (RECOMPILE) at the end of the DMV will ensure that every time the query runs, it retrieves new data and not the cached data.
This dynamic management view collects all the information since the time when the SQL Server services have been restarted. You can also manually clear the wait stats using the following command:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
Once the wait stats are collected, we can start analysis them and try to see what is causing any particular wait stats to achieve higher percentages than the others.
Many waits stats are related to one another. When the CPU pressure is high, all the CPU-related wait stats show up on top. But when that is fixed, all the wait stats related to the CPU start showing reasonable percentages. It is difficult to have a sure solution, but there are good indications and good suggestions on how to solve this.
I will keep this blog post updated as I will post more details about wait stats and how I reduce them. The reference to Book On Line is over here.
Of course, I have selected February to run this Wait Stats series. I am already cheating by having the smallest month to run this series. :)
Read all the post in the Wait Types and Queue series.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Looking forward to seeing what you come up with.
1 of 28.. Fantastic to see 28 posts on this topic….
Thanks a lot
I too will be interested to see your 28 blog posts about this subject. It will be a lot of work, but it will be good for you !
Brilliant topic and looking forward to reading more!
I have often found the documentation for the Wait Types that I have referenced to be vague and well to be honest just not that helpful. I’m hoping that this series will add some meat to the bones.
what is the meaning of joining CTE waits to itself on:
W2.rn <= W1.rn
I just can't figure it out.
Thank you in advance.
W1 and W2 are the alias names for Waits and we are joining Waits table with the Waits table again by giving two alias names viz W1 and W2 it on the column rn.
Hence W2.rn<=W1.rn is given to avoid the same matching values coming again and again.
this is what i got
ONDEMAND_TASK_QUEUE 54205.45 99.75 99.75
I have two rows of data executing your query:
DIRTY_PAGE_POLL 429790 46,49.96 49.96
HADR_FILESTREAM_IOMGR_IOCOMPLETION 429754.36 49.95 99.91
Would you please let me know what do they mean. Thanks.
Did you solved above waits?
DIRTY_PAGE_POLL can be ignored.
wait_type wait_time_s pct running_pct
PAGEIOLATCH_SH 1444450.86 98.63 98.63
IO_COMPLETION 8863.65 0.61 99.23
Can You Please give what is happening in my system
@Digant Dudharejia: most probably you have a DISK, IO subsystem issue, check with your Storage admin.
I would agree with Hany.
wait_type wait_time_s pct running_pct
CXPACKET 12205236.55 55.59 55.59
PAGELATCH_UP 5460672.73 24.87 80.46
LATCH_EX 2412872.58 10.99 91.45
PAGEIOLATCH_SH 477383.76 2.17 93.63
PAGELATCH_SH 264119.57 1.20 94.83
BROKER_EVENTHANDLER 231293.07 1.05 95.88
SOS_SCHEDULER_YIELD 169129.85 0.77 96.65
EXECSYNC 165021.55 0.75 97.41
LCK_M_S 145500.53 0.66 98.07
LATCH_SH 95372.91 0.43 98.50
BROKER_RECEIVE_WAITFOR 60310.53 0.27 98.78
PAGEIOLATCH_EX 49145.23 0.22 99.00
looks like parallelism and TempDB
CXPACKET 9284436.75 41.35 41.35
SQLTRACE_WAIT_ENTRIES 8432657.06 37.55 78.90
BROKER_EVENTHANDLER 2279408.12 10.15 89.05
LATCH_EX 420773.82 1.87 90.92
PAGEIOLATCH_SH 387884.31 1.73 92.65
OLEDB 303395.27 1.35 94.00
BACKUPBUFFER 279220.37 1.24 95.25
ASYNC_IO_COMPLETION 242254.66 1.08 96.32
BACKUPIO 218730.18 0.97 97.30
PAGEIOLATCH_EX 111566.86 0.50 97.80
SOS_SCHEDULER_YIELD 93330.73 0.42 98.21
IO_COMPLETION 91645.38 0.41 98.62
BROKER_RECEIVE_WAITFOR 84000.28 0.37 98.99
WRITELOG 62652.47 0.28 99.27