ProHoster > Log > administrasjon > Noen aspekter ved MS SQL Server-overvåking. Retningslinjer for innstilling av sporingsflagg

Noen aspekter ved MS SQL Server-overvåking. Retningslinjer for innstilling av sporingsflagg

Forord

Ganske ofte møter brukere, utviklere og administratorer av MS SQL Server DBMS ytelsesproblemer for databasen eller DBMS som helhet, så MS SQL Server-overvåking er veldig relevant.
Denne artikkelen er et tillegg til artikkelen Bruke Zabbix til å overvåke MS SQL Server-databasen og den vil dekke noen aspekter ved overvåking av MS SQL Server, spesielt: hvordan du raskt kan finne ut hvilke ressurser som mangler, samt anbefalinger for å sette sporingsflagg.
For at følgende skript skal fungere, må du opprette et inf-skjema i ønsket database som følger:
Opprette et infoskjema

use <имя_БД>;
go
create schema inf;

Metode for å oppdage mangel på RAM

Den første indikatoren på mangel på RAM er tilfellet når en forekomst av MS SQL Server spiser opp all RAM som er allokert til den.
For å gjøre dette vil vi lage følgende representasjon av inf.vRAM:
Opprette inf.vRAM-visningen

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;

Deretter kan du fastslå at en forekomst av MS SQL Server bruker alt minnet som er tildelt den ved følgende spørring:

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

Hvis SQL_server_physical_memory_in_use_Mb konsekvent er større enn eller lik SQL_server_committed_target_Mb, bør ventestatistikken sjekkes.
For å fastslå mangelen på RAM gjennom ventestatistikk, la oss lage inf.vWaits-visningen:
Opprette inf.vWaits-visningen

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];

I dette tilfellet kan du fastslå mangelen på RAM med følgende spørring:

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

Her må du ta hensyn til indikatorene Prosent og AvgWait_S. Hvis de er betydelige i sin helhet, er det en svært stor sannsynlighet for at det ikke er nok RAM for MS SQL Server-forekomsten. Signifikante verdier bestemmes individuelt for hvert system. Du kan imidlertid starte med følgende: Prosent>=1 og AvgWait_S>=0.005.
For å sende ut indikatorer til et overvåkingssystem (for eksempel Zabbix), kan du opprette følgende to spørringer:

  1. hvor mange typer ventetider er opptatt av RAM i prosent (summen av alle slike typer ventetider): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. hvor mange RAM-ventetyper tar i millisekunder (maksimumsverdien av alle gjennomsnittlige forsinkelser for alle slike ventetyper): 
    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'
  );

Basert på dynamikken til de oppnådde verdiene for disse to indikatorene, kan vi konkludere om det er nok RAM for en forekomst av MS SQL Server.

Deteksjonsmetode for CPU-overbelastning

For å identifisere mangelen på prosessortid er det nok å bruke systemvisningen sys.dm_os_schedulers. Her, hvis runnable_tasks_count er konstant større enn 1, er det stor sannsynlighet for at antall kjerner ikke er nok for MS SQL Server-forekomsten.
For å sende ut en indikator til et overvåkingssystem (for eksempel Zabbix), kan du opprette følgende spørring:

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

Basert på dynamikken til de oppnådde verdiene for denne indikatoren, kan vi konkludere om det er nok prosessortid (antall CPU-kjerner) for en forekomst av MS SQL Server.
Det er imidlertid viktig å huske på at forespørsler i seg selv kan be om flere tråder samtidig. Og noen ganger kan ikke optimalisereren estimere kompleksiteten til selve spørringen. Da kan forespørselen bli tildelt for mange tråder som ikke kan behandles samtidig på det gitte tidspunktet. Og dette forårsaker også en type venting assosiert med mangel på prosessortid, og vekst i køen for planleggere som bruker spesifikke CPU-kjerner, det vil si at runnable_tasks_count-indikatoren vil vokse under slike forhold.
I dette tilfellet, før du øker antallet CPU-kjerner, er det nødvendig å konfigurere parallellitetsegenskapene til selve MS SQL Server-forekomsten riktig, og fra 2016-versjonen konfigurere parallellitetsegenskapene til de nødvendige databasene riktig:
Noen aspekter ved MS SQL Server-overvåking. Retningslinjer for innstilling av sporingsflagg

