🥇MS SQL Server 모니터링의 몇 가지 측면. 추적 플래그 설정을 위한 권장 사항

머리말

종종 MS SQL Server DBMS의 사용자, 개발자 및 관리자는 데이터베이스 또는 DBMS 전체의 성능 문제에 직면하므로 MS SQL Server 모니터링은 매우 관련이 있습니다.
이 글은 글에 추가된 글입니다 Zabbix를 사용하여 MS SQL Server 데이터베이스 모니터링 MS SQL Server 모니터링의 몇 가지 측면, 특히 누락된 리소스를 빠르게 확인하는 방법과 추적 플래그 설정에 대한 권장 사항을 다룹니다.
다음 스크립트가 작동하려면 다음과 같이 원하는 데이터베이스에 inf 스키마를 생성해야 합니다.
inf 스키마 생성

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

RAM 부족 감지 방법

RAM 부족의 첫 번째 지표는 MS SQL Server 인스턴스가 할당된 모든 RAM을 차지하는 경우입니다.
이를 위해 다음과 같은 inf.vRAM 표현을 생성합니다.
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;

그런 다음 MS SQL Server 인스턴스가 다음 쿼리에 의해 할당된 모든 메모리를 소비하는지 확인할 수 있습니다.

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

SQL_server_physical_memory_in_use_Mb가 지속적으로 SQL_server_committed_target_Mb보다 크거나 같으면 대기 통계를 확인해야 합니다.
대기 통계를 통해 RAM 부족을 확인하기 위해 inf.vWaits 뷰를 생성해 보겠습니다.
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];

이 경우 다음 쿼리를 사용하여 RAM 부족을 확인할 수 있습니다.

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

여기에서 Percentage 및 AvgWait_S 지표에 주의를 기울여야 합니다. 전체적으로 중요한 경우 MS SQL Server 인스턴스에 대한 RAM이 충분하지 않을 가능성이 매우 높습니다. 중요한 값은 각 시스템에 대해 개별적으로 결정됩니다. 그러나 Percentage>=1 및 AvgWait_S>=0.005로 시작할 수 있습니다.
모니터링 시스템(예: Zabbix)에 지표를 출력하려면 다음 두 쿼리를 생성할 수 있습니다.

RAM이 차지하는 대기 유형의 백분율(이러한 모든 대기 유형의 합계):

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

얼마나 많은 RAM 대기 유형이 밀리초에 걸리는지(이러한 모든 대기 유형에 대한 모든 평균 지연의 최대값):

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

이 두 지표에 대해 얻은 값의 역학을 기반으로 MS SQL Server 인스턴스에 충분한 RAM이 있는지 결론을 내릴 수 있습니다.

CPU 과부하 감지 방법

프로세서 시간 부족을 식별하려면 sys.dm_os_schedulers 시스템 보기를 사용하는 것으로 충분합니다. 여기서 runnable_tasks_count가 지속적으로 1보다 크면 MS SQL Server 인스턴스에 대한 코어 수가 충분하지 않을 가능성이 높습니다.
모니터링 시스템(예: Zabbix)에 표시기를 출력하려면 다음 쿼리를 생성할 수 있습니다.

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

이 표시기에 대해 얻은 값의 역학을 기반으로 MS SQL Server 인스턴스에 충분한 프로세서 시간(CPU 코어 수)이 있는지 여부를 결론 내릴 수 있습니다.
그러나 요청 자체가 한 번에 여러 스레드를 요청할 수 있다는 사실을 염두에 두는 것이 중요합니다. 때로는 옵티마이저가 쿼리 자체의 복잡성을 정확하게 예측할 수 없습니다. 그러면 주어진 시간에 동시에 처리할 수 없는 너무 많은 스레드가 요청에 할당될 수 있습니다. 또한 이것은 프로세서 시간 부족과 관련된 대기 유형과 특정 CPU 코어를 사용하는 스케줄러의 대기열 증가, 즉 runnable_tasks_count 표시기가 이러한 조건에서 증가하는 원인이 됩니다.
이 경우 CPU 코어 수를 늘리기 전에 MS SQL Server 인스턴스 자체의 병렬 처리 속성을 올바르게 구성해야 하며 2016 버전부터는 필요한 데이터베이스의 병렬 처리 속성을 올바르게 구성해야 합니다.

