🥇MS SQL Server monitorizatzearen alderdi batzuk. Arrastoen banderak ezartzeko jarraibideak

hitzaurrea

Askotan, MS SQL Server DBMS-en erabiltzaileek, garatzaileek eta administratzaileek datu-basearen edo DBMS osoaren errendimendu-arazoak aurkitzen dituzte, beraz, MS SQL Serverren jarraipena oso garrantzitsua da.
Artikulu hau artikuluaren gehigarria da Zabbix erabiliz MS SQL Server datu-basea kontrolatzeko eta MS SQL Server monitorizatzeko alderdi batzuk landuko ditu, bereziki: nola azkar zehaztu zein baliabide falta diren, baita traza-markak ezartzeko gomendioak ere.
Script hauek funtziona dezaten, nahi duzun datu-basean inf eskema bat sortu behar duzu honela:
Inf eskema bat sortzea

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

RAM falta detektatzeko metodoa

RAM ezaren lehen adierazlea MS SQL Server-en instantzia batek esleitutako RAM guztia jaten duenean gertatzen da.
Horretarako, inf.vRAM-ren irudikapen hau sortuko dugu:
inf.vRAM ikuspegia sortzea

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;

Ondoren, MS SQL Server-en instantzia batek kontsulta honek esleitutako memoria guztia kontsumitzen duela zehaztu dezakezu:

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

SQL_server_physical_memory_in_use_Mb SQL_server_committed_target_Mb koherentziaz handiagoa bada edo berdina bada, orduan itxaron-estatistikak egiaztatu behar dira.
Itxaron estatistiken bidez RAM falta zehazteko, sor dezagun inf.vWaits ikuspegia:
inf.vWaits ikuspegia sortzea

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

Kasu honetan, RAM falta zehaztu dezakezu honako kontsulta honekin:

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

Hemen Ehuneko eta AvgWait_S adierazleei arreta jarri behar diezu. Osotasunean esanguratsuak badira, oso probabilitate handia dago MS SQL Server instantziarako RAM nahikorik ez egotea. Sistema bakoitzerako balio esanguratsuak banan-banan zehazten dira. Hala ere, honako hauekin has zaitezke: Ehunekoa>=1 eta AvgWait_S>=0.005.
Jarraipen-sistema batera adierazleak ateratzeko (adibidez, Zabbix), bi kontsulta hauek sor ditzakezu:

RAMek zenbat itxaron mota okupatzen dituen ehunekotan (itxaron mota guztien batura):

select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );

zenbat RAM itxaron-mota hartzen duten milisegundotan (itxaron-mota guztien batez besteko atzerapen guztien gehienezko balioa):

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'
  );

Bi adierazle hauetarako lortutako balioen dinamikan oinarrituta, MS SQL Server-en instantzia baterako RAM nahikoa dagoen ondoriozta dezakegu.

CPU Gainkarga detektatzeko metodoa

Prozesadorearen denbora falta identifikatzeko, nahikoa da sys.dm_os_schedulers sistemaren ikuspegia erabiltzea. Hemen, runnable_tasks_count etengabe 1 baino handiagoa bada, orduan probabilitate handia dago nukleo kopurua nahikoa ez izateko MS SQL Server instantziarako.
Adierazle bat monitorizazio-sistema batera ateratzeko (adibidez, Zabbix), hurrengo kontsulta sor dezakezu:

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

Adierazle honetarako lortutako balioen dinamikan oinarrituta, ondorioztatu dezakegu prozesadore-denbora nahikoa dagoen (PUZaren nukleoen kopurua) MS SQL Server-en instantzia baterako.
Hala ere, kontuan izan behar da eskaerak beraiek hainbat hari eska ditzaketela aldi berean. Eta batzuetan, optimizatzaileak ezin du behar bezala estimatu kontsultaren beraren konplexutasuna. Orduan, eskaerari hari gehiegi esleitu zaizkio, unean aldi berean prozesatu ezin direnak. Eta horrek prozesadorearen denbora faltarekin eta PUZaren nukleo espezifikoak erabiltzen dituzten programatzaileen ilararen hazkundea ere eragiten du, hau da, runnable_tasks_count adierazlea hazi egingo da horrelako baldintzetan.
Kasu honetan, CPU nukleoen kopurua handitu aurretik, beharrezkoa da MS SQL Server instantziaren beraren paralelismo-propietateak behar bezala konfiguratzea, eta 2016ko bertsiotik aurrera, behar diren datu-baseen paralelismo-propietateak behar bezala konfiguratzea:

Hemen parametro hauei arreta jarri behar diezu:

Gehienezko paralelismo-maila - eskaera bakoitzari esleitu daitezkeen hari kopuru maximoa ezartzen du (lehenetsia 0 da - sistema eragileak berak eta MS SQL Server-en edizioak soilik mugatuta)
Paralelismoaren kostuaren atalasea - paralelismoaren kostu estimatua (lehenetsia 5 da)
Max DOP - datu-base mailan kontsulta bakoitzari esleitu daitezkeen hari kopuru maximoa ezartzen du (baina ez "Max Degree of Paralelism" propietatearen balioa baino gehiago) (lehenetsia 0 da - sistema eragileak berak bakarrik mugatua eta MS SQL Server-en edizioa, baita MS SQL Server-en instantzia osoaren "Max Degree of Paralelism" propietatearen murrizketa ere)

Hemen ezinezkoa da kasu guztietarako errezeta berdin bat ematea, hau da, kontsulta astunak aztertu behar dituzu.
Nire esperientziatik, paralelismoaren propietateak ezartzeko OLTP sistemetarako ekintzen algoritmoa gomendatzen dut:

lehenik eta behin, desgaitu paralelismoa instantzia osorako Paralelismoaren Gehienezko Gradua 1ean ezarriz
eskaerarik astunenak aztertu eta hari kopuru optimoa hautatzeko
ezarri Gehienezko Paralelismo-maila 2. urratsean lortutako hari kopuru optimoan hautatutakoarekin, eta datu-base espezifikoetarako ezarri 2. urratsean lortutako Max DOP balioa datu-base bakoitzeko.
eskaerarik astunenak aztertu eta multithreading-aren eragin negatiboa identifikatzea. Hala bada, handitu Paralelismoaren Kostuen Atalasea.
1C, Microsoft CRM eta Microsoft NAV bezalako sistemetarako, kasu gehienetan, hari anitzeko erabilera debekatzea egokia da.

Gainera, edizio estandarra badago, kasu gehienetan hari anitzeko debekua egokia da edizio hau CPU nukleoen kopuruan mugatua dagoelako.
OLAP sistemetarako, goian deskribatutako algoritmoa ez da egokia.
Nire esperientziatik, paralelismoaren propietateak ezartzeko OLAP sistemetarako ekintzen algoritmoa gomendatzen dut:

eskaerarik astunenak aztertu eta hari kopuru optimoa hautatzeko
ezarri Gehienezko Paralelismo-maila 1. urratsean lortutako hari kopuru optimoan hautatutakoarekin, eta datu-base espezifikoetarako ezarri 1. urratsean lortutako Max DOP balioa datu-base bakoitzeko.
kontsultarik astunenak aztertzea eta aldiberekotasuna mugatzearen eragin negatiboa identifikatzea. Hala bada, jaitsi Paralelismoaren kostuaren atalasea edo errepikatu algoritmo honen 1-2 urratsak.

Hau da, OLTP sistemetan hari bakarretik hari anitzeko izatera pasatzen gara, eta OLAP-sistemetarako, aitzitik, hari anitzetik hari bakarrera pasatzen gara. Horrela, datu-base zehatz baterako eta MS SQL Server-en instantzia osorako paralelismo-ezarpen optimoak aukeratu ditzakezu.
Gainera, garrantzitsua da ulertzea paralelismoaren propietateen ezarpenak denboran zehar aldatu behar direla, MS SQL Server-en errendimenduaren jarraipenaren emaitzetan oinarrituta.

Arrastoen banderak ezartzeko jarraibideak

Nire esperientziatik eta nire lankideen esperientziatik, errendimendu optimorako, MS SQL Server zerbitzuaren exekuzio mailan 2008-2016 bertsioetarako honako arrasto-marka hauek ezartzea gomendatzen dut:

610 - Indexatutako tauletan txertaketen erregistroa murriztu da. Erregistro asko eta transakzio asko dituzten tauletan txertatzen lagun dezake, WRITELOG-en maiz itxaronaldi luzeekin indizeetan aldaketak egiteko
1117 - Fitxategi-talde bateko fitxategi batek hazkuntza automatikoko atalasearen baldintzak betetzen baditu, fitxategi-taldeko fitxategi guztiak hazi egiten dira
1118 - Objektu guztiak hedadura ezberdinetan kokatzea behartzen du (hedadura mistoak debekatzea), eta horrek SGAM orria eskaneatu beharra minimizatzen du, hedadura mistoak jarraitzeko erabiltzen dena.
1224 - Blokeoen igoera desgaitzen du blokeo kopuruaren arabera. Hala ere, gehiegizko memoria erabiltzeak blokeoen igoera eragin dezake
2371 - Estatistiken eguneratze automatiko finkoaren atalasea estatistiken eguneratze automatiko dinamikoaren atalasea aldatzen du. Taula handietarako kontsulta-planak eguneratzeko garrantzitsua da, non erregistroen zenbaketa oker batek exekuzio-plan okerrak eragiten dituenean.
3226 - Errore-erregistroan babeskopien arrakasta-mezuak kentzen ditu
4199 - CUetan eta SQL Server Service Pack-etan kaleratutako kontsulta-optimizatzailearen aldaketak barne hartzen ditu
6532-6534 - Datu espazialen gaineko kontsulta-eragiketetarako errendimendu hobekuntzak biltzen ditu
8048 - NUMA partikatutako memoria-objektuak CPU partizionatutakoak bihurtzen ditu
8780 - Kontsulten plangintzarako denbora esleipen gehigarria gaitzen du. Baliteke marka hau ez duten eskaera batzuk baztertzea, kontsulta-planik ez dutelako (akats oso arraroa)
8780 - 9389 - Batch moduko adierazpenetarako esleipen dinamikoko memoria buffer gehigarria gaitzen du, eta horri esker, batch moduko operadoreak memoria gehiago eskatzea eta datuak tempdb-ra eramatea saihestu, memoria gehiago badago.

2016. urtea baino lehen ere, baliagarria da 2301 aztarna gaitzea, eta horrek erabakiak laguntzeko optimizazio hobeak ahalbidetzen ditu eta, beraz, kontsulta-plan zuzenagoak aukeratzen laguntzen du. Hala ere, 2016 bertsiotik aurrera, sarritan eragin negatiboa du kontsultaren exekuzio denbora orokor nahiko luzeetan.
Gainera, indize asko dituzten sistemetarako (adibidez, 1C datu-baseetarako), traza-marka 2330 gaitzea gomendatzen dut, indizeen erabilera biltzea desgaitzen duena, eta horrek orokorrean eragin positiboa du sisteman.
Traza-markei buruzko informazio gehiago lortzeko, ikus Hemen
Goiko estekatik, MS SQL Server-en bertsioak eta eraikuntzak ere kontuan hartzea garrantzitsua da, bertsio berriagoetarako, arrasto-marka batzuk lehenespenez gaituta daude edo ez dute eraginik.
Trazako bandera aktibatu eta desaktibatu dezakezu DBCC TRACEON eta DBCC TRACEOFF komandoekin, hurrenez hurren. Xehetasun gehiagorako, ikus Hemen
Traza-marken egoera lor dezakezu DBCC TRACESTATUS komandoa erabiliz: gehiago
MS SQL Server zerbitzuaren abiarazte automatikoan aztarna-markak sartu ahal izateko, SQL Server Configuration Managerra joan behar duzu eta gehitu arrasto-marka hauek -T bidez zerbitzuaren propietateetan:

Emaitzak

Artikulu honetan, MS SQL Server monitorizatzeko alderdi batzuk aztertu ziren, eta horien laguntzarekin azkar identifikatu ahal izango dituzu RAM eza eta CPU denbora librea, baita agerikoak ez diren beste hainbat arazo ere. Gehien erabiltzen diren arrastoen banderak berrikusi dira.

Iturriak:

» SQL Server itxaron-estatistikak
» SQL Server itxaron estatistikak edo mesedez esan iezadazu non min hartzen duen
» Sistemaren ikuspegia sys.dm_os_schedulers
» Zabbix erabiliz MS SQL Server datu-basea kontrolatzeko
» SQL Bizimodua
» Traza Banderak
» sql.ru

Iturria: www.habr.com

MS SQL Server monitorizazioaren alderdi batzuk. Arrastoen banderak ezartzeko jarraibideak