Noen aspekter ved MS SQL Server-overvåking. Retningslinjer for innstilling av sporingsflagg
Her bør du være oppmerksom på følgende parametere:

  1. Max Degree of Parallelism - angir maksimalt antall tråder som kan tildeles hver forespørsel (standard er 0 - begrenset kun av selve operativsystemet og utgaven av MS SQL Server)
  2. Kostnadsterskel for parallellisme - estimert kostnad for parallellisme (standard er 5)
  3. Maks DOP - angir maksimalt antall tråder som kan tildeles hver spørring på databasenivå (men ikke mer enn verdien av egenskapen "Max Degree of Parallelism") (standard er 0 - begrenset kun av selve operativsystemet og utgaven av MS SQL Server, samt begrensningen på egenskapen "Max Degree of Parallelism" for hele forekomsten av MS SQL Server)

Her er det umulig å gi en like god oppskrift for alle saker, det vil si at du må analysere tunge spørsmål.
Fra min egen erfaring anbefaler jeg følgende handlingsalgoritme for OLTP-systemer for å sette opp parallellitetsegenskaper:

  1. deaktiver først parallellisme ved å sette den instansomfattende Max Degree of Parallelism til 1
  2. analyser de tyngste forespørslene og velg det optimale antallet tråder for dem
  3. angi Max Degree of Parallelism til det valgte optimale antallet tråder hentet fra trinn 2, og for spesifikke databaser angi Max DOP-verdien hentet fra trinn 2 for hver database
  4. analysere de tyngste forespørslene og identifisere den negative effekten av multithreading. Hvis det er det, øk kostnadsterskelen for parallellisme.
    For systemer som 1C, Microsoft CRM og Microsoft NAV er det i de fleste tilfeller egnet å forby multithreading

Også, hvis det er en standardutgave, er i de fleste tilfeller forbudet mot multithreading egnet på grunn av det faktum at denne utgaven er begrenset i antall CPU-kjerner.
For OLAP-systemer er algoritmen beskrevet ovenfor ikke egnet.
Fra min egen erfaring anbefaler jeg følgende handlingsalgoritme for OLAP-systemer for å sette opp parallellitetsegenskaper:

  1. analyser de tyngste forespørslene og velg det optimale antallet tråder for dem
  2. angi Max Degree of Parallelism til det valgte optimale antallet tråder hentet fra trinn 1, og for spesifikke databaser angi Max DOP-verdien hentet fra trinn 1 for hver database
  3. analysere de tyngste spørsmålene og identifisere den negative effekten av å begrense samtidighet. Hvis det er det, kan du enten senke kostnadsterskelen for parallellisme, eller gjenta trinn 1-2 i denne algoritmen

Det vil si at for OLTP-systemer går vi fra enkelt-tråding til flertråding, og for OLAP-systemer går vi tvert imot fra flertråding til enkelttråding. Dermed kan du velge de optimale parallellitetsinnstillingene for både en spesifikk database og hele forekomsten av MS SQL Server.
Det er også viktig å forstå at innstillingene for parallellitetsegenskapene må endres over tid, basert på resultatene av overvåking av ytelsen til MS SQL Server.

Retningslinjer for innstilling av sporingsflagg

