ProHoster > Blog > Bestjoer > Guon aspekten fan MS SQL Server monitoring. Rjochtlinen foar it ynstellen fan traceflaggen

Guon aspekten fan MS SQL Server monitoring. Rjochtlinen foar it ynstellen fan traceflaggen

Foarwurd

Hiel faak, brûkers, ûntwikkelders en behearders fan 'e MS SQL Server DBMS tsjinkomme prestaasjesproblemen fan' e databank as de DBMS as gehiel, dus MS SQL Server-monitoring is heul relevant.
Dit artikel is in oanfolling op it artikel Zabbix brûke om MS SQL Server-database te kontrolearjen en it sil cover guon aspekten fan tafersjoch op MS SQL Server, benammen: hoe fluch bepale hokker boarnen ûntbrekke, likegoed as oanbefellings foar it ynstellen fan trace flaggen.
Foar de folgjende skripts om te wurkjen, moatte jo in inf-skema oanmeitsje yn 'e winske databank as folget:
It meitsjen fan in ynf-skema

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

Metoade foar it opspoaren fan gebrek oan RAM

De earste yndikator fan it ûntbrekken fan RAM is it gefal as in eksimplaar fan MS SQL Server alle RAM dy't deroan is tawiisd opfet.
Om dit te dwaan, sille wy de folgjende fertsjintwurdiging meitsje fan inf.vRAM:
It meitsjen fan de inf.vRAM werjefte

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 kinne jo bepale dat in eksimplaar fan MS SQL Server al it ûnthâld ferbrûkt dat deroan is tawiisd troch de folgjende query:

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

As SQL_server_physical_memory_in_use_Mb konsekwint grutter is as of gelyk oan SQL_server_committed_target_Mb, dan moatte de wachtstatistiken kontrolearre wurde.
Om it ûntbrekken fan RAM troch wachtstatistiken te bepalen, litte wy de werjefte inf.vWaits oanmeitsje:
It meitsjen fan de 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];

Yn dit gefal kinne jo it gebrek oan RAM bepale mei de folgjende query:

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

Hjir moatte jo omtinken jaan oan de yndikatoaren Persintaazje en AvgWait_S. As se wichtich binne yn har totaliteit, dan is d'r in heul hege kâns dat d'r net genôch RAM is foar it eksimplaar fan MS SQL Server. Wichtige wearden wurde yndividueel bepaald foar elk systeem. Jo kinne lykwols begjinne mei it folgjende: Percentage>=1 en AvgWait_S>=0.005.
Om yndikatoaren út te fieren nei in tafersjochsysteem (bygelyks Zabbix), kinne jo de folgjende twa fragen oanmeitsje:

  1. hoefolle soarten wachtsjen wurde beset troch RAM yn persintaazje (de som fan al sokke soarten wachtsjen): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. hoefolle RAM-wachttypen nimme yn millisekonden (de maksimale wearde fan alle gemiddelde fertragingen foar alle sokke wachttypen): 
    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'
  );

Op grûn fan 'e dynamyk fan' e krigen wearden foar dizze twa yndikatoaren, kinne wy ​​konkludearje oft d'r genôch RAM is foar in eksimplaar fan MS SQL Server.

CPU Overload Detection Metoade

Om te identifisearjen it gebrek oan prosessor tiid, it is genôch te brûken de sys.dm_os_schedulers systeem werjefte. Hjir, as de runnable_tasks_count konstant grutter is as 1, dan is d'r in hege kâns dat it oantal kearnen net genôch is foar de MS SQL Server-eksimplaar.
Om in yndikator út te fieren nei in tafersjochsysteem (bygelyks Zabbix), kinne jo de folgjende query oanmeitsje:

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

Op grûn fan de dynamyk fan 'e krigen wearden foar dizze yndikator, kinne wy ​​konkludearje oft der genôch prosessor tiid (it oantal CPU kearnen) foar in eksimplaar fan MS SQL Server.
It is lykwols wichtich om it feit te hâlden dat oanfragen sels meardere threaden tagelyk kinne oanfreegje. En soms kin de optimizer de kompleksiteit fan 'e query sels net goed skatte. Dan kin it fersyk tefolle triedden tawiisd wurde dy't net tagelyk op it opjûne momint kinne wurde ferwurke. En dit soarget ek foar in soarte fan wachtsjen ferbûn mei in gebrek oan prosessortiid, en groei fan 'e wachtrige foar planners dy't spesifike CPU-kearnen brûke, d.w.s. de runnable_tasks_count-yndikator sil groeie yn sokke omstannichheden.
Yn dit gefal, foardat it fergrutsjen fan it oantal CPU-kearnen, is it nedich om de parallellisme-eigenskippen fan 'e MS SQL Server-eksimplaar sels korrekt te konfigurearjen, en fan' e 2016-ferzje de parallelisme-eigenskippen fan 'e fereaske databases korrekt yn te stellen:
Guon aspekten fan MS SQL Server monitoring. Rjochtlinen foar it ynstellen fan traceflaggen

