🥇Baadhi ya vipengele vya ufuatiliaji wa Seva ya MS SQL. Mwongozo wa Kuweka Alama za Ufuatiliaji

utangulizi

Mara nyingi, watumiaji, wasanidi programu na wasimamizi wa MS SQL Server DBMS hukutana na matatizo ya utendaji wa hifadhidata au DBMS kwa ujumla, kwa hivyo ufuatiliaji wa Seva ya MS SQL ni muhimu sana.
Makala hii ni nyongeza ya makala Kutumia Zabbix Kufuatilia Hifadhidata ya Seva ya MS SQL na itashughulikia baadhi ya vipengele vya ufuatiliaji wa Seva ya MS SQL, hasa: jinsi ya kubaini haraka ni rasilimali zipi zinazokosekana, pamoja na mapendekezo ya kuweka alama za ufuatiliaji.
Ili maandishi yafuatayo yafanye kazi, unahitaji kuunda schema ya inf kwenye hifadhidata unayotaka kama ifuatavyo:
Kuunda schema ya inf

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

Njia ya kugundua ukosefu wa RAM

Kiashiria cha kwanza cha ukosefu wa RAM ni kesi wakati mfano wa Seva ya MS SQL inakula RAM yote iliyotengwa kwake.
Ili kufanya hivyo, tutaunda uwakilishi ufuatao wa inf.vRAM:
Inaunda mwonekano wa 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;

Basi unaweza kuamua kuwa mfano wa Seva ya MS SQL hutumia kumbukumbu yote iliyotengwa kwake na swali lifuatalo:

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

Ikiwa SQL_server_physical_memory_in_use_Mb ni kubwa kuliko au sawa na SQL_server_committed_target_Mb, basi takwimu za kusubiri zinapaswa kuangaliwa.
Ili kubainisha ukosefu wa RAM kupitia takwimu za kusubiri, hebu tuunde mwonekano wa inf.vWaits:
Inaunda Mwonekano wa 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];

Katika kesi hii, unaweza kuamua ukosefu wa RAM na swali lifuatalo:

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

Hapa unahitaji kulipa kipaumbele kwa viashiria Asilimia na AvgWait_S. Ikiwa ni muhimu katika jumla yao, basi kuna uwezekano mkubwa sana kwamba hakuna RAM ya kutosha kwa mfano wa Seva ya MS SQL. Thamani muhimu huamuliwa kibinafsi kwa kila mfumo. Hata hivyo, unaweza kuanza na yafuatayo: Asilimia>=1 na AvgWait_S>=0.005.
Ili viashiria vya pato kwa mfumo wa ufuatiliaji (kwa mfano, Zabbix), unaweza kuunda maswali mawili yafuatayo:

ni aina ngapi za kusubiri zinachukuliwa na RAM kwa asilimia (jumla ya aina zote za kusubiri):

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

ni aina ngapi za kusubiri za RAM huchukua kwa milisekunde (thamani ya juu zaidi ya ucheleweshaji wote wa wastani kwa aina zote kama hizo za kungojea):

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

Kulingana na mienendo ya maadili yaliyopatikana kwa viashiria hivi viwili, tunaweza kuhitimisha ikiwa kuna RAM ya kutosha kwa mfano wa MS SQL Server.

Mbinu ya Kugundua Upakiaji wa CPU

Ili kutambua ukosefu wa muda wa processor, inatosha kutumia mtazamo wa mfumo wa sys.dm_os_schedulers. Hapa, ikiwa runnable_tasks_count ni kubwa kila mara kuliko 1, basi kuna uwezekano mkubwa kwamba idadi ya cores haitoshi kwa mfano wa Seva ya MS SQL.
Ili kutoa kiashiria kwa mfumo wa ufuatiliaji (kwa mfano, Zabbix), unaweza kuunda swali lifuatalo:

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

Kulingana na mienendo ya maadili yaliyopatikana ya kiashiria hiki, tunaweza kuhitimisha ikiwa kuna muda wa kutosha wa processor (idadi ya cores za CPU) kwa mfano wa Seva ya MS SQL.
Hata hivyo, ni muhimu kukumbuka ukweli kwamba maombi yenyewe yanaweza kuomba nyuzi nyingi mara moja. Na wakati mwingine kiboreshaji hakiwezi kukadiria kwa usahihi ugumu wa hoja yenyewe. Kisha ombi linaweza kutengwa nyuzi nyingi sana ambazo haziwezi kuchakatwa kwa wakati mmoja kwa wakati husika. Na hii pia husababisha aina ya kusubiri inayohusishwa na ukosefu wa muda wa kichakataji, na ukuaji wa foleni kwa wapanga ratiba wanaotumia cores maalum za CPU, yaani kiashiria cha runnable_tasks_count kitakua katika hali kama hizi.
Katika kesi hii, kabla ya kuongeza idadi ya cores za CPU, ni muhimu kusanidi kwa usahihi sifa za usawa za mfano wa MS SQL Server yenyewe, na kutoka kwa toleo la 2016, usanidi kwa usahihi sifa za usawa za hifadhidata zinazohitajika:

