{"id":1003,"date":"2017-03-05T11:56:31","date_gmt":"2017-03-05T06:26:31","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=1003"},"modified":"2017-03-05T11:56:31","modified_gmt":"2017-03-05T06:26:31","slug":"msssql-server-tuning-find-wait-stats","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=1003","title":{"rendered":"MSSSQL Server Tuning – Find Wait stats"},"content":{"rendered":"

Problem Statement: <\/strong>Script to get the SQL Server\u00a0wait stats. You can find the significance of most\u00a0common waits below it.
\nSolution:<\/strong> Knowing resource waits is the good way to start the performance tuning.Following is the good script i got from SQL skills<\/a>.<\/h2>\n

WITH<\/span>\u00a0[Waits]<\/span>
\nAS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0[wait_type]<\/span>,<\/span>
\n[wait_time_ms]<\/span>\u00a0\/<\/span>\u00a01000.0<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0[WaitS]<\/span>,<\/span>
\n(<\/span>\u00a0[wait_time_ms]<\/span>\u00a0–<\/span>\u00a0[signal_wait_time_ms]<\/span>\u00a0)<\/span>\u00a0\/<\/span>\u00a01000.0<\/span>\u00a0AS<\/span>
\n[ResourceS]<\/span>,<\/span>
\n[signal_wait_time_ms]<\/span>\u00a0\/<\/span>\u00a01000.0<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0[SignalS]<\/span>
\n,<\/span>
\n[waiting_tasks_count]<\/span>
\nAS<\/span>\u00a0[WaitCount]<\/span>,<\/span>
\n100.0<\/span>\u00a0*<\/span>\u00a0[wait_time_ms]<\/span>\u00a0\/<\/span>\u00a0Sum<\/i><\/span>\u00a0(<\/span>[wait_time_ms]<\/span>)<\/span>
\nOVER<\/span>(<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>
\n[Percentage]<\/span>,<\/span>
\nROW_NUMBER<\/b><\/span>(<\/span>)<\/span>
\nOVER<\/span>(<\/span>
\nORDER<\/span>\u00a0BY<\/span>\u00a0[wait_time_ms]<\/span>\u00a0DESC<\/span>)<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0[RowNum]<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_os_wait_stats<\/span>
\nWHERE<\/span>\u00a0\u00a0[wait_type]<\/span>\u00a0NOT<\/span>\u00a0IN<\/span>\u00a0(<\/span>
\nN’BROKER_EVENTHANDLER’<\/span>,<\/span>\u00a0N’BROKER_RECEIVE_WAITFOR’<\/span>,<\/span>
\nN’BROKER_TASK_STOP’<\/span>,<\/span>
\nN’BROKER_TO_FLUSH’<\/span>,<\/span>
\nN’BROKER_TRANSMITTER’<\/span>,<\/span>\u00a0N’CHECKPOINT_QUEUE’<\/span>,<\/span>
\nN’CHKPT’<\/span>,<\/span>
\nN’CLR_AUTO_EVENT’<\/span>,<\/span>
\nN’CLR_MANUAL_EVENT’<\/span>,<\/span>\u00a0N’CLR_SEMAPHORE’<\/span>,<\/span>
\n—\u00a0Maybe\u00a0uncomment\u00a0these\u00a0four\u00a0if\u00a0you\u00a0have\u00a0mirroring\u00a0issues<\/i><\/span>
\nN’DBMIRROR_DBM_EVENT’<\/span>,<\/span>\u00a0N’DBMIRROR_EVENTS_QUEUE’<\/span>,<\/span>
\nN’DBMIRROR_WORKER_QUEUE’<\/span>,<\/span>\u00a0N’DBMIRRORING_CMD’<\/span>,<\/span>
\nN’DIRTY_PAGE_POLL’<\/span>,<\/span>
\nN’DISPATCHER_QUEUE_SEMAPHORE’<\/span>,<\/span>
\nN’EXECSYNC’<\/span>,<\/span>\u00a0N’FSAGENT’<\/span>,<\/span>
\nN’FT_IFTS_SCHEDULER_IDLE_WAIT’<\/span>,<\/span>
\nN’FT_IFTSHC_MUTEX’<\/span>,<\/span>
\n—\u00a0Maybe\u00a0uncomment\u00a0these\u00a0six\u00a0if\u00a0you\u00a0have\u00a0AG\u00a0issues<\/i><\/span>
\nN’HADR_CLUSAPI_CALL’<\/span>,<\/span><\/p>\n

,<\/span>
\nN’HADR_LOGCAPTURE_WAIT’<\/span>,<\/span>
\nN’HADR_NOTIFICATION_DEQUEUE’<\/span>,<\/span>
\nN’HADR_TIMER_TASK’<\/span>,<\/span>\u00a0N’HADR_WORK_QUEUE’<\/span>,<\/span>
\nN’KSOURCE_WAKEUP’<\/span>,<\/span>
\nN’LAZYWRITER_SLEEP’<\/span>
\n,<\/span>
\nN’LOGMGR_QUEUE’<\/span>,<\/span>\u00a0N’MEMORY_ALLOCATION_EXT’<\/span>,<\/span>
\nN’ONDEMAND_TASK_QUEUE’<\/span>,<\/span>
\nN’PREEMPTIVE_XE_GETTARGETSTATE’<\/span>,<\/span>
\nN’PWAIT_ALL_COMPONENTS_INITIALIZED’<\/span>,<\/span>
\nN’PWAIT_DIRECTLOGCONSUMER_GETNEXT’<\/span>,<\/span>
\nN’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’<\/span>
\n,<\/span>
\nN’QDS_ASYNC_QUEUE’<\/span>,<\/span>
\nN’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’<\/span>,<\/span>
\nN’QDS_SHUTDOWN_QUEUE’<\/span>,<\/span>
\nN’REDO_THREAD_PENDING_WORK’<\/span>,<\/span>
\nN’REQUEST_FOR_DEADLOCK_SEARCH’<\/span>,<\/span>
\nN’RESOURCE_QUEUE’<\/span>,<\/span>\u00a0N’SERVER_IDLE_CHECK’<\/span>,<\/span>
\nN’SLEEP_BPOOL_FLUSH’<\/span>,<\/span>
\nN’SLEEP_DBSTARTUP’<\/span>,<\/span>
\nN’SLEEP_DCOMSTARTUP’<\/span>,<\/span>\u00a0N’SLEEP_MASTERDBREADY’<\/span>,<\/span>
\nN’SLEEP_MASTERMDREADY’<\/span>,<\/span>
\nN’SLEEP_MASTERUPGRADED’<\/span>,<\/span>
\nN’SLEEP_MSDBSTARTUP’<\/span>,<\/span>\u00a0N’SLEEP_SYSTEMTASK’<\/span>,<\/span>
\nN’SLEEP_TASK’<\/span>,<\/span>
\nN’SLEEP_TEMPDBSTARTUP’<\/span>,<\/span>
\nN’SNI_HTTP_ACCEPT’<\/span>,<\/span>\u00a0N’SP_SERVER_DIAGNOSTICS_SLEEP’<\/span>,<\/span>
\nN’SQLTRACE_BUFFER_FLUSH’<\/span>,<\/span>
\nN’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’<\/span>,<\/span>
\nN’SQLTRACE_WAIT_ENTRIES’<\/span>,<\/span>\u00a0N’WAIT_FOR_RESULTS’<\/span>,<\/span>
\nN’WAITFOR’<\/span>,<\/span>
\nN’WAITFOR_TASKSHUTDOWN’<\/span>,<\/span>
\nN’WAIT_XTP_RECOVERY’<\/span>,<\/span>\u00a0N’WAIT_XTP_HOST_WAIT’<\/span>,<\/span>
\nN’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’<\/span>,<\/span>
\nN’WAIT_XTP_CKPT_CLOSE’<\/span>,<\/span>
\nN’XE_DISPATCHER_JOIN’<\/span>,<\/span>\u00a0N’XE_DISPATCHER_WAIT’<\/span>,<\/span>
\nN’XE_TIMER_EVENT’<\/span>\u00a0)<\/span>
\nAND<\/span>\u00a0[waiting_tasks_count]<\/span>\u00a0><\/span>\u00a00<\/span>)<\/span>
\nSELECT<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[wait_type]<\/span>)<\/span>
\nAS<\/span>
\n[WaitType]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitS]<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>
\nAS<\/span>\u00a0[Wait_S]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[ResourceS]<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>
\nAS<\/span>\u00a0[Resource_S]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[SignalS]<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>
\nAS<\/span>\u00a0[Signal_S]<\/span>,<\/span>
\nMax<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitCount]<\/span>)<\/span>
\nAS<\/span>\u00a0[WaitCount]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[Percentage]<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>\u00a0(<\/span>5<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>
\nAS<\/span>\u00a0[Percentage]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>(<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitS]<\/span>)<\/span>\u00a0\/<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitCount]<\/span>)<\/span>\u00a0)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a04<\/span>)<\/span>)<\/span>
\nAS<\/span>
\n[AvgWait_S]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>(<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[ResourceS]<\/span>)<\/span>\u00a0\/<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitCount]<\/span>)<\/span>\u00a0)<\/span>\u00a0AS<\/span>
\nDECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a04<\/span>)<\/span>)<\/span>\u00a0AS<\/span>
\n[AvgRes_S]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>(<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[SignalS]<\/span>)<\/span>\u00a0\/<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[WaitCount]<\/span>)<\/span>\u00a0)<\/span>\u00a0AS<\/span>
\nDECIMAL<\/i><\/span>\u00a0(<\/span>16<\/span>,<\/span>\u00a04<\/span>)<\/span>)<\/span>\u00a0\u00a0\u00a0AS<\/span>
\n[AvgSig_S]<\/span>,<\/span>
\nCast<\/i><\/span>\u00a0(<\/span>‘https:\/\/www.sqlskills.com\/help\/waits\/’<\/span>
\n+<\/span>\u00a0Max<\/i><\/span>\u00a0(<\/span>[W1]<\/span>.<\/span>[wait_type]<\/span>)<\/span>\u00a0AS<\/span>\u00a0XML<\/i><\/span>)<\/span>
\nAS<\/span>\u00a0[Help\/Info\u00a0URL]<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0[Waits]<\/span>\u00a0AS<\/span>\u00a0[W1]<\/span>
\nINNER<\/span>\u00a0JOIN<\/span>\u00a0[Waits]<\/span>\u00a0AS<\/span>\u00a0[W2]<\/span>
\nON<\/span>\u00a0[W2]<\/span>.<\/span>[RowNum]<\/span>\u00a0<=<\/span>\u00a0[W1]<\/span>.<\/span>[RowNum]<\/span>
\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0[W1]<\/span>.<\/span>[RowNum]<\/span>
\nHAVING<\/span>\u00a0Sum<\/i><\/span>\u00a0(<\/span>[W2]<\/span>.<\/span>[Percentage]<\/span>)<\/span>\u00a0–<\/span>\u00a0Max<\/i><\/span>(<\/span>[W1]<\/span>.<\/span>[Percentage]<\/span>)<\/span>\u00a0<<\/span>\u00a095<\/span>;<\/span>\u00a0—\u00a0percentage\u00a0threshold<\/i><\/span><\/p>\n

