🥇Mõned aspektid MS SQL Serveri monitooringust. Jälgimislipude seadistamise soovitused

Eessõna

Kasutajad, arendajad ja MS SQL Serveri andmebaasi administratorid seisavad sageli silmitsi andmebaasi või SQL Serveri tervikuna jõudlusprobleemidega, seega on MS SQL Serveri jälgimine väga oluline.
See artikkel on lisaks artiklile Zabbixi kasutamine MS SQL Serveri andmebaasi jälgimiseks ja käsitleb mõningaid aspekte MS SQL Serveri jälgimisest, sealhulgas: kuidas kiiresti määrata, millest ressursse puudu jääb, samuti soovitusi jälgimislipukeste seadistamiseks.
Allpool toodud skripti kasutamiseks tuleb luua inf skeem vajalikus andmebaasis järgmiselt:
Inf skeemi loomine

use ;
go
create schema inf;

Meetod operatiivmälu puuduse tuvastamiseks

Esimene näitaja operatiivmälu puudusest on see, kui MS SQL Server tarvitab kogu talle eraldatud RAM-i.
Selleks loome järgmise vaate inf.vRAM:
Inf.vRAM vaate loomine

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]						-- kui palju RAM-i on serveris MB-s vaba
		 , a.[RAM_Avail_Percent]					-- protsent vaba RAM-i serveris
		 , a.[Server_physical_memory_Mb]				-- kui palju RAM-i on serveris MB-s kokku
		 , a.[SQL_server_committed_target_Mb]			-- kui palju RAM-i on MS SQL Serverile MB-s eraldatud
		 , a.[SQL_server_physical_memory_in_use_Mb] 		-- kui palju RAM-i MS SQL Server hetkel MB-s tarvitab
		 , a.[SQL_RAM_Avail_Percent]				-- protsent vaba RAM-i MS SQL Serverile võrreldes kogu eraldatud RAM-iga
		 , a.[StateMemorySQL]						-- kas MS SQL Serverile piisab RAM-ist
		 , a.[SQL_RAM_Reserve_Percent]				-- protsent RAM-ist, mis on MS SQL Serverile eraldatud võrreldes kogu RAM-iga serveris
		-- kas serveril on piisavalt RAM-i
		, (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;

Siis saab määrata, et MS SQL Serveri eksemplar kasutab kogu määratud mälu järgmise päringu kaudu:

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

Kui SQL_server_physical_memory_in_use_Mb näitaja on pidevalt väiksem kui SQL_server_committed_target_Mb, tuleb kontrollida ootamisstatistikat.
Ootamisstatistika kaudu RAM-i puuduse määramiseks loome vaate inf.vWaits:
Vaate inf.vWaits loomine

LOO view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --oote ootmise tüüp
        [wait_time_ms] / 1000.0 AS [WaitS],--Oote aja kogus selle tüübi korral millisekundites. See aeg sisaldab signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Oote aja kogus selle tüübi korral millisekundites ilma signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Erinevus signaalimise ootevoo ja selle täitmise alguse aja vahel
        [waiting_tasks_count] AS [WaitCount],--Ootete arvu selle tüübi puhul. See loendur suureneb iga kord, kui ootus algab
        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],--Oote aja kogus selle tüübi korral millisekundites. See aeg sisaldab signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Oote aja kogus selle tüübi korral millisekundites ilma signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Erinevus signaalimise ootevoo ja selle täitmise alguse aja vahel
	    [W1].[WaitCount] AS [WaitCount],--Ootete arvu selle tüübi puhul. See loendur suureneb iga kord, kui ootus algab
	    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 -- protsendi lävi
)
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];

Sellise juhtumi korral saab mälu puudumise määramiseks kasutada järgmist päringut:

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

Siin tuleks pöörata tähelepanu näitajatele Percentage ja AvgWait_S. Kui need on oma kogusumma poolest olulised, tuleb tõenäoliselt MS SQL Serveri instantsil mälu defitsiit. Olulisi väärtusi määratakse igas süsteemis individuaalselt. Siiski, alguses võib lähtuda järgmistest näitajatest: Percentage>=1 ja AvgWait_S>=0.005.
Tulemuste edastamiseks jälgimissüsteemi (näiteks Zabbix) saab luua järgmised kaks päringut:

kui suur osa ootustest röövib OZM-i (%):

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

kui kaua ootuste tüübid viibivad OZM-i (kõrgeim keskmine viivitus kõigi selliste ootuste tüüpide seas) ms:

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'
  );

