Rozwiązywanie problemów SOS_SCHEDULER

Prowadząc naszą korporacyjną platformę ERP (Dynamics AX 2012) zauważyłem, że nasze środowisko produkcyjne wydawało się znacznie wolniejsze niż nasze systemy programistyczne.

Po wykonaniu tych samych czynności zarówno w środowisku programistycznym, jak i produkcyjnym podczas śledzenia, potwierdziłem, że zapytania SQL działały bardzo wolno w naszym środowisku produkcyjnym w porównaniu do programowania (średnio 10-50x wolniej).

Na początku przypisałem to do obciążenia i ponownie uruchomiłem te same działania w środowisku produkcyjnym w czasie wolnym od pracy i znalazłem te same wyniki w śladzie.

Wyczyściłem statystyki czekania w programie SQL Server, a następnie pozwoliłem serwerowi na działanie pod normalnym obciążeniem produkcyjnym przez chwilę, a następnie uruchomiłem to zapytanie:

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
    WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold

Moje wyniki są następujące:

WaitType               Wait_S  Resource_S  Signal_S  WaitCount  Percentage  AvgWait_S  AvgRes_S  AvgSig_S
SOS_SCHEDULER_YIELD   4162.52        3.64   4158.88    4450085       77.33     0.0009    0.0000    0.0009
ASYNC_NETWORK_IO       457.98      331.59    126.39     351113        8.51     0.0013    0.0009    0.0004
PAGELATCH_EX           252.94        5.14    247.80     796348        4.70     0.0003    0.0000    0.0003
WRITELOG               166.01       48.01    118.00     302209        3.08     0.0005    0.0002    0.0004
LCK_M_U                145.47      145.45      0.02        123        2.70     1.1827    1.1825    0.0002

Wydaje się, że jak dotąd największym Waitem jest SOS_Scheduler_Yield, a ja przeszukałem go i zauważyłem, że zazwyczaj dotyczy to niemożności nadążania za procesorem.

Następnie uruchomiłem to zapytanie wiele razy z rzędu.

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

Wiem, że powinienem szukać harmonogramów z niezerowym runnable_tasks_count lub pending_disk_io_count, ale w zasadzie prawie zero.

Powinienem również wspomnieć, że maksymalny stopień równoległości został ustawiony na 1, ponieważ obciążenie Dynamics AX ma zazwyczaj charakter OLTP, a zmiana go 8 nie zrobiła dużej różnicy w powyższych statystykach oczekiwania, stały się prawie identyczne z tym samym problemy z wydajnością.

Nie wiem, dokąd mam się udać, w zasadzie mam SQL Servera, który najwyraźniej jest obciążony procesorem, ale nie czeka na runnable_tasks lub IO.

Wiem, że podsystem IO tego programu SQL Server nie jest zbyt dobry, ponieważ uruchamianie SQLIO na dysku zawierającym rzeczywiste bazy danych może prowadzić do dość niskich liczb (pomyśl 10 MB na sekundę dla niektórych rodzajów odczytu / zapisu), co mówi: nie wydaje się, że SQL czeka na to z powodu ilości pamięci na serwerze, która buforuje większość baz danych.

Oto kilka informacji o środowisku, które mogą pomóc:

Środowisko produkcyjne:

SQL Server
HP ProLian DL360p Gen8
Intel Xeon E5-2650 0 @ 2.00GHz x 2 z hyperthreading (32 rdzenie logiczne)
184 GB pamięci
Windows Server 2012
2 wystąpienia SQL Server 2012 Standard (RTM, niezałatany)
Napęd Raid 1 279 GB (15 KB) Dysk C: zawiera bazy danych i system operacyjny
Plik strony i TempDB na odrębnych, oddzielnych dyskach (półprzewodnikowych)

Mój DEV:

Obsługiwany przez Hyper-V serwer SQL Server i serwer AOS Dynamics AX 2012
Core i7 3,4 GHz z hiperwątkiem (8 rdzeni logicznych)
8 GB pamięci
Windows Server 2008 R2
SSD dla całej maszyny wirtualnej.

Byłbym wdzięczny za wszelkie uwagi dotyczące innych rzeczy, których należy szukać.

— Nicholas Peterson
źródło

Więc rozwiązałem ten problem, okazuje się, że funkcje zarządzania energią zostały włączone na naszym serwerze SQL, które skalowały częstotliwość procesora w górę i w dół, ale nie były wystarczająco szybkie, aby nadążyć za małym popytem i wprowadziły SOS_Scheduler_Yield czekać. Po zmianie go na działanie zawsze z wysoką wydajnością problem zniknął, a teraz oczekiwania są bardziej normalne (rzeczy typu LatchIO).

— Nicholas Peterson
źródło

Rozwiązywanie problemów SOS_SCHEDULER_YIELD czekaj