Hapa unapaswa kuzingatia vigezo vifuatavyo:

Kiwango cha Juu cha Usambamba - huweka idadi ya juu zaidi ya nyuzi ambazo zinaweza kugawiwa kwa kila ombi (chaguo-msingi ni 0 - imezuiwa tu na mfumo wa uendeshaji wenyewe na toleo la MS SQL Server)
Kiwango cha Gharama kwa Usambamba - makadirio ya gharama ya ulinganifu (chaguo-msingi ni 5)
Max DOP - huweka idadi ya juu zaidi ya nyuzi ambazo zinaweza kugawiwa kwa kila hoja katika kiwango cha hifadhidata (lakini si zaidi ya thamani ya mali ya "Max Degree of Parallelism") (chaguo-msingi ni 0 - imepunguzwa tu na mfumo wa uendeshaji yenyewe na toleo la Seva ya MS SQL, na vile vile kizuizi cha sifa ya "Max Degree of Parallelism" ya mfano mzima wa Seva ya MS SQL)

Hapa haiwezekani kutoa kichocheo kizuri kwa kesi zote, i.e. unahitaji kuchambua maswali mazito.
Kutoka kwa uzoefu wangu mwenyewe, ninapendekeza algorithm ifuatayo ya vitendo kwa mifumo ya OLTP ya kusanidi sifa za usawa:

kwanza zima usambamba kwa kuweka Kiwango cha Juu cha Usambamba cha mfano kwa 1
kuchambua maombi mazito zaidi na uchague idadi kamili ya nyuzi kwao
weka Kiwango cha Juu cha Usambamba kwa idadi kamili iliyochaguliwa ya nyuzi zilizopatikana kutoka kwa hatua ya 2, na kwa hifadhidata maalum weka thamani ya Max DOP iliyopatikana kutoka hatua ya 2 kwa kila hifadhidata.
kuchambua maombi mazito zaidi na kutambua athari mbaya ya usomaji mwingi. Ikiwa ndivyo, basi ongeza Kizingiti cha Gharama kwa Usambamba.
Kwa mifumo kama 1C, Microsoft CRM na Microsoft NAV, katika hali nyingi, kukataza usomaji mwingi kunafaa.

Pia, ikiwa kuna toleo la Kawaida, basi katika hali nyingi kukataza kwa multithreading kunafaa kutokana na ukweli kwamba toleo hili ni mdogo kwa idadi ya cores CPU.
Kwa mifumo ya OLAP, algorithm iliyoelezwa hapo juu haifai.
Kutoka kwa uzoefu wangu mwenyewe, ninapendekeza algorithm ifuatayo ya vitendo kwa mifumo ya OLAP ya kusanidi sifa za usawa:

kuchambua maombi mazito zaidi na uchague idadi kamili ya nyuzi kwao
weka Kiwango cha Juu cha Usambamba kwa idadi kamili iliyochaguliwa ya nyuzi zilizopatikana kutoka kwa hatua ya 1, na kwa hifadhidata maalum weka thamani ya Max DOP iliyopatikana kutoka hatua ya 1 kwa kila hifadhidata.
kuchanganua hoja nzito zaidi na utambue athari mbaya ya kuzuia upatanishi. Ikiwa ndivyo, basi punguza Kizingiti cha Gharama kwa Thamani ya Usambamba, au rudia hatua 1-2 za algorithm hii.

Hiyo ni, kwa mifumo ya OLTP tunatoka kwa thread-moja hadi nyuzi nyingi, na kwa mifumo ya OLAP, kinyume chake, tunatoka kwa nyuzi nyingi hadi kwa moja. Kwa hivyo, unaweza kuchagua mipangilio bora ya usawa kwa hifadhidata maalum na mfano mzima wa Seva ya MS SQL.
Pia ni muhimu kuelewa kwamba mipangilio ya mali ya usawa inahitaji kubadilishwa kwa muda, kulingana na matokeo ya ufuatiliaji wa utendaji wa MS SQL Server.

Miongozo ya Kuweka Alama za Ufuatiliaji

Kutoka kwa uzoefu wangu mwenyewe na uzoefu wa wenzangu, kwa utendaji bora, ninapendekeza kuweka bendera zifuatazo za ufuatiliaji katika kiwango cha uendeshaji wa huduma ya MS SQL Server kwa matoleo 2008-2016:

610 - Kupunguza ukataji wa viingilio kwenye jedwali zenye faharasa. Inaweza kusaidia kwa kuingiza kwenye majedwali yenye rekodi nyingi na miamala mingi, huku WRITELOG ikisubiri mabadiliko ya faharasa mara kwa mara.
1117 - Ikiwa faili katika kikundi cha faili inakidhi mahitaji ya kizingiti cha ukuaji kiotomatiki, faili zote kwenye kikundi cha faili hukua.
1118 - Hulazimisha vitu vyote kuwekwa katika viwango tofauti (marufuku ya viwango mchanganyiko), ambayo hupunguza hitaji la kuchanganua ukurasa wa SGAM, unaotumika kufuatilia viwango mchanganyiko.
1224 - Huzima upandaji wa kufuli kulingana na idadi ya kufuli. Walakini, utumiaji mwingi wa kumbukumbu unaweza kusababisha kuongezeka kwa kufuli
2371 - Hubadilisha kiwango kisichobadilika cha kusasisha takwimu hadi kiwango cha kusasisha takwimu kiotomatiki kinachobadilika. Muhimu kwa kusasisha mipango ya hoja ya jedwali kubwa, ambapo hesabu isiyo sahihi ya rekodi husababisha mipango ya utekelezaji yenye makosa.
3226 - Inakandamiza ujumbe wa mafanikio ya chelezo kwenye logi ya makosa
4199 - Inajumuisha mabadiliko kwa kiboresha hoja kilichotolewa katika CUs na SQL Server Service Packs
6532-6534 - Inajumuisha uboreshaji wa utendakazi kwa uendeshaji wa hoja kwenye aina za data anga
8048 - Hubadilisha vitu vya kumbukumbu vilivyogawanywa vya NUMA kuwa vilivyogawanywa vya CPU
8780 - Huwasha mgao wa muda wa ziada kwa ajili ya kupanga hoja. Baadhi ya maombi bila bendera hii yanaweza kukataliwa kwa sababu hayana mpango wa kuuliza (hitilafu adimu sana)
8780 - 9389 - Huwasha akiba ya kumbukumbu ya ziada inayobadilika kwa taarifa za hali ya kundi, ambayo huruhusu opereta wa modi ya bechi kuomba kumbukumbu zaidi na kuepuka kuhamisha data kwa tempdb ikiwa kumbukumbu zaidi inapatikana.

Pia kabla ya 2016, ni muhimu kuwasha alama ya ufuatiliaji 2301, ambayo huwezesha uboreshaji wa usaidizi wa maamuzi na hivyo kusaidia katika kuchagua mipango sahihi zaidi ya hoja. Walakini, kama toleo la 2016, mara nyingi huwa na athari mbaya kwa nyakati ndefu za utekelezaji wa hoja.
Pia, kwa mifumo iliyo na faharasa nyingi (kwa mfano, kwa hifadhidata za 1C), ninapendekeza kuwezesha alama ya ufuatiliaji 2330, ambayo inalemaza mkusanyiko wa matumizi ya faharasa, ambayo kwa ujumla ina athari chanya kwenye mfumo.
Kwa habari zaidi kuhusu kufuatilia bendera, ona hapa
Kutoka kwa kiungo kilicho hapo juu, ni muhimu pia kuzingatia matoleo na miundo ya MS SQL Server, kwani kwa matoleo mapya, baadhi ya alama za ufuatiliaji huwashwa kwa chaguomsingi au hazina athari.
Unaweza kuwasha na kuzima alama ya ufuatiliaji kwa amri za DBCC TRACEON na DBCC TRACEOFF, mtawalia. Kwa maelezo zaidi tazama hapa
Unaweza kupata hali ya bendera kwa kutumia amri ya DBCC TRACESTATUS: zaidi
Ili kufuatilia bendera kujumuishwa katika uanzishaji kiotomatiki wa huduma ya Seva ya MS SQL, lazima uende kwa Kidhibiti cha Usanidi cha Seva ya SQL na uongeze alama hizi za ufuatiliaji kupitia -T katika sifa za huduma:

Matokeo ya

Katika makala hii, baadhi ya vipengele vya ufuatiliaji wa MS SQL Server vilichambuliwa, kwa msaada wa ambayo unaweza kutambua haraka ukosefu wa RAM na muda wa bure wa CPU, pamoja na matatizo mengine yasiyo ya wazi. Bendera za ufuatiliaji zinazotumiwa sana zimekaguliwa.

Vyanzo:

» Takwimu za kusubiri za Seva ya SQL
» Takwimu za kusubiri za Seva ya SQL au tafadhali niambie inaumiza wapi
» Mwonekano wa mfumo sys.dm_os_schedulers
» Kutumia Zabbix Kufuatilia Hifadhidata ya Seva ya MS SQL
» Maisha ya SQL
» Fuatilia Bendera
» sql.ru

Chanzo: mapenzi.com

Baadhi ya vipengele vya ufuatiliaji wa Seva ya MS SQL. Miongozo ya Kuweka Alama za Ufuatiliaji