🥇Ilang aspeto ng pagsubaybay sa MS SQL Server. Mga Alituntunin para sa Pagtatakda ng Mga Trace Flag

paunang salita

Kadalasan, ang mga user, developer at administrator ng MS SQL Server DBMS ay nakatagpo ng mga problema sa pagganap ng database o ng DBMS sa kabuuan, kaya ang pagsubaybay sa MS SQL Server ay napakahalaga.
Ang artikulong ito ay karagdagan sa artikulo Gamit ang Zabbix para Subaybayan ang MS SQL Server Database at sasakupin nito ang ilang aspeto ng pagsubaybay sa MS SQL Server, sa partikular: kung paano mabilis na matukoy kung aling mga mapagkukunan ang nawawala, pati na rin ang mga rekomendasyon para sa pagtatakda ng mga bakas na flag.
Para gumana ang mga sumusunod na script, kailangan mong lumikha ng inf schema sa gustong database gaya ng sumusunod:
Paglikha ng inf schema

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

Paraan para sa pag-detect ng kakulangan ng RAM

Ang unang tagapagpahiwatig ng kakulangan ng RAM ay ang kaso kapag ang isang halimbawa ng MS SQL Server ay kumakain ng lahat ng RAM na inilaan dito.
Upang gawin ito, gagawa kami ng sumusunod na representasyon ng inf.vRAM:
Paglikha ng inf.vRAM view

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;

Pagkatapos ay matutukoy mo na ang isang instance ng MS SQL Server ay gumagamit ng lahat ng memorya na inilaan dito sa pamamagitan ng sumusunod na query:

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

Kung ang SQL_server_physical_memory_in_use_Mb ay patuloy na mas malaki kaysa o katumbas ng SQL_server_committed_target_Mb, dapat suriin ang mga istatistika ng paghihintay.
Upang matukoy ang kakulangan ng RAM sa pamamagitan ng mga istatistika ng paghihintay, gawin natin ang inf.vWaits view:
Paglikha ng inf.vWaits View

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

Sa kasong ito, matutukoy mo ang kakulangan ng RAM gamit ang sumusunod na query:

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

Dito kailangan mong bigyang pansin ang mga indicator na Porsyento at AvgWait_S. Kung sila ay makabuluhan sa kanilang kabuuan, kung gayon mayroong napakataas na posibilidad na walang sapat na RAM para sa halimbawa ng MS SQL Server. Ang mga makabuluhang halaga ay tinutukoy nang paisa-isa para sa bawat system. Gayunpaman, maaari kang magsimula sa sumusunod: Porsyento>=1 at AvgWait_S>=0.005.
Upang maglabas ng mga indicator sa isang monitoring system (halimbawa, Zabbix), maaari kang lumikha ng sumusunod na dalawang query:

kung gaano karaming mga uri ng paghihintay ang inookupahan ng RAM sa porsyento (ang kabuuan ng lahat ng naturang mga uri ng paghihintay):

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

kung gaano karaming mga uri ng paghihintay ng RAM ang tumatagal sa millisecond (ang maximum na halaga ng lahat ng average na pagkaantala para sa lahat ng naturang uri ng paghihintay):

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

Batay sa dinamika ng mga nakuhang halaga para sa dalawang tagapagpahiwatig na ito, maaari nating tapusin kung mayroong sapat na RAM para sa isang halimbawa ng MS SQL Server.

Paraan ng Pagtukoy sa Overload ng CPU

Upang matukoy ang kakulangan ng oras ng processor, sapat na gamitin ang view ng system ng sys.dm_os_schedulers. Dito, kung ang runnable_tasks_count ay patuloy na higit sa 1, kung gayon ay may mataas na posibilidad na ang bilang ng mga core ay hindi sapat para sa halimbawa ng MS SQL Server.
Upang mag-output ng indicator sa isang monitoring system (halimbawa, Zabbix), maaari mong gawin ang sumusunod na query:

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

Batay sa dinamika ng mga nakuhang halaga para sa tagapagpahiwatig na ito, maaari nating tapusin kung mayroong sapat na oras ng processor (ang bilang ng mga core ng CPU) para sa isang halimbawa ng MS SQL Server.
Gayunpaman, mahalagang tandaan na ang mga kahilingan mismo ay maaaring humiling ng maraming thread nang sabay-sabay. At kung minsan ay hindi tama na matantya ng optimizer ang pagiging kumplikado ng mismong query. Kung gayon ang kahilingan ay maaaring maglaan ng masyadong maraming mga thread na hindi maproseso nang sabay sa ibinigay na oras. At nagdudulot din ito ng uri ng paghihintay na nauugnay sa kakulangan ng oras ng processor, at paglaki ng pila para sa mga scheduler na gumagamit ng mga partikular na CPU core, ibig sabihin, lalago ang runnable_tasks_count indicator sa ganitong mga kundisyon.
Sa kasong ito, bago dagdagan ang bilang ng mga core ng CPU, kinakailangan na wastong i-configure ang parallelism na katangian ng MS SQL Server instance mismo, at mula sa 2016 na bersyon, wastong i-configure ang parallelism properties ng mga kinakailangang database:

