Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server–pomoc przy statystykach oczekiwań

O statystykach oczekiwań w SQL Server powiedziano i napisano już chyba wszystko. Były dobre, acz dzisiaj już leciwe oficjalne dokumenty Microsoft, były ciekawe prezentacje wielu znakomitych prelegentów (że wspomnę sesję Boba Warda z SQLDay 2014 z tych sesji, które było mi dane obejrzeć). Generalnie, od dłuższego czasu polecam wszystkim administratorom i osobom zajmującym się optymalizacją baz danych SQL Server korzystanie z dobrodziejstwa w postaci widoku sys.dm_os_wait_stats do śledzenia, na co oczekuje instancja SQL Server i procesy w niej działające.

Niedawno, jeden z najbardziej rozpoznawalnych na świecie ekspertów w zakresie wiedzy na temat SQL Server, Paul S. Randal, pokusił się o sporządzenie bardzo solidnego repozytorium wiedzy na temat stanów oczekiwań. Opisał on każdy z typów oczekiwań spotykanych w SQL Server, podając najczęstsze przyczyny powstawania każdego z typów oczekiwań, a nawet podając stosy wywołań (to już dla master-geeków a la Bob Ward, ale fajnie, że można na takie rzeczy popatrzeć bez grzebania samemu w debuggerze :-)). Całe repozytorium Paula jest do znalezienia pod adresem: https://www.sqlskills.com/help/waits.

Pomyślałem, że często zdarza mi się, że zapominam, co oznaczają poszczególne typy oczekiwań i że dobrze byłoby w jakiś sposób sprzęgnąć typowe zapytanie, którym się posługuję oglądając statystyki oczekiwań serwerów, z repozytorium Paula. I tak powstała mała modyfikacja zapytania diagnostycznego autorstwa Glenna Barry’ego (tu wersja dla SQL Server 2016):

WITH [Waits] 
AS (SELECT wait_type, wait_time_ms/ 1000.0 AS [WaitS],
          (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [ResourceS],
           signal_wait_time_ms / 1000.0 AS [SignalS],
           waiting_tasks_count AS [WaitCount],
           100.0 *  wait_time_ms / SUM (wait_time_ms) OVER() AS [Percentage],
           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    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_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'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
		N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
		N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
		N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
		N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
		N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 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'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
		N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
		N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
		N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
    AND waiting_tasks_count > 0)
SELECT
    MAX (W1.wait_type) AS [WaitType],
    CAST (MAX (W1.WaitS) AS DECIMAL (16,2)) AS [Wait_Sec],
    CAST (MAX (W1.ResourceS) AS DECIMAL (16,2)) AS [Resource_Sec],
    CAST (MAX (W1.SignalS) AS DECIMAL (16,2)) AS [Signal_Sec],
    MAX (W1.WaitCount) AS [Wait Count],
    CAST (MAX (W1.Percentage) AS DECIMAL (5,2)) AS [Wait Percentage],
    CAST ((MAX (W1.WaitS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgWait_Sec],
    CAST ((MAX (W1.ResourceS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgRes_Sec],
    CAST ((MAX (W1.SignalS) / MAX (W1.WaitCount)) AS DECIMAL (16,4)) AS [AvgSig_Sec],
    --Dodajmy link do pomocy - opisu typu oczekiwania wg Paula S. Randala
    CONVERT(xml, 'https://www.sqlskills.com/help/waits/' + LOWER(MAX(W1.wait_type)) + '/') AS [Wait URL]
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum
HAVING SUM (W2.Percentage) - MAX (W1.Percentage) < 99 -- percentage threshold
OPTION (RECOMPILE);

Dla osób, które powyższy kod widzą po raz pierwszy – to zapytanie ma na celu pokazanie tych typów oczekiwań, które zajęły instancji SQL Server najwięcej czasu (dokładnie pokazanie tych, które w sumie zajęły 99% czasu całkowitego oczekiwania systemu z sortowaniem wg najdłuższych czasów oczekiwania). Przykładowy wynik wykonania tego zapytania:

image

Na czerwono zaznaczona dodatkowa kolumna z… linkiem do odpowiedniej strony w repozytorium Paula :-) Niestety, potrzeba aż dwóch kliknięć z tego miejsca (w tym jedno z przytrzymanym Ctrl), żeby przejść do strony opisu typu oczekiwania. Ale lepsze to, niż nic :-) Nie znam sposobu (i chyba go nie ma), żeby przejść do adresu wskazywanego przez URL bezpośrednio z okienka wyniku w SSMS.

I tak, jeśli tylko masz dostęp do sieci, nie musisz uczyć się na pamięć typów oczekiwań. Możesz skorzystać z nieoficjalnej dokumentacji :-) A czytając statystyki serwerów i przeglądając repozytorium Paula możesz się sporo nauczyć. Tak, jak i ja się uczę.

Warto nadmienić, że Paul sporządził też repozytorium opisujące rodzaje latchy: https://www.sqlskills.com/help/latches/. Ale to już temat na inny wpis. Na dzisiaj tyle. Dobrego monitorowania serwerów!

5 thoughts on “SQL Server–pomoc przy statystykach oczekiwań

    1. Zatrzask? :-) Ja nie tłumaczę. Ważniejsze jest chyba rozumienie, jak ten mechanizm działa ;-)

  1. W tej branży nie powinno się tłumaczyć, to tylko generuje kłopoty i nieporozumienia :)

      1. Cześć, Pawle! Kopę lat :-) Każdy z nas choć raz w życiu miał taką prezentację ;-)

Leave a Reply