ProHoster > Blog > Uprava > Nekateri vidiki spremljanja MS SQL Server. Smernice za nastavitev zastavic sledenja

Nekateri vidiki spremljanja MS SQL Server. Smernice za nastavitev zastavic sledenja

Predgovor

Pogosto se uporabniki, razvijalci in skrbniki DBMS MS SQL Server srečujejo s težavami pri delovanju baze podatkov ali DBMS kot celote, zato je spremljanje MS SQL Server zelo pomembno.
Ta članek je dodatek k članku Uporaba Zabbixa za spremljanje baze podatkov MS SQL Server in zajemal bo nekatere vidike spremljanja MS SQL Server, zlasti: kako hitro ugotoviti, kateri viri manjkajo, kot tudi priporočila za nastavitev zastavic za sledenje.
Da bodo naslednji skripti delovali, morate ustvariti inf shemo v želeni bazi podatkov, kot sledi:
Ustvarjanje inf sheme

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

Metoda za odkrivanje pomanjkanja RAM-a

Prvi pokazatelj pomanjkanja RAM-a je primer, ko primerek strežnika MS SQL poje ves RAM, ki mu je dodeljen.
Da bi to naredili, bomo ustvarili naslednjo predstavitev inf.vRAM:
Ustvarjanje pogleda inf.vRAM

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;

Nato lahko ugotovite, da primerek strežnika MS SQL Server porabi ves pomnilnik, ki mu je dodeljen z naslednjo poizvedbo:

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

Če je SQL_server_physical_memory_in_use_Mb dosledno večji ali enak SQL_server_committed_target_Mb, je treba preveriti statistiko čakanja.
Če želite ugotoviti pomanjkanje RAM-a s statistiko čakanja, ustvarimo pogled inf.vWaits:
Ustvarjanje pogleda 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];

V tem primeru lahko ugotovite pomanjkanje RAM-a z naslednjo poizvedbo:

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

Tukaj morate biti pozorni na kazalnike Percentage in AvgWait_S. Če so v celoti pomembni, potem obstaja zelo velika verjetnost, da ni dovolj RAM-a za primerek MS SQL Server. Pomembne vrednosti se določijo posebej za vsak sistem. Vendar lahko začnete z naslednjim: Percentage>=1 in AvgWait_S>=0.005.
Če želite izpisati indikatorje v sistem za spremljanje (na primer Zabbix), lahko ustvarite naslednji dve poizvedbi:

  1. koliko vrst čakanj zasede RAM v odstotkih (vsota vseh takih vrst čakanj): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. koliko čakajočih tipov RAM-a traja v milisekundah (največja vrednost vseh povprečnih zakasnitev za vse takšne vrste čakanja): 
    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'
  );

Na podlagi dinamike dobljenih vrednosti za ta dva indikatorja lahko sklepamo, ali je dovolj RAM-a za primerek MS SQL Server.

Metoda zaznavanja preobremenitve procesorja

Če želite ugotoviti pomanjkanje procesorskega časa, je dovolj, da uporabite sistemski pogled sys.dm_os_schedulers. Tukaj, če je runnable_tasks_count stalno večji od 1, obstaja velika verjetnost, da število jeder ni dovolj za primerek MS SQL Server.
Če želite izpisati indikator v nadzorni sistem (na primer Zabbix), lahko ustvarite naslednjo poizvedbo:

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

Na podlagi dinamike dobljenih vrednosti tega kazalnika lahko sklepamo, ali je dovolj procesorskega časa (število jeder CPU) za primerek MS SQL Server.
Vendar je pomembno upoštevati dejstvo, da lahko same zahteve zahtevajo več niti hkrati. In včasih optimizator ne more pravilno oceniti kompleksnosti same poizvedbe. Potem je lahko zahtevi dodeljenih preveč niti, ki jih ob danem času ni mogoče obdelati hkrati. In to povzroča tudi vrsto čakanja, povezano s pomanjkanjem procesorskega časa, in rast čakalne vrste za razporejevalnike, ki uporabljajo določena jedra CPU, tj. indikator runnable_tasks_count bo v takšnih pogojih narasel.
V tem primeru je treba pred povečanjem števila jeder CPU pravilno konfigurirati lastnosti vzporednosti same instance MS SQL Server, od različice 2016 naprej pa pravilno konfigurirati lastnosti vzporednosti zahtevanih baz podatkov:
Nekateri vidiki spremljanja MS SQL Server. Smernice za nastavitev zastavic sledenja