Saadud näitajate dünaamika põhjal saab teha järeldusi, kas MS SQL Serveri instantsil on piisavalt OZM-i.

Meetod protsessorikoormuse tuvastamiseks

Protsessoriaja puuduse tuvastamiseks on piisav kasutada süsteemivaadet sys.dm_os_schedulers. Siin, kui runnable_tasks_count näitaja on pidevalt suurem kui 1, on suur tõenäosus, et CPU tuumade arv on MS SQL Serveri instantsile ebapiisav.
Näitajate edastamiseks jälgimissüsteemi (näiteks Zabbix) saab luua järgmise päringu:

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

Saadud näitajate dünaamika põhjal saab teha järeldusi, kas protsessoriaega (CPU tuumade arvu) on piisavalt MS SQL Serveri instantsile.
Siiski on oluline meeles pidada, et päringud võivad samal ajal nõuda mitut töövoogu. Ja mõnikord ei suuda optimeerija õigesti hinnata päringu keerukust. Sellisel juhul võib päringule määrata liiga palju töövooge, mida ei saa hetkel samal ajal töödelda. See põhjustab samuti ooteaega, mis on seotud protsessoriaja puudumisega, ning järjekordade kasvamine planeerijates, mis kasutavad konkreetseid CPU tuumasid, seega sellistes tingimustes suureneb runnable_tasks_count.
Sel juhul, enne CPU tuumade arvu suurendamist, on oluline õigesti seadistada MS SQL Serveri eksemplari paralleelsuse omadused ning alates 2016. aastast õigesti seadistada vajalike andmebaaside paralleelsuse omadused:

Siin tasub pöörata tähelepanu järgmistele parameetritele:

Max Degree of Parallelism - määrab maksimaalse töövoogude arvu, mis võib iga päringu jaoks olla eraldatud (vaikimisi on 0 - piirang ainult operatsioonisüsteemile ja MS SQL Serveri väljaandele).
Cost Threshold for Parallelism - hinnanguline paralelsuse maksumus (vaikimisi on 5).
Max DOP - määrab maksimaalse töövoogude arvu, mis võib iga päringu jaoks andmebaasi tasemel olla eraldatud (kuid mitte rohkem kui väärtus 'Max Degree of Parallelism' omadusest) (vaikimisi on 0 - piirang ainult operatsioonisüsteemile ja MS SQL Serveri väljaandele ning piirang 'Max Degree of Parallelism' kogu MS SQL Serveri eksemplari kohta).

Siin ei saa anda ühesugust head retsepti kõigi juhtumite jaoks, seega tuleb analüüsida keerulisi päringuid.
Oma kogemuste põhjal soovitan järgmist tegevusalgoritmi OLTP-süsteemide paralleelsuse seadistamiseks:

esimese sammu raames keelata paralleelsus, seades kogu eksemplari tasemel Max Degree of Parallelism 1-le.
analüüsige kõige keerukamaid päringuid ja valige nende jaoks optimaalse töövoogude arvu.
seadke Max Degree of Parallelism valitud optimaalsele töövoogude arvule, saadud punktist 2, ning samuti seadke konkreetsete andmebaaside jaoks Max DOP väärtus, mis on saadud punktist 2 igale andmebaasile.
analüüsige kõige keerukamaid päringuid ja tuvastage negatiivne mõju mitme töövoo kasutamisest. Kui see esineb, siis suurendage Cost Threshold for Parallelism.
Selliste süsteemide nagu 1C, Microsoft CRM ja Microsoft NAV puhul sobib enamikul juhtudel mitme töövoo keelamine.

Samuti, kui on valitud Standard väljaanne, sobib enamikul juhtudel protsessorite mitmeühtlustamise keelamine, kuna see väljaanne on piiratud protsessorituumade arvu poolest.
OLAP-süsteemide jaoks ei sobi eespool kirjeldatud algoritm.
Oma kogemuste põhjal soovitan järgmist tegevusjärgestust OLAP-süsteemide puhul paralleelsuse seadete seadmiseks:

analüüsige kõige keerukamaid päringuid ja valige nende jaoks optimaalse töövoogude arvu.
seada Max Degree of Parallelism välja valitud optimaalseks kiiruskoefitsiendiks, mis on saadud p.1-st, ja märkida konkreetsetele andmebaasidele Max DOP väärtus, mis on saadud p.1 iga andmebaasi jaoks
analüüsida kõige raskemaid päringuid ja tuvastada negatiivne mõju paralleelsuse piiramisega. Kui see esineb, vähendada kas Cost Threshold for Parallelism väärtust või korrata samme 1-2 selles algoritmis

