ProHoster > Blog > administrasie > Sommige aspekte van MS SQL Server-monitering. Riglyne vir die opstel van spoorvlae

Sommige aspekte van MS SQL Server-monitering. Riglyne vir die opstel van spoorvlae

voorwoord

Gebruikers, ontwikkelaars en administrateurs van die MS SQL Server DBMS ondervind dikwels prestasieprobleme van die databasis of die DBBS as 'n geheel, so MS SQL Server monitering is baie relevant.
Hierdie artikel is 'n toevoeging tot die artikel Gebruik Zabbix om MS SQL Server-databasis te monitor en dit sal 'n paar aspekte van die monitering van MS SQL Server dek, veral: hoe om vinnig te bepaal watter hulpbronne ontbreek, sowel as aanbevelings vir die opstel van spoorvlae.
Vir die volgende skrifte om te werk, moet jy 'n inf-skema in die verlangde databasis soos volg skep:
Skep 'n inf-skema

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

Metode om 'n gebrek aan RAM op te spoor

Die eerste aanduiding van die gebrek aan RAM is die geval wanneer 'n instansie van MS SQL Server al die RAM wat daaraan toegewys is, opvreet.
Om dit te doen, sal ons die volgende voorstelling van inf.vRAM skep:
Die skep van die inf.vRAM-aansig

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;

Dan kan jy bepaal dat 'n instansie van MS SQL Server al die geheue verbruik wat daaraan toegeken is deur die volgende navraag:

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

As SQL_server_physical_memory_in_use_Mb konsekwent groter as of gelyk is aan SQL_server_committed_target_Mb, dan moet die wagstatistieke nagegaan word.
Om die gebrek aan RAM deur wagstatistieke te bepaal, kom ons skep die inf.vWaits-aansig:
Die skep van die inf.vWaits-aansig

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

In hierdie geval kan u die gebrek aan RAM bepaal met die volgende navraag:

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

Hier moet jy aandag gee aan die aanwysers Persentasie en AvgWait_S. As hulle beduidend in hul totaliteit is, dan is daar 'n baie hoë waarskynlikheid dat daar nie genoeg RAM vir die MS SQL Server-instansie is nie. Beduidende waardes word individueel vir elke stelsel bepaal. Jy kan egter met die volgende begin: Persentasie>=1 en AvgWait_S>=0.005.
Om aanwysers na 'n moniteringstelsel (byvoorbeeld Zabbix) uit te voer, kan jy die volgende twee navrae skep:

  1. hoeveel tipes wagte word in persentasie deur RAM beset (die som van al sulke tipes wagte): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. hoeveel RAM-wagtipes in millisekondes neem (die maksimum waarde van alle gemiddelde vertragings vir al sulke wagtipes): 
    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'
  );

Gebaseer op die dinamika van die verkryde waardes vir hierdie twee aanwysers, kan ons aflei of daar genoeg RAM is vir 'n geval van MS SQL Server.

SVE oorlading opsporing metode

Om die gebrek aan verwerkertyd te identifiseer, is dit genoeg om die sys.dm_os_schedulers-stelselaansig te gebruik. Hier, as die runnable_tasks_count voortdurend groter as 1 is, dan is daar 'n hoë waarskynlikheid dat die aantal kerns nie genoeg is vir die MS SQL Server-instansie nie.
Om 'n aanwyser na 'n moniteringstelsel (byvoorbeeld Zabbix) uit te voer, kan jy die volgende navraag skep:

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

Gebaseer op die dinamika van die verkrygde waardes vir hierdie aanwyser, kan ons aflei of daar genoeg verwerkertyd (die aantal SVE-kerns) is vir 'n geval van MS SQL Server.
Dit is egter belangrik om die feit in gedagte te hou dat versoeke self verskeie drade tegelyk kan aanvra. En soms kan die optimaliseerder nie die kompleksiteit van die navraag self korrek skat nie. Dan kan die versoek te veel drade toegeken word wat nie op dieselfde tyd op die gegewe tyd verwerk kan word nie. En dit veroorsaak ook 'n tipe wag wat verband hou met 'n gebrek aan verwerker tyd, en groei van die tou vir skeduleerders wat spesifieke SVE kerns gebruik, dit wil sê die runnable_tasks_count aanwyser sal groei in sulke toestande.
In hierdie geval, voordat die aantal SVE-kerns verhoog word, is dit nodig om die parallellisme-eienskappe van die MS SQL Server-instansie self korrek op te stel, en vanaf die 2016-weergawe, die parallelisme-eienskappe van die vereiste databasisse korrek op te stel:
Sommige aspekte van MS SQL Server-monitering. Riglyne vir die opstel van spoorvlae

