ProHoster > Dienoraštis > Administravimas > Kai kurie MS SQL Server stebėjimo aspektai. Atsekimo vėliavėlių nustatymo gairės

Kai kurie MS SQL Server stebėjimo aspektai. Atsekimo vėliavėlių nustatymo gairės

pratarmė

Gana dažnai MS SQL Server DBVS vartotojai, kūrėjai ir administratoriai susiduria su duomenų bazės ar visos DBVS veikimo problemomis, todėl MS SQL Server stebėjimas yra labai aktualus.
Šis straipsnis yra straipsnio papildymas „Zabbix“ naudojimas MS SQL serverio duomenų bazei stebėti ir jis apims kai kuriuos MS SQL Server stebėjimo aspektus, visų pirma: kaip greitai nustatyti, kurių išteklių trūksta, taip pat rekomendacijas dėl sekimo vėliavėlių nustatymo.
Kad šie scenarijai veiktų, reikia sukurti inf schemą norimoje duomenų bazėje taip:
Inf schemos kūrimas

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

RAM trūkumo nustatymo metodas

Pirmasis RAM trūkumo rodiklis yra atvejis, kai MS SQL Server egzempliorius suvalgo visą jam skirtą RAM.
Norėdami tai padaryti, sukursime tokį inf.vRAM atvaizdą:
Inf.vRAM rodinio kūrimas

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;

Tada galite nustatyti, kad MS SQL Server egzempliorius sunaudoja visą jam skirtą atmintį pagal šią užklausą:

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

Jei SQL_server_physical_memory_in_use_Mb yra nuolat didesnis arba lygus SQL_server_committed_target_Mb, tada reikia patikrinti laukimo statistiką.
Norėdami nustatyti RAM trūkumą naudodami laukimo statistiką, sukurkime inf.vWaits rodinį:
Vaizdo inf.vWaits kūrimas

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

Tokiu atveju galite nustatyti RAM trūkumą naudodami šią užklausą:

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

Čia reikia atkreipti dėmesį į rodiklius Percentage ir AvgWait_S. Jei jie yra reikšmingi visuma, tada yra labai didelė tikimybė, kad MS SQL Server egzemplioriui neužteks RAM. Reikšmingos vertės nustatomos atskirai kiekvienai sistemai. Tačiau galite pradėti nuo šių: Procentas>=1 ir AvgWait_S>=0.005.
Norėdami išvesti indikatorius į stebėjimo sistemą (pavyzdžiui, Zabbix), galite sukurti šias dvi užklausas:

  1. kiek laukimo tipų procentais užima RAM (visų tokių laukimų tipų suma): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. kiek RAM laukimo tipų trunka milisekundėmis (maksimali visų vidutinių vėlavimų vertė visiems tokiems laukimo tipams): 
    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'
  );

Remiantis gautų šių dviejų rodiklių verčių dinamika, galime daryti išvadą, ar pakanka RAM MS SQL Server egzemplioriui.

CPU perkrovos aptikimo metodas

Norint nustatyti procesoriaus laiko trūkumą, pakanka naudoti sistemos rodinį sys.dm_os_schedulers. Čia, jei runnable_tasks_count yra nuolat didesnis nei 1, tada yra didelė tikimybė, kad branduolių skaičiaus nepakanka MS SQL Server egzemplioriui.
Norėdami išvesti indikatorių stebėjimo sistemai (pavyzdžiui, Zabbix), galite sukurti šią užklausą:

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

Remdamiesi gautų šio rodiklio verčių dinamika, galime daryti išvadą, ar MS SQL Server egzemplioriui pakanka procesoriaus laiko (procesoriaus branduolių skaičiaus).
Tačiau svarbu nepamiršti, kad pačios užklausos vienu metu gali prašyti kelių gijų. Ir kartais optimizatorius negali teisingai įvertinti pačios užklausos sudėtingumo. Tada užklausai gali būti skirta per daug gijų, kurių negalima apdoroti tuo pačiu metu nurodytu laiku. Tai taip pat sukelia laukimo tipą, susijusį su procesoriaus laiko stoka ir planuoklių, naudojančių konkrečius procesoriaus branduolius, eilės augimu, t. y. runnable_tasks_count indikatorius tokiomis sąlygomis augs.
Tokiu atveju, prieš didinant procesoriaus branduolių skaičių, būtina teisingai sukonfigūruoti paties MS SQL Server egzemplioriaus lygiagretumo ypatybes, o nuo 2016 m. versijos - teisingai sukonfigūruoti reikalingų duomenų bazių lygiagretumo ypatybes:
Kai kurie MS SQL Server stebėjimo aspektai. Atsekimo vėliavėlių nustatymo gairės

