ПроХостер > блог > Администрација > Неки аспекти праћења МС СКЛ Сервера. Препоруке за постављање ознака за праћење

Неки аспекти праћења МС СКЛ Сервера. Препоруке за постављање ознака за праћење

Предговор

Често се корисници, програмери и администратори МС СКЛ Сервер ДБМС суочавају са проблемима перформанси базе података или ДБМС-а у целини, па је праћење МС СКЛ Сервер-а веома релевантно.
Овај чланак је додатак чланку Коришћење Заббик-а за надгледање базе података МС СКЛ сервера и испитаће неке аспекте надгледања МС СКЛ Сервера, посебно: како брзо одредити који ресурси недостају, као и препоруке за постављање ознака праћења.
Да би следеће скрипте функционисале, потребно је да креирате инф шему у жељеној бази података на следећи начин:
Креирање инф шеме

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

Метода за откривање недостатка РАМ-а

Први показатељ недостатка РАМ-а је када инстанца МС СКЛ Сервера поједе сву РАМ меморију која јој је додељена.
Да бисте то урадили, креирајте следећу репрезентацију инф.вРАМ:
Креирање приказа инф.вРАМ

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;

Затим можете утврдити да инстанца МС СКЛ Сервера троши сву меморију која јој је додељена помоћу следећег упита:

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

Ако индикатор СКЛ_сервер_пхисицал_мемори_ин_усе_Мб константно није мањи од СКЛ_сервер_цоммиттед_таргет_Мб, онда морате да проверите статистику чекања.
Да бисмо утврдили недостатак РАМ-а кроз статистику чекања, направимо репрезентацију инф.вВаитс:
Креирање приказа инф.вВаитс

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

Овде морате обратити пажњу на индикаторе Перцентаге и АвгВаит_С. Ако су значајни у својој укупности, онда постоји веома велика вероватноћа да инстанца МС СКЛ Сервера нема довољно РАМ-а. Битне вредности се одређују појединачно за сваки систем. Међутим, можете почети са следећим индикатором: Перцентаге>=1 и АвгВаит_С>=0.005.
Да бисте приказали индикаторе систему за надзор (на пример, Заббик), можете креирати следећа два упита:

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

На основу динамике добијених вредности за ова два индикатора, можемо закључити да ли има довољно РАМ-а за МС СКЛ Сервер инстанцу.

Метода за откривање прекомерног оптерећења процесора

Да бисте идентификовали недостатак ЦПУ времена, само користите системски приказ сис.дм_ос_сцхедулерс. Овде, ако је индикатор руннабле_таскс_цоунт константно већи од 1, онда постоји велика вероватноћа да број језгара није довољан за инстанцу МС СКЛ Сервера.
Да бисте приказали индикатор у систему за праћење (на пример, Заббик), можете креирати следећи захтев:

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

На основу динамике добијених вредности за овај индикатор, можемо закључити да ли има довољно процесорског времена (број ЦПУ језгара) за инстанцу МС СКЛ Сервера.
Међутим, важно је запамтити чињеницу да сами упити могу да постављају упите за више нити одједном. А понекад оптимизатор не може исправно проценити сложеност самог упита. Тада захтеву може бити додељено превише нити, које се у датом тренутку не могу обрадити истовремено. И то такође узрокује врсту чекања повезаног са недостатком процесорског времена и раст реда за планере који користе одређена језгра ЦПУ-а, односно индикатор руннабле_таскс_цоунт ће се повећати у таквим условима.
У овом случају, пре него што повећате број ЦПУ језгара, потребно је да правилно конфигуришете својства паралелизма саме инстанце МС СКЛ Сервера, а од верзије 2016, исправно конфигуришете својства паралелизма жељених база података:
Неки аспекти праћења МС СКЛ Сервера. Препоруке за постављање ознака за праћење

Неки аспекти праћења МС СКЛ Сервера. Препоруке за постављање ознака за праћење
Овде треба обратити пажњу на следеће параметре:

  1. Максимални степен паралелизма—поставља максималан број нити које се могу доделити сваком захтеву (подразумевано је 0—ограничено само самим оперативним системом и издањем МС СКЛ Сервера)
  2. Праг цене за паралелизам – процењена цена паралелизма (подразумевано је 5)
  3. Мак ДОП—поставља максималан број нити које се могу доделити сваком упиту на нивоу базе података (али не више од вредности својства „Максимални степен паралелизма“) (подразумевано је 0—ограничено само оперативним системом себе и издање МС СКЛ Сервера, као и ограничење на својство „Максимални степен паралелизма“ целе инстанце МС СКЛ Сервера)

Немогуће је дати подједнако добар рецепт за све случајеве, односно потребно је анализирати тешке упите.
На основу сопственог искуства, препоручујем следећи алгоритам акција за ОЛТП системе за конфигурисање својстава паралелизма:

  1. прво онемогућите паралелизам тако што ћете поставити максимални степен паралелизма на 1 на нивоу целе инстанце
  2. анализирати најтеже упите и одабрати оптималан број нити за њих
  3. подесите максимални степен паралелизма на изабрани оптимални број нити добијених из корака 2, а такође за одређене базе података подесите максималну вредност ДОП добијену из корака 2 за сваку базу података
  4. анализира најтеже упите и идентификује негативан ефекат вишенитног рада. Ако јесте, онда повећајте праг трошкова за паралелизам.
    За системе као што су 1Ц, Мицрософт ЦРМ и Мицрософт НАВ, у већини случајева је погодна забрана вишенитног рада