Dito dapat mong bigyang-pansin ang mga sumusunod na parameter:

Max Degree of Parallelism - nagtatakda ng maximum na bilang ng mga thread na maaaring ilaan sa bawat kahilingan (ang default ay 0 - limitado lamang ng operating system mismo at ng edisyon ng MS SQL Server)
Cost Threshold para sa Parallelism - tinantyang halaga ng parallelism (default ay 5)
Max DOP - nagtatakda ng maximum na bilang ng mga thread na maaaring ilaan sa bawat query sa antas ng database (ngunit hindi hihigit sa halaga ng property na "Max Degree of Parallelism") (default ay 0 - limitado lamang ng operating system mismo at ang edisyon ng MS SQL Server, pati na rin ang paghihigpit sa "Max Degree of Parallelism" na pag-aari ng buong instance ng MS SQL Server)

Dito imposibleng magbigay ng pantay na mahusay na recipe para sa lahat ng mga kaso, ibig sabihin, kailangan mong pag-aralan ang mabibigat na mga query.
Mula sa sarili kong karanasan, inirerekomenda ko ang sumusunod na algorithm ng mga aksyon para sa mga OLTP system para sa pagse-set up ng mga katangian ng parallelism:

huwag munang paganahin ang parallelism sa pamamagitan ng pagtatakda ng instance-wide Max Degree of Parallelism sa 1
pag-aralan ang pinakamabibigat na kahilingan at piliin ang pinakamainam na bilang ng mga thread para sa kanila
itakda ang Max Degree of Parallelism sa napiling pinakamainam na bilang ng mga thread na nakuha mula sa step 2, at para sa mga partikular na database itakda ang Max DOP value na nakuha mula sa step 2 para sa bawat database
pag-aralan ang pinakamabigat na kahilingan at tukuyin ang negatibong epekto ng multithreading. Kung oo, dagdagan ang Hangganan ng Gastos para sa Paralelismo.
Para sa mga system tulad ng 1C, Microsoft CRM at Microsoft NAV, sa karamihan ng mga kaso, ang pagbabawal sa multithreading ay angkop

Gayundin, kung mayroong isang Standard na edisyon, kung gayon sa karamihan ng mga kaso ang pagbabawal ng multithreading ay angkop dahil sa ang katunayan na ang edisyong ito ay limitado sa bilang ng mga core ng CPU.
Para sa mga OLAP system, ang algorithm na inilarawan sa itaas ay hindi angkop.
Mula sa sarili kong karanasan, inirerekomenda ko ang sumusunod na algorithm ng mga aksyon para sa mga OLAP system para sa pag-set up ng mga katangian ng parallelism:

pag-aralan ang pinakamabibigat na kahilingan at piliin ang pinakamainam na bilang ng mga thread para sa kanila
itakda ang Max Degree of Parallelism sa napiling pinakamainam na bilang ng mga thread na nakuha mula sa step 1, at para sa mga partikular na database itakda ang Max DOP value na nakuha mula sa step 1 para sa bawat database
pag-aralan ang pinakamabibigat na mga query at tukuyin ang negatibong epekto ng paglilimita ng concurrency. Kung oo, babaan ang Cost Threshold para sa Parallelism na halaga, o ulitin ang mga hakbang 1-2 ng algorithm na ito

Ibig sabihin, para sa mga OLTP system, mula sa single-threading tungo sa multi-threading, at para sa OLAP-systems, sa kabaligtaran, mula sa multi-threading tungo sa single-threading. Kaya, maaari mong piliin ang pinakamainam na setting ng parallelism para sa parehong partikular na database at ang buong instance ng MS SQL Server.
Mahalaga rin na maunawaan na ang mga setting ng mga katangian ng paralelismo ay kailangang baguhin sa paglipas ng panahon, batay sa mga resulta ng pagsubaybay sa pagganap ng MS SQL Server.

Mga Alituntunin para sa Pagtatakda ng Mga Trace Flag

Mula sa sarili kong karanasan at karanasan ng aking mga kasamahan, para sa pinakamainam na pagganap, inirerekumenda ko ang pagtatakda ng mga sumusunod na trace flag sa antas ng pagpapatakbo ng serbisyo ng MS SQL Server para sa mga bersyon 2008-2016:

