ProHoster > Blog > Administratioun > E puer Aspekter vun der MS SQL Server Iwwerwachung. Richtlinnen fir eng Formatioun Trace Fändelen

E puer Aspekter vun der MS SQL Server Iwwerwachung. Richtlinnen fir eng Formatioun Trace Fändelen

Viruerteel

Zimlech dacks, Benotzer, Entwéckler an Administrateuren vun der MS SQL Server DBMS begéinen Performance Problemer vun der Datebank oder der DBMS als Ganzt, sou MS SQL Server Iwwerwachung ass ganz relevant.
Dësen Artikel ass eng Ergänzung zum Artikel Benotzt Zabbix fir MS SQL Server Datebank ze iwwerwaachen an et wäert e puer Aspekter vun der Iwwerwaachung vun der MS SQL Server decken, besonnesch: wéi séier festzestellen, wéi eng Ressourcen fehlen, souwéi Empfehlungen fir Spuerfändelen ze setzen.
Fir déi folgend Skripte ze schaffen, musst Dir en Inf Schema an der gewënschter Datebank erstellen wéi follegt:
En Inf Schema erstellen

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

Methode fir de Mangel u RAM z'entdecken

Den éischten Indikator vum Mangel u RAM ass de Fall wann eng Instanz vum MS SQL Server all de RAM, deen him zougewisen ass, ësst.
Fir dëst ze maachen, wäerte mir déi folgend Representatioun vun inf.vRAM erstellen:
Schafen der inf.vRAM Vue

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;

Da kënnt Dir feststellen datt eng Instanz vum MS SQL Server all d'Erënnerung verbraucht, déi him duerch déi folgend Ufro zougewisen ass:

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

Wann SQL_server_physical_memory_in_use_Mb konsequent méi grouss ass wéi oder gläich wéi SQL_server_committed_target_Mb, da sollt d'Waardestatistik iwwerpréift ginn.
Fir de Mangel u RAM duerch Waartstatistiken ze bestëmmen, loosst eis d'inf.vWaits Vue erstellen:
Schafen der inf.vWaits View

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

An dësem Fall kënnt Dir de Mangel u RAM mat der folgender Ufro bestëmmen:

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

Hei musst Dir op d'Indicateuren Prozentsaz an AvgWait_S oppassen. Wann se an hirer Gesamtheet bedeitend sinn, da gëtt et eng ganz héich Wahrscheinlechkeet datt et net genuch RAM fir d'MS SQL Server Instanz ass. Wichteg Wäerter ginn individuell fir all System festgeluegt. Wéi och ëmmer, Dir kënnt mat de folgenden ufänken: Prozentsaz>=1 an AvgWait_S>=0.005.
Fir Indicateuren an e Iwwerwaachungssystem (zum Beispill Zabbix) auszeginn, kënnt Dir déi folgend zwou Ufroen erstellen:

  1. wéi vill Aarte vu Waarde sinn duerch RAM am Prozentsaz besat (d'Zomm vun all esou Aarte vu Waarden): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. wéivill RAM Warttypen an Millisekonnen daueren (de maximale Wäert vun all duerchschnëttlech Verzögerungen fir all esou Warttypen): 
    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'
  );

Baséierend op der Dynamik vun de kritt Wäerter fir dës zwee Indicateuren, kënne mir schléissen ob et genuch RAM fir eng Instanz vun MS SQL Server ass.

CPU Overload Detection Method

Fir de Mangel u Prozessor Zäit z'identifizéieren, ass et genuch fir d'Sys.dm_os_schedulers System Vue ze benotzen. Hei, wann de runnable_tasks_count stänneg méi grouss ass wéi 1, da gëtt et eng héich Wahrscheinlechkeet datt d'Zuel vun de Cores net genuch ass fir d'MS SQL Server Instanz.
Fir en Indikator an e Iwwerwaachungssystem (zum Beispill Zabbix) auszeginn, kënnt Dir déi folgend Ufro erstellen:

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

