🥇Niektóre aspekty monitorowania MS SQL Server. Rekomendacje dotyczące konfiguracji flag śledzenia

Wstęp

Użytkownicy, programiści i administratorzy baz danych MS SQL Server często napotykają problemy z wydajnością baz danych lub samego systemu zarządzania bazą danych, dlatego monitoring MS SQL Server jest bardzo istotny.
Niniejszy artykuł jest uzupełnieniem artykułu Zastosowanie Zabbix do monitorowania bazy danych MS SQL Server i omawia niektóre aspekty monitoringu MS SQL Server, w szczególności: jak szybko zidentyfikować brakujące zasoby oraz zalecenia dotyczące konfiguracji flag śledzenia.
Aby skorzystać z poniższych skryptów, należy utworzyć schemat inf w odpowiedniej bazie danych w następujący sposób:
Tworzenie schematu inf

use ;
go
create schema inf;

Metoda wykrywania niedoboru pamięci RAM

Pierwszym wskaźnikiem braku pamięci RAM jest sytuacja, gdy instancja MS SQL Server wykorzystuje całą przypisaną jej pamięć RAM.
Aby to zrobić, utworzymy następujący widok inf.vRAM:
Tworzenie widoku inf.vRAM

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]                  -- ile pamięci RAM jest wolne na serwerze w MB
		 , a.[RAM_Avail_Percent]                    -- procent wolnej pamięci RAM na serwerze
		 , a.[Server_physical_memory_Mb]            -- całkowita pamięć RAM na serwerze w MB
		 , a.[SQL_server_committed_target_Mb]        -- całkowita pamięć RAM przydzielona dla MS SQL Server w MB
		 , a.[SQL_server_physical_memory_in_use_Mb] -- całkowita pamięć RAM, którą wykorzystuje MS SQL Server w danym momencie w MB
		 , a.[SQL_RAM_Avail_Percent]                -- procent wolnej pamięci RAM dla MS SQL Server w odniesieniu do całkowitej przydzielonej pamięci RAM
		 , a.[StateMemorySQL]                        -- czy pamięci RAM wystarcza dla MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]              -- procent przydzielonej pamięci RAM dla MS SQL Server w odniesieniu do całkowitej pamięci RAM na serwerze
		 -- czy pamięci RAM wystarcza dla serwera
		, (case when a.[RAM_Avail_Percent]5 and a.[TotalAvailOSRam_Mb]<8192 then 'Warning' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Danger' else 'Normal' end) as [StateMemoryServer]
	from
	(
		select cast(a0.available_physical_memory_kb/1024.0 as int) as TotalAvailOSRam_Mb
			 , cast((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [RAM_Avail_Percent]
			 , a0.system_low_memory_signal_state
			 , ceiling(b.physical_memory_kb/1024.0) as [Server_physical_memory_Mb]
			 , ceiling(b.committed_target_kb/1024.0) as [SQL_server_committed_target_Mb]
			 , ceiling(a.physical_memory_in_use_kb/1024.0) as [SQL_server_physical_memory_in_use_Mb]
			 , cast(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Avail_Percent]
			 , cast((b.committed_target_kb/casT(a0.total_physical_memory_kb as float))*100 as numeric(5,2)) as [SQL_RAM_Reserve_Percent]
			 , (case when (ceiling(b.committed_target_kb/1024.0)-1024)<ceiling(a.physical_memory_in_use_kb/1024.0) then 'Warning' else 'Normal' end) as [StateMemorySQL]
		from sys.dm_os_sys_memory as a0
		cross join sys.dm_os_process_memory as a
		cross join sys.dm_os_sys_info as b
		cross join sys.dm_os_sys_memory as v
	) as a;

Aby ustalić, że instancja MS SQL Server wykorzystuje całą przydzieloną pamięć, można użyć następującego zapytania:

select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];

Jeśli wskaźnik SQL_server_physical_memory_in_use_Mb nieustannie jest mniejszy od SQL_server_committed_target_Mb, należy sprawdzić statystyki oczekiwań.
Aby określić niedobór pamięci operacyjnej na podstawie statystyk oczekiwań, utworzymy widok inf.vWaits:
Utworzenie widoku inf.vWaits

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --nazwa typu oczekiwania
        [wait_time_ms] / 1000.0 AS [WaitS],--Całkowity czas oczekiwania dla tego typu w milisekundach. Czas ten obejmuje signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Całkowity czas oczekiwania dla tego typu w milisekundach bez signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Różnica między czasem sygnalizacji oczekującego wątku a czasem rozpoczęcia jego wykonania
        [waiting_tasks_count] AS [WaitCount],--Liczba oczekiwań dla tego typu. Ten licznik zwiększa się za każdym razem, gdy rozpoczyna się oczekiwanie
        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 [waiting_tasks_count]>0
		and [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'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        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_TIMER_EVENT')
    )
, ress as (
	SELECT
	    [W1].[wait_type] AS [WaitType],
	    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],--Całkowity czas oczekiwania dla tego typu w milisekundach. Czas ten obejmuje signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Całkowity czas oczekiwania dla tego typu w milisekundach bez signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Różnica między czasem sygnalizacji oczekującego wątku a czasem rozpoczęcia jego wykonania
	    [W1].[WaitCount] AS [WaitCount],--Liczba oczekiwań dla tego typu. Ten licznik zwiększa się za każdym razem, gdy rozpoczyna się oczekiwanie
	    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
	    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
	    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
	    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 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 -- próg procentowy
)
SELECT [WaitType]
      ,MAX([Wait_S]) as [Wait_S]
      ,MAX([Resource_S]) as [Resource_S]
      ,MAX([Signal_S]) as [Signal_S]
      ,MAX([WaitCount]) as [WaitCount]
      ,MAX([Percentage]) as [Percentage]
      ,MAX([AvgWait_S]) as [AvgWait_S]
      ,MAX([AvgRes_S]) as [AvgRes_S]
      ,MAX([AvgSig_S]) as [AvgSig_S]
  FROM ress
  group by [WaitType];

