ProHoster > Blogi > antaminen > Jotkut MS SQL Server -valvonnan näkökohdat. Ohjeet jäljityslippujen asettamiseen

Jotkut MS SQL Server -valvonnan näkökohdat. Ohjeet jäljityslippujen asettamiseen

Esipuhe

Melko usein MS SQL Server DBMS:n käyttäjät, kehittäjät ja ylläpitäjät kohtaavat tietokannan tai koko DBMS:n suorituskykyongelmia, joten MS SQL Serverin valvonta on erittäin tärkeää.
Tämä artikkeli on lisäys artikkeliin Zabbixin käyttäminen MS SQL Server -tietokannan valvontaan ja se kattaa joitain MS SQL Serverin valvonnan näkökohtia, erityisesti: kuinka nopeasti selvitetään, mitkä resurssit puuttuvat, sekä suosituksia jäljityslippujen asettamisesta.
Jotta seuraavat komentosarjat toimisivat, sinun on luotava inf-skeema haluttuun tietokantaan seuraavasti:
Inf-skeeman luominen

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

Menetelmä RAM-muistin puutteen havaitsemiseksi

Ensimmäinen merkki RAM-muistin puutteesta on tapaus, jossa MS SQL Server -esiintymä syö kaiken sille varatun RAM-muistin.
Tätä varten luomme seuraavan inf.vRAM-muodon:
Luodaan inf.vRAM-näkymää

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;

Sitten voit määrittää, että MS SQL Serverin esiintymä kuluttaa kaiken sille varatun muistin seuraavalla kyselyllä:

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

Jos SQL_server_physical_memory_in_use_Mb on jatkuvasti suurempi tai yhtä suuri kuin SQL_server_committed_target_Mb, odotustilastot tulee tarkistaa.
Jos haluat määrittää RAM-muistin puutteen odotustilastoilla, luodaan inf.vWaits-näkymä:
Luodaan inf.vWaits-näkymää

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

Tässä tapauksessa voit määrittää RAM-muistin puutteen seuraavalla kyselyllä:

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

Tässä sinun on kiinnitettävä huomiota indikaattoreihin Percentage ja AvgWait_S. Jos ne ovat kokonaisuutena merkittäviä, on erittäin suuri todennäköisyys, että RAM-muistia ei ole tarpeeksi MS SQL Server -instanssille. Merkittävät arvot määritetään jokaiselle järjestelmälle erikseen. Voit kuitenkin aloittaa seuraavasti: Percentage>=1 ja AvgWait_S>=0.005.
Jos haluat tulostaa indikaattoreita valvontajärjestelmään (esimerkiksi Zabbix), voit luoda seuraavat kaksi kyselyä:

  1. kuinka monta odotustyyppiä RAM-muisti käyttää prosentteina (kaikkien tällaisten odotustyyppien summa): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. kuinka monta RAM- odotustyyppiä kestää millisekunneissa (kaikkien odotustyyppien kaikkien keskimääräisten viiveiden enimmäisarvo): 
    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'
  );

Näiden kahden indikaattorin saatujen arvojen dynamiikan perusteella voimme päätellä, onko MS SQL Server -esiintymälle tarpeeksi RAM-muistia.

Prosessorin ylikuormituksen tunnistusmenetelmä

Prosessoriajan puutteen tunnistamiseksi riittää sys.dm_os_schedulers järjestelmänäkymän käyttäminen. Tässä, jos runnable_tasks_count on jatkuvasti suurempi kuin 1, on suuri todennäköisyys, että ytimien määrä ei riitä MS SQL Server -esiintymään.
Jos haluat lähettää ilmaisimen valvontajärjestelmään (esimerkiksi Zabbix), voit luoda seuraavan kyselyn:

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

Tämän indikaattorin saatujen arvojen dynamiikan perusteella voimme päätellä, onko prosessoriaikaa (suorittimen ytimien lukumäärä) tarpeeksi MS SQL Server -esiintymälle.
On kuitenkin tärkeää pitää mielessä, että itse pyynnöt voivat pyytää useita säikeitä kerralla. Ja joskus optimoija ei voi arvioida oikein itse kyselyn monimutkaisuutta. Tällöin pyynnölle voi olla varattu liian monta säiettä, joita ei voida käsitellä samanaikaisesti tiettynä aikana. Ja tämä aiheuttaa myös eräänlaisen odotuksen, joka liittyy prosessorin ajan puutteeseen ja tiettyjä prosessoriytimiä käyttävien ajoittajien jonon kasvuun, eli runnable_tasks_count-indikaattori kasvaa tällaisissa olosuhteissa.
Tässä tapauksessa ennen CPU-ytimien määrän lisäämistä on tarpeen määrittää oikein itse MS SQL Server -esiintymän rinnakkaisominaisuudet ja 2016-versiosta alkaen määrittää oikein vaadittujen tietokantojen rinnakkaisominaisuudet:
Jotkut MS SQL Server -valvonnan näkökohdat. Ohjeet jäljityslippujen asettamiseen

