🥇Některé aspekty monitorování MS SQL Serveru. Doporučení pro nastavení flagů sledování

Úvod

Uživatelé, vývojáři a administrátoři databází MS SQL Server se často setkávají s problémy s výkonem databáze nebo celého systému SGBD, proto je monitorování MS SQL Serveru velmi aktuální téma.
Tento článek je doplňkem k článku Použití Zabbixu pro sledování databáze MS SQL Server a bude se zabývat některými aspekty monitorování MS SQL Serveru, konkrétně: jak rychle zjistit, jaké zdroje chybí, a doporučení pro nastavení trasovacích flagů.
Aby bylo možné použít následující skripty, je nutné vytvořit schéma inf v požadované databázi následujícím způsobem:
Vytvoření schématu inf

use ;
go
create schema inf;

Metoda identifikace nedostatku operační paměti

Prvním indikátorem nedostatku operační paměti je situace, kdy instance MS SQL Serveru využívá veškerou přidělenou RAM.
Pro tento účel vytvoříme následující pohled inf.vRAM:
Vytvoření pohledu inf.vRAM

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]						--kolik je na serveru volné RAM v MB
		 , a.[RAM_Avail_Percent]					--procento volné RAM na serveru
		 , a.[Server_physical_memory_Mb]				--kolik má server celkem RAM v MB
		 , a.[SQL_server_committed_target_Mb]			--kolik celkem RAM bylo přiděleno pro MS SQL Server v MB
		 , a.[SQL_server_physical_memory_in_use_Mb] 		--kolik RAM v současné době využívá MS SQL Server v MB
		 , a.[SQL_RAM_Avail_Percent]				--procento volné RAM pro MS SQL Server vzhledem k celkově přidělené RAM pro MS SQL Server
		 , a.[StateMemorySQL]						--je RAM dostatečná pro MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]				--procento přidělené RAM pro MS SQL Server vzhledem k celkové RAM serveru
		 --je RAM dostatečná pro server
		, (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;

Pak definovat, že instance MS SQL Serveru spotřebovává veškerou jemu přidělenou paměť, lze pomocí následujícího dotazu:

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

Pokud je hodnota SQL_server_physical_memory_in_use_Mb trvale nižší než SQL_server_committed_target_Mb, je třeba zkontrolovat statistiky čekání.
Pro určení nedostatku operační paměti prostřednictvím statistik čekání vytvoříme pohled inf.vWaits:
Vytvoření pohledu inf.vWaits

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], -- název typu čekání
        [wait_time_ms] / 1000.0 AS [WaitS],-- Celkový čas čekání na tento typ v milisekundách. Tento čas zahrnuje signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],-- Celkový čas čekání na tento typ v milisekundách bez signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],-- Rozdíl mezi časem signalizace čekajícího vlákna a časem, kdy začalo jeho vykonávání
        [waiting_tasks_count] AS [WaitCount],-- Počet čekání na tento typ. Tento čítač se zvyšuje každýkrát, když začíná čekání
        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],-- Celkový čas čekání na tento typ v milisekundách. Tento čas zahrnuje signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],-- Celkový čas čekání na tento typ v milisekundách bez signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],-- Rozdíl mezi časem signalizace čekajícího vlákna a časem, kdy začalo jeho vykonávání
	    [W1].[WaitCount] AS [WaitCount],-- Počet čekání na tento typ. Tento čítač se zvyšuje každýkrát, když začíná čekání
	    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 -- procentní práh
)
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];

V tomto případě lze nedostatek paměti RAM určit následujícím dotazem:

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

Zde je potřeba věnovat pozornost ukazatelům Percentage a AvgWait_S. Pokud jsou jejich součty významné, existuje vysoká pravděpodobnost, že paměti RAM pro instanci MS SQL Server chybí. Významné hodnoty se určují individuálně pro každý systém. Nicméně lze začít s následujícím ukazatelem: Percentage>=1 a AvgWait_S>=0.005.
Pro výstup ukazatelů do monitorovacího systému (například Zabbix) lze vytvořit následující dva dotazy:

kolik procent činí typy čekání na paměti (součet všech těchto typů čekání):

select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