Kai kurie MS SQL Server stebėjimo aspektai. Atsekimo vėliavėlių nustatymo gairės
Čia turėtumėte atkreipti dėmesį į šiuos parametrus:

  1. Max Degree of Parallelism – nustato maksimalų gijų, kurias galima priskirti kiekvienai užklausai, skaičių (numatytasis yra 0 – riboja tik pati operacinė sistema ir MS SQL Server leidimas)
  2. Lygiagretumo išlaidų slenkstis – apskaičiuota lygiagretumo kaina (numatytasis yra 5)
  3. Max DOP – nustato maksimalų gijų skaičių, kurį galima priskirti kiekvienai užklausai duomenų bazės lygiu (bet ne daugiau kaip ypatybės „Max Degree of Parallelism“ vertė) (numatytasis nustatymas yra 0 – riboja tik pati operacinė sistema ir „MS SQL Server“ leidimas, taip pat viso „MS SQL Server“ egzemplioriaus „Max Degree of Parallelism“ nuosavybės apribojimas)

Čia neįmanoma pateikti vienodai gero recepto visiems atvejams, t.y. reikia analizuoti sunkias užklausas.
Remdamasis savo patirtimi, rekomenduoju šį OLTP sistemų veiksmų algoritmą lygiagretumo savybėms nustatyti:

  1. pirmiausia išjunkite lygiagretumą nustatydami viso egzemplioriaus maksimalų lygiagretumo laipsnį į 1
  2. analizuoti sunkiausias užklausas ir parinkti jiems optimalų gijų skaičių
  3. nustatykite maksimalų lygiagretumo laipsnį į pasirinktą optimalų gijų skaičių, gautą iš 2 veiksmo, ir konkrečioms duomenų bazėms nustatykite maksimalią DOP reikšmę, gautą iš 2 veiksmo kiekvienai duomenų bazei.
  4. išanalizuoti sunkiausias užklausas ir nustatyti neigiamą kelių gijų poveikį. Jei taip, padidinkite lygiagretumo išlaidų slenkstį.
    Sistemoms, tokioms kaip 1C, Microsoft CRM ir Microsoft NAV, daugeliu atvejų tinka kelių gijų draudimas.

Be to, jei yra standartinis leidimas, tada daugeliu atvejų kelių gijų draudimas yra tinkamas dėl to, kad šiame leidime yra ribotas procesoriaus branduolių skaičius.
OLAP sistemoms aukščiau aprašytas algoritmas netinka.
Iš savo patirties aš rekomenduoju šį veiksmų algoritmą OLAP sistemoms lygiagretumo savybėms nustatyti:

  1. analizuoti sunkiausias užklausas ir parinkti jiems optimalų gijų skaičių
  2. nustatykite maksimalų lygiagretumo laipsnį į pasirinktą optimalų gijų skaičių, gautą iš 1 veiksmo, ir konkrečioms duomenų bazėms nustatykite maksimalią DOP reikšmę, gautą iš 1 veiksmo kiekvienai duomenų bazei.
  3. analizuoti sunkiausias užklausas ir nustatyti neigiamą lygiagretumo ribojimo poveikį. Jei taip, sumažinkite lygiagretumo vertės sąnaudų slenkstį arba pakartokite šio algoritmo 1–2 veiksmus.

Tai yra, OLTP sistemose pereiname nuo vienos gijos prie kelių gijų, o OLAP sistemose, priešingai, nuo kelių sriegių pereiname prie vienos gijos. Taigi galite pasirinkti optimalius lygiagretumo parametrus tiek konkrečiai duomenų bazei, tiek visam MS SQL Server egzemplioriui.
Taip pat svarbu suprasti, kad paralelizmo ypatybių parametrai turi būti keičiami laikui bėgant, atsižvelgiant į MS SQL Server našumo stebėjimo rezultatus.

Trace vėliavėlių nustatymo gairės