Seega liikuda OLTP-süsteemide puhul üheltüübist mitmeühtlustamise suunas ja OLAP-süsteemide puhul vastupidi – mitmeühtlustamisest üheühtlustamiseni. Nii saab seadistada optimaalsed paralleelsuse seaded nii konkreetse andmebaasi kui ka kogu MS SQL Serveri näite jaoks.
Samuti on oluline mõista, et paralleelsuse seadete muutmine on vajalik ajas, tuginedes MS SQL Serveri jõudluse jälgimise tulemustele.

Soovitused jälgimislipude seadmiseks

Oma kogemuste ning kolleegide kogemuste põhjal soovitan MS SQL Serveri teenuse käivitamisel 2008-2016 versioonide jaoks seadistada järgmised jälgimislipud:

610 — Indekseeritud tabelitesse sisestamise protokollimise vähendamine. See võib aidata tabelitesse, kus on palju kirjeid ja palju tehinguid, päringute osas, kui on sageli pikki ootusi WRITELOG-i tõttu indeksites muutumiste osas.
1117 — Kui fail vastab automaatse suurendamise künnise nõuetele, suurendatakse kõiki faile failigruppides.
1118 — Paneb kõik objektid asuma erinevatesse ekstentidesse (segatud ekstentide keelamine), minimeerides SGAM lehe skanimeerimise vajadust, mida kasutatakse segatud ekstentide jälgimiseks.
1224 — Keelab lukustuste agregatsiooni lukustuste arvu põhjal. Kuid liiga aktiivne mälu kasutamine võib aktiveerida lukustuste agregatsiooni.
2371 — Muudab staatilisi automaatsete statistika värskenduste läve dünaamiliste automaatsete statistika värskenduste läve. Oluline on päringute plaanide värskendamiseks suurte tabelitega, kus vale ridade arv viib vale täitmisplaanideni.
3226 — Vaikimisi päringute varukoopiate edukas täitmine ei kajastu vealogis.
4199 — Lülitab sisse päringute optimeerija muudatused, mis on välja antud kogumite värskendustes ja SQL Serveri värskenduspakettides.
6532-6534 — Parandab päringute operatsioonide jõudlust, kui kasutatakse ruumilisi andmetüüpe.
8048 — Muudab NUMA järgi jaotatud mälobjektid CPU järgi jaotatuks.
8780 — Aktiveerib täiendava aja eraldamise päringute plaani koostamiseks. Mõned päringud, millel puudub see lipp, võivad olla tagasi lükatud, kuna neil pole päringute plaani (väga harv viga).
8780 — 9389 — Aktiveerib täiendava dünaamiliselt eraldatud mälu puhvrina pakettrežiimi operaatoritele, võimaldades pakettrežiimi operaatoril küsida täiendavat mälu ja vältida andmete edastamist tempdb-sse, kui täiendav mälu on saadaval.

Samuti on enne 2016. aastat kasulik aktiveerida jälgimislipu 2301, mis võimaldab täiustatud otsustusprotsessi optimeerimist ning seeläbi toetab õigete päringute plaanide valikut. Siiski, alates 2016. aastast on sellel sageli negatiivne mõju päringute kogutäitevatele aegadele.
Samuti soovitan süsteemides, kus on väga palju indekseid (nt 1C andmebaaside jaoks), aktiveerida jälgimislipu 2330, mis keelab indeksi kasutamise kogumise, mis üldiselt mõjutab süsteemi positiivselt.
Jälgimislipu kohta leiate rohkem teavet. siin
Ülaltoodud lingilt on oluline arvesse võtta ka MS SQL Serveri versioone ja kogusid, kuna uuemate versioonide puhul on mõned jälgimislipud vaikimisi lubatud või ei oma mingit mõju.
Jälgimislipu lubamine ja keelamine on võimalik DBCC TRACEON ja DBCC TRACEOFF käskude abil vastavalt. Täiendav teave on saadaval. siin
Jälgimislipu staatust saab vaadata DBCC TRACESTATUS käsu kaudu: rohkem
Ettevalmistamiseks MS SQL Serveri teenuse automaatseks käivitamiseks tuleb avada SQL Serveri konfiguratsioonihaldur ja teenuse omadustes lisada jälgimislipud läbi -T:

Summary

Selles artiklis käsitletakse mõned aspektid MS SQL Serveri monitooringust, mille abil võib kiiresti tuvastada mälupuuduse ja CPU vabade ajavahemike puuduse, samuti mitmeid vähem ilmseid probleeme. Enimkasutatud jälgimislipud on ülevaatusel.