ProHoster > Blog > Administrimi > Disa aspekte të monitorimit të MS SQL Server. Rekomandime për konfigurimin e flagjeve të gjurmimit.

Disa aspekte të monitorimit të MS SQL Server. Rekomandime për konfigurimin e flagjeve të gjurmimit.

Parathënie

Shumë shpesh përdoruesit, zhvilluesit dhe administratorët e DBMS MS SQL Server përballen me probleme të performancës së DB ose të DBMS si një tërësi, prandaj monitorimi i MS SQL Server është shumë i rëndësishëm.
Ky artikull është një shtesë për artikullin Përdorimi i Zabbix për monitorimin e bazës së të dhënave MS SQL Server dhe në të do të shqyrtohen disa aspekte të monitorimit të MS SQL Server, veçanërisht: si të përcaktojmë shpejt cilat burime mungojnë, si dhe rekomandime për konfigurimin e flamujve të gjurmimit.
Për të përdorur skriptet e dhëna më poshtë, është e domosdoshme të krijoni skemën inf në bazën e të dhënave përkatëse në këtë mënyrë:
Krijimi i skemës inf

use ;
go
create schema inf;

Metoda për të identifikuar mungesën e memories operative

Shenji i parë i mungesës së memories operative është kur instanca e MS SQL Server konsumon të gjithë RAM-in e dedikuar.
Për këtë, le të krijojmë pamjen inf.vRAM:
Krijimi i pamjes inf.vRAM

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]						--sa RAM është e lirë në server në MB
		 , a.[RAM_Avail_Percent]					--përqindja e RAM-it të lirë në server
		 , a.[Server_physical_memory_Mb]				--sa gjithsej RAM është në server në MB
		 , a.[SQL_server_committed_target_Mb]			--sa gjithsej RAM është e dedikuar për MS SQL Server në MB
		 , a.[SQL_server_physical_memory_in_use_Mb] 		--sa gjithsej RAM po konsumon MS SQL Server aktualisht në MB
		 , a.[SQL_RAM_Avail_Percent]				--përqindja e RAM-it të lirë për MS SQL Server në raport me të gjithë RAM-in e dedikuar për MS SQL Server
		 , a.[StateMemorySQL]						--a ka mjaftueshëm RAM për MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]				--përqindja e RAM-it të dedikuar për MS SQL Server në raport me të gjithë RAM-in e serverit
		 --a ka mjaftueshëm RAM për server
		, (case when 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;

Atëherë, për të përcaktuar se sa RAM po konsumon instanca e MS SQL Server, mund të përdorim kërkesën e mëposhtme:

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

Nëse treguesi SQL_server_physical_memory_in_use_Mb konstant nuk është më i vogël se SQL_server_committed_target_Mb, atëherë duhet të kontrolloni statistikën e pritjeve.
Për të identifikuar mungesën e memories operative përmes statistikës së pritjeve, le të krijojmë pamjen inf.vWaits:
Krijimi i pamjes inf.vWaits

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

В этом случае определить нехватку оперативной памяти можно следующим запросом:

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

Здесь нужно обратить внимание на показатели Percentage и AvgWait_S. Если они существенны по своей совокупности, то есть очень большая вероятность того, что оперативной памяти не хватает экземпляру MS SQL Server. Существенные значения определяются индивидуально для каждой системы. Однако, можно начинать со следующего показателя: Percentage>=1 и AvgWait_S>=0.005.
Для вывода показателей в систему мониторинга (например, Zabbix) можно создать следующие два запроса:

  1. сколько в процентах занимают типы ожиданий по ОЗУ (сумма по всем таким типам ожиданий):
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. сколько в миллисекундах занимают типы ожиданий по ОЗУ (максимальное значение из всех средних задержек по всем таким типам ожиданий):
    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'
  );

Исходя из динамики полученных значений по этим двум показателям, можно сделать вывод достаточно ли ОЗУ для экземпляра MS SQL Server.

Метод выявления чрезмерной нагрузки на ЦПУ

Для выявления нехватки процессорного времени достаточно воспользоваться системным представлением sys.dm_os_schedulers. Здесь, если показатель runnable_tasks_count постоянно больше 1, то существует большая вероятность того, что количество ядер не хватает экземпляру MS SQL Server.
Для вывода показателя в систему мониторинга (например, Zabbix) можно создать следующий запрос:

zgjedh maksimumin ([runnable_tasks_count]) si [runnable_tasks_count]
nga sys.dm_os_schedulers
ku identifikuesi i orarit <255;

