🥇MS SQL Server monitoringining ba'zi jihatlari. Trace bayroqlarini o'rnatish bo'yicha ko'rsatmalar

muqaddima

Ko'pincha MS SQL Server DBMS foydalanuvchilari, ishlab chiquvchilari va ma'murlari ma'lumotlar bazasi yoki umuman ma'lumotlar bazasi ishlashi bilan bog'liq muammolarga duch kelishadi, shuning uchun MS SQL Server monitoringi juda dolzarbdir.
Ushbu maqola maqolaga qo'shimcha hisoblanadi MS SQL Server ma'lumotlar bazasini kuzatish uchun Zabbix-dan foydalanish va u MS SQL Server monitoringining ba'zi jihatlarini qamrab oladi, xususan: qanday resurslar etishmayotganligini tezda aniqlash, shuningdek, kuzatuv bayroqlarini o'rnatish bo'yicha tavsiyalar.
Quyidagi skriptlar ishlashi uchun siz kerakli ma'lumotlar bazasida quyidagi tarzda inf sxemasini yaratishingiz kerak:
Inf sxemasini yaratish

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

RAM etishmasligini aniqlash usuli

Operativ xotira etishmasligining birinchi ko'rsatkichi MS SQL Server nusxasi unga ajratilgan barcha operativ xotirani yeb qo'yishidir.
Buning uchun biz inf.vRAM ning quyidagi tasvirini yaratamiz:
inf.vRAM ko'rinishini yaratish

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;

Keyin MS SQL Server nusxasi unga ajratilgan barcha xotirani iste'mol qilishini quyidagi so'rov orqali aniqlashingiz mumkin:

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

Agar SQL_server_physical_memory_in_use_Mb doimiy ravishda SQL_server_committed_target_Mb dan katta yoki teng bo'lsa, kutish statistikasini tekshirish kerak.
Kutish statistikasi orqali operativ xotira etishmasligini aniqlash uchun inf.vWaits ko'rinishini yaratamiz:
inf.vWaits ko'rinishini yaratish

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

Bunday holda, siz quyidagi so'rov bilan RAM etishmasligini aniqlashingiz mumkin:

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

Bu erda siz foiz va AvgWait_S ko'rsatkichlariga e'tibor berishingiz kerak. Agar ular o'zlarining umumiyligida muhim bo'lsa, MS SQL Server misoli uchun RAM etarli emasligi ehtimoli juda yuqori. Muhim qiymatlar har bir tizim uchun alohida belgilanadi. Biroq, siz quyidagilardan boshlashingiz mumkin: Foiz>=1 va AvgWait_S>=0.005.
Monitoring tizimiga ko'rsatkichlarni chiqarish uchun (masalan, Zabbix) siz quyidagi ikkita so'rovni yaratishingiz mumkin:

Operativ xotira foizda qancha kutish turlarini egallaydi (barcha kutish turlarining yig'indisi):

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

millisekundlarda qancha operativ xotira kutish turini oladi (barcha bunday kutish turlari uchun barcha o'rtacha kechikishlarning maksimal qiymati):

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

Ushbu ikki ko'rsatkich uchun olingan qiymatlar dinamikasiga asoslanib, biz MS SQL Server namunasi uchun RAM etarli yoki yo'qligini xulosa qilishimiz mumkin.

CPU ortiqcha yukini aniqlash usuli

Protsessor vaqtining etishmasligini aniqlash uchun sys.dm_os_schedulers tizim ko'rinishidan foydalanish kifoya. Bu erda, agar runnable_tasks_count doimiy ravishda 1 dan katta bo'lsa, u holda yadrolar soni MS SQL Server misoli uchun etarli emasligi ehtimoli yuqori.
Monitoring tizimiga indikatorni chiqarish uchun (masalan, Zabbix) quyidagi so'rovni yaratishingiz mumkin:

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

Ushbu ko'rsatkich uchun olingan qiymatlar dinamikasiga asoslanib, biz MS SQL Server namunasi uchun protsessor vaqti (protsessor yadrolari soni) etarli yoki yo'qligini xulosa qilishimiz mumkin.
Biroq, so'rovlarning o'zi bir vaqtning o'zida bir nechta mavzularni so'rashi mumkinligini yodda tutish kerak. Va ba'zida optimallashtiruvchi so'rovning murakkabligini to'g'ri baholay olmaydi. Keyin so'rovga bir vaqtning o'zida ma'lum bir vaqtda ishlov berilmaydigan juda ko'p mavzular ajratilishi mumkin. Va bu, shuningdek, protsessor vaqtining etishmasligi bilan bog'liq kutish turiga va ma'lum CPU yadrolaridan foydalanadigan rejalashtiruvchilar uchun navbatning o'sishiga olib keladi, ya'ni bunday sharoitlarda runnable_tasks_count indikatori o'sadi.
Bunday holda, protsessor yadrolari sonini ko'paytirishdan oldin, MS SQL Server nusxasining parallellik xususiyatlarini to'g'ri sozlash va 2016 versiyasidan kerakli ma'lumotlar bazalarining parallellik xususiyatlarini to'g'ri sozlash kerak:

Bu erda siz quyidagi parametrlarga e'tibor berishingiz kerak:

Parallellikning maksimal darajasi - har bir so'rov uchun ajratilishi mumkin bo'lgan maksimal ip sonini belgilaydi (standart 0 - faqat operatsion tizimning o'zi va MS SQL Server nashri tomonidan cheklangan)
Parallellik uchun xarajat chegarasi - parallelizmning taxminiy qiymati (standart 5)
Max DOP - ma'lumotlar bazasi darajasida har bir so'rovga ajratilishi mumkin bo'lgan maksimal ip sonini o'rnatadi (lekin "Paralellikning maksimal darajasi" xususiyati qiymatidan oshmasligi kerak) (standart 0 - faqat operatsion tizimning o'zi tomonidan cheklangan va MS SQL Server nashri, shuningdek, MS SQL Serverning butun nusxasining "Parallellikning maksimal darajasi" xususiyatiga cheklovlar)

Bu erda barcha holatlar uchun bir xil darajada yaxshi retsept berish mumkin emas, ya'ni siz og'ir so'rovlarni tahlil qilishingiz kerak.
O'z tajribamdan parallelizm xususiyatlarini o'rnatish uchun OLTP tizimlari uchun quyidagi harakatlar algoritmini tavsiya qilaman:

birinchi misol bo'ylab Parallellikning maksimal darajasini 1 ga o'rnatish orqali parallelizmni o'chiring
eng og'ir so'rovlarni tahlil qiling va ular uchun teglarning optimal sonini tanlang
Parallellikning maksimal darajasini 2-bosqichda olingan iplarning tanlangan optimal soniga o'rnating va ma'lum ma'lumotlar bazalari uchun har bir ma'lumotlar bazasi uchun 2-bosqichdan olingan maksimal DOP qiymatini o'rnating.
eng og'ir so'rovlarni tahlil qilish va multithreadingning salbiy ta'sirini aniqlash. Agar shunday bo'lsa, Parallelizm uchun xarajat chegarasini oshiring.
1C, Microsoft CRM va Microsoft NAV kabi tizimlar uchun ko'p hollarda ko'p oqimni taqiqlash mos keladi.

Bundan tashqari, agar standart nashr mavjud bo'lsa, ko'p hollarda ko'p ish zarralarini taqiqlash ushbu nashr protsessor yadrolari sonida cheklanganligi sababli mos keladi.
OLAP tizimlari uchun yuqorida tavsiflangan algoritm mos emas.
O'z tajribamdan kelib chiqib, parallelizm xususiyatlarini o'rnatish uchun OLAP tizimlari uchun quyidagi harakatlar algoritmini tavsiya qilaman:

eng og'ir so'rovlarni tahlil qiling va ular uchun teglarning optimal sonini tanlang
Parallellikning maksimal darajasini 1-bosqichda olingan iplarning tanlangan optimal soniga o'rnating va ma'lum ma'lumotlar bazalari uchun har bir ma'lumotlar bazasi uchun 1-bosqichdan olingan maksimal DOP qiymatini o'rnating.
eng og'ir so'rovlarni tahlil qilish va parallellikni cheklashning salbiy ta'sirini aniqlash. Agar shunday bo'lsa, Parallellik qiymati uchun xarajat chegarasini pasaytiring yoki ushbu algoritmning 1-2 bosqichlarini takrorlang.

Ya'ni, OLTP tizimlari uchun biz bir oqimdan ko'p tarmoqliga o'tamiz, OLAP-tizimlari uchun esa, aksincha, ko'p tarmoqlidan bitta oqimga o'tamiz. Shunday qilib, siz ma'lum bir ma'lumotlar bazasi va MS SQL Serverning butun nusxasi uchun optimal parallellik sozlamalarini tanlashingiz mumkin.
MS SQL Server ishlashini monitoring qilish natijalariga ko'ra, parallellik xususiyatlarining sozlamalarini vaqt o'tishi bilan o'zgartirish kerakligini tushunish ham muhimdir.

Trace bayroqlarini o'rnatish bo'yicha ko'rsatmalar

O'z tajribamdan va hamkasblarimning tajribasidan optimal ishlash uchun men 2008-2016 versiyalari uchun MS SQL Server xizmatining ishga tushirish darajasida quyidagi kuzatuv bayroqlarini o'rnatishni tavsiya qilaman:

610 - Indekslangan jadvallarga qo'shimchalar ro'yxatini qisqartirish. Ko'p yozuvlar va ko'plab tranzaksiyalarga ega jadvallarga qo'shimchalar kiritishda yordam berishi mumkin, WRITELOG indekslardagi o'zgarishlarni tez-tez kutadi.
1117 - Agar fayl guruhidagi fayl avtomatik o'sish chegarasi talablariga javob bersa, fayllar guruhidagi barcha fayllar o'sadi
1118 - Barcha ob'ektlarni turli darajada joylashtirishga majbur qiladi (aralash kengaytmalarni taqiqlash), bu aralash hajmlarni kuzatish uchun ishlatiladigan SGAM sahifasini skanerlash zaruratini kamaytiradi.
1224 - Qulflar soniga qarab qulfni kuchaytirishni o'chiradi. Biroq, xotiradan haddan tashqari foydalanish blokirovkaning kuchayishiga olib kelishi mumkin
2371 - Ruxsat etilgan avtomatik statistika yangilanish chegarasini dinamik avtomatik statistika yangilanish chegarasiga o'zgartiradi. Katta jadvallar uchun so'rov rejalarini yangilash uchun muhim, bu erda yozuvlarning noto'g'ri soni noto'g'ri bajarilishiga olib keladi
3226 - Xatolar jurnalida zaxira muvaffaqiyati haqida xabarlarni bosadi
4199 - CU va SQL Server xizmat paketlarida chiqarilgan so'rovlar optimallashtiruvchisiga kiritilgan o'zgarishlarni o'z ichiga oladi
6532-6534 - fazoviy ma'lumotlar turlari bo'yicha so'rovlar uchun ishlash yaxshilanishini o'z ichiga oladi
8048 - NUMA bo'lingan xotira ob'ektlarini protsessor bo'lingan ob'ektlarga o'zgartiradi
8780 - So'rovni rejalashtirish uchun qo'shimcha vaqt ajratishni yoqadi. Bu bayroqsiz baʼzi soʻrovlar rad etilishi mumkin, chunki ularda soʻrov rejasi yoʻq (juda kam uchraydigan xatolik)
8780 - 9389 - Ommaviy ish rejimi bayonotlari uchun qo'shimcha dinamik grant xotira buferini yoqadi, bu esa ommaviy rejim operatoriga ko'proq xotira so'rash va agar ko'proq xotira mavjud bo'lsa, ma'lumotlarni tempdb ga ko'chirishdan qochish imkonini beradi.

Shuningdek, 2016-yildan oldin 2301 kuzatuv bayrog‘ini yoqish foydali bo‘ladi, bu esa qarorlarni qo‘llab-quvvatlashni optimallashtirishni takomillashtiradi va shu tariqa to‘g‘ri so‘rov rejalarini tanlashga yordam beradi. Biroq, 2016-versiyaga ko'ra, u ko'pincha juda uzoq umumiy so'rovlarni bajarish vaqtlariga salbiy ta'sir ko'rsatadi.
Bundan tashqari, indekslari ko'p bo'lgan tizimlar uchun (masalan, 1C ma'lumotlar bazalari uchun) men indeksdan foydalanishni to'plashni o'chirib qo'yadigan 2330 iz bayrog'ini yoqishni tavsiya qilaman, bu umuman tizimga ijobiy ta'sir qiladi.
Track bayroqlari haqida ko'proq ma'lumot olish uchun qarang shu yerda
Yuqoridagi havoladan MS SQL Server versiyalari va tuzilmalarini ko'rib chiqish ham muhim, chunki yangi versiyalar uchun ba'zi kuzatuv bayroqlari sukut bo'yicha yoqilgan yoki hech qanday ta'sir qilmaydi.
Siz mos ravishda DBCC TRACEON va DBCC TRACEOFF buyruqlari yordamida kuzatuv bayrog'ini yoqishingiz va o'chirishingiz mumkin. Batafsil ma'lumot uchun qarang shu yerda
DBCC TRACESTATUS buyrug'i yordamida kuzatuv bayroqlarining holatini olishingiz mumkin: batafsil ma'lumot
MS SQL Server xizmatining avtomatik ishga tushirilishiga kuzatuv bayroqlari qo'shilishi uchun siz SQL Server konfiguratsiya menejeriga o'tishingiz va ushbu kuzatuv bayroqlarini xizmat xususiyatlariga -T orqali qo'shishingiz kerak:

natijalar

Ushbu maqolada MS SQL Server monitoringining ba'zi jihatlari tahlil qilindi, ularning yordami bilan siz tezkor xotira va bo'sh protsessor vaqtining etishmasligini, shuningdek, boshqa bir qator kamroq aniq muammolarni tezda aniqlashingiz mumkin. Eng ko'p ishlatiladigan kuzatuv bayroqlari ko'rib chiqildi.

Manbalar:

» SQL Server kutish statistikasi
» SQL Server statistikasini kuting yoki qayerda og'riyotganini ayting
» Tizim ko'rinishi sys.dm_os_schedulers
» MS SQL Server ma'lumotlar bazasini kuzatish uchun Zabbix-dan foydalanish
» SQL turmush tarzi
» Track bayroqlari
» sql.ru

Manba: www.habr.com

MS SQL Server monitoringining ba'zi jihatlari. Trace bayroqlarini o'rnatish bo'yicha ko'rsatmalar