🥇Mõned aspektid MS SQL Serveri jälgimisest. Soovitused jälgimislipukeste seadistamiseks

Eessõna

Kasutajad, arendajad ja MS SQL Serveri andmebaasiadministraatorid puutuvad sageli kokku andmebaasi või andmebaasisüsteemi üldise jõudluse probleemidega, seetõttu on MS SQL Serveri jälgimine äärmiselt oluline.
See artikkel on täienduseks artiklile Zabbixi kasutamine MS SQL Serveri andmebaasi jälgimiseks ja siin käsitletakse mõningaid MS SQL Serveri jälgimise aspekte, sealhulgas: kuidas kiiresti kindlaks teha, milliseid ressursse vajatakse, ning soovitusi jälgimisflaggide seadistamiseks.
Järgmiste skriptide tööks on vajalik luua inf skeem soovitud andmebaasis järgmiselt:
Inf skeemi loomine

use ;
go
create schema inf;

Mälu puuduse tuvastamise meetod

Esimene näitaja mälu puudusest on see, kui MS SQL Serveri instants sööb kogu talle eraldatud RAM-i.
Selleks loome järgmise vaate inf.vRAM:
Inf.vRAM vaate loomine

Loo vaade [inf].[vRAM] kui
select a.[TotalAvailOSRam_Mb]						--kui palju on serveris vabamälu MB-des
		 , a.[RAM_Avail_Percent]					--vaba mälu protsent serveris
		 , a.[Server_physical_memory_Mb]				--kui palju on serveris kokku mälu MB-des
		 , a.[SQL_server_committed_target_Mb]			--kui palju mälu on MS SQL Serverile eraldatud MB-des
		 , a.[SQL_server_physical_memory_in_use_Mb] 		--kui palju mälu kasutab MS SQL Server hetkel MB-des
		 , a.[SQL_RAM_Avail_Percent]				--MS SQL Serverile eraldatud mälu suhtes vaba mälu protsent
		 , a.[StateMemorySQL]						--kas MS SQL Serverile on piisavalt mälu
		 , a.[SQL_RAM_Reserve_Percent]				--MS SQL Serverile eraldatud mälu protsent serveri kogu mälust
		 --kas serverile on piisavalt mälu
		, (case when a.[RAM_Avail_Percent]<10 and a.[RAM_Avail_Percent]>5 and a.[TotalAvailOSRam_Mb]<8192 then 'Hoiatus' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Oht' else 'Normaalne' 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 'Hoiatus' else 'Normaalne' 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;

Seega saab määrata, et MS SQL Serveri eksemplar kasutab kogu talle määratud mälu järgmise päringuga:

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, tuleks kontrollida ootuste statistikat.
Operatiivmälu puuduse määramiseks ootuste statistika kaudu loome vaate inf.vWaits:
Vaate inf.vWaits loomine

LOO view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], -- ootamise tüübi nimi
        [wait_time_ms] / 1000.0 AS [WaitS],--Antud tüübi ootamise koguaeg millisekundites. See aeg sisaldab signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Antud tüübi ootamise koguaeg millisekundites ilma signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Erinevus ootava niidi signalisatsiooni lõpu aja ja selle käivitamise alguse vahel
        [waiting_tasks_count] AS [WaitCount],--Ootavate arv antud tüübi puhul. See loendur suureneb iga kord, kui ootamine 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],--Antud tüübi ootamise koguaeg millisekundites. See aeg sisaldab signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Antud tüübi ootamise koguaeg millisekundites ilma signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Erinevus ootava niidi signalisatsiooni lõpu aja ja selle käivitamise alguse vahel
	    [W1].[WaitCount] AS [WaitCount],--Ootavate arv antud tüübi puhul. See loendur suureneb iga kord, kui ootamine 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 künnis
)
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];

Sell juhul saab mälukoha puudujäägi tuvastada järgmise päringuga:

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

Siin tuleb tähelepanu pöörata näitajatele Percentage ja AvgWait_S. Kui need on oluliselt kõrged, on väga suur tõenäosus, et MS SQL Serveri näidis kannatab mälupuuduse all. Olulised väärtused määratakse individuaalselt igas süsteemis. Siiski võib alustada järgmistest näitajatest: Percentage>=1 ja AvgWait_S>=0.005.
Jälgimistarkvarasse (nt Zabbix) näitajate edastamiseks võib luua järgmised kaks päringut:

kui suure protsendi ulatuses võtavad mälu ootustüübid (kõikide selliste ootustüüpide summa):

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

