Predslov
Používatelia, vývojári a správcovia MS SQL Server DBMS sa pomerne často stretávajú s problémami s výkonom databázy alebo DBMS ako celku, takže monitorovanie MS SQL Server je veľmi dôležité.
Tento článok je doplnkom k článku a bude pokrývať niektoré aspekty monitorovania MS SQL Server, najmä: ako rýchlo určiť, ktoré zdroje chýbajú, ako aj odporúčania pre nastavenie príznakov sledovania.
Aby nasledujúce skripty fungovali, musíte v požadovanej databáze vytvoriť schému inf takto:
Vytvorenie schémy inf
use <имя_БД>;
go
create schema inf;
Metóda zisťovania nedostatku pamäte RAM
Prvým indikátorom nedostatku pamäte RAM je prípad, keď inštancia MS SQL Server spotrebuje všetku pamäť RAM, ktorá je jej pridelená.
Na tento účel vytvoríme nasledujúcu reprezentáciu inf.vRAM:
Vytvorenie zobrazenia 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čiť, že inštancia MS SQL Server spotrebuje všetku pridelenú pamäť pomocou nasledujúceho dotazu:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Ak je hodnota SQL_server_physical_memory_in_use_Mb konzistentne väčšia alebo rovná hodnote SQL_server_committed_target_Mb, potom by sa mala skontrolovať štatistika čakania.
Ak chcete zistiť nedostatok pamäte RAM pomocou štatistiky čakania, vytvorte zobrazenie inf.vWaits:
Vytvorenie zobrazenia 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 prípade môžete určiť nedostatok pamäte RAM pomocou nasledujúceho dotazu:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Tu je potrebné venovať pozornosť ukazovateľom Percentage a AvgWait_S. Ak sú významné vo svojom celku, potom je veľmi vysoká pravdepodobnosť, že nie je dostatok pamäte RAM pre inštanciu MS SQL Server. Významné hodnoty sa určujú individuálne pre každý systém. Môžete však začať s nasledujúcim: Percentage>=1 a AvgWait_S>=0.005.
Na výstup indikátorov do monitorovacieho systému (napríklad Zabbix) môžete vytvoriť nasledujúce dva dotazy:
- koľko typov čakaní zaberá RAM v percentách (súčet všetkých takýchto typov čakaní):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - koľko typov čakania RAM trvá v milisekundách (maximálna hodnota všetkých priemerných oneskorení pre všetky takéto typy čakania):
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áklade dynamiky získaných hodnôt pre tieto dva ukazovatele môžeme usúdiť, či je dostatok pamäte RAM pre inštanciu MS SQL Server.
Metóda detekcie preťaženia CPU
Na identifikáciu nedostatku času procesora stačí použiť systémový pohľad sys.dm_os_schedulers. Ak je počet runnable_tasks_count neustále väčší ako 1, potom je vysoká pravdepodobnosť, že počet jadier nestačí pre inštanciu MS SQL Server.
Ak chcete odoslať indikátor do monitorovacieho systému (napríklad Zabbix), môžete vytvoriť nasledujúci dotaz:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Na základe dynamiky získaných hodnôt pre tento ukazovateľ môžeme usúdiť, či je dostatok procesorového času (počet jadier CPU) pre inštanciu MS SQL Server.
Je však dôležité mať na pamäti skutočnosť, že samotné požiadavky môžu požadovať viacero vlákien naraz. A niekedy optimalizátor nedokáže správne odhadnúť zložitosť samotného dotazu. Potom môže byť požiadavke pridelených príliš veľa vlákien, ktoré nemožno spracovať súčasne v danom čase. A to tiež spôsobuje typ čakania spojeného s nedostatkom času procesora a nárastom frontu pre plánovače, ktoré používajú špecifické jadrá CPU, t. j. indikátor runnable_tasks_count bude v takýchto podmienkach rásť.
V tomto prípade je pred zvýšením počtu jadier CPU potrebné správne nakonfigurovať vlastnosti paralelizmu samotnej inštancie MS SQL Server a od verzie 2016 správne nakonfigurovať vlastnosti paralelizmu požadovaných databáz:
Tu by ste mali venovať pozornosť nasledujúcim parametrom:
- Maximálny stupeň paralelnosti – nastavuje maximálny počet vlákien, ktoré je možné prideliť každej požiadavke (predvolená hodnota je 0 – obmedzené iba samotným operačným systémom a edíciou MS SQL Server)
- Hranica nákladov pre paralelizmus – odhadované náklady na paralelizmus (predvolená hodnota je 5)
- Max DOP - nastavuje maximálny počet vlákien, ktoré je možné prideliť každému dotazu na úrovni databázy (ale nie viac ako hodnotu vlastnosti "Max stupeň paralelnosti") (predvolená hodnota je 0 - obmedzené iba samotným operačným systémom a vydanie MS SQL Server, ako aj obmedzenie vlastnosti "Maximálny stupeň paralelnosti" celej inštancie MS SQL Server)
Tu nie je možné poskytnúť rovnako dobrý recept pre všetky prípady, t.j. musíte analyzovať ťažké otázky.
Z vlastnej skúsenosti odporúčam nasledujúci algoritmus akcií pre systémy OLTP na nastavenie vlastností paralelizmu:
- najprv zakážte paralelizmus nastavením maximálneho stupňa paralelnosti pre celú inštanciu na 1
- analyzovať najťažšie požiadavky a vybrať pre ne optimálny počet vlákien
- nastavte maximálny stupeň paralelnosti na zvolený optimálny počet vlákien získaných z kroku 2 a pre špecifické databázy nastavte hodnotu maximálneho DOP získanú z kroku 2 pre každú databázu
- analyzovať najnáročnejšie požiadavky a identifikovať negatívny vplyv multithreadingu. Ak áno, zvýšte prah nákladov pre paralelizmus.
Pre systémy ako 1C, Microsoft CRM a Microsoft NAV je vo väčšine prípadov vhodný zákaz multithreadingu
Taktiež ak existuje edícia Standard, tak vo väčšine prípadov je zákaz multithreadingu vhodný vzhľadom na to, že táto edícia je obmedzená počtom CPU jadier.
Pre systémy OLAP nie je algoritmus opísaný vyššie vhodný.
Z vlastnej skúsenosti odporúčam nasledujúci algoritmus akcií pre systémy OLAP na nastavenie vlastností paralelizmu:
- analyzovať najťažšie požiadavky a vybrať pre ne optimálny počet vlákien
- nastavte maximálny stupeň paralelnosti na zvolený optimálny počet vlákien získaných z kroku 1 a pre špecifické databázy nastavte hodnotu maximálneho DOP získanú z kroku 1 pre každú databázu
- analyzovať najťažšie otázky a identifikovať negatívny vplyv obmedzenia súbežnosti. Ak áno, potom buď znížte prah ceny pre paralelnosť, alebo zopakujte kroky 1-2 tohto algoritmu
To znamená, že pri systémoch OLTP ideme od jednovláknového k viacvláknovému a pri OLAP-systémoch naopak od viacvláknového k jednovláknovému. Môžete si tak zvoliť optimálne nastavenia paralelizmu pre konkrétnu databázu aj celú inštanciu MS SQL Server.
Je tiež dôležité pochopiť, že nastavenia vlastností paralelizmu je potrebné časom meniť na základe výsledkov sledovania výkonu MS SQL Server.
Pokyny na nastavenie príznakov sledovania
Z vlastnej skúsenosti a skúseností mojich kolegov pre optimálny výkon odporúčam nastaviť nasledujúce príznaky sledovania na úrovni spustenia služby MS SQL Server pre verzie 2008-2016:
- 610 - Znížené zaznamenávanie vložiek do indexovaných tabuliek. Môže pomôcť s vkladaním do tabuliek s mnohými záznamami a transakciami, s častým dlhým čakaním WRITELOG na zmeny v indexoch
- 1117 - Ak súbor v skupine súborov spĺňa požiadavky prahu automatického rastu, všetky súbory v skupine súborov rastú
- 1118 - Vynúti, aby boli všetky objekty umiestnené v rôznych rozsahoch (zákaz zmiešaných rozsahov), čo minimalizuje potrebu skenovania stránky SGAM, ktorá sa používa na sledovanie zmiešaných rozsahov
- 1224 - Zakáže eskaláciu zámkov na základe počtu zámkov. Nadmerné využitie pamäte však môže spustiť eskaláciu uzamknutia
- 2371 - Zmení pevný prah automatickej aktualizácie štatistík na prah dynamickej automatickej aktualizácie štatistík. Dôležité pre aktualizáciu plánov dotazov pre veľké tabuľky, kde nesprávny počet záznamov vedie k chybným plánom vykonávania
- 3226 - Potlačí správy o úspešnom zálohovaní v protokole chýb
- 4199 - Zahŕňa zmeny optimalizátora dotazov vydaného v CU a SQL Server Service Pack
- 6532-6534 - Zahŕňa vylepšenia výkonu pre operácie dotazov na typy priestorových údajov
- 8048 - Konvertuje NUMA rozdelené pamäťové objekty na CPU rozdelené
- 8780 - Povolí dodatočné pridelenie času na plánovanie dotazov. Niektoré žiadosti bez tohto príznaku môžu byť zamietnuté, pretože nemajú plán dopytov (veľmi zriedkavá chyba)
- 8780 - 9389 - Povolí dodatočnú vyrovnávaciu pamäť dynamického prideľovania pre príkazy v dávkovom režime, čo umožňuje operátorovi dávkového režimu požiadať o dodatočnú pamäť a vyhnúť sa presunu údajov do databázy tempdb, ak je k dispozícii dodatočná pamäť
Pred rokom 2016 je tiež užitočné povoliť príznak sledovania 2301, ktorý umožňuje vylepšenú optimalizáciu podpory rozhodovania a pomáha tak pri výbere správnejších plánov dotazov. Od verzie 2016 má však často negatívny vplyv na pomerne dlhé celkové časy vykonávania dotazov.
Tiež pre systémy s množstvom indexov (napríklad pre databázy 1C) odporúčam povoliť príznak sledovania 2330, ktorý zakáže zhromažďovanie používania indexov, čo má vo všeobecnosti pozitívny vplyv na systém.
Ďalšie informácie o príznakoch sledovania nájdete v časti
Z vyššie uvedeného odkazu je tiež dôležité zvážiť verzie a zostavy MS SQL Server, pretože pre novšie verzie sú niektoré príznaky sledovania predvolene povolené alebo nemajú žiadny účinok.
Príznak sledovania môžete zapnúť a vypnúť pomocou príkazov DBCC TRACEON a DBCC TRACEOFF. Viac podrobností viď
Stav príznakov sledovania môžete získať pomocou príkazu DBCC TRACESTATUS:
Aby boli príznaky sledovania zahrnuté do automatického spustenia služby MS SQL Server, musíte prejsť do SQL Server Configuration Manager a pridať tieto príznaky sledovania cez -T vo vlastnostiach služby:
Výsledky
V tomto článku boli analyzované niektoré aspekty monitorovania MS SQL Server, pomocou ktorých môžete rýchlo identifikovať nedostatok pamäte RAM a voľného času CPU, ako aj množstvo ďalších menej zjavných problémov. Najbežnejšie používané príznaky sledovania boli preskúmané.
Zdroje:
»
»
»
»
»
»
»
Zdroj: hab.com