predgovor
Često se korisnici, programeri i administratori MS SQL Server DBMS-a suočavaju s problemima performansi baze podataka ili DBMS-a u cjelini, tako da je praćenje MS SQL Servera vrlo važno.
Ovaj članak je dodatak članku te će ispitati neke aspekte nadzora MS SQL Servera, posebno: kako brzo utvrditi koji resursi nedostaju, kao i preporuke za postavljanje oznaka praćenja.
Da bi sljedeće skripte radile, morate stvoriti inf shemu u željenoj bazi podataka na sljedeći način:
Izrada inf sheme
use <имя_БД>;
go
create schema inf;
Metoda za otkrivanje nedostatka RAM-a
Prvi pokazatelj nedostatka RAM-a je kada instanca MS SQL Servera pojede sav RAM koji mu je dodijeljen.
Da biste to učinili, stvorite sljedeći prikaz inf.vRAM:
Stvaranje inf.vRAM pogleda
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;
Zatim možete utvrditi da instanca MS SQL Servera troši svu memoriju koja mu je dodijeljena pomoću sljedećeg upita:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Ako indikator SQL_server_physical_memory_in_use_Mb konstantno nije manji od SQL_server_committed_target_Mb, tada trebate provjeriti statistiku čekanja.
Kako bismo utvrdili nedostatak RAM-a kroz statistiku čekanja, stvorimo prikaz inf.vWaits:
Stvaranje inf.vWaits pogleda
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];
U ovom slučaju možete odrediti nedostatak RAM-a pomoću sljedećeg upita:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Ovdje morate obratiti pozornost na indikatore Percentage i AvgWait_S. Ako su značajni u svojoj ukupnosti, tada postoji vrlo velika vjerojatnost da instanca MS SQL Servera nema dovoljno RAM-a. Bitne vrijednosti se određuju pojedinačno za svaki sustav. Međutim, možete početi sa sljedećim indikatorom: Percentage>=1 i AvgWait_S>=0.005.
Za izlaz indikatora u sustav praćenja (na primjer, Zabbix), možete kreirati sljedeća dva upita:
- Koliki je postotak vrsta čekanja za RAM (zbroj za sve takve vrste čekanja):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - koliko RAM vrsta čekanja traje u milisekundama (maksimalna vrijednost svih prosječnih kašnjenja za sve takve vrste čekanja):
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' );
Na temelju dinamike dobivenih vrijednosti za ova dva pokazatelja možemo zaključiti ima li dovoljno RAM-a za instancu MS SQL Servera.
Metoda otkrivanja preopterećenja CPU-a
Da biste identificirali nedostatak CPU vremena, samo upotrijebite sistemski prikaz sys.dm_os_schedulers. Ovdje, ako je indikator runnable_tasks_count stalno veći od 1, tada postoji velika vjerojatnost da broj jezgri nije dovoljan za instancu MS SQL Servera.
Za izlaz indikatora u sustav za praćenje (na primjer, Zabbix), možete kreirati sljedeći upit:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Na temelju dinamike dobivenih vrijednosti za ovaj pokazatelj možemo zaključiti ima li dovoljno procesorskog vremena (broja CPU jezgri) za instancu MS SQL Servera.
Međutim, važno je zapamtiti činjenicu da sami upiti mogu postavljati upite za više niti odjednom. A ponekad optimizator ne može ispravno procijeniti složenost samog upita. Tada se zahtjevu može dodijeliti previše niti koje se u danom trenutku ne mogu obraditi istovremeno. A to također uzrokuje vrstu čekanja povezanu s nedostatkom procesorskog vremena i rast čekanja za planere koji koriste određene CPU jezgre, odnosno pokazatelj runnable_tasks_count će se povećati u takvim uvjetima.
U ovom slučaju, prije povećanja broja CPU jezgri, trebate ispravno konfigurirati svojstva paralelizma same instance MS SQL Servera, a od verzije 2016 ispravno konfigurirati svojstva paralelizma potrebnih baza podataka:
Ovdje morate obratiti pozornost na sljedeće parametre:
- Max Degree of Parallelism - postavlja najveći broj niti koje se mogu dodijeliti svakom zahtjevu (zadano je 0 - ograničeno samo samim operativnim sustavom i izdanjem MS SQL Servera)
- Prag cijene za paralelizam - procijenjeni trošak paralelizma (zadano je 5)
- Max DOP—postavlja maksimalan broj niti koje se mogu dodijeliti svakom upitu na razini baze podataka (ali ne više od vrijednosti svojstva "Max Degree of Parallelism") (prema zadanim postavkama to je 0—ograničeno samo operativnim sustavom sebe i izdanje MS SQL Servera, kao i ograničenje svojstva "Maksimalni stupanj paralelizma" cijele instance MS SQL Servera)
Nemoguće je dati jednako dobar recept za sve slučajeve, odnosno potrebno je analizirati teške upite.
Na temelju vlastitog iskustva, preporučujem sljedeći algoritam radnji za OLTP sustave za konfiguriranje svojstava paralelizma:
- najprije onemogućite paralelizam postavljanjem maksimalnog stupnja paralelizma za cijelu instancu na 1
- analizirati najteže upite i odabrati optimalan broj niti za njih
- postavite Max Degree of Parallelism na odabrani optimalni broj niti dobiven iz koraka 2, a također za određene baze podataka postavite Max DOP vrijednost dobivenu iz koraka 2 za svaku bazu podataka
- analizirati najteže zahtjeve i identificirati negativan učinak multithreadinga. Ako jest, povećajte prag troškova za paralelizam.
Za sustave kao što su 1C, Microsoft CRM i Microsoft NAV, u većini slučajeva prikladna je zabrana višenitnosti
Također, ako imate standardno izdanje, tada je u većini slučajeva primjerena zabrana višenitnosti zbog činjenice da je ovo izdanje ograničeno u broju CPU jezgri.
Gore opisani algoritam nije prikladan za OLAP sustave.
Na temelju vlastitog iskustva, preporučujem sljedeći algoritam radnji za OLAP sustave za konfiguriranje svojstava paralelizma:
- analizirati najteže upite i odabrati optimalan broj niti za njih
- postavite Max Degree of Parallelism na odabrani optimalni broj niti dobiven iz koraka 1, a također za određene baze podataka postavite Max DOP vrijednost dobivenu iz koraka 1 za svaku bazu podataka
- analizirati najteže upite i identificirati negativan učinak ograničavanja istovremenosti. Ako jest, tada ili smanjite vrijednost praga troška za paralelizam ili ponovite korake 1-2 ovog algoritma
To jest, za OLTP sustave idemo s jednonitnosti na višenitnost, a za OLAP sustave, naprotiv, idemo s višenitnosti na jednonitnost. Na taj način možete odabrati optimalne postavke paralelizma i za određenu bazu podataka i za cijelu instancu MS SQL Servera.
Također je važno razumjeti da se postavke svojstava istovremenosti moraju mijenjati tijekom vremena, na temelju rezultata praćenja performansi MS SQL Servera.
Preporuke za postavljanje oznaka praćenja
Iz vlastitog iskustva i iskustva mojih kolega, za optimalnu izvedbu, preporučujem postavljanje sljedećih oznaka praćenja na razini izvođenja usluge MS SQL Server za verzije 2008-2016:
- 610 - Smanjite bilježenje umetaka u indeksirane tablice. Može pomoći kod umetanja u tablice s mnogo zapisa i mnogo transakcija, s čestim dugim WRITELOG čekanjima na promjene u indeksima
- 1117 - Ako datoteka u grupi datoteka dostigne prag automatskog rasta, sve datoteke u grupi datoteka se povećavaju
- 1118 - Prisilno postavlja sve objekte u različite opsege (onemogućuje miješane opsege), što minimizira potrebu za skeniranjem SGAM stranice, koja se koristi za praćenje miješanih opsega
- 1224 - Onemogućuje eskalaciju zaključavanja na temelju broja zaključavanja. Međutim, prekomjerna upotreba memorije može omogućiti eskalaciju zaključavanja
- 2371 - Mijenja fiksni prag automatskog ažuriranja statistike u prag dinamičkog automatskog ažuriranja statistike. Važno za ažuriranje planova upita za velike tablice, gdje netočan broj zapisa rezultira pogrešnim planovima izvršenja
- 3226 - Potiskuje poruke o uspješnom sigurnosnom kopiranju u zapisniku grešaka
- 4199 - Uključuje promjene alata za optimizaciju upita objavljenog u zbirnim ažuriranjima i servisnim paketima za SQL Server
- 6532-6534 - Uključuje poboljšanja izvedbe za upite s vrstama prostornih podataka
- 8048 - Pretvara NUMA particionirane memorijske objekte u CPU particionirane
- 8780 - Omogućuje dodatnu dodjelu vremena za planiranje upita. Neki zahtjevi bez ove oznake mogu biti odbijeni jer nemaju plan upita (vrlo rijetka pogreška)
- 8780 - 9389 - Omogućuje dodatni dinamički privremeni memorijski međuspremnik za operatore skupnog načina rada, dopuštajući operateru skupnog načina rada da zatraži dodatnu memoriju i izbjegne prijenos podataka u tempdb ako je dodatna memorija dostupna
Prije 2016. također je korisno omogućiti oznaku praćenja 2301, koja omogućuje poboljšane optimizacije podrške odlučivanju i na taj način pomaže u odabiru ispravnijih planova upita. Međutim, od verzije 2016. često ima negativan učinak na prilično duga ukupna vremena izvršenja upita.
Također, za sustave s puno indeksa (na primjer, za 1C baze podataka), preporučujem uključivanje oznake praćenja 2330, koja onemogućuje prikupljanje korištenja indeksa, što općenito ima pozitivan učinak na sustav.
Možete saznati više o oznakama praćenja
Iz gornje veze također je važno uzeti u obzir verzije i međugradnje MS SQL Servera, budući da su za novije verzije neke oznake praćenja omogućene prema zadanim postavkama ili nemaju učinka.
Možete omogućiti ili onemogućiti oznaku praćenja korištenjem naredbi DBCC TRACEON i DBCC TRACEOFF. Pogledajte više detalja
Status zastavica praćenja možete dobiti pomoću naredbe DBCC TRACESTATUS:
Kako bi zastavice praćenja bile uključene u automatsko pokretanje usluge MS SQL Server, trebate ići na SQL Server Configuration Manager i dodati ove oznake praćenja putem -T u svojstvima usluge:
Rezultati
U ovom članku analizirani su neki aspekti nadzora MS SQL Servera, uz pomoć kojih možete brzo identificirati nedostatak RAM-a i slobodnog CPU vremena, kao i niz drugih manje očitih problema. Pregledane su najčešće korištene oznake praćenja.
Izvori:
»
»
»
»
»
»
»
Izvor: www.habr.com