Baséierend op der Dynamik vun de kritt Wäerter fir dësen Indikator, kënne mir schléissen ob et genuch Prozessor Zäit (d'Zuel vun CPU Cores) fir eng Instanz vun MS SQL Server ass.
Wéi och ëmmer, et ass wichteg am Kapp ze halen datt Ufroe selwer verschidde Threads gläichzäiteg ufroen kënnen. An heiansdo kann den Optimizer d'Komplexitéit vun der Ufro selwer net korrekt schätzen. Da kann d'Ufro ze vill Threads zougewisen ginn, déi net zur selwechter Zäit zu der bestëmmter Zäit veraarbecht kënne ginn. An dëst verursaacht och eng Aart vu Waart, déi mat engem Mangel u Prozessor Zäit assoziéiert, a Wuesstum vun der Schlaang fir Scheduler déi spezifesch CPU Cores benotzen, dh de runnable_tasks_count Indikator wäert an esou Konditiounen wuessen.
An dësem Fall, ier d'Zuel vun den CPU Cores eropgeet, ass et néideg d'Parallelismuseigenschaften vun der MS SQL Server Instanz selwer richteg ze konfiguréieren, a vun der 2016 Versioun, d'Parallelismuseigenschaften vun den erfuerderlechen Datenbanken korrekt ze konfiguréieren:
E puer Aspekter vun der MS SQL Server Iwwerwachung. Richtlinnen fir eng Formatioun Trace Fändelen

E puer Aspekter vun der MS SQL Server Iwwerwachung. Richtlinnen fir eng Formatioun Trace Fändelen
Hei sollt Dir op déi folgend Parameter oppassen:

  1. Max Degree of Parallelism - setzt d'maximal Unzuel u Threads, déi op all Ufro zougewisen kënne ginn (de Standard ass 0 - limitéiert nëmme vum Betribssystem selwer an d'Editioun vum MS SQL Server)
  2. Käschteschwell fir Parallelismus - geschätzte Käschte vum Parallelismus (Standard ass 5)
  3. Max DOP - setzt déi maximal Unzuel u Threads, déi un all Ufro um Datebankniveau zougewisen kënne ginn (awer net méi wéi de Wäert vun der "Max Degree of Parallelism" Eegeschafte) (Standard ass 0 - limitéiert nëmme vum Betribssystem selwer an d'Editioun vum MS SQL Server, wéi och d'Restriktioun op der "Max Degree of Parallelism" Eegeschafte vun der ganzer Instanz vum MS SQL Server)

Hei ass et onméiglech fir all Fäll e gläich gutt Rezept ze ginn, dh Dir musst schwéier Ufroen analyséieren.
Aus menger eegener Erfahrung recommandéieren ech de folgenden Algorithmus vun Aktiounen fir OLTP Systemer fir Parallelismuseigenschaften opzestellen:

  1. Éischt deaktivéiere Parallelismus andeems Dir den Instanzbreet Max Degree of Parallelism op 1 setzt
  2. analyséiert déi schwéierst Ufroen a wielt déi optimal Unzuel u Threads fir si
  3. Set de Max Degree of Parallelism op déi gewielte optimal Unzuel vun Threads, déi vum Schrëtt 2 kritt goufen, a fir spezifesch Datenbanken de Max DOP-Wäert, dee vum Schrëtt 2 fir all Datebank kritt gëtt, setzen
  4. analyséiert déi schwéierst Ufroen an identifizéieren den negativen Effekt vu Multithreading. Wann et ass, da erhéicht de Käschteschwell fir Parallelismus.
    Fir Systemer wéi 1C, Microsoft CRM a Microsoft NAV ass an de meeschte Fäll d'Verbuet vu Multithreading gëeegent

Och, wann et eng Standard Editioun ass, ass am meeschte Fäll de Verbuet vun Multithreading gëeegent wéinst der Tatsaach, datt dës Editioun an der Zuel vun CPU Cores limitéiert ass.
Fir OLAP Systemer ass den uewen beschriwwenen Algorithmus net gëeegent.
Aus menger eegener Erfahrung recommandéieren ech de folgenden Algorithmus vun Aktiounen fir OLAP Systemer fir Parallelismuseigenschaften opzestellen:

  1. analyséiert déi schwéierst Ufroen a wielt déi optimal Unzuel u Threads fir si
  2. Set de Max Degree of Parallelism op déi gewielte optimal Unzuel vun Threads, déi vum Schrëtt 1 kritt goufen, a fir spezifesch Datenbanken de Max DOP-Wäert, dee vum Schrëtt 1 fir all Datebank kritt gëtt, setzen
  3. analyséiert déi schwéierst Ufroen an identifizéieren den negativen Effekt vun der Limitatioun vun der Zoustëmmung. Wann et ass, senken entweder d'Käschteschwell fir Parallelismus Wäert, oder widderhuelen d'Schrëtt 1-2 vun dësem Algorithmus

Dat ass, fir OLTP Systemer gi mir vun Single-threading op Multi-threading, a fir OLAP-Systemer, am Géigendeel, gi mir vu Multi-threading op Single-threading. Also kënnt Dir déi optimal Parallelismus Astellunge fir béid eng spezifesch Datebank an déi ganz Instanz vum MS SQL Server wielen.
Et ass och wichteg ze verstoen datt d'Astellunge vun de Parallelismus Eegeschafte mat der Zäit geännert musse ginn, baséiert op d'Resultater vun der Iwwerwaachung vun der Leeschtung vum MS SQL Server.

Richtlinnen fir eng Formatioun Trace Fändelen

