🥇Vissa aspekter av övervakning av MS SQL Server. Rekommendationer för inställning av spårningsflaggor

Förord

Användare, utvecklare och administratörer av MS SQL Server-databaser stöter ofta på prestandaproblem med databas eller databashanteringssystem som helhet, vilket gör att övervakning av MS SQL Server är mycket aktuellt.
Denna artikel är ett komplement till artikeln Användningen av Zabbix för övervakning av databasen MS SQL Server och den kommer att ta upp vissa aspekter av övervakning av MS SQL Server, särskilt: hur man snabbt identifierar vilka resurser som saknas, samt rekommendationer för att konfigurera spårningsflaggor.
För att köra de följande skripten är det nödvändigt att skapa schemat inf i den önskade databasen på följande sätt:
Skapa schemat inf

använd <databasenamn>;
go
skapa schema inf;

Metod för att identifiera brist på RAM

En första indikator på brist på RAM är det fall där instansen av MS SQL Server använder all tilldelad RAM.
För detta ändamål ska vi skapa följande vy inf.vRAM:
Skapa vyn inf.vRAM

SKAPA vy [inf].[vRAM] som
välj a.[TotalAvailOSRam_Mb]						-- hur mycket ledigt RAM finns på servern i MB
		 , a.[RAM_Avail_Percent]					-- procent ledigt RAM på servern
		 , a.[Server_physical_memory_Mb]				-- hur mycket totalt RAM finns på servern i MB
		 , a.[SQL_server_committed_target_Mb]			-- hur mycket totalt RAM som har tilldelats till MS SQL Server i MB
		 , a.[SQL_server_physical_memory_in_use_Mb] 		-- hur mycket RAM som MS SQL Server använder för tillfället i MB
		 , a.[SQL_RAM_Avail_Percent]				-- procent ledigt RAM för MS SQL Server i förhållande till hela det tilldelade RAM för MS SQL Server
		 , a.[StateMemorySQL]						-- är RAM tillräckligt för MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]				-- procent reserverat RAM för MS SQL Server i förhållande till hela RAM på servern
		 -- är RAM tillräckligt för servern
		, (fall när a.[RAM_Avail_Percent]<10 och a.[RAM_Avail_Percent]>5 och a.[TotalAvailOSRam_Mb]<8192 då 'Varning' när a.[RAM_Avail_Percent]<=5 och a.[TotalAvailOSRam_Mb]<2048 då 'Risk' annars 'Normal' slut) som [StateMemoryServer]
	från
	(
		välj kasta(a0.available_physical_memory_kb/1024.0 som int) som TotalAvailOSRam_Mb
			 , kasta((a0.available_physical_memory_kb/casT(a0.total_physical_memory_kb som float))*100 som numerisk(5,2)) som [RAM_Avail_Percent]
			 , a0.system_low_memory_signal_state
			 , tak(b.physical_memory_kb/1024.0) som [Server_physical_memory_Mb]
			 , tak(b.committed_target_kb/1024.0) som [SQL_server_committed_target_Mb]
			 , tak(a.physical_memory_in_use_kb/1024.0) som [SQL_server_physical_memory_in_use_Mb]
			 , kasta(((b.committed_target_kb-a.physical_memory_in_use_kb)/casT(b.committed_target_kb som float))*100 som numerisk(5,2)) som [SQL_RAM_Avail_Percent]
			 , kasta((b.committed_target_kb/casT(a0.total_physical_memory_kb som float))*100 som numerisk(5,2)) som [SQL_RAM_Reserve_Percent]
			 , (fall när (tak(b.committed_target_kb/1024.0)-1024)<tak(a.physical_memory_in_use_kb/1024.0) då 'Varning' annars 'Normal' slut) som [StateMemorySQL]
		från sys.dm_os_sys_memory som a0
		korskoppla sys.dm_os_process_memory som a
		korskoppla sys.dm_os_sys_info som b
		korskoppla sys.dm_os_sys_memory som v
	) som a;

Då kan vi fastställa att instansen av MS SQL Server konsumerar hela den tilldelade minnet med följande fråga:

välj  SQL_server_physical_memory_in_use_Mb,  SQL_server_committed_target_Mb
från [inf].[vRAM];

Om indikatorn SQL_server_physical_memory_in_use_Mb konstant är mindre än SQL_server_committed_target_Mb, bör vi kontrollera väntestatistiken.
För att avgöra bristen på RAM genom väntestatistik skapar vi vyn inf.vWaits:
Skapa vyn inf.vWaits

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --namn på väntetyp
        [wait_time_ms] / 1000.0 AS [WaitS],--Total väntetid för denna typ i millisekunder. Denna tid inkluderar signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Total väntetid för denna typ i millisekunder utan signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Skillnaden mellan signaleringstiden för den väntande tråden och tiden för dess påbörjande
        [waiting_tasks_count] AS [WaitCount],--Antal väntningar för denna typ. Denna räknare ökar varje gång en väntan inleds
        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],--Total väntetid för denna typ i millisekunder. Denna tid inkluderar signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Total väntetid för denna typ i millisekunder utan signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Skillnaden mellan signaleringstiden för den väntande tråden och tiden för dess påbörjande
	    [W1].[WaitCount] AS [WaitCount],--Antal väntningar för denna typ. Denna räknare ökar varje gång en väntan inleds
	    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 -- procentgräns
)
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];