kui palju kulub ootustüüpidele mälu (kõrgeim väärtus kõigi selliste ootuste keskmisest viivitustest):

valige coalesce(max([AvgWait_S])*1000, 0.00) as [AvgWait_MS]
from [inf].[vWaits]
       where [WaitType] in (
            'PAGEIOLATCH_XX',
            'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

Võttes arvesse nende kahe näitaja dünaamikat, võib järeldada, kas MS SQL Serveri eksemplaril on piisavalt RAM-i.

Liigne koormuse tuvastamise meetod

Protsessorite aja puuduse tuvastamiseks piisab süsteemi vaate sys.dm_os_schedulers kasutamisest. Siin, kui näitaja runnable_tasks_count on pidevalt suurem kui 1, on suur tõenäosus, et tuumade arv ei ole MS SQL Serveri eksemplari jaoks piisav.
Jälgimissüsteemi (näiteks Zabbix) näitaja väljastamiseks saab koostada järgmise päringu:

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

Võttes arvesse selle näitaja dünaamikat, võib järeldada, kas protsessorite aeg (tuumade arv) on MS SQL Serveri eksemplari jaoks piisav.
Kuid on oluline meeles pidada, et päringud võivad samaaegselt nõuda mitut voogu. Ja mõnikord ei suuda optimeerija õigesti hinnata päringu enda keerukust. Sel juhul võib päringule määrata liiga palju vooge, mida ei saa samal ajal töödelda. See loob ka oote tüübi, mis on seotud protsessorite aja puudumisega ning kasvava järjekorraga planeerijatel, mis kasutavad konkreetseid CPU tuumasid, seega runnable_tasks_count näitaja sellistes tingimustes kasvab.
Sellisel juhul on enne, kui suurendada CPU tuumade arvu, vajalik õigesti seadistada MS SQL Serveri esinemise paralleelsuse omadused, ning alates 2016. aastast - õigesti seadistada vajalike andmebaaside paralleelsuse omadused:

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

Max Degree of Parallelism - määrab maksimaalse voogude arvu, mis võivad igale päringule eralduda (vaikimisi on see 0 - piirang ainult operatsioonisüsteemilt ja MS SQL Serveri väljaandest)
Cost Threshold for Parallelism - paralleelsuse hindamisjõud (vaikimisi on see 5)
Max DOP määrab maksimaalse samaaegsete voogude arvu, mis võivad olla määratud iga päringu jaoks andmebaasi tasemel (kuid mitte rohkem kui "Max Degree of Parallelism" omaduse väärtus) (vaikimisi on see 0 - piirang ainult operatsioonisüsteemile ja MS SQL Serveri väljaandele, samuti kogu MS SQL Serveri eksemplari "Max Degree of Parallelism" omaduse piirang).

Siin ei ole võimalik anda ühte head retsepti kõigi juhtumite jaoks, seega tuleb analüüsida keerulisi päringuid.
Oma kogemusest soovitan järgmist tegevusalgoritmi OLTP-süsteemide korral paralleelsuse omaduste seadistamiseks:

algul piirata paralleelsust, seades kogu eksemplari tasemel Max Degree of Parallelism väärtuseks 1.
analüüsida kõige keerulisemaid päringuid ja valida nende jaoks optimaalse voogude arvu.
seada Max Degree of Parallelism vastavalt valitud optimaalsele voogude arvule, mis saadakse punktist 2, samuti määrata iga andmebaasi jaoks Max DOP väärtus, mis saadakse punktist 2.
analüüsida kõige keerulisemaid päringuid ja tuvastada mitme voogude kasutamise negatiivne mõju. Kui see on olemas, siis tõsta Cost Threshold for Parallelism.
Süsteemide nagu 1C, Microsoft CRM ja Microsoft NAV jaoks sobib enamikul juhtudel mitme ülesande keelamine.

Kui kasutatakse Standard väljaannet, siis enamikus olukordades sobib mitme ülesande keelamine, arvestades, et see väljaanne on CPU tuumade arvu osas piiratud.
OLAP-süsteemide jaoks ei sobi ülaltoodud algoritm.
Olen enda kogemuste põhjal soovitanud järgmisi samme OLAP-süsteemide paralleelsuse seadistamiseks:

analüüsida kõige keerulisemaid päringuid ja valida nende jaoks optimaalse voogude arvu.
seada Max Degree of Parallelism soovitatud optimaalseks lõimearvuks, mis on saadud punktist 1, ning iga konkreetse andmebaasi jaoks seada Max DOP väärtus, mis on saadud punktist 1.
analüüsida kõige raskemaid päringuid ja tuvastada paralleelsuse piiramise negatiivne mõju. Kui see esineb, tuleks kas vähendada Cost Threshold for Parallelism väärtust või korrata samme 1-2 selles algoritmis.

Seega OLTP-süsteemide puhul liigume ühesuunaliselt mitme ülesande suunast ühele ülesandele, samas kui OLAP-süsteemide puhul vastupidi - liigume mitme ülesande suunast ühele ülesandele. Nii saab seadistada optimaalsed paralleelsuse seadistused nii konkreetse andmebaasi kui ka kogu MS SQL Serveri näite jaoks.
Samuti on oluline mõista, et paralleelsuse omaduste seadistusi tuleb ajakohastada vastavalt MS SQL Serveri jõudluse monitoorimise tulemustele.

Juhised jälgimislipukeste seadistamiseks

Oma ja kolleegide kogemuste põhjal soovitan MS SQL Serveri teenuse käivitamisel versioonides 2008–2016 seada järgmised jälgimislipukesed:

610 — Vähendab indekseeritud tabelitesse lisamise logimist. See võib aidata suurt arvu ja mitme tehinguga tabelitesse lisamisel, kui on sageli pikki ootamisi WRITELOG indeksite muutmise tõttu.
1117 — Kui fail failigrupp täidab automaatse suurendamise lävendi nõudeid, suurenevad kõik failid failigruppides.
1118 — Sunnib kõik objektid asuma erinevatesse ulatustesse (segmenteerimise keelamine), mis vähendab lehe SGAM skaneerimise vajadust, mida kasutatakse segmenteeritud ulatuste jälgimiseks.
1224 — Lülitab välja plokkide suurendamise, mis põhineb plokkide arvul. Ent liiga aktiivne mälu kasutamine võib aktiveerida plokkide suurendamise.
2371 — Muudab fikseeritud automaatse statistika värskendamise läve dünaamiliseks automaatseks statistika värskendamiseks. Oluline suure tabeli päringute plaanide värskendamiseks, kus vale rekordite arvu määramine viib vale teostusplaanideni.
3226 — Supresseerib veateates edukas varundamise täitmise teated.
4199 — Aktiveerib muudatused päringute optimeerijas, mis on välja antud kogumipakettides ja SQL Serveri värskendustes.
6532-6534 — Aktiveerib päringute operatsioonide jõudluse paranemise ruumiliste andmetüüpide puhul.
8048 — Muudab NUMA põhjal jagatud mäl 객체d protsessori põhjal jagatud objektideks.
8780 — Aktiveerib täiendava aja eraldamise päringute plaani koostamiseks. Mõned päringud võivad ilma selle liputa olla tagasi lükatud, kuna neil puudub päringu plaan (väga haruldane viga).
8780 — 9389 — See includes an additional dynamically allocated memory buffer for batch mode operators, allowing the batch operator to request extra memory and avoid data transfer to tempdb when additional memory is available.

It's also advisable to include trace flag 2301 in versions prior to 2016, which enables optimization for advanced decision support, helping to choose more appropriate query plans. However, starting from version 2016, it often negatively impacts the overall execution time of queries.
For systems with a large number of indexes (e.g., for 1C databases), I recommend enabling trace flag 2330, which disables index usage collection, positively impacting the system overall.
For more details about the trace flags, you can learn more here. siit
In addition to the link provided above, it's important to consider the versions and builds of MS SQL Server, as in newer versions some trace flags are enabled by default or have no effect.
Selleks et sisse ja välja lülitada jälgimise lipp, saab kasutada käske DBCC TRACEON ja DBCC TRACEOFF vastavalt. Täiendava teabe saamiseks vaadake siit
Jälgimise lippude oleku saamiseks saab kasutada käsku DBCC TRACESTATUS: täiendavalt
Kuna jälgimise lipud peavad olema MS SQL Serveri teenuse automaatse käivitamise osaks, tuleb siseneda SQL Serveri konfiguratsioonihaldurile ja teenuse omadustes lisada need jälgimise lipud -T: kaudu.

Kokkuvõte

Selles artiklis käsitleti MS SQL Serveri jälgimise mõningaid aspekte, mille abil saab kiiresti tuvastada mälu ja CPU vabade ressursside puudujäägid ning mitmed teised vähem ilmseid probleeme. Ülevaates keskenduti kõige sagedamini kasutatavatele jälgimise lipudele.