GO<\/span><\/p><\/blockquote>\n

<\/strong>Common Significant Wait types explanations are as:<\/u><\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Sno.<\/strong><\/td>\nWait Type<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
1.<\/td>\nLCK_M_IX<\/td>\nOccurs when a task is waiting to acquire an Intent Exclusive (IX) lock<\/td>\n<\/tr>\n
2.<\/td>\nLCK_M_IU<\/td>\nOccurs when a task is waiting to acquire an Intent Update (IU) lock<\/td>\n<\/tr>\n
3.<\/td>\nLCK_M_S<\/td>\nOccurs when a task is waiting to acquire a Shared lock<\/td>\n<\/tr>\n
4.<\/td>\nASYNC_NETWORK_IO<\/td>\nOccurs on network writes when the task is blocked behind the network<\/td>\n<\/tr>\n
5.<\/td>\nASYNC_IO_COMPLETION<\/td>\nOccurs when a task is waiting for I\/Os to finish<\/td>\n<\/tr>\n
6.<\/td>\n <\/p>\n

IO_COMPLETION<\/td>\n

Occurs while waiting for I\/O operations to complete.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 This wait type generally represents non-data page I\/Os. Data page I\/O completion waits appear<\/td>\n<\/tr>\n
7.<\/td>\nPAGEIOLATCH_SH<\/td>\nOccurs when a task is waiting on a latch for a buffer that is in an I\/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.<\/td>\n<\/tr>\n
8.<\/td>\n <\/p>\n

 <\/p>\n