Duke u nisur nga dinamika e vlerave të marra për këtë tregues, mund të përfundojmë se a ka mjaft kohë procesori (numri i bërthamave të CPU) për instancën e MS SQL Server.
Megjithatë, është e rëndësishme të mbajmë parasysh se vetë kërkesat mund të kërkojnë disa trama menjëherë. Dhe ndonjëherë optimizuesi nuk mund të vlerësojë saktësisht kompleksitetin e vetë kërkesës. Atëherë, këtyre kërkesave mund t'u jepet shumë trama, të cilat në atë moment nuk mund të përpunohen të gjitha së bashku. Kjo gjithashtu shkakton një lloj priteje të lidhur me mungesën e kohës së procesorit dhe rritjen e radhës në oraristët që përdorin bërthama të caktuara të CPU, pra treguesi i [runnable_tasks_count] në këto kushte do të rritet.
Në këtë rast, para se të rrisim numrin e bërthamave të CPU, është e nevojshme të konfigurojmë saktë parametrat e paralelizmit të vetë instancës së MS SQL Server, dhe që nga versioni 2016 - të konfigurojmë saktë parametrat e paralelizmit për bazat e të dhënave të nevojshme:
Disa aspekte të monitorimit të MS SQL Server. Rekomandime për konfigurimin e flagjeve të gjurmimit.

Disa aspekte të monitorimit të MS SQL Server. Rekomandime për konfigurimin e flagjeve të gjurmimit.
Këtu duhet të kushtohet vëmendje parametrave të mëposhtëm:

  1. Max Degree of Parallelism - përcakton numrin maksimal të grupeve që mund t'i atribuohen secilës kërkesë (në mënyrë default është 0 - kufizim vetëm nga vetë sistemi operativ dhe edicioni i MS SQL Server)
  2. Cost Threshold for Parallelism - kostoja e vlerësimit për paralelizmin (në mënyrë default është 5)
  3. Max DOP - përcakton numrin maksimal të grupeve që mund t'i atribuohen secilës kërkesë në nivelin e bazës së të dhënave (por jo më shumë se vlera e parametrave „Max Degree of Parallelism”) (në mënyrë default është 0 - kufizim vetëm nga vetë sistemi operativ dhe edicioni i MS SQL Server, si dhe kufizimi nga parametri „Max Degree of Parallelism” të gjithë instancës së MS SQL Server)

Këtu nuk është e mundur të jepet një recetë e mirë për të gjitha rastet, pra është e nevojshme të analizohen kërkesat e rënduara.
Nga përvoja ime, rekomandoj algoritmin e mëposhtëm për sistemet OLTP për konfigurimin e parametrave të paralelizmit:

  1. së pari ndaloni paralelizmin, duke ose caktuar Max Degree of Parallelism në nivelin e instancës në 1
  2. analizoni kërkesat më të rënda dhe përcaktoni numrin optimal të grupeve për to
  3. vendosni Max Degree of Parallelism në numrin optimal të grupeve të përcaktuar nga pika 2, si dhe për bazat specifike të të dhënave vendosni vlerën e Max DOP të marrë nga pika 2 për çdo bazë të dhënash
  4. analizoni kërkesat më të rënda dhe identifikoni efektin negativ të shumëprocesimit. Nëse ekziston, rrisni Cost Threshold for Parallelism.
    Për sisteme si 1C, Microsoft CRM dhe Microsoft NAV, në shumicën e rasteve është e mjaftueshme të ndalohet shumëprocesimi.

Nëse gjithashtu po përdorni një edicion Standard, atëherë në shumicën e rasteve është e mjaftueshme të ndalohet shumëprocesimi për shkak të faktit se ky edicion është i kufizuar në numrin e bërthamave të CPU.
Për sistemet OLAP, algoritmi i përshkruar më sipër nuk është i përshtatshëm.
Nga përvoja ime, rekomandoj algoritmin e mëposhtëm për sistemet OLAP për konfigurimin e parametrave të paralelizmit:

  1. analizoni kërkesat më të rënda dhe përcaktoni numrin optimal të grupeve për to
  2. vendosni Max Degree of Parallelism në numrin optimal të grupeve të caktuar nga pikën 1, si dhe për bazat specifike të të dhënave, vendosni vlerën e Max DOP të marrë nga pika 1 për secilën bazë të dhënash
  3. analizoni kërkesat më të rënda dhe identifikoni efektin negativ të kufizimit të paralelizmit. Nëse ekziston, atëherë ose ulni vlerën e Cost Threshold for Parallelism, ose përsëritni hapat 1-2 të këtij algoritmi.

Pra, për sistemet OLTP, ne kalojmë nga një proces i vetëm në shumëprocese, kurse për sistemet OLAP bëjmë inversin - kalojmë nga shumëprocese në një proces të vetëm. Kështu mund të përshtatim cilësimet optimale të paralelizmit për secilën bazë të dhënash, si dhe për tërë instancën e MS SQL Server.
Është gjithashtu e rëndësishme të kuptoni se parametrat e paralelizmit duhet të ndryshohen me kalimin e kohës, bazuar në rezultatet e monitorimit të performancës së MS SQL Server.

Rekomandime për konfigurimin e flamujve të gjurmimit

