Prefață
Destul de des, utilizatorii, dezvoltatorii și administratorii SGBD MS SQL Server întâmpină probleme de performanță ale bazei de date sau ale SGBD în ansamblu, astfel încât monitorizarea MS SQL Server este foarte relevantă.
Acest articol este o completare la articol și va acoperi unele aspecte ale monitorizării MS SQL Server, în special: cum să determinați rapid ce resurse lipsesc, precum și recomandări pentru setarea semnalizatoarelor de urmărire.
Pentru ca următoarele scripturi să funcționeze, trebuie să creați o schemă inf în baza de date dorită, după cum urmează:
Crearea unei scheme inf
use <имя_БД>;
go
create schema inf;
Metoda de detectare a lipsei RAM
Primul indicator al lipsei RAM este cazul în care o instanță de MS SQL Server consumă toată RAM-ul alocat acesteia.
Pentru a face acest lucru, vom crea următoarea reprezentare a inf.vRAM:
Se creează vizualizarea 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;
Apoi puteți determina că o instanță a MS SQL Server consumă toată memoria alocată acesteia prin următoarea interogare:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Dacă SQL_server_physical_memory_in_use_Mb este în mod constant mai mare decât sau egal cu SQL_server_committed_target_Mb, atunci statisticile de așteptare trebuie verificate.
Pentru a determina lipsa memoriei RAM prin statistici de așteptare, să creăm vizualizarea inf.vWaits:
Se creează vizualizarea 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];
În acest caz, puteți determina lipsa memoriei RAM cu următoarea interogare:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Aici trebuie să acordați atenție indicatorilor Procent și AvgWait_S. Dacă sunt semnificative în totalitatea lor, atunci există o probabilitate foarte mare ca RAM să nu fie suficientă pentru instanța MS SQL Server. Valorile semnificative sunt determinate individual pentru fiecare sistem. Cu toate acestea, puteți începe cu următoarele: Procent>=1 și AvgWait_S>=0.005.
Pentru a afișa indicatorii către un sistem de monitorizare (de exemplu, Zabbix), puteți crea următoarele două interogări:
- câte tipuri de așteptări sunt ocupate de RAM în procente (suma tuturor acestor tipuri de așteptări):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - câte tipuri de așteptare RAM iau în milisecunde (valoarea maximă a tuturor întârzierilor medii pentru toate astfel de tipuri de așteptare):
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' );
Pe baza dinamicii valorilor obținute pentru acești doi indicatori, putem concluziona dacă există suficientă RAM pentru o instanță de MS SQL Server.
Metoda de detectare a supraîncărcării procesorului
Pentru a identifica lipsa timpului procesorului, este suficient să folosiți vizualizarea de sistem sys.dm_os_schedulers. Aici, dacă runnable_tasks_count este în mod constant mai mare decât 1, atunci există o probabilitate mare ca numărul de nuclee să nu fie suficient pentru instanța MS SQL Server.
Pentru a scoate un indicator către un sistem de monitorizare (de exemplu, Zabbix), puteți crea următoarea interogare:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Pe baza dinamicii valorilor obținute pentru acest indicator, putem concluziona dacă există suficient timp de procesor (numărul de nuclee CPU) pentru o instanță a MS SQL Server.
Cu toate acestea, este important să țineți cont de faptul că cererile în sine pot solicita mai multe fire simultan. Și uneori, optimizatorul nu poate estima corect complexitatea interogării în sine. Apoi cererea poate fi alocată prea multe fire de execuție care nu pot fi procesate în același timp la momentul dat. Și acest lucru provoacă, de asemenea, un tip de așteptare asociat cu lipsa timpului procesorului și creșterea cozii pentru programatorii care folosesc anumite nuclee CPU, adică indicatorul runnable_tasks_count va crește în astfel de condiții.
În acest caz, înainte de a crește numărul de nuclee CPU, este necesar să configurați corect proprietățile de paralelism ale instanței MS SQL Server în sine, iar din versiunea 2016, să configurați corect proprietățile de paralelism ale bazelor de date necesare:
Aici ar trebui să acordați atenție următorilor parametri:
- Max Degree of Parallelism - setează numărul maxim de fire care pot fi alocate fiecărei solicitări (valoarea implicită este 0 - limitat doar de sistemul de operare însuși și de ediția MS SQL Server)
- Pragul de cost pentru paralelism - costul estimat al paralelismului (implicit este 5)
- Max DOP - setează numărul maxim de fire care pot fi alocate fiecărei interogări la nivel de bază de date (dar nu mai mult decât valoarea proprietății „Max Degree of Parallelism”) (implicit este 0 - limitat doar de sistemul de operare însuși și ediția MS SQL Server, precum și restricția asupra proprietății „Grad maxim de paralelism” a întregii instanțe a MS SQL Server)
Aici este imposibil să oferi o rețetă la fel de bună pentru toate cazurile, adică trebuie să analizezi interogările grele.
Din propria mea experiență, recomand următorul algoritm de acțiuni pentru sistemele OLTP pentru configurarea proprietăților de paralelism:
- mai întâi dezactivați paralelismul setând gradul maxim de paralelism la 1
- analizați cele mai grele solicitări și selectați numărul optim de fire pentru acestea
- setați gradul maxim de paralelism la numărul optim selectat de fire de execuție obținute de la pasul 2 și, pentru anumite baze de date, setați valoarea DOP maximă obținută de la pasul 2 pentru fiecare bază de date
- analizați cele mai grele solicitări și identificați efectul negativ al multithreading-ului. Dacă este, atunci creșteți pragul de cost pentru paralelism.
Pentru sisteme precum 1C, Microsoft CRM și Microsoft NAV, în majoritatea cazurilor, interzicerea multithreading-ului este potrivită
De asemenea, dacă există o ediție Standard, atunci în majoritatea cazurilor interzicerea multithreading-ului este potrivită datorită faptului că această ediție este limitată în numărul de nuclee CPU.
Pentru sistemele OLAP, algoritmul descris mai sus nu este potrivit.
Din propria mea experiență, recomand următorul algoritm de acțiuni pentru sistemele OLAP pentru configurarea proprietăților de paralelism:
- analizați cele mai grele solicitări și selectați numărul optim de fire pentru acestea
- setați gradul maxim de paralelism la numărul optim selectat de fire de execuție obținute de la pasul 1 și, pentru anumite baze de date, setați valoarea DOP maximă obținută de la pasul 1 pentru fiecare bază de date
- analizați cele mai grele interogări și identificați efectul negativ al limitării concurenței. Dacă este, atunci fie reduceți valoarea Pragului de cost pentru paralelism, fie repetați pașii 1-2 ai acestui algoritm
Adică, pentru sistemele OLTP trecem de la single-threading la multi-threading, iar pentru sistemele OLAP, dimpotrivă, trecem de la multi-threading la single-threading. Astfel, puteți alege setările optime de paralelism atât pentru o anumită bază de date, cât și pentru întreaga instanță a MS SQL Server.
De asemenea, este important să înțelegeți că setările proprietăților de paralelism trebuie modificate în timp, pe baza rezultatelor monitorizării performanței MS SQL Server.
Ghid pentru setarea semnalizatoarelor de urmărire
Din experiența proprie și a colegilor mei, pentru performanțe optime, recomand să setați următoarele semnalizatoare de urmărire la nivelul de rulare al serviciului MS SQL Server pentru versiunile 2008-2016:
- 610 - Înregistrare redusă a inserărilor în tabelele indexate. Poate ajuta cu inserări în tabele cu multe înregistrări și multe tranzacții, cu așteptări frecvente lungi în WRITELOG pentru modificări ale indicilor
- 1117 - Dacă un fișier dintr-un grup de fișiere îndeplinește cerințele pragului de creștere automată, toate fișierele din grupul de fișiere cresc
- 1118 - Forțează ca toate obiectele să fie amplasate în diferite întinderi (interzicerea extinderilor mixte), ceea ce reduce la minimum necesitatea scanării paginii SGAM, care este folosită pentru a urmări întinderi mixte
- 1224 - Dezactivează escaladarea blocării în funcție de numărul de blocări. Cu toate acestea, utilizarea excesivă a memoriei poate declanșa escaladarea blocării
- 2371 - Schimbă pragul fix de actualizare automată a statisticilor în pragul de actualizare automată dinamică a statisticilor. Important pentru actualizarea planurilor de interogări pentru tabele mari, unde un număr incorect de înregistrări duce la planuri de execuție eronate
- 3226 - Suprimă mesajele de succes ale copiei de rezervă din jurnalul de erori
- 4199 - Include modificări la optimizatorul de interogări lansat în CU și pachete de servicii SQL Server
- 6532-6534 - Include îmbunătățiri de performanță pentru operațiunile de interogare pe tipuri de date spațiale
- 8048 - Convertește obiectele de memorie partiționate de NUMA în cele partiționate de CPU
- 8780 - Permite alocarea suplimentară de timp pentru planificarea interogărilor. Unele solicitări fără acest semnalizare pot fi respinse deoarece nu au un plan de interogare (bucătură foarte rară)
- 8780 - 9389 - Activează memoria tampon de acordare dinamică suplimentară pentru instrucțiunile modului lot, care permite operatorului modului lot să solicite mai multă memorie și să evite mutarea datelor în tempdb dacă este disponibilă mai multă memorie
De asemenea, înainte de 2016, este util să activați semnalul de urmărire 2301, care permite optimizări îmbunătățite ale suportului decizional și, astfel, ajută la alegerea unor planuri de interogare mai corecte. Cu toate acestea, începând cu versiunea 2016, are adesea un efect negativ asupra unui timp general de execuție a interogării destul de lung.
De asemenea, pentru sistemele cu o mulțime de indici (de exemplu, pentru bazele de date 1C), recomand activarea semnalizatorului de urmărire 2330, care dezactivează colectarea utilizării indicilor, care are în general un efect pozitiv asupra sistemului.
Pentru mai multe informații despre semnalizatoarele de urmărire, consultați
Din linkul de mai sus, este, de asemenea, important să luați în considerare versiunile și versiunile MS SQL Server, deoarece pentru versiunile mai noi, unele semnalizatoare de urmărire sunt activate implicit sau nu au niciun efect.
Puteți activa și dezactiva indicatorul de urmărire cu comenzile DBCC TRACEON și, respectiv, DBCC TRACEOFF. Pentru mai multe detalii vezi
Puteți obține starea semnalizatoarelor de urmărire folosind comanda DBCC TRACESTATUS:
Pentru ca semnalizatoarele de urmărire să fie incluse în pornirea automată a serviciului MS SQL Server, trebuie să mergeți la Managerul de configurare SQL Server și să adăugați acești indicatori de urmărire prin -T în proprietățile serviciului:
Rezultatele
În acest articol au fost analizate câteva aspecte ale monitorizării MS SQL Server, cu ajutorul cărora puteți identifica rapid lipsa RAM și timpul liber CPU, precum și o serie de alte probleme mai puțin evidente. Au fost revizuite cele mai frecvent utilizate semnalizatoare de urmărire.
Surse:
»
»
»
»
»
»
»
Sursa: www.habr.com