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

Дадаць каментар