610 - Binawasan ang pag-log ng mga pagsingit sa mga naka-index na talahanayan. Makakatulong sa mga pagsingit sa mga talahanayan na may maraming mga tala at maraming mga transaksyon, na may madalas na mahabang paghihintay ng WRITELOG para sa mga pagbabago sa mga index
1117 - Kung ang isang file sa isang filegroup ay nakakatugon sa mga kinakailangan sa threshold ng autogrowth, ang lahat ng mga file sa filegroup ay lalago
1118 - Pinipilit na ilagay ang lahat ng bagay sa iba't ibang lawak (pagbabawal sa magkahalong lawak), na nagpapaliit sa pangangailangang i-scan ang pahina ng SGAM, na ginagamit upang subaybayan ang magkahalong lawak
1224 - Hindi pinapagana ang pagtaas ng lock batay sa bilang ng mga lock. Gayunpaman, ang labis na paggamit ng memorya ay maaaring mag-trigger ng lock escalation
2371 - Binabago ang nakapirming threshold ng pag-update ng awtomatikong mga istatistika sa threshold ng dynamic na awtomatikong pag-update ng istatistika. Mahalaga para sa pag-update ng mga query plan para sa malalaking talahanayan, kung saan ang maling bilang ng mga tala ay nagreresulta sa mga maling plano sa pagpapatupad
3226 - Pinipigilan ang mga backup na mensahe ng tagumpay sa log ng error
4199 - Kasama ang mga pagbabago sa query optimizer na inilabas sa mga CU at SQL Server Service Pack
6532-6534 - Kasama ang mga pagpapahusay sa pagganap para sa mga pagpapatakbo ng query sa mga spatial na uri ng data
8048 - Kino-convert ang NUMA partitioned memory object sa CPU partitioned
8780 - Pinapagana ang karagdagang paglalaan ng oras para sa pagpaplano ng query. Maaaring tanggihan ang ilang kahilingan na walang flag na ito dahil wala silang query plan (napakabihirang bug)
8780 - 9389 - Pinapagana ang karagdagang dynamic na grant memory buffer para sa mga statement ng batch mode, na nagpapahintulot sa batch mode operator na humiling ng higit pang memory at maiwasan ang paglipat ng data sa tempdb kung mas maraming memory ang magagamit

Bago din ang 2016, kapaki-pakinabang na paganahin ang trace flag 2301, na nagbibigay-daan sa pinahusay na pag-optimize ng suporta sa desisyon at sa gayon ay nakakatulong sa pagpili ng mas tamang mga plano sa query. Gayunpaman, mula sa bersyon 2016, madalas itong may negatibong epekto sa medyo matagal na pangkalahatang mga oras ng pagpapatupad ng query.
Gayundin, para sa mga system na may maraming index (halimbawa, para sa mga database ng 1C), inirerekomenda kong i-enable ang trace flag 2330, na hindi pinapagana ang koleksyon ng paggamit ng index, na sa pangkalahatan ay may positibong epekto sa system.
Para sa higit pang impormasyon tungkol sa mga trace flag, tingnan ang dito
Mula sa link sa itaas, mahalagang isaalang-alang din ang mga bersyon at build ng MS SQL Server, tulad ng para sa mga mas bagong bersyon, ang ilang trace flag ay pinagana bilang default o walang epekto.
Maaari mong i-on at i-off ang trace flag gamit ang DBCC TRACEON at DBCC TRACEOFF command, ayon sa pagkakabanggit. Para sa higit pang mga detalye tingnan dito
Makukuha mo ang status ng mga trace flag gamit ang DBCC TRACESTATUS command: pa
Upang maisama ang mga trace flag sa autostart ng serbisyo ng MS SQL Server, dapat kang pumunta sa SQL Server Configuration Manager at idagdag ang mga trace flag na ito sa pamamagitan ng -T sa mga katangian ng serbisyo:

Mga resulta ng

Sa artikulong ito, nasuri ang ilang aspeto ng pagsubaybay sa MS SQL Server, sa tulong kung saan mabilis mong matukoy ang kakulangan ng RAM at libreng oras ng CPU, pati na rin ang ilang iba pang hindi gaanong halata na mga problema. Nasuri na ang mga pinakakaraniwang ginagamit na trace flag.

Pinagmulan:

» Mga istatistika ng paghihintay ng SQL Server
» SQL Server wait statistics o pakisabi sa akin kung saan masakit
» System view sys.dm_os_schedulers
» Gamit ang Zabbix para Subaybayan ang MS SQL Server Database
» SQL Lifestyle
» Mga Bandila ng Bakas
» sql.ru

Pinagmulan: www.habr.com

Ilang aspeto ng pagsubaybay sa MS SQL Server. Mga Alituntunin para sa Pagtatakda ng mga Trace Flag