ProHoster > Blogi > Haldamine > Mõned MS SQL Serveri jälgimise aspektid. Juhised jälgimislippude seadistamiseks

Mõned MS SQL Serveri jälgimise aspektid. Juhised jälgimislippude seadistamiseks

Eessõna

Üsna sageli puutuvad MS SQL Serveri DBMS-i kasutajad, arendajad ja administraatorid kokku andmebaasi või DBMS-i kui terviku jõudlusprobleemidega, seega on MS SQL Serveri jälgimine väga asjakohane.
See artikkel on artikli täiendus Zabbixi kasutamine MS SQL Serveri andmebaasi jälgimiseks ja see hõlmab mõningaid MS SQL Serveri jälgimise aspekte, eelkõige: kuidas kiiresti kindlaks teha, millised ressursid puuduvad, samuti soovitusi jälgimislippude seadistamiseks.
Järgmiste skriptide töötamiseks peate soovitud andmebaasis looma inf-skeemi järgmiselt.
Infoskeemi loomine

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

RAM-i puudumise tuvastamise meetod

RAM-i puudumise esimene näitaja on juhtum, kui MS SQL Serveri eksemplar sööb ära kogu talle eraldatud RAM-i.
Selleks loome inf.vRAM-i järgmise esituse:
Inf.vRAM-vaate loomine

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;

Seejärel saate kindlaks teha, et MS SQL Serveri eksemplar tarbib kogu talle järgmise päringu abil eraldatud mälu:

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

Kui SQL_server_physical_memory_in_use_Mb on püsivalt suurem kui SQL_server_committed_target_Mb või sellega võrdne, tuleks ootestatistikat kontrollida.
RAM-i puudumise kindlakstegemiseks ootestatistika abil loome kuva inf.vWaits:
Vaate inf.vWaits loomine

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

Sel juhul saate RAM-i puudumise kindlaks teha järgmise päringuga:

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

Siin tuleb tähelepanu pöörata näitajatele Percentage ja AvgWait_S. Kui need on tervikuna märkimisväärsed, siis on väga suur tõenäosus, et MS SQL Serveri eksemplari jaoks pole piisavalt RAM-i. Olulised väärtused määratakse iga süsteemi jaoks eraldi. Siiski võite alustada järgmistest: Protsent>=1 ja AvgWait_S>=0.005.
Indikaatorite väljastamiseks jälgimissüsteemi (näiteks Zabbix) saate luua kaks järgmist päringut.

  1. mitut tüüpi ooteaegu RAM hõivab protsentides (kõikide seda tüüpi ootamiste summa): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. mitu RAM-i ootetüüpi millisekundites kulub (kõigi selliste ootetüüpide keskmiste viivituste maksimaalne väärtus): 
    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'
  );

Nende kahe näitaja saadud väärtuste dünaamika põhjal saame järeldada, kas MS SQL Serveri eksemplari jaoks on piisavalt RAM-i.

Protsessori ülekoormuse tuvastamise meetod

Protsessori ajapuuduse tuvastamiseks piisab süsteemivaate sys.dm_os_schedulers kasutamisest. Kui siin on runnable_tasks_count pidevalt suurem kui 1, siis on suur tõenäosus, et tuumade arvust ei piisa MS SQL Serveri eksemplari jaoks.
Indikaatori väljastamiseks seiresüsteemi (näiteks Zabbix) saate luua järgmise päringu:

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

Selle indikaatori saadud väärtuste dünaamika põhjal saame järeldada, kas MS SQL Serveri eksemplari jaoks on piisavalt protsessori aega (CPU tuumade arv).
Siiski on oluline meeles pidada asjaolu, et taotlused võivad taotleda mitut lõime korraga. Ja mõnikord ei saa optimeerija päringu enda keerukust õigesti hinnata. Siis võidakse päringule eraldada liiga palju lõime, mida ei saa antud ajahetkel samal ajal töödelda. Ja see põhjustab ka teatud tüüpi ootamist, mis on seotud protsessori aja puudumisega, ja konkreetseid protsessorituumi kasutavate planeerijate järjekorra kasvuga, st näitaja runnable_tasks_count kasvab sellistes tingimustes.
Sel juhul tuleb enne CPU tuumade arvu suurendamist õigesti konfigureerida MS SQL Serveri eksemplari enda paralleelsuse atribuudid ja alates 2016. aasta versioonist õigesti konfigureerida vajalike andmebaaside paralleelsuse atribuudid:
Mõned MS SQL Serveri jälgimise aspektid. Juhised jälgimislippude seadistamiseks