Remdamasis savo ir kolegų patirtimi, siekdamas optimalaus našumo, rekomenduoju 2008–2016 m. versijų MS SQL Server paslaugos vykdymo lygiu nustatyti šias sekimo vėliavėles:

  1. 610 – Sumažintas intarpų registravimas į indeksuotas lenteles. Gali padėti įterpti į lenteles, kuriose yra daug įrašų ir daug operacijų, dažnai WRITELOG laukiant indeksų pasikeitimų
  2. 1117 – jei failas failų grupėje atitinka automatinio augimo slenksčio reikalavimus, visi failų grupėje esantys failai auga
  3. 1118 – priverčia visus objektus išsidėstyti skirtingu mastu (mišraus masto draudimas), o tai sumažina poreikį nuskaityti SGAM puslapį, kuris naudojamas įvairiems dydžiams sekti.
  4. 1224 – išjungiamas užrakto eskalavimas pagal užraktų skaičių. Tačiau per didelis atminties naudojimas gali sukelti užrakto eskalavimą
  5. 2371 – pakeičia fiksuotą automatinio statistikos atnaujinimo slenkstį į dinaminio automatinio statistikos atnaujinimo slenkstį. Svarbu atnaujinti didelių lentelių užklausų planus, kai neteisingas įrašų skaičius sukelia klaidingus vykdymo planus
  6. 3226 – klaidų žurnale išjungiami sėkmingi atsarginės kopijos pranešimai
  7. 4199 – apima užklausų optimizavimo priemonės pakeitimus, išleistus CU ir SQL serverio pakeitimų paketuose
  8. 6532-6534 – apima erdvinių duomenų tipų užklausų operacijų našumo patobulinimus
  9. 8048 – konvertuoja NUMA skaidytus atminties objektus į CPU skaidytus objektus
  10. 8780 – įgalina papildomą laiko paskirstymą užklausų planavimui. Kai kurios užklausos be šios žymos gali būti atmestos, nes jos neturi užklausų plano (labai reta klaida)
  11. 8780 - 9389 - Įgalinamas papildomas dinaminis suteikimo atminties buferis paketinio režimo pareiškimams, kuris leidžia paketinio režimo operatoriui prašyti papildomos atminties ir išvengti duomenų perkėlimo į tempdb, jei yra papildomos atminties

Taip pat iki 2016 m. naudinga įjungti sekimo vėliavėlę 2301, kuri įgalina patobulintą sprendimų palaikymo optimizavimą ir taip padeda pasirinkti teisingesnius užklausų planus. Tačiau nuo 2016 m. versijos tai dažnai neigiamai veikia gana ilgą bendrą užklausos vykdymo laiką.
Be to, sistemoms, kuriose yra daug indeksų (pavyzdžiui, 1C duomenų bazėms), rekomenduoju įjungti sekimo vėliavėlę 2330, kuri išjungia indekso naudojimo rinkimą, o tai paprastai turi teigiamą poveikį sistemai.
Norėdami gauti daugiau informacijos apie pėdsakų vėliavėles, žr čia
Remiantis aukščiau pateikta nuoroda, taip pat svarbu atsižvelgti į MS SQL Server versijas ir versijas, nes naujesnėms versijoms kai kurios sekimo vėliavėlės yra įjungtos pagal numatytuosius nustatymus arba neturi jokio poveikio.
Galite įjungti ir išjungti sekimo vėliavėlę atitinkamai naudodami DBCC TRACEON ir DBCC TRACEOFF komandas. Daugiau informacijos žr čia
Galite sužinoti sekimo vėliavėlių būseną naudodami komandą DBCC TRACESTATUS: daugiau
Kad sekimo vėliavėlės būtų įtrauktos į automatinį MS SQL Server paslaugos paleidimą, turite eiti į SQL serverio konfigūracijos tvarkyklę ir pridėti šias sekimo vėliavėles naudodami -T paslaugos ypatybėse:
Kai kurie MS SQL Server stebėjimo aspektai. Atsekimo vėliavėlių nustatymo gairės

rezultatai

Šiame straipsnyje buvo išanalizuoti kai kurie MS SQL Server stebėjimo aspektai, kurių pagalba galite greitai nustatyti RAM ir laisvo procesoriaus laiko trūkumą bei daugybę kitų ne tokių akivaizdžių problemų. Apžvelgtos dažniausiai naudojamos pėdsakų vėliavėlės.

Šaltiniai:

» SQL serverio laukimo statistika
» SQL serverio laukimo statistika arba pasakykite man, kur tai kenkia
» Sistemos rodinys sys.dm_os_schedulers
» „Zabbix“ naudojimas MS SQL serverio duomenų bazei stebėti
» SQL gyvenimo būdas
» Pėdsakai vėliavos
» sql.ru

Šaltinis: www.habr.com

Добавить комментарий