Guon aspekten fan MS SQL Server monitoring. Rjochtlinen foar it ynstellen fan traceflaggen
Hjir moatte jo omtinken jaan oan de folgjende parameters:

  1. Max Degree of Parallelism - stelt it maksimum oantal triedden yn dat kin wurde tawiisd oan elk fersyk (de standert is 0 - allinich beheind troch it bestjoeringssysteem sels en de edysje fan MS SQL Server)
  2. Kostendrempel foar parallellisme - rûsde kosten fan parallelisme (standert is 5)
  3. Max DOP - stelt it maksimum oantal triedden yn dat kin wurde tawiisd oan elke query op it databanknivo (mar net mear as de wearde fan 'e eigenskip "Max Degree of Parallelism") (standert is 0 - allinich beheind troch it bestjoeringssysteem sels en de edysje fan MS SQL Server, lykas de beheining op 'e eigenskip "Max Degree of Parallelism" fan it folsleine eksimplaar fan MS SQL Server)

Hjir is it ûnmooglik om foar alle gefallen in like goed resept te jaan, d.w.s. jo moatte swiere fragen analysearje.
Ut myn eigen ûnderfining advisearje ik it folgjende algoritme fan aksjes foar OLTP-systemen foar it ynstellen fan parallellisme-eigenskippen:

  1. Skeakelje earst parallelisme út troch de eksimplaarbrede Max Degree of Parallelism op 1 yn te stellen
  2. analysearje de swierste oanfragen en selektearje it optimale oantal triedden foar har
  3. set de Max Degree of Parallelism yn op it selekteare optimale oantal triedden krigen fan stap 2, en foar spesifike databases set de Max DOP-wearde fan stap 2 yn foar elke databank
  4. analysearje de swierste oanfragen en identifisearje it negative effekt fan multithreading. As it is, ferheegje dan de kostendrompel foar parallelisme.
    Foar systemen lykas 1C, Microsoft CRM en Microsoft NAV is yn 'e measte gefallen it ferbieden fan multithreading geskikt

Ek, as der in standert edysje is, dan is yn 'e measte gefallen it ferbod fan multithreading geskikt fanwege it feit dat dizze edysje beheind is yn it oantal CPU-kearnen.
Foar OLAP-systemen is it hjirboppe beskreaune algoritme net geskikt.
Ut myn eigen ûnderfining advisearje ik it folgjende algoritme fan aksjes foar OLAP-systemen foar it ynstellen fan parallellisme-eigenskippen:

  1. analysearje de swierste oanfragen en selektearje it optimale oantal triedden foar har
  2. set de Max Degree of Parallelism yn op it selekteare optimale oantal triedden krigen fan stap 1, en foar spesifike databases set de Max DOP-wearde fan stap 1 yn foar elke databank
  3. analysearje de swierste fragen en identifisearje it negative effekt fan it beheinen fan tagelyk. As dat sa is, ferleegje dan de kostendrompel foar parallelisme wearde, of werhelje stappen 1-2 fan dit algoritme

Dat is, foar OLTP-systemen geane wy ​​fan single-threading nei multi-threading, en foar OLAP-systemen, krekt oarsom, geane wy ​​fan multi-threading nei single-threading. Sa kinne jo de optimale parallelisme-ynstellings kieze foar sawol in spesifike databank as it heule eksimplaar fan MS SQL Server.
It is ek wichtich om te begripen dat de ynstellingen fan 'e parallelisme-eigenskippen moatte wurde feroare yn' e rin fan 'e tiid, basearre op' e resultaten fan tafersjoch op de prestaasjes fan MS SQL Server.

Rjochtlinen foar it ynstellen fan traceflaggen