Sommige aspekte van MS SQL Server-monitering. Riglyne vir die opstel van spoorvlae
Hier moet u aandag gee aan die volgende parameters:

  1. Max Degree of Parallelism - stel die maksimum aantal drade wat aan elke versoek toegeken kan word (die verstek is 0 - beperk slegs deur die bedryfstelsel self en die uitgawe van MS SQL Server)
  2. Kostedrempel vir parallelisme - beraamde koste van parallelisme (verstek is 5)
  3. Max DOP - stel die maksimum aantal drade wat aan elke navraag op die databasisvlak toegewys kan word (maar nie meer as die waarde van die "Max Degree of Parallelism"-eienskap nie) (verstek is 0 - beperk slegs deur die bedryfstelsel self en die uitgawe van MS SQL Server, sowel as die beperking op die "Max Degree of Parallelism"-eienskap van die hele instansie van MS SQL Server)

Hier is dit onmoontlik om 'n ewe goeie resep vir alle gevalle te gee, dit wil sê jy moet swaar navrae ontleed.
Uit my eie ervaring beveel ek die volgende algoritme van aksies aan vir OLTP-stelsels vir die opstel van parallellisme-eienskappe:

  1. deaktiveer eers parallelisme deur die instansiewye Max Degree of Parallelism op 1 te stel
  2. ontleed die swaarste versoeke en kies die optimale aantal drade daarvoor
  3. stel die Max Degree of Parallelism op die geselekteerde optimale aantal drade verkry vanaf stap 2, en stel vir spesifieke databasisse die Max DOP-waarde verkry vanaf stap 2 vir elke databasis
  4. ontleed die swaarste versoeke en identifiseer die negatiewe effek van multithreading. Indien wel, verhoog dan die kostedrempel vir parallelisme.
    Vir stelsels soos 1C, Microsoft CRM en Microsoft NAV is die verbod op multithreading in die meeste gevalle geskik

Ook, as daar 'n standaard uitgawe is, dan is die verbod op multithreading in die meeste gevalle geskik as gevolg van die feit dat hierdie uitgawe beperk is in die aantal SVE-kerns.
Vir OLAP-stelsels is die algoritme wat hierbo beskryf is nie geskik nie.
Uit my eie ervaring beveel ek die volgende algoritme van aksies aan vir OLAP-stelsels vir die opstel van parallellisme-eienskappe:

  1. ontleed die swaarste versoeke en kies die optimale aantal drade daarvoor
  2. stel die Max Degree of Parallelism op die geselekteerde optimale aantal drade verkry vanaf stap 1, en stel vir spesifieke databasisse die Max DOP-waarde verkry vanaf stap 1 vir elke databasis
  3. ontleed die swaarste navrae en identifiseer die negatiewe effek van die beperking van sameloop. Indien wel, verlaag dan óf die kostedrempel vir parallelisme waarde, óf herhaal stappe 1-2 van hierdie algoritme

Dit wil sê, vir OLTP-stelsels gaan ons van enkel-draad na multi-draad, en vir OLAP-stelsels, inteendeel, ons gaan van multi-threading na enkel-threading. U kan dus die optimale parallelisme-instellings kies vir beide 'n spesifieke databasis en die hele instansie van MS SQL Server.
Dit is ook belangrik om te verstaan ​​dat die instellings van die parallelisme-eienskappe oor tyd verander moet word, gebaseer op die resultate van die monitering van die werkverrigting van MS SQL Server.

Riglyne vir die opstel van spoorvlae