kolik milisekund činí typy čekání na paměti (maximální hodnota ze všech průměrných zpoždění všech těchto typů čekání):

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 základě dynamiky získaných hodnot těchto dvou ukazatelů lze usoudit, zda je paměti RAM pro instanci MS SQL Server dost.

Metoda identifikace nadměrné zátěže CPU

Pro identifikaci nedostatku procesorového času stačí využít systémový pohled sys.dm_os_schedulers. Zde, pokud je ukazatel runnable_tasks_count trvale vyšší než 1, existuje vysoká pravděpodobnost, že počet jader pro instanci MS SQL Server nestačí.
Pro výstup ukazatele do monitorovacího systému (například Zabbix) lze vytvořit následující dotaz:

select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;

Na základě dynamiky získaných hodnot tohoto ukazatele lze usoudit, zda je procesorového času (počet jader CPU) pro instanci MS SQL Server dost.
Je však důležité mít na paměti, že samotné dotazy mohou vyžadovat více proudů najednou. A často optimalizátor nemůže správně odhadnout složitost samotného dotazu. Poté může být dotazu přiděleno příliš mnoho proudů, které nelze v daném okamžiku zpracovat současně. To také vyvolává typ čekání spojený s nedostatkem procesorového času a narůstáním fronty na plánovače, které používají konkrétní jádra CPU, tedy ukazatel runnable_tasks_count v těchto podmínkách poroste.
V takovém případě je před zvýšením počtu jader CPU nutné správně nastavit vlastnosti paralelismu samotného instance MS SQL Serveru, a od verze 2016 také správně nastavit vlastnosti paralelismu potřebných databází:

Zde je třeba věnovat pozornost následujícím parametrům:

Max Degree of Parallelism - určuje maximální počet proudů, které mohou být přiděleny každému dotazu (výchozí hodnota je 0 - omezení pouze samotným operačním systémem a edicí MS SQL Serveru)
Cost Threshold for Parallelism - odhadovaná cena paralelismu (výchozí hodnota je 5)
Max DOP - určuje maximální počet proudů, které mohou být přiděleny každému dotazu na úrovni databáze (ale ne více než hodnota vlastnosti "Max Degree of Parallelism") (výchozí hodnota je 0 - omezení pouze samotným operačním systémem a edicí MS SQL Serveru, stejně jako omezení podle vlastnosti "Max Degree of Parallelism" celé instance MS SQL Serveru)

Zde nelze poskytnout stejný dobrý recept pro všechny případy, tedy je třeba analyzovat náročné dotazy.
Na základě vlastní zkušenosti doporučuji následující algoritmus akcí pro OLTP systémy k nastavení vlastností paralelismu:

nejprve zakázat paralelismus nastavením na úrovni celé instance Max Degree of Parallelism na 1
analyzovat nejnáročnější dotazy a zvolit pro ně optimální počet proudů
nastavit Max Degree of Parallelism na zvolené optimální množství proudů získaných z bodu 2 a také pro konkrétní databáze nastavit hodnotu Max DOP získanou z bodu 2 pro každou databázi
analyzovat nejnáročnější dotazy a zjistit negativní účinky vícestandardnosti. Pokud existuje, je třeba zvýšit Cost Threshold for Parallelism.
Pro takové systémy jako 1C, Microsoft CRM a Microsoft NAV ve většině případů postačí zakázání vícestandardnosti.

Pokud je nastavena edice Standard, v mnoha případech bude stačit zakázání paralelního zpracování vzhledem k tomu, že tato edice je omezena počtem jader CPU.
Pro OLAP systémy není výše popsaný algoritmus vhodný.
Na základě vlastních zkušeností doporučuji následující postup pro OLAP systémy při nastavování vlastností paralelismu:

analyzovat nejnáročnější dotazy a zvolit pro ně optimální počet proudů
nastavit Max Degree of Parallelism na optimální počet vláken z bodu 1, a také pro konkrétní databáze nastavit hodnotu Max DOP podle bodu 1 pro každou databázi
analyzovat nejtěžší dotazy a identifikovat negativní efekt omezení paralelismu. Pokud existuje, buď snížit hodnotu Cost Threshold for Parallelism, nebo zopakovat kroky 1-2 této procedury