여기에서 다음 매개변수에 주의해야 합니다.

Max Degree of Parallelism - 각 요청에 할당할 수 있는 최대 스레드 수를 설정합니다(기본값은 0 - 운영 체제 자체 및 MS SQL Server 버전에 의해서만 제한됨).
병렬 처리에 대한 비용 임계값 - 병렬 처리의 예상 비용(기본값은 5)
Max DOP - 데이터베이스 수준에서 각 쿼리에 할당할 수 있는 최대 스레드 수를 설정합니다(그러나 "Max Degree of Parallelism" 속성 값 이하)(기본값은 0 - 운영 체제 자체에 의해서만 제한됨). MS SQL Server의 에디션 및 전체 MS SQL Server 인스턴스의 "Max Degree of Parallelism" 속성에 대한 제한)

여기서 모든 경우에 대해 똑같이 좋은 레시피를 제공하는 것은 불가능합니다. 즉, 무거운 쿼리를 분석해야 합니다.
내 경험상 병렬 처리 속성을 설정하기 위해 OLTP 시스템에 대해 다음 작업 알고리즘을 권장합니다.

먼저 인스턴스 전체의 Max Degree of Parallelism을 1로 설정하여 병렬 처리를 비활성화합니다.
가장 무거운 요청을 분석하고 최적의 스레드 수를 선택합니다.
Max Degree of Parallelism을 2단계에서 얻은 선택된 최적의 스레드 수로 설정하고 특정 데이터베이스에 대해 각 데이터베이스에 대해 2단계에서 얻은 최대 DOP 값을 설정합니다.
가장 많은 요청을 분석하고 멀티스레딩의 부정적인 영향을 식별합니다. 그렇다면 병렬 처리에 대한 비용 임계값을 늘립니다.
1C, Microsoft CRM 및 Microsoft NAV와 같은 시스템의 경우 대부분의 경우 멀티스레딩 금지가 적합합니다.

또한 Standard 에디션이 있는 경우 이 에디션은 CPU 코어 수가 제한되어 있기 때문에 대부분의 경우 멀티스레딩 금지가 적합합니다.
OLAP 시스템의 경우 위에서 설명한 알고리즘이 적합하지 않습니다.
내 경험상 병렬 처리 속성을 설정하기 위해 OLAP 시스템에 대해 다음 작업 알고리즘을 권장합니다.

가장 무거운 요청을 분석하고 최적의 스레드 수를 선택합니다.
Max Degree of Parallelism을 1단계에서 얻은 선택된 최적의 스레드 수로 설정하고 특정 데이터베이스에 대해 각 데이터베이스에 대해 1단계에서 얻은 최대 DOP 값을 설정합니다.
가장 무거운 쿼리를 분석하고 동시성 제한의 부정적인 영향을 식별합니다. 그렇다면 병렬 처리 비용 임계값을 낮추거나 이 알고리즘의 1-2단계를 반복합니다.

즉, OLTP 시스템의 경우 단일 스레딩에서 다중 스레딩으로 이동하고 OLAP 시스템의 경우 반대로 다중 스레딩에서 단일 스레딩으로 이동합니다. 따라서 특정 데이터베이스와 MS SQL Server의 전체 인스턴스 모두에 대해 최적의 병렬 처리 설정을 선택할 수 있습니다.
MS SQL Server의 성능을 모니터링한 결과에 따라 시간 경과에 따라 병렬 처리 속성의 설정을 변경해야 한다는 점을 이해하는 것도 중요합니다.

추적 플래그 설정 지침

