ProHoster > Блог > адміністрування > Деякі аспекти моніторингу MS SQL Server. Рекомендації щодо налаштування прапорів трасування

Деякі аспекти моніторингу MS SQL Server. Рекомендації щодо налаштування прапорів трасування

Передмова

Досить часто користувачі, розробники та адміністратори СУБД MS SQL Server стикаються з проблемами продуктивності БД або СУБД загалом, тому дуже актуальним є моніторинг MS SQL Server.
Ця стаття є доповненням до статті Використання Zabbix для стеження за базою даних MS SQL Server і в ній буде розібрано деякі аспекти моніторингу MS SQL Server, зокрема: як швидко визначити, яких ресурсів не вистачає, а також рекомендації щодо налаштування прапорів трасування.
Для роботи наступних наведених скриптів, необхідно створити схему inf у потрібній базі даних таким чином:
Створення схеми inf

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

Метод виявлення нестачі оперативної пам'яті

Першим показником нестачі оперативної пам'яті є випадок, коли екземпляр MS SQL Server з'їдає всю виділену йому ОЗУ.
Для цього створимо наступне уявлення 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, необхідно перевірити статистику очікувань.
Для визначення нестачі оперативної пам'яті через статистику очікувань створимо уявлення 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];

У цьому випадку визначити нестачу оперативної пам'яті можна таким запитом:

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

Тут потрібно звернути увагу на показники Percentage та AvgWait_S. Якщо вони суттєві за своєю сукупністю, то є дуже велика ймовірність того, що оперативної пам'яті не вистачає екземпляру MS SQL Server. Істотні значення визначаються індивідуально кожної системи. Однак, можна починати з наступного показника: Percentage>=1 та AvgWait_S>=0.005.
Для виведення показників у систему моніторингу (наприклад, Zabbix) можна створити такі два запити:

  1. скільки у відсотках займають типи очікувань за ОЗП (сума за всіма такими типами очікувань): 
    select coalesce(sum([Percentage]), 0.00) as [Percentage]
