NOTE: This script will just work fine with earlier version of SQL Server (2008, 2012, 2014) along with SQL Server 2016. It will give the expected results in all the SQL Server versions. This script will help you identify resource bottlenecks in your SQL Server.
A few years ago, I wrote the script which will help us identify SQL Wait Stats and Queues. It is a very popular script. You can find the link to the script over here: Introduction to Wait Stats and Wait Types. However, it has been a long time since I wrote that script and it was written for SQL Server 2008.
Now a days lot of customers have moved to the latest version of SQL Server. I use this script primary to get started with my performance tuning engagements. As I use this script more and more on the latest version, I realized that I needed to exclude few more innocent or harmless wait types from this query. I keep on changing my original script to accommodate new wait types. In my recent performance tuning consulting engagement, I get the opportunity to work with 60 SQL Server 2016 all together. While fixing performance for all the 60 servers running SQL Server 2016, I realized that it is not possible to keep using my old script and I have written a new script while observing various outputs of those servers.
Here is the script which can help you find “What are various resource bottlenecks?”
-- SQL Wait Stats and Queies -- (C) Pinal Dave https://blog.sqlauthority.com/ ) 2016 -- Send query result to firstname.lastname@example.org for quick feedback SELECT wait_type AS Wait_Type, wait_time_ms / 1000.0 AS Wait_Time_Seconds, waiting_tasks_count AS Waiting_Tasks_Count, -- CAST((wait_time_ms / 1000.0)/waiting_tasks_count AS decimal(10,4)) AS AVG_Waiting_Tasks_Count, wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS Percentage_WaitTime --,waiting_tasks_count * 100.0 / SUM(waiting_tasks_count) OVER() AS Percentage_Count FROM sys.dm_os_wait_stats WHERE wait_type NOT IN (N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_DBM_MUTEX', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_OS_AUTHENTICATIONOPS', N'PREEMPTIVE_OS_AUTHORIZATIONOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CREATEFILE', N'PREEMPTIVE_OS_CRYPTOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY', N'PREEMPTIVE_OS_VERIFYTRUST', N'PREEMPTIVE_OS_WAITFORSINGLEOBJECT', N'PREEMPTIVE_OS_WRITEFILEGATHER', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', N'PREEMPTIVE_XE_GETTARGETSTATE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'UCS_SESSION_REGISTRATION', N'WAIT_FOR_RESULTS', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_RECOVERY', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT' ) AND wait_time_ms >= 1 ORDER BY Wait_Time_Seconds DESC -- ORDER BY Waiting_Tasks_Count DESC
Once you run above script, you can find a list of the resource bottlenecks in your system. Here is the list of all the resources you may need to resolve any issue with SQL Wait Stats. Make sure that you bookmark this page as it contains links to valuable resources.
Alternatively, you can also send your SQL Wait Stats to me (via email) in excel file and I will take a look at that and will give you my quick feedback about your resource bottleneck.
Note: If you think I should add or remove any wait types to the list do let me know. The process to improve the script is a constant process.
Reference: Pinal Dave (https://blog.sqlauthority.com/)
I have just sent an attachment.Could you please review.
If you have sent email, I reply every email in six hours.
Hi Pinal, can you give me feedback on my results for wait time? I have attached a file
We are using SQL Server 2016 developer edition and getting high wait stats: PARALLEL_REDO_WORKER_WAIT_WORK.
could you please help us to resolve this.
You can disabled parallel redo by trace flag 3459
How to find the db query taking time while doing in the performance test. and how to sort it out.
I have SQL Server 2016, and it have very high PWAIT_DIRECTLOGCONSUMER_GETNEXT
Wait Type : PWAIT_DIRECTLOGCONSUMER_GETNEXT
Number of Waits : 5256047
Wait Time (sec) : 6694882.612
% Wait Time : 99.97%
Max Wait Time (ms) : 98689647
Avg Wait Time (ms) : 1273.7
Could you please help us to resolve this.