Uit my eie ervaring en die ervaring van my kollegas, vir optimale werkverrigting, beveel ek aan om die volgende spoorvlae op die loopvlak van die MS SQL Server-diens vir weergawes 2008-2016 te stel:

  1. 610 - Verminderde aanteken van insetsels in geïndekseerde tabelle. Kan help met invoegings in tabelle met baie rekords en baie transaksies, met gereelde lang WRITELOG-wagte vir veranderinge in indekse
  2. 1117 - As 'n lêer in 'n lêergroep aan die outogroei-drempelvereistes voldoen, groei alle lêers in die lêergroep
  3. 1118 - Dwing alle voorwerpe om in verskillende mate geleë te wees (verbod op gemengde omvang), wat die behoefte verminder om die SGAM-bladsy te skandeer, wat gebruik word om gemengde omvang op te spoor
  4. 1224 - Deaktiveer slot-eskalasie gebaseer op die aantal slotte. Oormatige geheuegebruik kan egter slot-eskalasie veroorsaak
  5. 2371 - Verander die vaste outomatiese statistiekbywerkingsdrempel na die dinamiese outomatiese statistiekopdateringsdrempel. Belangrik vir die opdatering van navraagplanne vir groot tabelle, waar 'n verkeerde telling van rekords tot foutiewe uitvoeringsplanne lei
  6. 3226 - Onderdruk rugsteunsuksesboodskappe in die foutlogboek
  7. 4199 - Sluit veranderinge in aan die navraagoptimeerder wat in CU's en SQL Server Service Packs vrygestel is
  8. 6532-6534 - Sluit prestasieverbeterings in vir navraagbewerkings op ruimtelike datatipes
  9. 8048 - Skakel NUMA-gepartisioneerde geheue-voorwerpe om na CPU-gepartisioneerde voorwerpe
  10. 8780 - Aktiveer bykomende tydtoewysing vir navraagbeplanning. Sommige versoeke sonder hierdie vlag kan dalk afgekeur word omdat hulle nie 'n navraagplan het nie (baie skaars fout)
  11. 8780 - 9389 - Aktiveer addisionele dinamiese toekenningsgeheuebuffer vir bondelmodusstellings, wat die bondelmodusoperateur in staat stel om bykomende geheue aan te vra en vermy om data na tempdb te skuif as bykomende geheue beskikbaar is

Ook voor 2016 is dit nuttig om spoorvlag 2301 te aktiveer, wat verbeterde besluitsteunoptimalisasies moontlik maak en dus help om meer korrekte navraagplanne te kies. Vanaf weergawe 2016 het dit egter dikwels 'n negatiewe uitwerking op redelik lang algehele navrae-uitvoertye.
Ook, vir stelsels met baie indekse (byvoorbeeld vir 1C-databasisse), beveel ek aan om spoorvlag 2330 te aktiveer, wat die versameling van indeksgebruik deaktiveer, wat oor die algemeen 'n positiewe uitwerking op die stelsel het.
Vir meer inligting oor spoorvlae, sien hier
Vanuit die skakel hierbo is dit ook belangrik om weergawes en weergawes van MS SQL Server te oorweeg, aangesien vir nuwer weergawes sommige spoorvlae by verstek geaktiveer is of geen effek het nie.
Jy kan die spoorvlag aan en af ​​skakel met onderskeidelik die DBCC TRACEON en DBCC TRACEOFF opdragte. Vir meer besonderhede sien hier
U kan die status van die spoorvlae kry deur die DBCC TRACESTATUS-opdrag te gebruik: meer
Ten einde spoorvlae by die outostart van die MS SQL Server-diens ingesluit te word, moet jy na SQL Server Configuration Manager gaan en hierdie spoorvlae via -T in die dienseienskappe byvoeg:
Sommige aspekte van MS SQL Server-monitering. Riglyne vir die opstel van spoorvlae

Resultate van

In hierdie artikel is 'n paar aspekte van die monitering van MS SQL Server ontleed, met behulp waarvan u vinnig die gebrek aan RAM en vrye SVE-tyd kan identifiseer, sowel as 'n aantal ander minder ooglopende probleme. Die mees gebruikte spoorvlae is hersien.

Bronne:

» SQL Server wag statistieke
» SQL Server wag statistieke of vertel my asseblief waar dit seermaak
» Stelselaansig sys.dm_os_schedulers
» Gebruik Zabbix om MS SQL Server-databasis te monitor
» SQL Leefstyl
» Spoor vlae
» sql.ru

Bron: will.com

Voeg 'n opmerking