Jotkut MS SQL Server -valvonnan näkökohdat. Ohjeet jäljityslippujen asettamiseen
Tässä sinun tulee kiinnittää huomiota seuraaviin parametreihin:

  1. Max Degree of Parallelism - asettaa enimmäismäärän säikeitä, jotka voidaan varata kullekin pyynnölle (oletus on 0 - rajoittaa vain itse käyttöjärjestelmä ja MS SQL Server -versio)
  2. Rinnakkaisuuden kustannuskynnys – arvioitu samansuuntaisuuden hinta (oletus on 5)
  3. Max DOP - määrittää säikeiden enimmäismäärän, joka voidaan varata kullekin kyselylle tietokantatasolla (mutta ei enempää kuin "Max Degree of Parallelism" -ominaisuuden arvo) (oletus on 0 - rajoitus koskee vain itse käyttöjärjestelmä ja MS SQL Server -versio, sekä rajoitus "Max Degree of the SQL Server-instanssin koko" -ominaisuudella)

Täällä on mahdotonta antaa yhtä hyvää reseptiä kaikkiin tapauksiin, eli sinun on analysoitava raskaat kyselyt.
Omasta kokemuksestani suosittelen seuraavaa toimintoalgoritmia OLTP-järjestelmille rinnakkaisuusominaisuuksien määrittämiseksi:

  1. poista ensin rinnakkaisuus käytöstä asettamalla instanssin laajuiseksi rinnakkaisuusasteeksi 1
  2. analysoida raskaimmat pyynnöt ja valita niille optimaalinen määrä säikeitä
  3. aseta Max Degree of Parallelism valittuun optimaaliseen säikeiden lukumäärään, joka on saatu vaiheesta 2, ja määritä tietyille tietokannoille vaiheesta 2 saatu maksimi DOP-arvo kullekin tietokannalle
  4. analysoida raskaimmat pyynnöt ja tunnistaa monisäikeisyyden negatiivinen vaikutus. Jos on, nosta rinnakkaisuuden kustannuskynnystä.
    1C:n, Microsoft CRM:n ja Microsoft NAV:n kaltaisille järjestelmille monisäikeyden kieltäminen sopii useimmissa tapauksissa

Lisäksi, jos on olemassa Standard-versio, niin useimmissa tapauksissa monisäikeyden kielto on sopiva, koska tässä versiossa on rajoitettu prosessoriytimien määrä.
OLAP-järjestelmille yllä kuvattu algoritmi ei sovellu.
Omasta kokemuksestani suosittelen seuraavaa toimintoalgoritmia OLAP-järjestelmille rinnakkaisuusominaisuuksien määrittämiseen:

  1. analysoida raskaimmat pyynnöt ja valita niille optimaalinen määrä säikeitä
  2. aseta Max Degree of Parallelism valittuun optimaaliseen säikeiden lukumäärään, joka on saatu vaiheesta 1, ja määritä tietyille tietokannoille vaiheesta 1 saatu maksimi DOP-arvo kullekin tietokannalle
  3. analysoida raskaimmat kyselyt ja tunnistaa samanaikaisuuden rajoittamisen kielteiset vaikutukset. Jos on, alenna samansuuntaisuuden kustannuskynnystä tai toista tämän algoritmin vaiheet 1-2

Eli OLTP-järjestelmissä siirrymme yksisäikeisestä monisäikeiseen, ja OLAP-järjestelmissä päinvastoin siirrymme monisäikeisestä yksisäikeiseen. Siten voit valita optimaaliset rinnakkaisasetukset sekä tietylle tietokannalle että koko MS SQL Server -instanssille.
On myös tärkeää ymmärtää, että rinnakkaisominaisuuksien asetuksia on muutettava ajan myötä MS SQL Serverin suorituskyvyn seurannan tulosten perusteella.

Ohjeet jäljityslippujen asettamiseen