Tedy pro OLTP systémy přecházíme od jednovláknového zpracování k vícevláknovému, zatímco pro OLAP systémy je to naopak – přecházíme od vícevláknového k jednovláknovému zpracování. Tímto způsobem lze vybrat optimální nastavení paralelismu jak pro konkrétní databázi, tak pro celý instance MS SQL Server.
Je také důležité chápat, že nastavení vlastností paralelismu je třeba časem měnit na základě výsledků monitorování výkonu MS SQL Server.

Doporučení pro nastavení sledovacích příznaků

Na základě vlastních zkušeností a zkušeností mých kolegů doporučuji nastavit při spuštění služby MS SQL Server pro verze 2008-2016 následující sledovací příznaky:

610 — Snižuje protokolování vložení do indexovaných tabulek. Může pomoci s vkládáním do tabulek s velkým počtem záznamů a četnými transakcemi, při častých dlouhých čekáních WRITELOG na změny v indexech
1117 — Pokud soubor ve skupině souborů splňuje požadavky prahu automatického zvyšování, všechny soubory ve skupině souborů se zvýší
1118 — Donutí všechny objekty umístit do různých extentů (zákaz smíšených extentů), což minimalizuje potřebu skenování stránky SGAM, která se používá k sledování smíšených extentů
1224 — Zakazuje zvětšování zámků na základě počtu zámků. Příliš aktivní používání paměti však může zapnout zvětšování zámků
2371 — Mění prahovou hodnotu pevného automatického aktualizování statistik na prahovou hodnotu dynamického automatického aktualizování statistik. Důležité pro aktualizaci plánů dotazů týkajících se velkých tabulek, kde nesprávné určení počtu záznamů vede k chybovým plánům provádění.
3226 — Potlačuje zprávy o úspěšném dokončení zálohování v chybovém protokolu.
4199 — Aktivuje změny v optimalizátoru dotazů vydané v kumulativních aktualizacích a aktualizacích SQL Serveru.
6532-6534 — Zahrnuje zlepšení výkonu operací dotazů se prostorovými datovými typy.
8048 — Převádí objekty paměti, které jsou segmentovány podle NUMA, na segmentované podle CPU.
8780 — Aktivuje další čas na sestavení plánu dotazu. Některé dotazy bez této vlajky mohou být odmítnuty, protože nemají plán dotazu (velmi vzácná chyba).
8780 — 9389 — Aktivuje další dynamicky přidělovanou paměť pro operátory dávkového režimu, což umožňuje operátoru dávkového režimu požadovat další paměť a vyhnout se přenosu dat do tempdb, pokud je další paměť k dispozici.

Také před verzí 2016 je užitečné zahrnout trasovací vlajku 2301, která aktivuje optimalizaci rozšířené podpory rozhodování a tím pomáhá při výběru správnějších plánů dotazů. Od verze 2016 však často má negativní vliv na celkový čas provádění dotazů.
Také pro systémy s mnoha indexy (například pro databáze 1C) doporučuji zahrnout trasovací vlajku 2330, která vypíná sledování používání indexů, což celkově pozitivně ovlivňuje systém.
Podrobněji o trasovacích vlajkách se můžete dozvědět. zde
Ve výše uvedeném odkazu je důležité také zohlednit verze a sestavení MS SQL Serveru, protože pro novější verze jsou některé trasovací vlajky zapnuty ve výchozím nastavení nebo nemají žádný efekt.
Trasovacích vlajek se lze aktivovat a deaktivovat pomocí příkazů DBCC TRACEON a DBCC TRACEOFF. Podrobněji viz. zde
Stav trasovacích vlajek lze zjistit pomocí příkazu DBCC TRACESTATUS: více informací
Aby byly flagy sledování zahrnuty do automatického spuštění služby MS SQL Server, je třeba otevřít Správce konfigurace SQL Server a v vlastnostech služby přidat příslušné flagy sledování pomocí -T:

Závěry

V tomto článku byly probrány některé aspekty monitorování MS SQL Serveru, které umožňují rychle odhalit nedostatek RAM a volného času CPU, jakož i řadu dalších méně zřejmých problémů. Byly zvažovány nejčastěji používané flagy sledování.