предговорот
Доста често, корисниците, програмерите и администраторите на MS SQL Server DBMS наидуваат на проблеми со перформансите на базата на податоци или на DBMS како целина, така што следењето на MS SQL Server е многу релевантно.
Оваа статија е додаток на статијата и ќе опфати некои аспекти од следењето на MS SQL Server, особено: како брзо да се одреди кои ресурси недостасуваат, како и препораки за поставување ознаки за трага.
За да функционираат следните скрипти, треба да креирате инфо шема во саканата база на податоци на следниов начин:
Креирање инфо шема
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'
);
Овде треба да обрнете внимание на индикаторите Процент и AvgWait_S. Ако тие се значајни во нивната севкупност, тогаш постои многу голема веројатност дека нема доволно RAM меморија за примерот на MS SQL Server. Значајните вредности се одредуваат поединечно за секој систем. Сепак, можете да започнете со следново: Процент>=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 - го поставува максималниот број на нишки што може да се распределат на секое барање на ниво на база на податоци (но не повеќе од вредноста на својството „Max Degree of Parallelism“) (стандардно е 0 - ограничен само од самиот оперативен систем и изданието на MS SQL Server, како и ограничувањето на својството „Max Degree of Parallelism“ на целата инстанца на 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
- 6532-6534 - Вклучува подобрувања на перформансите за операции за пребарување на типови на просторни податоци
- 8048 - Конвертира NUMA партиционирани мемориски објекти во партиционирани од процесорот
- 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 и слободното време на процесорот, како и голем број други помалку очигледни проблеми. Прегледани се најчесто користените ознаки за трага.
Извори:
»
»
»
»
»
»
»
Извор: www.habr.com