W takim przypadku można określić niedobór pamięci operacyjnej przy użyciu następującego zapytania:

SELECT [Percentage]
      ,[AvgWait_S]
  FROM [inf].[vWaits]
  WHERE [WaitType] IN (
    'PAGEIOLATCH_XX',
    'RESOURCE_SEMAPHORE',
    'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Należy zwrócić uwagę na wskaźniki Percentage i AvgWait_S. Jeśli są znaczne, to istnieje duża szansa, że instancja MS SQL Server ma zbyt mało pamięci operacyjnej. Znaczenie tych wskaźników ustala się indywidualnie dla każdego systemu. Można jednak zacząć od następującego wskaźnika: Percentage >= 1 oraz AvgWait_S >= 0.005.
Aby wyświetlić wskaźniki w systemie monitorowania (np. Zabbix), można stworzyć następujące dwa zapytania:

jaką część procentową zajmują typy oczekiwań dotyczące pamięci operacyjnej (suma wszystkich takich typów oczekiwań):

SELECT COALESCE(SUM([Percentage]), 0.00) AS [Percentage]
FROM [inf].[vWaits]
       WHERE [WaitType] IN (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

jaką część milisekund zajmują typy oczekiwań dotyczące pamięci operacyjnej (maksymalna wartość ze wszystkich średnich opóźnień dla tych typów oczekiwań):

SELECT COALESCE(MAX([AvgWait_S]) * 1000, 0.00) AS [AvgWait_MS]
FROM [inf].[vWaits]
       WHERE [WaitType] IN (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Na podstawie dynamiki uzyskanych wartości dla tych dwóch wskaźników można ocenić, czy pamięć operacyjna jest wystarczająca dla instancji MS SQL Server.

Metoda wykrywania nadmiernego obciążenia CPU.

Aby zidentyfikować niedobór czasu procesora, wystarczy skorzystać z widoku systemowego sys.dm_os_schedulers. Jeśli wskaźnik runnable_tasks_count przez cały czas jest większy niż 1, to istnieje duża szansa, że liczba rdzeni jest niewystarczająca dla instancji MS SQL Server.
Aby wyświetlić wskaźnik w systemie monitorowania (np. Zabbix), można stworzyć następujące zapytanie:

SELECT MAX([runnable_tasks_count]) AS [runnable_tasks_count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

Na podstawie dynamiki uzyskanych wartości dla tego wskaźnika można ocenić, czy czas procesora (liczba rdzeni CPU) jest wystarczający dla instancji MS SQL Server.
Jednak ważne jest, aby pamiętać, że same zapytania mogą wymagać jednocześnie kilku wątków. Czasami optymalizator nie potrafi właściwie ocenić złożoności zapytania. W takim przypadku może zostać przypisanych zbyt wiele wątków, które w danym momencie nie mogą być przetworzone jednocześnie. To również powoduje rodzaj oczekiwania, związany z brakiem czasu procesora i rozrostu kolejki w planistach, które korzystają z konkretnych rdzeni CPU, tzn. wskaźnik runnable_tasks_count w takich warunkach będzie rósł.
W takim przypadku, przed zwiększeniem liczby rdzeni CPU, należy prawidłowo skonfigurować właściwości równoległości samego przykładu MS SQL Server, a od wersji 2016 - prawidłowo skonfigurować właściwości równoległości potrzebnych baz danych:

Warto zwrócić uwagę na następujące parametry:

Max Degree of Parallelism - ustala maksymalną liczbę wątków, które mogą być przypisane do każdego zapytania (domyślnie wynosi 0 - ograniczenie nałożone tylko przez system operacyjny i edycję MS SQL Server)
Cost Threshold for Parallelism - szacunkowy koszt równoległości (domyślnie wynosi 5)
Max DOP - ustala maksymalną liczbę wątków, które mogą być przypisane do każdego zapytania na poziomie bazy danych (ale nie więcej niż wartość właściwości „Max Degree of Parallelism”) (domyślnie wynosi 0 - ograniczenie nałożone tylko przez system operacyjny i edycję MS SQL Server, a także ograniczenie wynikające z właściwości „Max Degree of Parallelism” całego przykładu MS SQL Server)

Nie można podać jednolitego, dobrego przepisu na wszystkie przypadki, tzn. należy analizować złożone zapytania.
Na podstawie własnego doświadczenia zalecam następujący algorytm działań dla systemów OLTP w celu konfigurowania właściwości równoległości:

najpierw zakazać równoległości, ustawiając na poziomie całego przykładu Max Degree of Parallelism na 1
przeanalizować najcięższe zapytania i dobrać dla nich optymalną liczbę wątków
ustawić Max Degree of Parallelism na dobraną optymalną liczbę wątków otrzymaną z pkt. 2, a także dla konkretnych baz danych ustawić wartość Max DOP uzyskaną z pkt. 2 dla każdej bazy danych
przeanalizować najcięższe zapytania i zidentyfikować negatywny wpływ wielowątkowości. Jeśli występuje, to zwiększyć Cost Threshold for Parallelism.
Dla takich systemów jak 1C, Microsoft CRM i Microsoft NAV w większości przypadków wystarczy zakazanie wielowątkowości.

Jeśli używasz edycji Standard, w większości przypadków będzie odpowiedni zakaz wielowątkowości z uwagi na ograniczenie tej edycji dotyczące liczby rdzeni CPU.
Opisany wyżej algorytm nie nadaje się dla systemów OLAP.
Na podstawie własnego doświadczenia polecam następujący algorytm działania dla systemów OLAP w celu dostosowania właściwości współbieżności:

przeanalizować najcięższe zapytania i dobrać dla nich optymalną liczbę wątków
ustawić Max Degree of Parallelism na optymalną liczbę wątków, uzyskaną z pkt. 1, a także dla konkretnych baz danych ustawić wartość Max DOP uzyskane z pkt. 1 dla każdej bazy danych,
analizować najcięższe zapytania i zidentyfikować negatywny wpływ ograniczenia współbieżności. Jeśli występuje, należy albo obniżyć wartość Cost Threshold for Parallelism, albo powtórzyć kroki 1-2 tego algorytmu.

Dla systemów OLTP przechodzimy od jednowątkowości do wielowątkowości, a dla systemów OLAP odwrotnie - od wielowątkowości do jednowątkowości. W ten sposób można znaleźć optymalne ustawienia współbieżności zarówno dla konkretnej bazy danych, jak i całego instancji MS SQL Server.
Ważne jest również, aby z czasem zmieniać ustawienia właściwości współbieżności, na podstawie wyników monitorowania wydajności MS SQL Server.

Zalecenia dotyczące ustawiania flag śledzenia

Na podstawie własnego doświadczenia i doświadczeń moich kolegów polecam ustawienie następujących flag śledzenia na poziomie uruchamiania usługi MS SQL Server dla wersji 2008-2016:

610 — Zmniejszenie rejestrowania wstawień w tabelach indeksowanych. Może pomóc w wstawieniach do tabel z dużą liczbą rekordów i wieloma transakcjami, w przypadku częstych długich oczekiwań WRITELOG związanych ze zmianami w indeksach.
1117 — Jeśli plik w grupie plików spełnia wymagania progu automatycznego zwiększenia, wszystkie pliki w grupie plików są zwiększane.
1118 — Zapewnia, że wszystkie obiekty znajdują się w różnych extentach (zakaz mieszanych extentów), co minimalizuje potrzebę skanowania strony SGAM, która jest używana do śledzenia mieszanych extentów.
1224 — Dezaktywuje agregację blokad na podstawie liczby blokad. Jednak zbyt aktywne wykorzystanie pamięci może spowodować włączenie agregacji blokad.
2371 — Zmienia próg statycznej automatycznej aktualizacji statystyk na próg dynamicznej automatycznej aktualizacji statystyk. Ważne dla aktualizacji planów zapytań dotyczących dużych tabel, w których błędne określenie liczby wierszy prowadzi do błędnych planów wykonania.
3226 — Tłumi komunikaty o pomyślnym zakończeniu tworzenia kopii zapasowej w dzienniku błędów.
4199 — Włącza zmiany w optymalizatorze zapytań, wydane w pakietach zbiorczych aktualizacji oraz pakietach aktualizacji SQL Server.
6532-6534 — Włącza poprawę wydajności operacji zapytań z przestrzennymi typami danych.
8048 — Przekształca obiekty pamięci, podzielone na NUMA, na podzielone według CPU.
8780 — Włącza dodatkowe przydzielanie czasu na kompilację planu zapytania. Niektóre zapytania bez tej flagi mogą zostać odrzucone, ponieważ nie mają planu zapytania (bardzo rzadki błąd).
8780 — 9389 — Włącza dodatkowy dynamicznie przydzielany bufor pamięci dla operatorów trybu wsadowego, co umożliwia operatorowi trybu wsadowego zażądanie dodatkowej pamięci i uniknięcie przenoszenia danych do tempdb, jeśli dodatkowa pamięć jest dostępna.

Także do wersji 2016 warto włączyć flagę śledzenia 2301, która włącza optymalizację rozszerzonego wsparcia podejmowania decyzji, a tym samym pomaga w wyborze bardziej odpowiednich planów zapytań. Jednak zaczynając od wersji 2016, często wywiera ona negatywny wpływ na całkowity czas wykonywania zapytań.
Dla systemów, w których jest bardzo wiele indeksów (na przykład dla baz danych 1C), zalecam również włączenie flagi śledzenia 2330, która wyłącza zbieranie danych o użyciu indeksów, co w ogólności pozytywnie wpływa na system.
Szczegółowe informacje na temat flag śledzenia można znaleźć. tutaj
Ważne jest również uwzględnienie wersji i kompilacji MS SQL Server wg powyższego linku, ponieważ w przypadku nowszych wersji niektóre flagi śledzenia są włączone domyślnie lub nie mają żadnego efektu.
Flagę śledzenia można włączyć i wyłączyć za pomocą poleceń DBCC TRACEON i DBCC TRACEOFF odpowiednio. Szczegóły znajdziesz w. tutaj
Stan flag śledzenia można uzyskać za pomocą polecenia DBCC TRACESTATUS: więcej informacji
Aby flagi śledzenia były włączone w autostarcie usługi MS SQL Server, należy przejść do Menedżera konfiguracji SQL Server i w właściwościach usługi dodać dane flagi śledzenia przez -T:

Podsumowanie

W tym artykule omówiono niektóre aspekty monitorowania MS SQL Server, dzięki którym można szybko zidentyfikować brak pamięci RAM i wolnego czasu CPU, a także szereg innych mniej oczywistych problemów. Rozpatrzono najczęściej używane flagi śledzenia.