🥇Някои аспекти на мониторинга на MS SQL Server. Указания за задаване на флагове за проследяване

предговор

Доста често потребителите, разработчиците и администраторите на СУБД на MS SQL Server срещат проблеми с производителността на базата данни или СУБД като цяло, така че наблюдението на 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. Ако те са значителни в своята съвкупност, тогава има много голяма вероятност да няма достатъчно RAM за екземпляра на MS SQL Server. Значимите стойности се определят индивидуално за всяка система. Можете обаче да започнете със следното: 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'
  );
```

Въз основа на динамиката на получените стойности за тези два показателя можем да заключим дали има достатъчно RAM за екземпляр на MS SQL Server.

Метод за откриване на претоварване на процесора

За да идентифицирате липсата на процесорно време, достатъчно е да използвате системния изглед 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.
Важно е обаче да имате предвид факта, че самите заявки могат да изискват няколко нишки наведнъж. И понякога оптимизаторът не може да оцени правилно сложността на самата заявка. Тогава на заявката може да бъдат разпределени твърде много нишки, които не могат да бъдат обработени едновременно в дадения момент. И това също причинява вид изчакване, свързано с липса на процесорно време, и нарастване на опашката за планировчици, които използват специфични процесорни ядра, т.е. индикаторът runnable_tasks_count ще нараства при такива условия.
В този случай, преди да увеличите броя на процесорните ядра, е необходимо правилно да конфигурирате свойствата за паралелизъм на самия екземпляр на MS SQL Server и от версия 2016 правилно да конфигурирате свойствата за паралелизъм на необходимите бази данни:

Тук трябва да обърнете внимание на следните параметри:

Максимална степен на паралелизъм - задава максималния брой нишки, които могат да бъдат разпределени за всяка заявка (по подразбиране е 0 - ограничено само от самата операционна система и изданието на MS SQL Server)
Праг на разходите за паралелизъм - прогнозна цена на паралелизма (по подразбиране е 5)
Max DOP - задава максималния брой нишки, които могат да бъдат разпределени за всяка заявка на ниво база данни (но не повече от стойността на свойството "Максимална степен на паралелност") (по подразбиране е 0 - ограничено само от самата операционна система и изданието на MS SQL Server, както и ограничението за свойството „Максимална степен на паралелизъм“ на целия екземпляр на MS SQL Server)

Тук е невъзможно да се даде еднакво добра рецепта за всички случаи, т.е. трябва да анализирате тежки заявки.
От моя собствен опит препоръчвам следния алгоритъм от действия за OLTP системи за настройка на паралелни свойства:

първо деактивирайте паралелизма, като зададете максималната степен на паралелизъм за цялата инстанция на 1
анализирайте най-тежките заявки и изберете оптималния брой нишки за тях
задайте Max Degree of Parallelism на избрания оптимален брой нишки, получени от стъпка 2, и за конкретни бази данни задайте Max DOP стойност, получена от стъпка 2 за всяка база данни
анализирайте най-тежките заявки и идентифицирайте отрицателния ефект от многопоточността. Ако е така, увеличете прага на разходите за паралелизъм.
За системи като 1C, Microsoft CRM и Microsoft NAV в повечето случаи е подходяща забрана на многопоточност

Освен това, ако има стандартно издание, тогава в повечето случаи забраната за многопоточност е подходяща поради факта, че това издание е ограничено в броя на процесорните ядра.
За OLAP системи алгоритъмът, описан по-горе, не е подходящ.
От моя собствен опит препоръчвам следния алгоритъм от действия за OLAP системи за настройка на паралелни свойства:

анализирайте най-тежките заявки и изберете оптималния брой нишки за тях
задайте Max Degree of Parallelism на избрания оптимален брой нишки, получени от стъпка 1, и за конкретни бази данни задайте Max DOP стойност, получена от стъпка 1 за всяка база данни
анализирайте най-тежките заявки и идентифицирайте отрицателния ефект от ограничаването на паралелността. Ако е така, тогава или намалете стойността на прага на разходите за паралелизъм, или повторете стъпки 1-2 от този алгоритъм

Тоест, за OLTP системите преминаваме от еднонишкови към многонишкови, а за OLAP-системите, напротив, преминаваме от многонишкови към еднонишкови. По този начин можете да изберете оптималните настройки за паралелизъм както за конкретна база данни, така и за цялото копие на MS SQL Server.
Също така е важно да се разбере, че настройките на свойствата на паралелизма трябва да се променят с течение на времето въз основа на резултатите от наблюдението на производителността на MS SQL Server.

Указания за задаване на флагове за проследяване

От моя собствен опит и опита на моите колеги, за оптимална производителност, препоръчвам да зададете следните флагове за проследяване на ниво изпълнение на услугата MS SQL Server за версии 2008-2016:

610 - Намалено регистриране на вмъквания в индексирани таблици. Може да помогне с вмъквания в таблици с много записи и много транзакции, с чести дълги чакания на WRITELOG за промени в индексите
1117 - Ако файл във файлова група отговаря на изискванията за праг за автоматично нарастване, всички файлове във файловата група нарастват
1118 - Принуждава всички обекти да бъдат разположени в различни екстенти (забрана за смесени екстенти), което минимизира необходимостта от сканиране на страницата SGAM, която се използва за проследяване на смесени екстенти
1224 - Деактивира ескалацията на заключването въз основа на броя на заключванията. Прекомерното използване на памет обаче може да предизвика ескалация на заключването
2371 - Променя фиксирания праг за автоматична актуализация на статистиката на прага за динамична автоматична актуализация на статистиката. Важно за актуализиране на планове за заявки за големи таблици, където неправилен брой записи води до грешни планове за изпълнение
3226 - Потиска съобщенията за успешно архивиране в регистъра на грешките
4199 - Включва промени в оптимизатора на заявки, пуснат в CU и SQL Server Service Packs
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 Configuration Manager и да добавите тези флагове за проследяване чрез -T в свойствата на услугата:

Резултати от

В тази статия бяха анализирани някои аспекти на мониторинга на MS SQL Server, с помощта на които можете бързо да идентифицирате липсата на RAM и свободно време на процесора, както и редица други по-малко очевидни проблеми. Прегледани са най-често използваните флагове за проследяване.

Източници:

» Статистика за изчакване на SQL Server
» Статистика за изчакване на SQL Server или моля, кажете ми къде боли
» Системен изглед sys.dm_os_schedulers
» Използване на Zabbix за наблюдение на MS SQL Server база данни
» SQL Lifestyle
» Флагове за проследяване
» sql.ru

Източник: www.habr.com

Някои аспекти на мониторинга на MS SQL Server. Указания за задаване на флагове за проследяване