Nekateri vidiki spremljanja MS SQL Server. Smernice za nastavitev zastavic sledenja
Tukaj morate biti pozorni na naslednje parametre:

  1. Največja stopnja vzporednosti - nastavi največje število niti, ki jih je mogoče dodeliti vsaki zahtevi (privzeta vrednost je 0 - omejena samo s samim operacijskim sistemom in izdajo MS SQL Server)
  2. Cost Threshold for Parallelism – ocenjeni stroški paralelizma (privzeto je 5)
  3. Max DOP - nastavi največje število niti, ki jih je mogoče dodeliti vsaki poizvedbi na ravni baze podatkov (vendar ne več kot vrednost lastnosti "Max Degree of Parallelism") (privzeto je 0 - omejeno samo s samim operacijskim sistemom in izdaja strežnika MS SQL Server, kot tudi omejitev lastnosti »Max Degree of Parallelism« celotnega primerka strežnika MS SQL Server)

Tu je nemogoče dati enako dober recept za vse primere, tj. analizirati morate težke poizvedbe.
Iz lastnih izkušenj priporočam naslednji algoritem dejanj za sisteme OLTP za nastavitev lastnosti paralelizma:

  1. najprej onemogočite vzporednost tako, da nastavite največjo stopnjo vzporednosti za celotno instanco na 1
  2. analizirajte najtežje zahteve in izberite optimalno število niti zanje
  3. nastavite največjo stopnjo vzporednosti na izbrano optimalno število niti, pridobljeno v 2. koraku, in za posebne baze podatkov nastavite največjo vrednost DOP, pridobljeno v 2. koraku za vsako zbirko podatkov
  4. analizirajte najtežje zahteve in ugotovite negativen učinek večnitnosti. Če je, potem povečajte prag stroškov za paralelizem.
    Za sisteme, kot so 1C, Microsoft CRM in Microsoft NAV, je v večini primerov primerna prepoved večnitnosti

Poleg tega, če obstaja standardna izdaja, je v večini primerov prepoved večnitnosti primerna zaradi dejstva, da je ta izdaja omejena na število jeder procesorja.
Za sisteme OLAP zgoraj opisani algoritem ni primeren.
Iz lastnih izkušenj priporočam naslednji algoritem dejanj za sisteme OLAP za nastavitev lastnosti paralelizma:

  1. analizirajte najtežje zahteve in izberite optimalno število niti zanje
  2. nastavite največjo stopnjo vzporednosti na izbrano optimalno število niti, pridobljeno v 1. koraku, in za posebne baze podatkov nastavite največjo vrednost DOP, pridobljeno v 1. koraku za vsako zbirko podatkov
  3. analizirajte najtežje poizvedbe in ugotovite negativen učinek omejevanja sočasnosti. Če je, znižajte vrednost praga stroškov za vzporednost ali ponovite korake 1–2 tega algoritma

To pomeni, da za sisteme OLTP gremo od enonitnosti do večnitnosti, pri sistemih OLAP pa, nasprotno, od večnitnosti do enonitnosti. Tako lahko izberete optimalne nastavitve vzporednosti tako za določeno bazo podatkov kot za celoten primerek MS SQL Server.
Pomembno je tudi razumeti, da je treba nastavitve lastnosti vzporednosti sčasoma spreminjati na podlagi rezultatov spremljanja zmogljivosti MS SQL Server.

Smernice za nastavitev zastavic sledenja

