сөздөр
Көбүнчө MS SQL Server DBMS колдонуучулары, иштеп чыгуучулары жана администраторлору маалымат базасынын же бүтүндөй МББнын иштешинин көйгөйлөрүнө туш болушат, андыктан MS SQL серверине мониторинг жүргүзүү абдан актуалдуу.
Бул макала макалага кошумча болуп саналат жана ал MS SQL серверине мониторинг жүргүзүүнүн кээ бир аспектилерин карап чыгат, атап айтканда: кайсы ресурстар жетишпей жатканын кантип тез аныктоо керек, ошондой эле изи желектерин орнотуу боюнча сунуштар.
Төмөнкү скрипттердин иштеши үчүн, сиз каалаган маалымат базасында төмөнкүдөй inf схемасын түзүшүңүз керек:
Inf схемасын түзүү
use <имя_БД>;
go
create schema inf;
RAM жетишсиздигин аныктоо ыкмасы
RAM жетишсиздигинин биринчи көрсөткүчү MS SQL серверинин инстанциясы ага бөлүнгөн бардык оперативдүү эстутумду жеп койгондо.
Бул үчүн, төмөнкү өкүлчүлүктү түзүү 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 көрсөткүчтөрүнө көңүл буруу керек. Эгерде алар жалпысынан маанилүү болсо, анда MS SQL Server инстанциясында RAM жетишсиздигинин ыктымалдыгы өтө жогору. Негизги баалуулуктар ар бир система үчүн жекече аныкталат. Бирок, сиз төмөнкү көрсөткүчтөн баштасаңыз болот: Процент>=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' );
Бул эки көрсөткүч үчүн алынган баалуулуктардын динамикасынын негизинде, биз MS SQL Server инстанциясы үчүн жетиштүү RAM бар же жокпу деген тыянак чыгарууга болот.
Ашыкча CPU жүгүн аныктоо ыкмасы
CPU убактысынын жетишсиздигин аныктоо үчүн, жөн гана 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 инстанциясы үчүн процессордун убактысы (CPU өзөктөрүнүн саны) жетиштүүбү деген тыянак чыгара алабыз.
Бирок, сурамдардын өзү бир эле учурда бир нече жипти сурай аларын эстен чыгарбоо керек. Кээде оптимизатор суроонун өзүнүн татаалдыгын туура баалай албайт. Анда суроо-талапка өтө көп жиптер бөлүнүшү мүмкүн, аларды бир убакта иштетүү мүмкүн эмес. Жана бул процессордун убактысынын жетишсиздиги менен байланышкан күтүүнүн түрүн пайда кылат жана белгилүү бир CPU өзөктөрүн колдонгон пландаштыруучулар үчүн кезектин өсүшү, башкача айтканда, мындай шарттарда runnable_tasks_count көрсөткүчү көбөйөт.
Бул учурда, CPU өзөктөрүнүн санын көбөйтүүдөн мурун, сиз MS SQL Server инстанциясынын параллелдүүлүк касиеттерин туура конфигурациялашыңыз керек, ал эми 2016-версиясынан баштап керектүү маалымат базаларынын параллелдүүлүк касиеттерин туура конфигурациялашыңыз керек:
Бул жерде сиз төмөнкү параметрлерге көңүл буруу керек:
- Max Degree of Parallelism — ар бир суроого бөлүштүрүлө турган жиптердин максималдуу санын белгилейт (демейки 0 — операциялык системанын өзү жана MS SQL Server басылмасы менен гана чектелген)
- Параллелдүүлүк үчүн нарк босогосу - параллелизмдин болжолдуу баасы (демейки 5)
- Max DOP — маалымат базасынын деңгээлинде ар бир суроого бөлүштүрүлө турган жиптердин максималдуу санын белгилейт (бирок “Параллелдүүлүктүн максималдуу даражасы” касиетинин маанисинен ашпайт) (демейки боюнча 0 — операциялык система тарабынан гана чектелген. өзү жана MS SQL Server басылмасы, ошондой эле бүтүндөй MS SQL Server инстанциясынын "Параллелдүүлүктүн максималдуу даражасы" касиетине чектөө)
Бардык учурларда бирдей жакшы рецепт берүү мүмкүн эмес, башкача айтканда, татаал суроолорду талдоо керек.
Өзүмдүн тажрыйбама таянып, мен параллелизм касиеттерин конфигурациялоо үчүн OLTP системалары үчүн төмөнкү аракеттердин алгоритмин сунуш кылам:
- адегенде параллелизмдин максималдуу даражасын бүт инстанциянын деңгээлинде 1ге коюу менен параллелизмди өчүрүңүз
- эң оор суроо-талаптарды талдап, алар үчүн жиптердин оптималдуу санын тандаңыз
- 2-кадамдан алынган жиптердин тандалган оптималдуу санына параллелдүүлүктүн максималдуу даражасын коюңуз, ошондой эле белгилүү бир маалымат базалары үчүн ар бир маалымат базасы үчүн 2-кадамдан алынган Max DOP маанисин коюңуз
- оор суроо-талаптарды талдоо жана multithreading терс таасирин аныктоо. Эгер ошондой болсо, анда Параллелдүүлүк үчүн чыгымдар босогосун жогорулатыңыз.
1C, Microsoft CRM жана Microsoft NAV сыяктуу системалар үчүн көпчүлүк учурларда көп агымга тыюу салуу ылайыктуу
Ошондой эле, эгер сизде Стандарттык чыгарылыш болсо, анда көпчүлүк учурларда бул чыгарылыш CPU өзөктөрүнүн саны боюнча чектелгендигине байланыштуу көп агымга тыюу салуу ылайыктуу.
Жогоруда сүрөттөлгөн алгоритм OLAP системалары үчүн ылайыктуу эмес.
Өзүмдүн тажрыйбама таянып, мен параллелизм касиеттерин конфигурациялоо үчүн OLAP системалары үчүн төмөнкү аракеттердин алгоритмин сунуш кылам:
- эң оор суроо-талаптарды талдап, алар үчүн жиптердин оптималдуу санын тандаңыз
- 1-кадамдан алынган жиптердин тандалган оптималдуу санына параллелдүүлүктүн максималдуу даражасын коюңуз, ошондой эле белгилүү бир маалымат базалары үчүн ар бир маалымат базасы үчүн 1-кадамдан алынган Max DOP маанисин коюңуз
- эң оор суроо-талаптарды талдоо жана параллелдүүлүктү чектөөнүн терс таасирин аныктоо. Эгер ошондой болсо, анда Параллелдүүлүк мааниси үчүн нарк босогосун төмөндөтүңүз же бул алгоритмдин 1-2 кадамдарын кайталаңыз
Башкача айтканда, OLTP системалары үчүн биз бир жиптен көп агымга, ал эми OLAP системалары үчүн, тескерисинче, биз көп жиптүүдөн бир агымга өтөбүз. Ошентип, сиз белгилүү бир маалымат базасы үчүн да, бүт MS SQL Server инстанциясы үчүн да оптималдуу параллелизм орнотууларын тандай аласыз.
Ошондой эле MS SQL серверинин иштешине мониторинг жүргүзүүнүн натыйжаларына таянып, убакыттын өтүшү менен параллелдүүлүк касиеттеринин жөндөөлөрү өзгөртүлүшү керек экенин түшүнүү керек.
Из желектерин орнотуу боюнча сунуштар
Өзүмдүн тажрыйбамдан жана кесиптештеримдин тажрыйбасынан оптималдуу иштеши үчүн мен 2008-2016 версиялары үчүн MS SQL Server кызматын иштетүү деңгээлинде төмөнкү изи желектерди коюуну сунуштайм:
- 610 - Индекстелген таблицаларга кыстармаларды жазууну кыскартуу. Көптөгөн жазуулар жана көптөгөн транзакциялар бар таблицаларга кыстаруу менен жардам бере алат, WRITELOG индекстердин өзгөрүшүн тез-тез күтөт.
- 1117 - Эгер файл тобунун файлы автоматтык өсүү босогосуна жооп берсе, файл тобунун бардык файлдары чоңойт
- 1118 - Бардык объекттерди ар кандай деңгээлде жайгаштырууга мажбурлайт (аралаш экстенттерге жол бербейт), бул аралаш экстенттерге көз салуу үчүн колдонулган SGAM барагын сканерлөө зарылдыгын азайтат.
- 1224 - Кулпунун санынын негизинде кулпунун эскалациясын өчүрөт. Бирок, эстутумду ашыкча колдонуу кулпунун эскалациясын иштетиши мүмкүн
- 2371 - Статистиканы автоматтык түрдө жаңыртуу босогосун динамикалык автоматтык статистика жаңыртуу босогосуна өзгөртөт. Чоң таблицалардагы суроо пландарын жаңыртуу үчүн маанилүү, анда жазуулардын санын туура эмес аныктоо пландарды ката аткарууга алып келет.
- 3226 - каталар журналында камдык ийгилик билдирүүлөрүн басат
- 4199 - SQL Server жаңыртуу топтомдорунда жана кызмат пакеттеринде чыгарылган суроо оптимизаторуна өзгөртүүлөрдү камтыйт
- 6532-6534 - мейкиндик маалымат түрлөрү менен суроо-талаптар үчүн аткарууну жакшыртууларды камтыйт
- 8048 - NUMA-бөлүштүрүлгөн эс объекттерин CPU-бөлүштүрүлгөн объекттерге айлантат
- 8780 - Сурамдарды пландаштыруу үчүн кошумча убакыт бөлүүнү иштетет. Бул желексиз айрым сурамдар четке кагылышы мүмкүн, анткени аларда суроо планы жок (өтө сейрек кездешүүчү ката)
- 8780 - 9389 - Пакет режиминин операторлору үчүн кошумча динамикалык убактылуу эс буферин иштетип, пакет режиминин операторуна кошумча эстутумду талап кылууга жана кошумча эс бар болсо, маалыматтарды tempdbге өткөрбөөгө мүмкүндүк берет
Ошондой эле 2016 версиясын 2301-жылга чейин иштетүү пайдалуу, ал чечимдерди колдоону оптималдаштырууга мүмкүндүк берет жана ошону менен жакшыраак суроо пландарын тандоого жардам берет. Бирок, версия 2016-жылдан бери, ал көп учурда бир кыйла узак жалпы суроо аткаруу убакыттарына терс таасирин тийгизет.
Ошондой эле, индекстери көп системалар үчүн (мисалы, 1С маалымат базалары үчүн) мен 2330 изи желегин иштетүүнү сунуштайм, ал индексти колдонууну чогултууну өчүрөт, бул жалпысынан системага оң таасирин тийгизет.
Сиз трассалар тууралуу көбүрөөк биле аласыз
Жогорудагы шилтемеден MS SQL серверинин версияларын жана түзүмдөрүн эске алуу да маанилүү, анткени жаңы версиялар үчүн, кээ бир изи желектер демейки боюнча иштетилген же эч кандай таасири жок.
Сиз тиешелүүлүгүнө жараша DBCC TRACEON жана DBCC TRACEOFF буйруктарын колдонуп, трек желекчесин иштетип же өчүрө аласыз. Көбүрөөк маалымат караңыз
Сиз DBCC TRACESTATUS буйругун колдонуп, изи желектеринин абалын ала аласыз:
Из желектери MS SQL Server кызматын автоматтык түрдө ишке киргизүү үчүн, сиз SQL Server Конфигурация Менеджерине барып, кызматтын касиеттерине -T аркылуу бул байкоо желектерин кошушуңуз керек:
натыйжалары
Бул макалада MS SQL серверине мониторинг жүргүзүүнүн кээ бир аспектилери каралып, анын жардамы менен сиз оперативдүү эс тутумдун жетишсиздигин жана CPU бош убактысын, ошондой эле бир катар башка анча айкын эмес көйгөйлөрдү тез аныктай аласыз. Эң көп колдонулган изи желектери каралып чыкты.
булагы:
»
»
»
»
»
»
»
Source: www.habr.com