Алғы сөз
Көбінесе MS SQL Server ДҚБЖ пайдаланушылары, әзірлеушілері және әкімшілері деректер қорының немесе тұтастай ДҚБЖ өнімділігі мәселелеріне тап болады, сондықтан 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'
);
Мұнда сіз пайыздық және AvgWait_S көрсеткіштеріне назар аударуыңыз керек. Егер олар өз жиынтығында маңызды болса, онда MS SQL Server данасы үшін жедел жады жеткіліксіз болу ықтималдығы өте жоғары. Маңызды мәндер әрбір жүйе үшін жеке анықталады. Дегенмен, келесіден бастауға болады: Пайыз>=1 және AvgWait_S>=0.005.
Мониторинг жүйесіне көрсеткіштерді шығару үшін (мысалы, Zabbix) келесі екі сұрауды жасауға болады:
- ЖЖҚ пайызбен күтудің қанша түрін алады (күтудің барлық түрлерінің сомасы):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - миллисекундтарда қанша ЖЖҚ күту түрін алады (барлық осындай күту түрлері үшін барлық орташа кідірістердің максималды мәні):
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 данасы үшін жедел жады жеткілікті ме деген қорытынды жасауға болады.
CPU шамадан тыс жүктелуін анықтау әдісі
Процессор уақытының жетіспеушілігін анықтау үшін sys.dm_os_schedulers жүйелік көрінісін пайдалану жеткілікті. Мұнда, егер орындалатын_тапсырмалар_саны үнемі 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 данасы үшін процессордың жеткілікті уақыты (процессордың ядроларының саны) бар ма деген қорытынды жасауға болады.
Дегенмен, сұраулардың өзі бірден бірнеше ағынды сұрай алатынын есте ұстаған жөн. Кейде оңтайландырушы сұраудың күрделілігін дұрыс бағалай алмайды. Содан кейін сұрауға берілген уақытта бір уақытта өңдеуге болмайтын тым көп ағындар бөлінуі мүмкін. Сондай-ақ бұл процессор уақытының жетіспеушілігімен байланысты күту түрін тудырады және белгілі бір процессорлық ядроларды пайдаланатын жоспарлаушылар үшін кезектің өсуін тудырады, яғни мұндай жағдайларда орындалатын_тапсырмалар_санының көрсеткіші өседі.
Бұл жағдайда процессор өзектерінің санын көбейтпес бұрын, MS SQL Server данасы параллелизм қасиеттерін дұрыс конфигурациялау қажет, ал 2016 нұсқасынан бастап қажетті дерекқорлардың параллельдік қасиеттерін дұрыс конфигурациялау қажет:
Мұнда келесі параметрлерге назар аудару керек:
- Параллелизмнің максималды дәрежесі - әрбір сұрауға бөлуге болатын ағындардың максималды санын орнатады (әдепкі мән 0 - тек операциялық жүйенің өзі және MS SQL серверінің шығарылымымен шектеледі)
- Параллельдік құнының шегі – параллелизмнің болжалды құны (әдепкі – 5)
- Max DOP - дерекқор деңгейіндегі әрбір сұрауға бөлуге болатын ағындардың максималды санын орнатады (бірақ «Параллелизмнің максималды дәрежесі» қасиетінің мәнінен артық емес) (әдепкі мәні 0 – тек операциялық жүйенің өзімен шектеледі және MS SQL серверінің шығарылымы, сонымен қатар MS SQL серверінің бүкіл данасының «Параллелизмнің максималды дәрежесі» қасиетіне шектеу)
Мұнда барлық жағдайларға бірдей жақсы рецепт беру мүмкін емес, яғни ауыр сұрауларды талдау керек.
Өз тәжірибемнен параллелизм қасиеттерін орнату үшін OLTP жүйелері үшін келесі әрекеттер алгоритмін ұсынамын:
- алдымен параллелизмнің даналық кеңістігін 1-ге орнату арқылы параллелизмді өшіріңіз
- ең ауыр сұрауларды талдаңыз және олар үшін ағындардың оңтайлы санын таңдаңыз
- 2-қадамда алынған ағындардың таңдалған оңтайлы санына параллелизмнің максималды дәрежесін орнатыңыз және нақты дерекқорлар үшін әрбір дерекқор үшін 2-қадамнан алынған максималды DOP мәнін орнатыңыз
- ең ауыр сұраныстарды талдап, көп ағынның теріс әсерін анықтау. Егер солай болса, параллелизм үшін шығындар шегін арттырыңыз.
1C, Microsoft CRM және Microsoft NAV сияқты жүйелер үшін көп жағдайда көп ағынға тыйым салу қолайлы.
Сондай-ақ, егер Стандартты шығарылым болса, көп жағдайда көп ағынға тыйым салу бұл басылымның CPU ядроларының санымен шектелгендіктен қолайлы.
OLAP жүйелері үшін жоғарыда сипатталған алгоритм қолайлы емес.
Өз тәжірибемнен параллелизм қасиеттерін орнату үшін OLAP жүйелері үшін келесі әрекеттер алгоритмін ұсынамын:
- ең ауыр сұрауларды талдаңыз және олар үшін ағындардың оңтайлы санын таңдаңыз
- 1-қадамда алынған ағындардың таңдалған оңтайлы санына параллелизмнің максималды дәрежесін орнатыңыз және нақты дерекқорлар үшін әрбір дерекқор үшін 1-қадамнан алынған максималды DOP мәнін орнатыңыз
- ең ауыр сұрауларды талдау және параллельді шектеудің теріс әсерін анықтау. Егер солай болса, параллелизм мәні үшін шығындар шегін төмендетіңіз немесе осы алгоритмнің 1-2 қадамдарын қайталаңыз.
Яғни, OLTP жүйелері үшін біз бір ағындыдан көп ағындыға, ал OLAP-жүйелері үшін, керісінше, көп ағындыдан бір ағындыға өтеміз. Осылайша, сіз нақты дерекқор үшін де, MS SQL серверінің бүкіл данасы үшін де оңтайлы параллелизм параметрлерін таңдай аласыз.
Сонымен қатар MS SQL Server өнімділігін бақылау нәтижелеріне негізделген параллелизм қасиеттерінің параметрлерін уақыт өте келе өзгерту қажет екенін түсіну маңызды.
Бақылау жалаушаларын орнату бойынша нұсқаулар
Өз тәжірибемнен және әріптестерімнің тәжірибесінен оңтайлы өнімділік үшін 2008-2016 нұсқалары үшін MS SQL Server қызметінің іске қосу деңгейінде келесі бақылау жалаушаларын орнатуды ұсынамын:
- 610 - Индекстелген кестелерге кірістірулерді тіркеу қысқарды. Көптеген жазбалар мен көптеген транзакциялары бар кестелерге кірістірулерге көмектесе алады, WRITELOG индекстердегі өзгерістерді жиі күтеді.
- 1117 - Файлдар тобындағы файл автоматты өсу шегі талаптарына сай болса, файлдар тобындағы барлық файлдар өседі.
- 1118 - Барлық нысандарды әртүрлі көлемде орналастыруға мәжбүрлейді (аралас экстенттерге тыйым салу), бұл аралас экстенттерді бақылау үшін пайдаланылатын SGAM бетін сканерлеу қажеттілігін азайтады.
- 1224 - Құлыптар санына негізделген құлыпты арттыруды өшіреді. Дегенмен, жадты шамадан тыс пайдалану құлыптың күшеюіне себеп болуы мүмкін
- 2371 - Статистиканы автоматты түрде жаңартудың бекітілген шегін динамикалық автоматты статистиканы жаңарту шегіне өзгертеді. Жазбалардың қате саны қате орындау жоспарларына әкелетін үлкен кестелер үшін сұрау жоспарларын жаңарту үшін маңызды.
- 3226 - Қате журналындағы сақтық көшірменің сәтті болуы туралы хабарларды басады
- 4199 - CUs және SQL Server қызмет бумаларында шығарылған сұрауды оңтайландырғышқа өзгертулерді қамтиды
- 6532-6534 - Кеңістіктік деректер түрлеріндегі сұрау әрекеттері үшін өнімділікті жақсартуларды қамтиды
- 8048 - NUMA бөлінген жад нысандарын орталық процессорға бөлінгендерге түрлендіреді
- 8780 - Сұрауды жоспарлау үшін қосымша уақыт бөлуді қосады. Бұл жалаушасыз кейбір сұраулар қабылданбауы мүмкін, себебі оларда сұрау жоспары жоқ (өте сирек кездесетін қате)
- 8780 - 9389 - пакеттік режим операторына көбірек жадты сұрауға және көбірек жад қолжетімді болса, деректерді tempdb-ге жылжытуды болдырмауға мүмкіндік беретін пакеттік режим мәлімдемелері үшін қосымша динамикалық рұқсат жады буферін қосады.
Сондай-ақ 2016 жылға дейін 2301 бақылау жалауын қосу пайдалы, ол шешімдерді қолдауды оңтайландыруға мүмкіндік береді және осылайша дұрысырақ сұрау жоспарларын таңдауға көмектеседі. Дегенмен, 2016 нұсқасы бойынша ол көбінесе жалпы сұрауды орындау уақытына теріс әсер етеді.
Сондай-ақ, индекстері көп жүйелер үшін (мысалы, 1С дерекқорлары үшін) индексті пайдалануды жинауды өшіретін 2330 бақылау жалауын қосуды ұсынамын, бұл жалпы жүйеге оң әсер етеді.
Бақылау жалаулары туралы қосымша ақпаратты қараңыз
Жоғарыдағы сілтемеден MS SQL серверінің нұсқалары мен құрастыруларын қарастыру да маңызды, өйткені жаңарақ нұсқалар үшін кейбір бақылау жалаулары әдепкі бойынша қосылады немесе ешқандай әсер етпейді.
Бақылау жалауын сәйкесінше DBCC TRACEON және DBCC TRACEOFF пәрмендерімен қосуға және өшіруге болады. Қосымша мәліметтер алу үшін қараңыз
Бақылау жалауларының күйін DBCC TRACESTATUS пәрмені арқылы алуға болады:
Бақылау жалаулары MS SQL сервері қызметінің автоқосылуына қосылуы үшін SQL серверінің конфигурация реттеушісіне өтіп, осы бақылау жалауларын қызмет сипаттарында -T арқылы қосу керек:
Нәтижелері
Бұл мақалада MS SQL Server мониторингінің кейбір аспектілері талданды, оның көмегімен жедел жадтың жетіспеушілігін және процессордың бос уақытын, сондай-ақ басқа да бірнеше анық емес проблемаларды жылдам анықтауға болады. Ең жиі қолданылатын бақылау жалаушалары қаралды.
Көздер:
»
»
»
»
»
»
»
Ақпарат көзі: www.habr.com