Mõned MS SQL Serveri jälgimise aspektid. Juhised jälgimislippude seadistamiseks
Siin peaksite pöörama tähelepanu järgmistele parameetritele:

  1. Max Degree of Parallelism – määrab igale päringule eraldatavate lõimede maksimaalse arvu (vaikimisi on 0 – piirab ainult operatsioonisüsteem ise ja MS SQL Serveri väljaanne)
  2. Paralleelsuse kululävi – paralleelsuse hinnanguline kulu (vaikimisi on 5)
  3. Max DOP - määrab maksimaalse lõimede arvu, mida saab andmebaasi tasemel igale päringule eraldada (kuid mitte rohkem kui atribuudi "Max Degree of Parallelism" väärtus) (vaikimisi on 0 - piirab ainult operatsioonisüsteem ise ja MS SQL Serveri väljaanne, samuti kogu MS SQL Serveri eksemplari atribuudi "Max Degree of Parallelism" piirang)

Siin on võimatu anda kõigile juhtumitele võrdselt head retsepti, st peate analüüsima raskeid päringuid.
Omast kogemusest soovitan paralleelsuse omaduste seadistamiseks OLTP-süsteemide jaoks järgmist toimingute algoritmi:

  1. esmalt keelake paralleelsus, määrates eksemplariülese maksimaalse paralleelsuse astme väärtuseks 1
  2. analüüsige kõige raskemaid taotlusi ja valige nende jaoks optimaalne lõimede arv
  3. seadke maksimaalne paralleelsuse aste valitud optimaalsele lõimede arvule, mis saadi etapist 2, ja konkreetsete andmebaaside jaoks määrake iga andmebaasi jaoks toimingust 2 saadud maksimaalne DOP väärtus
  4. analüüsida kõige raskemaid taotlusi ja tuvastada mitmelõime negatiivse mõju. Kui on, siis suurendage paralleelsuse kululäve.
    Selliste süsteemide jaoks nagu 1C, Microsoft CRM ja Microsoft NAV sobib enamikul juhtudel mitme lõimega ühendamise keelamine

Samuti, kui on olemas Standard väljaanne, siis enamikul juhtudel sobib mitmelõimestamise keeld, kuna sellel väljaandel on CPU tuumade arv piiratud.
OLAP-süsteemide jaoks ülalkirjeldatud algoritm ei sobi.
Omast kogemusest soovitan paralleelsuse atribuutide seadistamiseks OLAP-süsteemide jaoks järgmist tegevusalgoritmi:

  1. analüüsige kõige raskemaid taotlusi ja valige nende jaoks optimaalne lõimede arv
  2. seadke maksimaalne paralleelsuse aste valitud optimaalsele lõimede arvule, mis saadi etapist 1, ja konkreetsete andmebaaside jaoks määrake iga andmebaasi jaoks toimingust 1 saadud maksimaalne DOP väärtus
  3. analüüsida kõige raskemaid päringuid ja tuvastada samaaegsuse piiramise negatiivne mõju. Kui on, siis kas langetage paralleelsuse kululävi või korrake selle algoritmi samme 1-2

See tähendab, et OLTP-süsteemide puhul läheme ühe lõimega režiimilt mitme lõimega ja OLAP-süsteemide puhul, vastupidi, läheme mitme lõimega režiimilt ühe lõimele. Seega saate valida optimaalsed paralleelsuse sätted nii konkreetse andmebaasi kui ka kogu MS SQL Serveri eksemplari jaoks.
Samuti on oluline mõista, et paralleelsuse atribuutide sätteid tuleb aja jooksul muuta, lähtudes MS SQL Serveri jõudluse jälgimise tulemustest.

Juhised jälgimislippude seadistamiseks