Такође, ако имате Стандардно издање, онда је у већини случајева прикладна забрана вишенитног рада због чињенице да је ово издање ограничено у броју ЦПУ језгара.
Горе описани алгоритам није погодан за ОЛАП системе.
На основу сопственог искуства, препоручујем следећи алгоритам акција за ОЛАП системе за конфигурисање својстава паралелизма:

  1. анализирати најтеже упите и одабрати оптималан број нити за њих
  2. подесите максимални степен паралелизма на изабрани оптимални број нити добијених из корака 1, а такође за одређене базе података подесите максималну вредност ДОП добијену из корака 1 за сваку базу података
  3. анализира најтеже упите и идентификује негативан ефекат ограничавања истовремености. Ако јесте, онда или смањите граничну вредност цене за паралелизам или поновите кораке 1-2 овог алгоритма

Односно, за ОЛТП системе идемо са једнонитног на вишенитно, а за ОЛАП системе, напротив, идемо са вишенитног на једнонитни. На овај начин можете одабрати оптимална подешавања паралелизма како за одређену базу података тако и за читаву инстанцу МС СКЛ Сервера.
Такође је важно разумети да подешавања својства паралелности морају да се мењају током времена, на основу резултата праћења перформанси МС СКЛ Сервера.

Препоруке за постављање ознака за праћење

Из сопственог искуства и искуства мојих колега, за оптималне перформансе, препоручујем да поставите следеће ознаке праћења на нивоу покретања услуге МС СКЛ Сервер за верзије 2008-2016:

  1. 610 - Смањите евидентирање уметања у индексиране табеле. Може да помогне у уметању у табеле са великим бројем записа и много трансакција, са честим дугим ВРИТЕЛОГ чекањем на промене у индексима
  2. 1117 - Ако датотека у групи датотека достиже праг за аутоматско повећање, све датотеке у групи датотека се повећавају
  3. 1118 – Присиљава све објекте да буду лоцирани у различитим екстентима (забрањује мешовите опсеге), што минимизира потребу за скенирањем СГАМ странице, која се користи за праћење мешовитих екстента
  4. 1224 – Онемогућава ескалацију закључавања на основу броја закључавања. Међутим, прекомерна употреба меморије може омогућити ескалацију закључавања
  5. 2371 – Мења фиксни праг аутоматског ажурирања статистике у праг за динамичко аутоматско ажурирање статистике. Важно за ажурирање планова упита на великим табелама где погрешно дефинисање броја записа доводи до погрешних планова извршења
  6. 3226 – Потискује поруке о успеху резервне копије у евиденцији грешака
  7. 4199 – Укључује промене оптимизатора упита објављених у збирним пакетима ажурирања и сервисним пакетима за СКЛ Сервер
  8. 6532-6534 – Укључује побољшања перформанси за упите са типовима просторних података
  9. 8048 – Конвертује НУМА-партициониране меморијске објекте у ЦПУ-партициониране
  10. 8780 – Омогућава додатну доделу времена за планирање упита. Неки захтеви без ове ознаке могу бити одбијени јер немају план упита (веома ретка грешка)
  11. 8780 - 9389 - Омогућава додатни динамички привремени меморијски бафер за оператере групног режима, омогућавајући оператеру групног режима да захтева додатну меморију и избегава пренос података у темпдб ако је додатна меморија доступна

Такође је корисно омогућити ознаку праћења 2016 пре верзије 2301, што омогућава напредну оптимизацију подршке одлучивању и на тај начин помаже у одабиру бољих планова упита. Међутим, од верзије 2016, то често има негативан ефекат на прилично дуга укупна времена извршавања упита.
Такође, за системе са пуно индекса (на пример, за 1Ц базе података), препоручујем да омогућите заставицу праћења 2330, која онемогућава прикупљање коришћења индекса, што генерално има позитиван ефекат на систем.
Можете сазнати више о ознакама праћења овде
Са горње везе, такође је важно узети у обзир верзије и верзије МС СКЛ Сервера, јер су за новије верзије неке ознаке праћења омогућене подразумевано или немају ефекта.
Можете омогућити или онемогућити ознаку праћења помоћу команди ДБЦЦ ТРАЦЕОН и ДБЦЦ ТРАЦЕОФФ. Погледајте више детаља овде
Статус заставица праћења можете добити помоћу команде ДБЦЦ ТРАЦЕСТАТУС: више
Да би ознаке праћења биле укључене у аутоматско покретање услуге МС СКЛ Сервер, потребно је да одете у СКЛ Сервер Цонфигуратион Манагер и додате ове ознаке праћења преко -Т у својства услуге:
Неки аспекти праћења МС СКЛ Сервера. Препоруке за постављање ознака за праћење

Резултати

Овај чланак је испитао неке аспекте надгледања МС СКЛ Сервера, уз помоћ којих можете брзо идентификовати недостатак РАМ-а и слободног ЦПУ времена, као и низ других мање очигледних проблема. Прегледане су најчешће коришћене ознаке трагова.

Извори:

» Статистика чекања СКЛ сервера
» СКЛ Сервер статистике чекања или молим вас реците ми где боли
» Системски приказ сис.дм_ос_сцхедулерс
» Коришћење Заббик-а за надгледање базе података МС СКЛ сервера
» СКЛ Лифестиле
» Траце флагс
» скл.ру

Извор: ввв.хабр.цом

Додај коментар