from [inf].[vWaits]
       where [WaitType] in (
           'PAGEIOLATCH_XX',
           'RESOURCE_SEMAPHORE',
            'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  );
  2. скільки в мілісекундах займають типи очікувань за ОЗП (максимальне значення з усіх середніх затримок за такими типами очікувань): 
    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.

Метод виявлення надмірного навантаження на ЦПУ

Для виявлення нестачі процесорного часу достатньо скористатися системним уявленням 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 версії - правильно налаштувати властивості паралелізму потрібних баз даних:
Деякі аспекти моніторингу MS SQL Server. Рекомендації щодо налаштування прапорів трасування

Деякі аспекти моніторингу MS SQL Server. Рекомендації щодо налаштування прапорів трасування
Тут варто звернути увагу на такі параметри:

  1. Max Degree of Parallelism-задає максимальну кількість потоків, які можуть бути виділені кожному запиту (за замовчуванням стоїть 0-обмеження лише операційною системою та редакцією MS SQL Server)
  2. Cost Threshold for Parallelism-оцінна вартість паралелізму (за замовчуванням коштує 5)
  3. Max DOP-задає максимальну кількість потоків, які можуть бути виділені кожному запиту на рівні бази даних (але не більше, ніж значення властивості Max Degree of Parallelism) (за замовчуванням стоїть 0-обмеження тільки найбільш операційною системою та редакцією MS SQL Server, а також обмеження за якістю "Max Degree of Parallelism" всього екземпляра MS SQL Server)

Тут неможливо дати однаково хороший рецепт для всіх випадків, тобто потрібно аналізувати важкі запити.
За власним досвідом рекомендую наступний алгоритм дій для OLTP-систем для налаштування властивостей паралелізму:

  1. спочатку заборонити паралелізм, виставивши на рівні всього екземпляра Max Degree of Parallelism в 1
  2. проаналізувати найважчі запити та підібрати для них оптимальну кількість потоків
  3. виставити Max Degree of Parallelism у підібрану оптимальну кількість потоків, отриману з п.2, а також для конкретних баз даних виставити Max DOP значення, отримане з п.2 для кожної бази даних
  4. проаналізувати найважчі запити та виявити негативний ефект від багатопоточності. Якщо він є, то підвищувати Cost Threshold for Parallelism.
    Для таких систем, як 1С, Microsoft CRM і Microsoft NAV, у більшості випадків підійде заборона багатопоточності.

Також якщо стоїть редакція Standard, то в більшості випадків підійде заборона багатопоточності через те, що дана редакція обмежена за кількістю ядер ЦПУ.
Для OLAP систем описаний вище алгоритм не підходить.
За власним досвідом рекомендую наступний алгоритм дій для OLAP-систем для налаштування властивостей паралелізму:

  1. проаналізувати найважчі запити та підібрати для них оптимальну кількість потоків
  2. виставити Max Degree of Parallelism у підібрану оптимальну кількість потоків, отриману з п.1, а також для конкретних баз даних виставити Max DOP значення, отримане з п.1 для кожної бази даних
  3. проаналізувати найважчі запити та виявити негативний ефект від обмеження паралелізму. Якщо він є, то знижувати значення Cost Threshold for Parallelism, або повторити кроки 1-2 даного алгоритму

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

Рекомендації щодо налаштування прапорів трасування

З власного досвіду та досвіду моїх колег для оптимальної роботи рекомендую виставляти на рівні запуску служби MS SQL Server для 2008-2016 версій наступні прапори трасування:

  1. 610 - Зменшення протоколювання вставок в індексовані таблиці. Може допомогти зі вставками в таблиці з великою кількістю записів і безліччю транзакцій, при тривалих тривалих очікуваннях WRITELOG зі зміни в індексах
  2. 1117 — Якщо файл у файловій групі відповідає вимогам порога автоматичного збільшення, всі файли у файловій групі збільшуються.
  3. 1118 - Примушує всі об'єкти розташовуватися в різних екстентах (заборона на змішані екстенти), що зводить до мінімуму необхідність сканування сторінки SGAM, яка використовується для відстеження змішаних екстентів
  4. 1224 — Вимикає блокування на основі кількості блокувань. Проте надто активне використання пам'яті може увімкнути укрупнення блокувань.
  5. 2371 - Змінює поріг фіксованого автоматичного оновлення статистики на поріг динамічного автоматичного оновлення статистики. Важливо для оновлення планів запитів щодо великих таблиць, де неправильне визначення числа записів призводить до помилкових планів виконання
  6. 3226 — Пригнічує повідомлення про успішне виконання резервного копіювання у журналі помилок
  7. 4199 — Включає зміни в оптимізаторі запитів, випущені в накопичувальних пакетах оновлень та пакетах оновлень SQL Server
  8. 6532-6534 — Включає покращення продуктивності операцій запитів з просторовими типами даних
  9. 8048 - Перетворює об'єкти пам'яті, секційовані за NUMA, в секційні за ЦП
  10. 8780 — Включає додаткове виділення часу для планування запиту. Деякі запити без цього прапора можуть бути відхилені, оскільки вони не мають плану запиту (дуже рідкісна помилка)
  11. 8780 — 9389 — Включає додатковий динамічний буфер пам'яті, що тимчасово надається, для операторів пакетного режиму, що дає можливість оператору пакетного режиму запросити додаткову пам'ять і уникнути перенесення даних у tempdb, якщо додаткова пам'ять доступна

Також до 2016 року версії корисно включати прапор трасування 2301, який включає оптимізацію розширеної підтримки прийняття рішень і тим самим допомагає у виборі більш правильних планів запитів. Однак, починаючи з версії 2016, він часто чинить негативний ефект у досить тривалому загальному часі виконання запитів.
Також для систем, в яких дуже багато індексів (наприклад, для баз даних 1С), рекомендую включати прапор трасування 2330, який відключає збір використання індексів, що в цілому позитивно позначається на системі.
Детальніше про прапори трасування можна дізнатися тут
За наведеним вище посиланням важливо також враховувати версії та складання MS SQL Server, тому що для нових версій деякі прапори трасування включені за замовчуванням або не дають жодного ефекту.
Увімкнути та вимкнути прапор трасування можна за допомогою команд DBCC TRACEON та DBCC TRACEOFF відповідно. Детальніше дивіться тут
Отримати стан прапорів трасування можна за допомогою команди DBCC TRACESTATUS: Детальніше
Щоб прапори трасування були включені до автозапуску служби MS SQL Server, необхідно зайти в SQL Server Configuration Manager і у властивостях служби додати дані прапори трасування через -T:
Деякі аспекти моніторингу MS SQL Server. Рекомендації щодо налаштування прапорів трасування

Підсумки

У цій статті було розібрано деякі аспекти моніторингу MS SQL Server, за допомогою яких можна оперативно виявити нестачу ОЗУ та вільного часу ЦПУ, а також низку інших менш очевидних проблем. Були розглянуті прапори трасування, що найчастіше використовуються.

Джерела:

» Статистика очікування SQL Server
» Статистика очікувань SQL Server або будь ласка, скажіть мені, де болить
» Системне уявлення sys.dm_os_schedulers
» Використання Zabbix для стеження за базою даних MS SQL Server
» Стиль життя SQL
» Прапори трасування
» sql.ru

Джерело: habr.com

Додати коментар або відгук