Iz lastnih izkušenj in izkušenj mojih kolegov za optimalno delovanje priporočam nastavitev naslednjih zastavic sledenja na ravni izvajanja storitve MS SQL Server za različice 2008-2016:

  1. 610 – Zmanjšano beleženje vstavkov v indeksirane tabele. Lahko pomaga pri vstavljanju v tabele s številnimi zapisi in številnimi transakcijami, s pogostimi dolgimi čakanji WRITELOG na spremembe v indeksih
  2. 1117 – Če datoteka v datotečni skupini izpolnjuje zahteve praga samodejne rasti, se vse datoteke v datotečni skupini povečajo
  3. 1118 – Vsi objekti morajo biti locirani v različnih ekstentih (prepoved mešanih ekstentov), ​​kar zmanjša potrebo po skeniranju strani SGAM, ki se uporablja za sledenje mešanim ekstentom.
  4. 1224 – Onemogoči stopnjevanje zaklepanja glede na število zaklepanj. Vendar pa lahko prekomerna poraba pomnilnika sproži stopnjevanje zaklepanja
  5. 2371 – Spremeni fiksni prag samodejnega posodabljanja statističnih podatkov v dinamični prag samodejnega posodabljanja statističnih podatkov. Pomembno za posodabljanje načrtov poizvedb za velike tabele, kjer nepravilno število zapisov povzroči napačne načrte izvajanja
  6. 3226 - Zapre sporočila o uspešnem varnostnem kopiranju v dnevniku napak
  7. 4199 - Vključuje spremembe optimizatorja poizvedb, izdanega v CU in servisnih paketih za SQL Server
  8. 6532-6534 - Vključuje izboljšave zmogljivosti za poizvedovalne operacije na vrstah prostorskih podatkov
  9. 8048 - Pretvori NUMA particionirane pomnilniške objekte v particionirane CPE
  10. 8780 – Omogoča dodatno dodelitev časa za načrtovanje poizvedbe. Nekatere zahteve brez te zastavice bodo morda zavrnjene, ker nimajo načrta poizvedbe (zelo redka napaka)
  11. 8780 - 9389 - Omogoča dodatni medpomnilnik dinamičnega dodeljevanja pomnilnika za stavke paketnega načina, kar operaterju paketnega načina omogoča, da zahteva več pomnilnika in se izogne ​​premikanju podatkov v tempdb, če je na voljo več pomnilnika.

Tudi pred letom 2016 je koristno omogočiti zastavico sledenja 2301, ki omogoča izboljšane optimizacije podpore odločanju in tako pomaga pri izbiri pravilnejših načrtov poizvedb. Vendar ima od različice 2016 pogosto negativen učinek na precej dolge skupne čase izvajanja poizvedbe.
Prav tako za sisteme z veliko indeksi (npr. za baze podatkov 1C) priporočam vklop zastavice sledenja 2330, ki onemogoči zbiranje uporabe indeksa, kar na splošno pozitivno vpliva na sistem.
Za več informacij o zastavicah sledenja glejte tukaj
Glede na zgornjo povezavo je pomembno upoštevati tudi različice in gradnje strežnika MS SQL, saj so pri novejših različicah nekatere zastavice za sledenje privzeto omogočene ali nimajo učinka.
Zastavico sledenja lahko vklopite in izklopite z ukazoma DBCC TRACEON oziroma DBCC TRACEOFF. Za več podrobnosti glej tukaj
Stanje zastavic sledenja lahko dobite z ukazom DBCC TRACESTATUS: več
Če želite, da so zastavice sledenja vključene v samodejni zagon storitve MS SQL Server, morate iti v upravitelja konfiguracije strežnika SQL in dodati te zastavice sledenja prek -T v lastnostih storitve:
Nekateri vidiki spremljanja MS SQL Server. Smernice za nastavitev zastavic sledenja

Rezultati

V tem članku smo analizirali nekatere vidike spremljanja strežnika MS SQL, s pomočjo katerih lahko hitro ugotovite pomanjkanje RAM-a in prostega procesorskega časa ter številne druge manj očitne težave. Pregledane so bile najpogosteje uporabljene zastavice za sledenje.

Vir:

» Statistika čakanja SQL Server
» Statistika čakanja na SQL Server ali mi povejte, kje vas boli
» Sistemski pogled sys.dm_os_schedulers
» Uporaba Zabbixa za spremljanje baze podatkov MS SQL Server
» Življenjski slog SQL
» Zastavice za sledenje
» sql.ru

Vir: www.habr.com

Dodaj komentar