I det här fallet kan bristen på RAM bestämmas med följande fråga:

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

Här bör man vara uppmärksam på värdena Percentage och AvgWait_S. Om de är betydande i sin helhet finns det en hög sannolikhet för att RAM inte räcker till för MS SQL Server-instansen. Betydande värden bestäms individuellt för varje system. Man kan dock börja med följande värden: Percentage>=1 och AvgWait_S>=0.005.
För att visa värdena i övervakningssystemet (till exempel Zabbix) kan följande två frågor skapas:

hur mycket i procent som väntetyperna för RAM upptar (summan av alla sådana väntetyper):

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

hur mycket i millisekunder som väntetyperna för RAM upptar (det maximala värdet av alla genomsnittliga förseningar för alla sådana väntetyper):

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

Baserat på dynamiken i de erhållna värdena för dessa två indikatorer kan man dra slutsatsen om RAM räcker till för MS SQL Server-instansen.

Metod för att upptäcka överbelastning på CPU

För att identifiera bristen på processor-tid är det tillräckligt att använda systemvyn sys.dm_os_schedulers. Här, om värdet på runnable_tasks_count konstant är större än 1, finns det en stor sannolikhet för att antalet kärnor inte räcker för MS SQL Server-instansen.
För att visa värdet i övervakningssystemet (till exempel Zabbix) kan följande fråga skapas:

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

Baserat på dynamiken i de erhållna värdena för denna indikator kan man dra slutsatsen om processor-tid (antal CPU-kärnor) räcker till för MS SQL Server-instansen.
Det är dock viktigt att komma ihåg att själva förfrågningarna kan begära flera trådar samtidigt. Ibland kan optimeraren inte korrekt bedöma komplexiteten hos själva förfrågan. Då kan förfrågningen tilldelas för många trådar som inte kan bearbetas samtidigt vid den tidpunkten. Detta leder också till en typ av väntan, kopplad till bristen på processorcykler och växande köer på schemaläggarna som använder specifika CPU-kärnor, vilket innebär att indikatorn runnable_tasks_count under dessa förhållanden kommer att öka.
I sådana fall är det nödvändigt att korrekt konfigurera parallellismens egenskaper för instansen av MS SQL Server innan antalet CPU-kärnor ökas, och från version 2016 måste man korrekt konfigurera parallellismens egenskaper för de nödvändiga databaserna:

Här bör man uppmärksamma följande parametrar:

Max Degree of Parallelism - bestämmer maximalt antal trådar som kan tilldelas varje förfrågan (standardvärdet är 0 - begränsat av operativsystemet och versionen av MS SQL Server)
Cost Threshold for Parallelism - bedömningskostnaden för parallellism (standardvärdet är 5)
Max DOP - bestämmer maximalt antal trådar som kan tilldelas varje förfrågan på databasnivå (men inte mer än värdet för "Max Degree of Parallelism") (standardvärdet är 0 - begränsat av operativsystemet och versionen av MS SQL Server, samt begränsningen för egenskapen "Max Degree of Parallelism" för hela instansen av MS SQL Server)

Här går det inte att ge ett lika bra recept för alla fall, det vill säga man måste analysera tunga förfrågningar.
Utifrån egen erfarenhet rekommenderar jag följande handlingsplan för OLTP-system för att konfigurera parallellismens egenskaper:

först förbjuda parallellism genom att ställa in Max Degree of Parallelism på 1 för hela instansen
analysera de tyngsta förfrågningarna och välja det optimala antalet trådar för dem
ställ in Max Degree of Parallelism till det optimala antalet trådar som erhållits från punkt 2, samt ställ in Max DOP-värdet för specifika databaser som erhållits från punkt 2 för varje databas
analysera de tyngsta förfrågningarna och identifiera den negativa effekten av flertrådighet. Om det finns en negativ effekt, öka Cost Threshold for Parallelism.
För system som 1C, Microsoft CRM och Microsoft NAV passar det oftast att förbjuda flertrådighet.

Även om Standard-redigeringen är aktiverad, gäller det i de flesta fall att avaktivera multitrådad bearbetning på grund av att denna redigering är begränsad i antal CPU-kärnor.
För OLAP-system passar inte den ovan beskrivna algoritmen.
Enligt egen erfarenhet rekommenderar jag följande handlingsplan för OLAP-system för att ställa in inställningarna för parallellism:

analysera de tyngsta förfrågningarna och välja det optimala antalet trådar för dem
Ställ in Max Degree of Parallelism till det optimala antalet trådar, som erhållits från punkt 1, samt för specifika databaser ställ in Max DOP-värdet, som erhållits från punkt 1, för varje databas.
Analysera de mest resurskrävande frågorna och identifiera den negativa effekten av att begränsa parallellism. Om det finns en negativ effekt, antingen sänka värdet av Cost Threshold for Parallelism eller upprepa steg 1-2 i denna algoritm.

Det vill säga för OLTP-system går vi från entrådad bearbetning till multitrådad, medan vi i OLAP-system går från multitrådad bearbetning till entrådad. På så sätt kan vi optimera inställningarna för parallellism både för specifika databaser och för hela MS SQL Server-instansen.
Det är också viktigt att förstå att inställningarna för parallellismens egenskaper måste ändras över tid, baserat på resultaten av prestandamonitoreringen av MS SQL Server.

Rekommendationer för inställning av spårningsflaggor

Utifrån min egen erfarenhet och mina kollegors rekommenderar jag att ange följande spårningsflaggor vid start av MS SQL Server-tjänsten för versionerna 2008-2016:

610 - Minskar loggningen av insättningar i indexerade tabeller. Kan vara till hjälp för insättningar i tabeller med ett stort antal poster och många transaktioner, vid frekventa långa väntetider för WRITELOG på grund av förändringar i index.
1117 - Om en fil i filgruppen uppfyller kraven för automatisk ökning, ökas alla filer i filgruppen.
1118 - Tvingar alla objekt att placeras i olika extenter (förbud mot blandade extenter), vilket minimerar behovet av att skanna SGAM-sidan, som används för att spåra blandade extenter.
1224 - Inaktiverar uppgruppering av lås baserat på antalet lås. Men om minnet används för aktivt kan uppgruppering av lås aktiveras.
2371 - Ändrar tröskeln för fast automatisk uppdatering av statistik till tröskeln för dynamisk automatisk uppdatering av statistik. Viktigt för uppdatering av frågeplaner relaterade till stora tabeller, där felaktig bestämning av antalet poster leder till felaktiga exekveringsplaner.
3226 - Suppresserar meddelanden om lyckad säkerhetskopiering i felloggen.
4199 - Aktiverar ändringar i frågeoptimeraren som släpptes i kumulativa uppdateringspaket och uppdateringspaket för SQL Server.
6532-6534 - Aktiverar prestandaförbättringar för frågeoperationer med rumsliga datatyper.
8048 - Omvandlar NUMA-delade minnesobjekt till CPU-delade.
8780 - Aktiverar ytterligare tidsallokering för att kompilera frågeplaner. Vissa frågor utan denna flagga kan avvisas, eftersom de saknar en frågeplan (mycket sällsynt fel).
8780 - 9389 - Aktiverar en ytterligare dynamisk temporärt tilldelad minnesbuffert för batch-operatorer, vilket gör det möjligt för batch-operatorer att begära ytterligare minne och undvika att data flyttas till tempdb, om ytterligare minne är tillgängligt.

Även fram till version 2016 är det bra att aktivera spårningsflaggan 2301, som aktiverar optimering av utökad beslutsstöd och därigenom hjälper till med valet av mer korrekta frågeplaner. Men från version 2016 kan det ofta ha en negativ inverkan på den totala exekveringstiden för frågor.
För system med många index (t.ex. för 1C-databaser) rekommenderar jag också att aktivera spårningsflaggan 2330, som inaktiverar insamling av indexanvändning, vilket generellt har en positiv effekt på systemet.
För mer information om spårningsflaggor kan du läsa mer. här
Vid den angivna länken är det också viktigt att ta hänsyn till versioner och byggen av MS SQL Server, eftersom vissa spårningsflaggor är aktiverade som standard eller inte har någon effekt i nyare versioner.
Spårningsflaggor kan aktiveras och inaktiveras med hjälp av kommandona DBCC TRACEON och DBCC TRACEOFF respektive. Se mer detaljer. här
Du kan få status på spårningsflaggorna med kommandot DBCC TRACESTATUS: mer information
För att spårningsflaggor ska aktiveras för automatisk start av tjänsten MS SQL Server måste man gå till SQL Server Configuration Manager och i tjänstens egenskaper lägga till dessa spårningsflaggor via -T:

Sammanfattning

I denna artikel har vi diskuterat vissa aspekter av övervakning av MS SQL Server, med vars hjälp man snabbt kan identifiera brist på RAM och tillgänglig CPU-tid samt andra mindre uppenbara problem. Vi har även gått igenom de mest använda spårningsflaggorna.