PAGEIOLATCH_EX<\/p>\n

 <\/td>\n

Occurs when a task is waiting on a latch for a buffer that is in an I\/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.<\/p>\n

 <\/td>\n<\/tr>\n

9.<\/td>\nWRITELOG<\/p>\n

 <\/td>\n

Occurs while waiting for a log flush to complete.Common operations that cause log flushes are checkpoints and transaction commits.<\/td>\n<\/tr>\n
10.<\/td>\nPAGELATCH_EX<\/p>\n

 <\/td>\n

Occurs when a task is waiting on a latch for a buffer that is not in an I\/O request. The latch request is in Exclusive mode.<\/td>\n<\/tr>\n
11.<\/td>\nBACKUPIO<\/p>\n

 <\/td>\n

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data<\/td>\n<\/tr>\n
12.<\/td>\nSOS_SCHEDULER_YIELD<\/p>\n

 <\/td>\n

Occurs when a task voluntarily yields the scheduler for other tasks to execute.\u00a0 During this wait the task is waiting for its quantum to be renewed.<\/td>\n<\/tr>\n
13.<\/td>\nTHREADPOOL<\/p>\n

 <\/td>\n

Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking\u00a0 unusually long, thus reducing the number of workers available to satisfy other batches.<\/td>\n<\/tr>\n
14.<\/td>\nCX_PACKET<\/p>\n

 <\/td>\n

Occurs when trying to synchronize the query processor exchange iterator<\/td>\n<\/tr>\n
15.<\/td>\nONDEMAND_TASK_QUEUE<\/p>\n

 <\/td>\n

Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

Problem Statement: Script to get the SQL Server\u00a0wait stats. You can find the significance of most\u00a0common waits below it. Solution:[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[23],"tags":[75,141,192,193],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1003"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1003"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1003\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}