내 자신의 경험과 동료의 경험에 비추어 볼 때 최적의 성능을 위해 2008-2016 버전의 MS SQL Server 서비스 실행 수준에서 다음 추적 플래그를 설정하는 것이 좋습니다.

610 - 인덱싱된 테이블에 대한 삽입 로깅이 감소했습니다. 많은 레코드와 많은 트랜잭션이 있는 테이블에 삽입하는 데 도움이 될 수 있으며 WRITELOG는 인덱스 변경을 자주 기다립니다.
1117 - 파일 그룹의 파일이 자동 증가 임계값 요구 사항을 충족하면 파일 그룹의 모든 파일이 커집니다.
1118 - 모든 객체가 서로 다른 익스텐트에 위치하도록 강제하여(혼합 익스텐트 금지) 혼합 익스텐트를 추적하는 데 사용되는 SGAM 페이지를 스캔할 필요성을 최소화합니다.
1224 - 잠금 수에 따라 잠금 에스컬레이션을 비활성화합니다. 그러나 과도한 메모리 사용은 잠금 에스컬레이션을 트리거할 수 있습니다.
2371 - 고정 자동 통계 업데이트 임계값을 동적 자동 통계 업데이트 임계값으로 변경합니다. 잘못된 레코드 수로 인해 잘못된 실행 계획이 발생하는 대형 테이블의 쿼리 계획을 업데이트하는 데 중요합니다.
3226 - 오류 로그에서 백업 성공 메시지를 억제합니다.
4199 - CU 및 SQL Server 서비스 팩에서 릴리스된 쿼리 최적화 프로그램에 대한 변경 사항을 포함합니다.
6532-6534 - 공간 데이터 유형에 대한 쿼리 작업의 성능 향상을 포함합니다.
8048 - NUMA 분할 메모리 개체를 CPU 분할 메모리 개체로 변환
8780 - 쿼리 계획을 위한 추가 시간 할당을 활성화합니다. 이 플래그가 없는 일부 요청은 쿼리 계획이 없기 때문에 거부될 수 있습니다(매우 드문 버그).
8780 - 9389 - 배치 모드 명령문에 대한 추가 동적 부여 메모리 버퍼를 활성화하여 배치 모드 연산자가 더 많은 메모리를 요청하고 더 많은 메모리를 사용할 수 있는 경우 데이터를 tempdb로 이동하지 않도록 합니다.

또한 2016년 이전에는 추적 플래그 2301을 활성화하는 것이 유용합니다. 이는 향상된 의사 결정 지원 최적화를 가능하게 하여 더 정확한 쿼리 계획을 선택하는 데 도움이 됩니다. 그러나 2016 버전부터는 전체 쿼리 실행 시간이 상당히 길어지는 데 부정적인 영향을 미치는 경우가 많습니다.
또한 인덱스가 많은 시스템(예: 1C 데이터베이스)의 경우 일반적으로 시스템에 긍정적인 영향을 미치는 인덱스 사용 수집을 비활성화하는 추적 플래그 2330을 활성화하는 것이 좋습니다.
추적 플래그에 대한 자세한 내용은 다음을 참조하세요. 여기에
위의 링크에서 MS SQL Server의 버전과 빌드를 고려하는 것도 중요합니다. 최신 버전의 경우 일부 추적 플래그가 기본적으로 활성화되어 있거나 효과가 없습니다.
각각 DBCC TRACEON 및 DBCC TRACEOFF 명령을 사용하여 추적 플래그를 켜고 끌 수 있습니다. 자세한 내용은 참조 여기에
DBCC TRACESTATUS 명령을 사용하여 추적 플래그의 상태를 가져올 수 있습니다. 더
MS SQL Server 서비스의 자동 시작에 추적 플래그를 포함하려면 SQL Server 구성 관리자로 이동하여 서비스 속성에서 -T를 통해 다음 추적 플래그를 추가해야 합니다.