Nga përvoja ime dhe e kolegëve të mi, për funksionimin optimal rekomandoj të vendosni në nivelin e shërbimit të MS SQL Server për versionet 2008-2016 flamujt e gjurmimit të mëposhtëm:

  1. 610 - Reduktimi i regjistrimit të insertimeve në tabela të indeksuara. Mund të ndihmojë me insertimet në tabela me numër të madh rekordesh dhe shumë transaksionesh, gjatë pritjeve të shpeshta dhe të gjata WRITELOG për ndryshimet në indekset.
  2. 1117 - Nëse skeda në grupin e skedave përmbush kërkesat e pragut të rritjes automatike, të gjitha skedarët në grupin e skedave rriten.
  3. 1118 - Detyron të gjitha objektet të vendosen në ekstentet e ndryshme (ndalimi i ekstentëve të përzier), duke minimizuar nevojën për skanimin e faqes SGAM, e cila përdoret për të ndjekur ekstentët e përzier.
  4. 1224 — Çaktivizon bllokimet e grupeve duke u bazuar në numrin e bllokimeve. Megjithatë, përdorimi shumë aktiv i memories mund të aktivizojë bllokimet e grupeve.
  5. 2371 — Ndryshon pragun e azhurnimit automatik fiks të statistikave në pragun e azhurnimit dinamik të statistikave. I rëndësishëm për azhurnimin e planeve të pyetjeve në lidhje me tabelat e mëdha, ku përcaktimi i gabuar i numrit të regjistrimeve çon në plane ekzekutimi të gabuara.
  6. 3226 — Përdorimi i mesazheve të suksesshme të kopjimit në regjistrin e gabimeve.
  7. 4199 — Aktivizon ndryshime në optimizuesin e pyetjeve, të lëshuara në paketat akumuluese të azhurnimeve dhe paketat e azhurnimit të SQL Server.
  8. 6532-6534 — Aktivizon përmirësimin e performancës së operacioneve të pyetjeve me lloje të dhënash hapësinore.
  9. 8048 — Transformon objektet e memories, të ndara sipas NUMA, në të ndara sipas CPU.
  10. 8780 — Aktivizon alokimin e kohës shtesë për hartimin e planit të pyetjes. Disa pyetje pa këtë flamur mund të refuzohen, pasi ato nuk kanë një plan pyetjeje (një gabim shumë të rrallë).
  11. 8780 — 9389 — Aktivizon një tampon dinamik të ofruar përkohësisht për operatoret e modit paketë, duke i dhënë mundësinë operatorit të modit paketë të kërkojë memorie shtesë dhe të shmangë transferimin e të dhënave në tempdb, nëse ka memorie shtesë të disponueshme.

Po ashtu, deri në versionin 2016, është e dobishme të aktivizoni flamurin e gjurmimit 2301, i cili aktivizon optimizimin e mbështetjes së zgjeruar për marrëdhëniet dhe kështu ndihmon në zgjedhjen e planeve më të saktë të pyetjeve. Megjithatë, duke filluar nga versioni 2016, ai shpesh ka një efekt negativ në kohën e përgjithshme të ekzekutimit të pyetjeve.
Gjithashtu, për sistemet me shumë indekse (p.sh., për bazat e të dhënave 1C), rekomandoj aktivizimin e flamurit të gjurmimit 2330, i cili çaktivizon grumbullimin e përdorimit të indekseve, që në përgjithësi ka një ndikim pozitiv në sistem.
Më shumë detaje mbi flamujt e gjurmimit mund të merren. këtu
Në lidhjen e përmendur më sipër, është e rëndësishme gjithashtu të merren parasysh versionet dhe ndarjet e MS SQL Server, pasi për versionet më të reja disa flamuj gjurmimi janë të aktivizuar sipas parashikimeve ose nuk kanë asnjë efekt.
Flamujt e gjurmimit mund të aktivizohen dhe çaktivizohen me komandat DBCC TRACEON dhe DBCC TRACEOFF përkatësisht. Më shumë detaje shihni. këtu
Për të marrë gjendjen e flamujve të gjurmimit, mund të përdorni komandën DBCC TRACESTATUS: më shumë
Për të aktivizuar dhe çaktivizuar flamujt e gjurmimit në nisjen automatike të shërbimit MS SQL Server, duhet të shkoni në SQL Server Configuration Manager dhe në pronësitë e shërbimit të shtoni këta flamuj të gjurmimit përmes -T:
Disa aspekte të monitorimit të MS SQL Server. Rekomandime për konfigurimin e flagjeve të gjurmimit.

Përfundimet

Në këtë artikull janë shqyrtuar disa aspekte të monitorimit të MS SQL Server, me anë të cilave mund të zbuloni menjëherë mungesën e RAM-it dhe kohës së lirë CPU, si dhe një sërë problemesh më pak të dukshme. Janë shqyrtuar flamujt e gjurmimit më të përdorur.

Burimet:

» Statistikat e pritjes së SQL Server
» Statistikat e pritjes së SQL Server ose ju lutem, më thoni ku dhemb.
» Përfaqësimi sistemor sys.dm_os_schedulers
» Përdorimi i Zabbix për monitorimin e bazës së të dhënave MS SQL Server
» Stili i jetesës së SQL
» Flamujt e gjurmimit
» sql.ru

Burimi: habr.com

Bleni hostim të besueshëm për faqe me mbrojtje nga DDoS, serverë VPS VDS 🔥 Bleni hostim të besueshëm për faqe me mbrojtje nga DDoS, serverë VPS VDS | ProHoster