Enda ja kolleegide kogemuste põhjal soovitan optimaalse jõudluse tagamiseks määrata MS SQL Serveri teenuse käitamistasemel versioonide 2008–2016 jaoks järgmised jälgimislipud:

  1. 610 – Indekseeritud tabelite lisade logimise vähendamine. Saab aidata paljude kirjete ja paljude tehingutega tabelitesse sisestamisel, WRITELOG ootab sageli pikki indeksite muutusi
  2. 1117 - kui failirühmas olev fail vastab automaatse kasvu läve nõuetele, kasvavad kõik failirühma failid
  3. 1118 – sunnib kõik objektid asuma erineval määral (segaulatusliku kasutamise keeld), mis vähendab vajadust skaneerida SGAM-i lehte, mida kasutatakse segaulatustel jälgimiseks.
  4. 1224 – keelab lukkude arvu eskalatsiooni. Liigne mälukasutus võib aga käivitada lukustuse eskalatsiooni
  5. 2371 – muudab fikseeritud automaatse statistika värskendamise läve dünaamilise automaatse statistika värskendamise läveks. See on oluline suurte tabelite päringuplaanide värskendamisel, kus kirjete vale arv põhjustab ekslikke täitmisplaane
  6. 3226 – blokeerib vealogis varundamise õnnestumisteateid
  7. 4199 – sisaldab CU-des ja SQL Serveri hoolduspakettides avaldatud päringu optimeerija muudatusi
  8. 6532-6534 – sisaldab ruumiandmetüüpide päringutoimingute jõudluse täiustusi
  9. 8048 – teisendab NUMA partitsioonitud mäluobjekte CPU-ga partitsioonitud objektideks
  10. 8780 – lubab päringu planeerimiseks täiendava aja jaotuse. Mõned taotlused ilma selle lipuga võidakse tagasi lükata, kuna neil pole päringuplaani (väga harv viga)
  11. 8780 - 9389 - Lubab pakettrežiimi avalduste jaoks täiendava dünaamilise lubade mälupuhvri, mis võimaldab pakkrežiimi operaatoril nõuda rohkem mälu ja vältida andmete teisaldamist tempdb-sse, kui rohkem mälu on saadaval

Ka enne 2016. aastat on kasulik lubada jälgimislipp 2301, mis võimaldab täiustatud otsustustoe optimeerimist ja aitab seega valida õigemaid päringuplaane. Kuid alates versioonist 2016 on sellel sageli negatiivne mõju üsna pikkadele üldistele päringu täitmisaegadele.
Samuti soovitan paljude indeksidega süsteemide puhul (näiteks 1C andmebaaside jaoks) lubada jälgimislipu 2330, mis keelab indeksi kasutamise kogumise, millel on üldiselt süsteemile positiivne mõju.
Lisateavet jälgimislippude kohta vt siin
Ülaltoodud lingi põhjal on oluline arvestada ka MS SQL Serveri versioonide ja järgudega, kuna uuemate versioonide puhul on mõned jälgimislipud vaikimisi lubatud või neil puudub mõju.
Jälgimislipu saate sisse ja välja lülitada vastavalt käskudega DBCC TRACEON ja DBCC TRACEOFF. Täpsemat teavet vt siin
Jälituslippude oleku saate vaadata käsuga DBCC TRACESTATUS: rohkem
Jälituslippude kaasamiseks MS SQL Serveri teenuse automaatkäivitusse peate minema SQL Serveri konfiguratsioonihaldurisse ja lisama need jälgimislipud teenuse atribuutides klahvi -T kaudu:
Mõned MS SQL Serveri jälgimise aspektid. Juhised jälgimislippude seadistamiseks

Tulemused

Selles artiklis analüüsiti mõningaid MS SQL Serveri jälgimise aspekte, mille abil saate kiiresti tuvastada RAM-i ja vaba CPU aja puudumise ning mitmed muud vähem ilmsed probleemid. Kõige sagedamini kasutatavad jäljelipud on üle vaadatud.

Allikad:

» SQL Serveri ootestatistika
» SQL Serveri ootestatistika või öelge mulle, kus see valutab
» Süsteemivaade sys.dm_os_schedulers
» Zabbixi kasutamine MS SQL Serveri andmebaasi jälgimiseks
» SQL elustiil
» Jäljelipud
» sql.ru

Allikas: www.habr.com

Lisa kommentaar