Fra min egen erfaring og erfaringen til mine kolleger, for optimal ytelse, anbefaler jeg å sette følgende sporingsflagg på kjørenivået til MS SQL Server-tjenesten for versjoner 2008-2016:

  1. 610 - Redusert logging av innlegg i indekserte tabeller. Kan hjelpe med innsettinger i tabeller med mange poster og mange transaksjoner, med hyppige lange WRITELOG-ventinger på endringer i indekser
  2. 1117 - Hvis en fil i en filgruppe oppfyller kravene til autovekstterskel, vokser alle filene i filgruppen
  3. 1118 - Tvinger alle objekter til å bli lokalisert i forskjellige utstrekninger (forbud mot blandede utstrekninger), noe som minimerer behovet for å skanne SGAM-siden, som brukes til å spore blandede utstrekninger
  4. 1224 - Deaktiverer låseskalering basert på antall låser. Imidlertid kan overdreven minnebruk utløse låseskalering
  5. 2371 - Endrer den faste terskelen for automatisk statistikkoppdatering til den dynamiske terskelen for automatisk statistikkoppdatering. Viktig for oppdatering av spørringsplaner for store tabeller, der feil opptelling av poster resulterer i feilaktige utførelsesplaner
  6. 3226 - Undertrykker meldinger om suksess for sikkerhetskopiering i feilloggen
  7. 4199 - Inkluderer endringer i spørringsoptimereren utgitt i CUer og SQL Server Service Packs
  8. 6532-6534 - Inkluderer ytelsesforbedringer for spørringsoperasjoner på romlige datatyper
  9. 8048 - Konverterer NUMA-partisjonerte minneobjekter til CPU-partisjonerte
  10. 8780 - Muliggjør ekstra tidsallokering for spørringsplanlegging. Noen forespørsler uten dette flagget kan bli avvist fordi de ikke har en spørreplan (svært sjelden feil)
  11. 8780 - 9389 - Aktiverer ekstra dynamisk tildelingsminnebuffer for batchmodusuttalelser, som lar batchmodusoperatøren be om ekstra minne og unngå å flytte data til tempdb hvis ekstra minne er tilgjengelig

Også før 2016 er det nyttig å aktivere sporingsflagg 2301, som muliggjør forbedret beslutningsstøtteoptimalisering og dermed hjelper til med å velge riktigere spørringsplaner. Fra og med versjon 2016 har det imidlertid ofte en negativ effekt på ganske lange generelle spørringskjøringstider.
For systemer med mange indekser (for eksempel for 1C-databaser), anbefaler jeg også å aktivere sporingsflagg 2330, som deaktiverer innsamling av indeksbruk, noe som generelt har en positiv effekt på systemet.
For mer informasjon om sporingsflagg, se her
Fra lenken ovenfor er det også viktig å vurdere versjoner og bygg av MS SQL Server, siden for nyere versjoner er noen sporingsflagg aktivert som standard eller har ingen effekt.
Du kan slå sporingsflagget på og av med henholdsvis DBCC TRACEON og DBCC TRACEOFF kommandoene. For flere detaljer se her
Du kan få statusen til sporingsflaggene ved å bruke DBCC TRACESTATUS-kommandoen: mer
For at sporingsflagg skal inkluderes i autostarten av MS SQL Server-tjenesten, må du gå til SQL Server Configuration Manager og legge til disse sporingsflaggene via -T i tjenesteegenskapene:
Noen aspekter ved MS SQL Server-overvåking. Retningslinjer for innstilling av sporingsflagg

Resultater av

I denne artikkelen ble noen aspekter ved overvåking av MS SQL Server analysert, ved hjelp av disse kan du raskt identifisere mangelen på RAM og ledig CPU-tid, samt en rekke andre mindre åpenbare problemer. De mest brukte sporingsflaggene er gjennomgått.

Kilder:

» SQL Server ventestatistikk
» SQL Server ventestatistikk eller fortell meg hvor det gjør vondt
» Systemvisning sys.dm_os_schedulers
» Bruke Zabbix til å overvåke MS SQL Server-databasen
» SQL livsstil
» Spor flagg
» sql.ru

Kilde: www.habr.com

Legg til en kommentar