předmluva
Uživatelé, vývojáři a správci MS SQL Server DBMS se poměrně často setkávají s výkonnostními problémy databáze nebo DBMS jako celku, takže monitorování MS SQL Serveru je velmi relevantní.
Tento článek je doplňkem článku a bude pokrývat některé aspekty monitorování MS SQL Server, zejména: jak rychle zjistit, které zdroje chybí, a také doporučení pro nastavení příznaků trasování.
Aby následující skripty fungovaly, musíte v požadované databázi vytvořit schéma inf takto:
Vytvoření schématu inf
use <имя_БД>;
go
create schema inf;
Metoda pro zjištění nedostatku paměti RAM
Prvním indikátorem nedostatku paměti RAM je případ, kdy instance MS SQL Server spotřebuje veškerou jí přidělenou RAM.
K tomu vytvoříme následující reprezentaci inf.vRAM:
Vytvoření zobrazení inf.vRAM
CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb] --сколько свободно ОЗУ на сервере в МБ
, a.[RAM_Avail_Percent] --процент свободного ОЗУ на сервере
, a.[Server_physical_memory_Mb] --сколько всего ОЗУ на сервере в МБ
, a.[SQL_server_committed_target_Mb] --сколько всего ОЗУ выделено под MS SQL Server в МБ
, a.[SQL_server_physical_memory_in_use_Mb] --сколько всего ОЗУ потребляет MS SQL Server в данный момент времени в МБ
, a.[SQL_RAM_Avail_Percent] --поцент свободного ОЗУ для MS SQL Server относительно всего выделенного ОЗУ для MS SQL Server
, a.[StateMemorySQL] --достаточно ли ОЗУ для MS SQL Server
, a.[SQL_RAM_Reserve_Percent] --процент выделенной ОЗУ для MS SQL Server относительно всего ОЗУ сервера
--достаточно ли ОЗУ для сервера
, (case when a.[RAM_Avail_Percent]<10 and 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;
Potom můžete určit, že instance MS SQL Server spotřebovává veškerou paměť, která jí byla přidělena, 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 konzistentně větší nebo rovna hodnotě SQL_server_committed_target_Mb, je třeba zkontrolovat statistiku čekání.
Chcete-li zjistit nedostatek paměti RAM pomocí statistiky čekání, vytvořte pohled inf.vWaits:
Vytvoření zobrazení inf.vWaits
CREATE view [inf].[vWaits] as
WITH [Waits] AS
(SELECT
[wait_type], --имя типа ожидания
[wait_time_ms] / 1000.0 AS [WaitS],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
[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 [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],--Общее время ожидания данного типа в миллисекундах. Это время включает signal_wait_time_ms
CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Общее время ожидания данного типа в миллисекундах без signal_wait_time_ms
CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Разница между временем сигнализации ожидающего потока и временем начала его выполнения
[W1].[WaitCount] AS [WaitCount],--Число ожиданий данного типа. Этот счетчик наращивается каждый раз при начале ожидания
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 -- percentage threshold
)
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ě můžete nedostatek paměti RAM určit pomocí následujícího dotazu:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Zde je třeba věnovat pozornost ukazatelům Percentage a AvgWait_S. Pokud jsou významné ve svém celku, pak je velmi vysoká pravděpodobnost, že pro instanci MS SQL Server není dostatek paměti RAM. Významné hodnoty jsou stanoveny individuálně pro každý systém. Můžete však začít s následujícím: Procento>=1 a AvgWait_S>=0.005.
Pro výstup indikátorů do monitorovacího systému (například Zabbix) můžete vytvořit následující dva dotazy:
- kolik typů čekání je obsazeno RAM v procentech (součet všech takových 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 typů čekání RAM trvá v milisekundách (maximální hodnota všech průměrných zpoždění pro všechny takové typy č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 pro tyto dva indikátory můžeme usoudit, zda je dostatek paměti RAM pro instanci MS SQL Server.
Metoda detekce přetížení CPU
K identifikaci nedostatku času procesoru stačí použít systémový pohled sys.dm_os_schedulers. Pokud je zde počet runnable_tasks_count neustále větší než 1, pak je vysoká pravděpodobnost, že počet jader nestačí pro instanci MS SQL Server.
Chcete-li odeslat indikátor do monitorovacího systému (například Zabbix), můžete 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 pro tento ukazatel můžeme usoudit, zda je dostatek procesorového času (počet jader CPU) pro instanci MS SQL Server.
Je však důležité mít na paměti fakt, že samotné požadavky mohou vyžadovat více vláken najednou. A někdy optimalizátor nedokáže správně odhadnout složitost samotného dotazu. Potom může být požadavku přiděleno příliš mnoho vláken, která nelze zpracovat současně v daný čas. A to také způsobuje typ čekání spojený s nedostatkem času procesoru a růst fronty pro plánovače, které používají specifická jádra CPU, tj. indikátor runnable_tasks_count v takových podmínkách poroste.
V tomto případě je před zvýšením počtu jader CPU nutné správně nakonfigurovat vlastnosti paralelismu samotné instance MS SQL Server a od verze 2016 správně nakonfigurovat vlastnosti paralelismu požadovaných databází:
Zde byste měli věnovat pozornost následujícím parametrům:
- Maximální stupeň paralelismu – nastavuje maximální počet vláken, které lze přidělit každému požadavku (výchozí hodnota je 0 – omezeno pouze samotným operačním systémem a edicí MS SQL Serveru)
- Cost Threshold for Parallelism – odhadované náklady na paralelismus (výchozí je 5)
- Max DOP - nastavuje maximální počet vláken, které lze přidělit každému dotazu na úrovni databáze (ale ne více, než je hodnota vlastnosti "Max Degree of Parallelism") (výchozí je 0 - omezeno pouze samotným operačním systémem a edice MS SQL Server, stejně jako omezení vlastnosti "Max Degree of Parallelism" celé instance MS SQL Server)
Zde není možné dát stejně dobrý recept pro všechny případy, to znamená, že potřebujete analyzovat těžké dotazy.
Z vlastní zkušenosti doporučuji následující algoritmus akcí pro systémy OLTP pro nastavení vlastností paralelismu:
- nejprve deaktivujte paralelismus nastavením maximálního stupně paralelnosti pro celou instanci na 1
- analyzovat nejtěžší požadavky a vybrat pro ně optimální počet vláken
- nastavte maximální stupeň paralelnosti na vybraný optimální počet vláken získaných z kroku 2 a pro konkrétní databáze nastavte hodnotu maximálního DOP získanou z kroku 2 pro každou databázi
- analyzovat nejnáročnější požadavky a identifikovat negativní efekt multithreadingu. Pokud ano, zvyšte práh nákladů pro paralelismus.
Pro systémy jako 1C, Microsoft CRM a Microsoft NAV je ve většině případů vhodný zákaz multithreadingu
Také pokud existuje edice Standard, tak ve většině případů vyhovuje zákaz multithreadingu vzhledem k tomu, že tato edice je omezena počtem jader CPU.
Pro systémy OLAP není výše popsaný algoritmus vhodný.
Z vlastní zkušenosti doporučuji pro OLAP systémy pro nastavení vlastností paralelismu následující algoritmus akcí:
- analyzovat nejtěžší požadavky a vybrat pro ně optimální počet vláken
- nastavte maximální stupeň paralelnosti na vybraný optimální počet vláken získaných z kroku 1 a pro konkrétní databáze nastavte hodnotu maximálního DOP získanou z kroku 1 pro každou databázi
- analyzovat nejtěžší dotazy a identifikovat negativní efekt omezení souběžnosti. Pokud ano, pak buď snižte prahovou hodnotu nákladů pro paralelnost, nebo opakujte kroky 1-2 tohoto algoritmu
To znamená, že u systémů OLTP přecházíme od jednovláknového k vícevláknovému a u OLAP-systémů naopak od vícevláknového k jednovláknovému. Můžete tak zvolit optimální nastavení paralelismu jak pro konkrétní databázi, tak pro celou instanci MS SQL Serveru.
Je také důležité pochopit, že nastavení vlastností paralelismu je třeba v průběhu času měnit na základě výsledků sledování výkonu MS SQL Server.
Pokyny pro nastavení příznaků trasování
Z vlastní zkušenosti a zkušeností mých kolegů pro optimální výkon doporučuji nastavit na úrovni běhu služby MS SQL Server pro verze 2008-2016 následující příznaky trasování:
- 610 - Snížené protokolování vložení do indexovaných tabulek. Může pomoci s vkládáním do tabulek s mnoha záznamy a mnoha transakcemi, s častým dlouhým čekáním WRITELOG na změny v indexech
- 1117 - Pokud soubor ve skupině souborů splňuje požadavky na prahovou hodnotu automatického růstu, všechny soubory ve skupině souborů rostou
- 1118 - Vynutí, aby byly všechny objekty umístěny v různém rozsahu (zákaz smíšených rozsahů), což minimalizuje potřebu skenování stránky SGAM, která se používá ke sledování smíšených rozsahů
- 1224 – Zakáže eskalaci zámků na základě počtu zámků. Nadměrné využití paměti však může vyvolat eskalaci uzamčení
- 2371 - Změní pevný práh automatické aktualizace statistik na práh dynamické automatické aktualizace statistik. Důležité pro aktualizaci plánů dotazů pro velké tabulky, kde nesprávný počet záznamů vede k chybným plánům provádění
- 3226 - Potlačí zprávy o úspěšném zálohování v protokolu chyb
- 4199 - Zahrnuje změny optimalizátoru dotazů vydaného v CU a SQL Server Service Pack
- 6532-6534 - Zahrnuje vylepšení výkonu pro operace dotazů na typy prostorových dat
- 8048 - Převede objekty rozdělené paměti NUMA na objekty rozdělené na CPU
- 8780 - Povoluje další přidělení času pro plánování dotazů. Některé požadavky bez tohoto příznaku mohou být zamítnuty, protože nemají plán dotazů (velmi vzácná chyba)
- 8780 - 9389 - Povoluje další vyrovnávací paměť dynamického přidělení pro příkazy v dávkovém režimu, což umožňuje operátorovi dávkového režimu požadovat více paměti a vyhnout se přesunutí dat do databáze tempdb, pokud je k dispozici více paměti
Před rokem 2016 je také užitečné povolit příznak trasování 2301, který umožňuje vylepšené optimalizace podpory rozhodování a pomáhá tak při výběru správnějších plánů dotazů. Od verze 2016 má však často negativní vliv na celkem dlouhé doby provádění dotazů.
Také pro systémy s velkým množstvím indexů (například pro databáze 1C) doporučuji povolit příznak trace 2330, který zakáže shromažďování použití indexu, což má obecně pozitivní vliv na systém.
Další informace o příznacích trasování naleznete v tématu
Z výše uvedeného odkazu je také důležité vzít v úvahu verze a sestavení MS SQL Server, protože u novějších verzí jsou některé příznaky trasování ve výchozím nastavení povoleny nebo nemají žádný účinek.
Příznak trasování můžete zapnout a vypnout pomocí příkazů DBCC TRACEON a DBCC TRACEOFF. Další podrobnosti viz
Stav příznaků trasování můžete získat pomocí příkazu DBCC TRACESTATUS:
Aby byly příznaky trasování zahrnuty do automatického spuštění služby MS SQL Server, musíte přejít do SQL Server Configuration Manager a přidat tyto příznaky trasování pomocí -T ve vlastnostech služby:
Výsledky
V tomto článku byly analyzovány některé aspekty monitorování MS SQL Server, pomocí kterých můžete rychle identifikovat nedostatek paměti RAM a volného času CPU, stejně jako řadu dalších méně zřejmých problémů. Nejčastěji používané příznaky trasování byly přezkoumány.
Zdroje:
»
»
»
»
»
»
»
Zdroj: www.habr.com