Oman kokemukseni ja kollegoideni kokemusten perusteella optimaalisen suorituskyvyn saavuttamiseksi suosittelen seuraavien jäljityslippujen asettamista MS SQL Server -palvelun suoritustasolle versioille 2008-2016:

  1. 610 - Vähentynyt lisäysten kirjaaminen indeksoituihin taulukoihin. Voi auttaa lisäyksissä taulukoihin, joissa on monia tietueita ja monia tapahtumia, kun WRITELOG odottaa usein pitkiä muutoksia indekseihin
  2. 1117 - Jos tiedosto ryhmässä täyttää automaattisen kasvun kynnysvaatimukset, kaikki tiedostoryhmän tiedostot kasvavat
  3. 1118 - Pakottaa kaikki objektit sijoittamaan eri ulottuvuuksiin (sekalaajuuksien kielto), mikä minimoi tarpeen skannata SGAM-sivu, jota käytetään sekalaisten laajuuksien seuraamiseen
  4. 1224 - Poistaa lukkojen eskaloinnin käytöstä lukkojen lukumäärän perusteella. Liiallinen muistin käyttö voi kuitenkin laukaista lukituksen eskaloinnin
  5. 2371 - Muuttaa kiinteän automaattisen tilastopäivityksen kynnyksen dynaamisen automaattisen tilastopäivityksen kynnysarvoksi. Tärkeää päivitettäessä kyselysuunnitelmia suurille taulukoille, joissa tietueiden virheellinen määrä johtaa virheellisiin suoritussuunnitelmiin
  6. 3226 - Estää varmuuskopioinnin onnistumisviestit virhelokissa
  7. 4199 - Sisältää muutokset kyselyn optimoijaan, jotka on julkaistu CU:issa ja SQL Server Service Pack -paketeissa
  8. 6532-6534 - Sisältää suorituskyvyn parannuksia paikkatietotyyppien kyselytoimintoihin
  9. 8048 - Muuntaa NUMA-osioidut muistiobjektit CPU-osioiduiksi
  10. 8780 - Mahdollistaa lisäajan varauksen kyselyn suunnitteluun. Jotkut pyynnöt ilman tätä lippua voidaan hylätä, koska niillä ei ole kyselysuunnitelmaa (erittäin harvinainen virhe)
  11. 8780 - 9389 - Mahdollistaa dynaamisen lisämuistipuskurin eräajotilalauseille, mikä antaa eräajotilan operaattorille mahdollisuuden pyytää lisämuistia ja välttää tietojen siirtämisen tempdb:hen, jos lisämuistia on käytettävissä.

Myös ennen vuotta 2016 on hyödyllistä ottaa käyttöön jäljityslippu 2301, joka mahdollistaa tehostetut päätöstuen optimoinnit ja auttaa siten valitsemaan oikeampia kyselysuunnitelmia. Versiosta 2016 lähtien sillä on kuitenkin usein negatiivinen vaikutus melko pitkiin kyselyn suoritusaikoihin.
Lisäksi järjestelmissä, joissa on paljon indeksejä (esimerkiksi 1C-tietokantoja varten), suosittelen jäljityslipun 2330 käyttöönottoa, joka estää indeksien käytön keräämisen, millä on yleensä positiivinen vaikutus järjestelmään.
Lisätietoja jäljityslipuista on kohdassa täällä
Yllä olevasta linkistä on myös tärkeää ottaa huomioon MS SQL Serverin versiot ja koontiversiot, koska uudemmissa versioissa jotkin jäljitysliput ovat oletuksena käytössä tai niillä ei ole vaikutusta.
Voit ottaa jäljityslipun käyttöön ja poistaa sen käytöstä vastaavasti DBCC TRACEON- ja DBCC TRACEOFF -komennoilla. Katso lisätietoja täällä
Voit saada jäljityslippujen tilan komennolla DBCC TRACESTATUS: lisää
Jotta jäljitysliput voidaan sisällyttää MS SQL Server -palvelun automaattiseen käynnistykseen, sinun on siirryttävä SQL Server Configuration Manageriin ja lisättävä nämä jäljitysliput -T:n kautta palvelun ominaisuuksiin:
Jotkut MS SQL Server -valvonnan näkökohdat. Ohjeet jäljityslippujen asettamiseen

Tulokset

Tässä artikkelissa analysoitiin joitain MS SQL Serverin valvonnan näkökohtia, joiden avulla voit nopeasti tunnistaa RAM-muistin ja vapaan CPU-ajan puutteen sekä joukon muita vähemmän ilmeisiä ongelmia. Yleisimmin käytetyt jäljitysliput on tarkistettu.

Lähteet:

» SQL Serverin odotustilastot
» SQL Server odota tilastoja tai kerro minulle, missä se sattuu
» Järjestelmänäkymä sys.dm_os_schedulers
» Zabbixin käyttäminen MS SQL Server -tietokannan valvontaan
» SQL Lifestyle
» Jäljitysliput
» sql.ru

Lähde: will.com

Lisää kommentti