Ut myn eigen ûnderfining en de ûnderfining fan myn kollega's, foar optimale prestaasjes, advisearje ik de folgjende spoarflaggen yn te stellen op it runnivo fan 'e MS SQL Server-tsjinst foar ferzjes 2008-2016:

  1. 610 - Fermindere logging fan ynserts yn yndeksearre tabellen. Kin helpe mei inserts yn tabellen mei in protte records en in protte transaksjes, mei faak lange WRITELOG wachtet op feroarings yn yndeksen
  2. 1117 - As in bestân yn in bestângroep foldocht oan de autogrowth-drompeleasken, groeie alle bestannen yn 'e bestângroep
  3. 1118 - Twingt alle objekten om yn ferskate omfangen te lizzen (ferbod op mingde omfangen), wat de needsaak minimearret om de SGAM-side te scannen, dy't wurdt brûkt om mingde omfangen te folgjen
  4. 1224 - Skeakelt slot eskalaasje basearre op it oantal slûzen. Oerstallich ûnthâldgebrûk kin lykwols lock-eskalaasje útlizze
  5. 2371 - Feroaret de fêste drompel foar automatyske bywurking fan statistyk nei de drompel foar dynamyske automatyske bywurking fan statistyk. Wichtich foar it bywurkjen fan queryplannen foar grutte tabellen, wêr't in ferkearde telling fan records resulteart yn ferkearde útfieringsplannen
  6. 3226 - Underdrukt backup súkses berjochten yn it flater log
  7. 4199 - Omfettet wizigingen oan 'e query-optimizer útbrocht yn CU's en SQL Server Service Packs
  8. 6532-6534 - Omfettet prestaasjesferbetteringen foar query-operaasjes op romtlike gegevenstypen
  9. 8048 - Konvertearret NUMA partitioned ûnthâld objekten nei CPU partitioned ones
  10. 8780 - Aktivearret ekstra tiid tawizing foar query planning. Guon oanfragen sûnder dizze flagge kinne wurde ôfwiisd omdat se gjin queryplan hawwe (hiel seldsume brek)
  11. 8780 - 9389 - Aktivearret ekstra dynamyske subsydzje-ûnthâldbuffer foar batch-modus-útspraken, wêrtroch de batchmodus-operator mear ûnthâld oanfreegje kin en it ferpleatsen fan gegevens nei tempdb foarkomme as mear ûnthâld beskikber is

Ek foarôfgeand oan 2016 is it nuttich om spoarflagge 2301 yn te skeakeljen, wat ferbettere optimalisaasjes foar beslútstipe mooglik makket en sa helpt by it kiezen fan mear korrekte queryplannen. Sûnt ferzje 2016 hat it lykwols faak in negatyf effekt op frij lange algemiene query-útfieringstiden.
Ek, foar systemen mei in protte yndeksen (Bygelyks, foar 1C databases), Ik riede it ynskeakeljen fan trace flagge 2330, dy't útskeakelje kolleksje fan yndeks gebrûk, dy't oer it algemien hat in posityf effekt op it systeem.
Foar mear ynformaasje oer trace flaggen, sjoch hjir
Fanút de boppesteande keppeling is it ek wichtich om ferzjes en builds fan MS SQL Server te beskôgjen, lykas foar nijere ferzjes binne guon spoarflaggen standert ynskeakele of hawwe gjin effekt.
Jo kinne de spoarflagge yn- en útskeakelje mei respektivelik de kommando's DBCC TRACEON en DBCC TRACEOFF. Foar mear details sjoch hjir
Jo kinne de status fan 'e spoarflaggen krije mei it kommando DBCC TRACESTATUS: mear ynfo
Om spoarflaggen op te nimmen yn 'e autostart fan' e MS SQL Server-tsjinst, moatte jo nei SQL Server Configuration Manager gean en dizze spoarflaggen tafoegje fia -T yn 'e tsjinsteigenskippen:
Guon aspekten fan MS SQL Server monitoring. Rjochtlinen foar it ynstellen fan traceflaggen

Resultaten

Yn dit artikel, guon aspekten fan tafersjoch MS SQL Server waarden analysearre, mei help fan dat kinne jo fluch identifisearje it gebrek oan RAM en frije CPU tiid, en ek in oantal oare minder foar de hân lizzende problemen. De meast brûkte spoarflaggen binne hifke.

Boarne:

» SQL Server wachtstatistiken
» SQL Server wachtsje statistiken of fertel my asjebleaft wêr't it docht sear
» Systeem werjefte sys.dm_os_schedulers
» Zabbix brûke om MS SQL Server-database te kontrolearjen
» SQL Lifestyle
» Trace Flaggen
» sql.ru

Boarne: www.habr.com

Add a comment