Aus menger eegener Erfahrung an der Erfahrung vu menge Kollegen, fir eng optimal Leeschtung, recommandéieren ech déi folgend Trace Fändelen um Runniveau vum MS SQL Server Service fir Versiounen 2008-2016 ze setzen:

  1. 610 - Reduzéiert Logged vun Inserts an indexéiert Dëscher. Kann hëllefen mat Inserts an Dëscher mat villen Opzeechnungen a villen Transaktiounen, mat heefeg laange WRITELOG Waarden op Ännerungen an Indexen
  2. 1117 - Wann e Fichier an enger Dateiegrupp den Autogrowth-Schwellfuerderunge entsprécht, wuessen all Dateien an der Dateiegrupp
  3. 1118 - Forcéiert all Objeten a verschiddene Ausmooss ze lokaliséieren (Verbuet vu gemëschten Ausmooss), wat d'Noutwendegkeet miniméiert fir d'SGAM Säit ze scannen, déi benotzt gëtt fir gemëschten Ausmooss ze verfolgen
  4. 1224 - Desaktivéiert Spär Eskalatioun baséiert op der Unzuel vun de Spären. Wéi och ëmmer, exzessiv Erënnerungsverbrauch kann d'Späreskalatioun ausléisen
  5. 2371 - Ännert de fixen automatesche Statistikaktualiséierungsschwell op déi dynamesch automatesch Statistik Updateschwell. Wichteg fir d'Aktualiséierung vu Query Pläng fir grouss Dëscher, wou eng falsch Unzuel vun records zu falschen Ausféierungspläng resultéiert
  6. 3226 - Ënnerdréckt Backupsatellit Messagen am Feeler Log
  7. 4199 - Enthält Ännerungen am Ufrooptimizer verëffentlecht an CUs a SQL Server Service Packs
  8. 6532-6534 - Ëmfaasst Leeschtungsverbesserunge fir Ufrooperatiounen op raimlech Datentypen
  9. 8048 - Konvertéiert NUMA partitionéiert Erënnerungsobjekter op CPU partitionéiert
  10. 8780 - Erlaabt zousätzlech Zäitallokatioun fir Ufroplanung. E puer Ufroe ouni dëse Fändel kënne refuséiert ginn well se keen Ufroplang hunn (ganz rar Feeler)
  11. 8780 - 9389 - Aktivéiert zousätzlech dynamesch Grant Memory Buffer fir Batch Modus Aussoen, wat de Batch Modus Bedreiwer erlaabt zousätzlech Erënnerung ze froen an ze vermeiden datt Daten op tempdb verschécken wann zousätzlech Erënnerung verfügbar ass

Och virum 2016 ass et nëtzlech fir Trace Fändel 2301 z'aktivéieren, wat verbessert Entscheedungsunterstëtzungsoptimiséierungen erméiglecht an doduerch hëlleft méi korrekt Ufropläng ze wielen. Wéi och ëmmer, wéi vun der Versioun 2016, huet et dacks en negativen Effekt op zimlech laang allgemeng Ufro Ausféierungszäiten.
Och, fir Systemer mat vill Index (zum Beispill, fir 1C Datenbanken), Ech recommandéieren Spuer Fändel 2330 z'aktivéieren, déi Kollektioun vun Index Benotzen auszeschalten, déi allgemeng e positiven Effekt op de System huet.
Fir méi Informatiounen iwwert Spuer Fändelen, gesinn hei
Vum Link hei uewen ass et och wichteg Versiounen a Builds vum MS SQL Server ze berücksichtegen, well fir méi nei Versioune sinn e puer Trace Fändelen par défaut aktivéiert oder hunn keen Effekt.
Dir kënnt de Trace Fändel un an ausschalten mat den DBCC TRACEON an DBCC TRACEOFF Kommandoen, respektiv. Fir méi Detailer kuckt hei
Dir kënnt de Status vun den Trace Fändelen mat dem DBCC TRACESTATUS Kommando kréien: méi gewuer ze ginn
Fir datt Trace Fändelen am Autostart vum MS SQL Server Service abegraff sinn, musst Dir op SQL Server Configuration Manager goen an dës Trace Fändelen iwwer -T an de Service Properties addéieren:
E puer Aspekter vun der MS SQL Server Iwwerwachung. Richtlinnen fir eng Formatioun Trace Fändelen

Resultater

An dësem Artikel goufen e puer Aspekter vun der Iwwerwachung vum MS SQL Server analyséiert, mat der Hëllef vun deem Dir séier de Mangel u RAM a gratis CPU-Zäit identifizéieren, wéi och eng Rei aner manner evident Probleemer. Déi meescht benotzt Trace Fändelen goufen iwwerpréift.

Quell:

» SQL Server wait Statistiken
» SQL Server wait Statistiken oder sot mir weg wou et wéideet
» System Vue sys.dm_os_schedulers
» Benotzt Zabbix fir MS SQL Server Datebank ze iwwerwaachen
» SQL Liewensstil
» Trace Fändelen
» sql.ru

Source: will.com

Setzt e Commentaire