Predgovor
Često se korisnici, programeri i administratori MS SQL Server DBMS susreću s problemima performansi baze podataka ili DBMS-a u cjelini, pa je praćenje MS SQL Servera vrlo relevantno.
Ovaj članak je dodatak članku i raspravljaće se o nekim aspektima nadgledanja MS SQL Servera, posebno: kako brzo odrediti koji resursi nedostaju, kao i o preporukama za postavljanje oznaka praćenja.
Da bi sljedeće skripte funkcionirale, morate kreirati inf shemu u željenoj bazi podataka na sljedeći način:
Kreiranje inf šeme
use <имя_БД>;
go
create schema inf;
Metoda za otkrivanje nedostatka RAM-a
Prvi pokazatelj nedostatka RAM-a je slučaj kada instanca MS SQL Servera pojede svu RAM memoriju koja joj je dodijeljena.
Da bismo to uradili, kreiraćemo sledeću reprezentaciju inf.vRAM-a:
Kreiranje inf.vRAM prikaza
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 odrediti da instanca MS SQL Servera troši svu memoriju koja joj je dodijeljena sljedećim upitom:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Ako je SQL_server_physical_memory_in_use_Mb konstantno veći ili jednak SQL_server_committed_target_Mb, tada treba provjeriti statistiku čekanja.
Da odredimo nedostatak RAM-a kroz statistiku čekanja, napravimo prikaz inf.vWaits:
Kreiranje prikaza 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];
U ovom slučaju možete utvrditi 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 pažnju na indikatore Postotak i AvgWait_S. Ako su značajni u svojoj ukupnosti, onda postoji vrlo velika vjerovatnoća da nema dovoljno RAM-a za instancu MS SQL Servera. Značajne vrijednosti se određuju pojedinačno za svaki sistem. Međutim, možete početi sa sljedećim: Percentage>=1 i AvgWait_S>=0.005.
Za izlaz indikatora u sistem za nadzor (na primjer, Zabbix), možete kreirati sljedeća dva upita:
- koliko vrsta čekanja zauzima RAM u procentima (zbir svih takvih vrsta čekanja):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - koliko tipova čekanja RAM-a 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 osnovu dinamike dobijenih vrijednosti za ova dva indikatora možemo zaključiti da li ima dovoljno RAM-a za primjerak MS SQL Servera.
Metoda detekcije preopterećenja procesora
Da biste identifikovali nedostatak procesorskog vremena, dovoljno je koristiti sistemski prikaz sys.dm_os_schedulers. Ovdje, ako je runnable_tasks_count konstantno veći od 1, tada postoji velika vjerovatnoća da broj jezgara nije dovoljan za instancu MS SQL Servera.
Za izlaz indikatora na sistem za nadzor (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 osnovu dinamike dobijenih vrijednosti za ovaj indikator možemo zaključiti da li postoji dovoljno procesorskog vremena (broj CPU jezgri) za primjerak MS SQL Servera.
Međutim, važno je imati na umu činjenicu da sami zahtjevi mogu zahtijevati 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 ne mogu obraditi u isto vrijeme u datom trenutku. I to također uzrokuje tip čekanja povezan s nedostatkom procesorskog vremena i rast reda za planere koji koriste specifične CPU jezgre, tj. indikator runnable_tasks_count će rasti u takvim uvjetima.
U ovom slučaju, prije povećanja broja CPU jezgara, potrebno je ispravno konfigurirati svojstva paralelizma same instance MS SQL Servera, a od verzije 2016. ispravno konfigurirati svojstva paralelizma potrebnih baza podataka:
Ovdje treba obratiti pažnju na sljedeće parametre:
- Max Degree of Parallelism - postavlja maksimalan broj niti koje se mogu dodijeliti svakom zahtjevu (podrazumevano je 0 - ograničeno samo samim operativnim sistemom i izdanjem MS SQL Servera)
- Prag troškova za paralelizam - procijenjeni trošak paralelizma (podrazumevano je 5)
- Max DOP - postavlja maksimalni broj niti koje se mogu dodijeliti svakom upitu na nivou baze podataka (ali ne više od vrijednosti svojstva "Max Degree of Parallelism") (podrazumevano je 0 - ograničeno samo samim operativnim sistemom i izdanje MS SQL Servera, kao i ograničenje na svojstvo "Maksimalni stepen paralelizma" cijele instance MS SQL Servera)
Ovdje je nemoguće dati jednako dobar recept za sve slučajeve, tj. morate analizirati teške upite.
Iz vlastitog iskustva preporučujem sljedeći algoritam radnji za OLTP sisteme za postavljanje svojstava paralelizma:
- prvo onemogućite paralelizam postavljanjem maksimalnog stepena paralelizma za cijelu instancu na 1
- analizirajte najteže zahtjeve i odaberite optimalan broj niti za njih
- postavite maksimalni stupanj paralelizma na odabrani optimalni broj niti dobivenih iz koraka 2, a za određene baze podataka postavite maksimalnu vrijednost DOP-a dobivenu iz koraka 2 za svaku bazu podataka
- analizirajte najteže zahtjeve i identificirajte negativne efekte višenitnog rada. Ako jeste, onda povećajte prag troškova za paralelizam.
Za sisteme kao što su 1C, Microsoft CRM i Microsoft NAV, u većini slučajeva prikladna je zabrana višenitnog rada
Također, ako postoji Standardno izdanje, tada je u većini slučajeva prikladna zabrana višenitnog rada zbog činjenice da je ovo izdanje ograničeno u broju CPU jezgara.
Za OLAP sisteme, gore opisani algoritam nije prikladan.
Iz vlastitog iskustva preporučujem sljedeći algoritam radnji za OLAP sisteme za postavljanje svojstava paralelizma:
- analizirajte najteže zahtjeve i odaberite optimalan broj niti za njih
- postavite maksimalni stupanj paralelizma na odabrani optimalni broj niti dobivenih iz koraka 1, a za određene baze podataka postavite maksimalnu vrijednost DOP-a dobivenu iz koraka 1 za svaku bazu podataka
- analizirajte najteže upite i identificirajte negativan učinak ograničavanja istodobnosti. Ako jeste, onda ili snizite prag troškova za vrijednost paralelizma ili ponovite korake 1-2 ovog algoritma
Odnosno, za OLTP sisteme idemo sa jednonitnog na višenitno, a za OLAP-sisteme, naprotiv, idemo sa višenitnog na jednonitno. Dakle, možete odabrati optimalne postavke paralelizma i za određenu bazu podataka i za cijelu instancu MS SQL Servera.
Takođe je važno shvatiti da se postavke svojstava paralelizma moraju mijenjati tokom vremena, na osnovu rezultata praćenja performansi MS SQL Servera.
Smjernice za postavljanje oznaka za praćenje
Iz vlastitog iskustva i iskustva mojih kolega, za optimalne performanse, preporučujem postavljanje sljedećih oznaka praćenja na nivou pokretanja usluge MS SQL Server za verzije 2008-2016:
- 610 - Smanjeno evidentiranje umetanja u indeksirane tabele. Može pomoći kod umetanja u tabele sa mnogo zapisa i mnogo transakcija, sa čestim dugim WRITELOG čekanjem na promene u indeksima
- 1117 - Ako datoteka u grupi datoteka zadovoljava zahtjeve praga automatskog rasta, sve datoteke u grupi datoteka rastu
- 1118 - Prisiljava sve objekte da budu locirani u različitim ekstentima (zabrana mješovitih ekstenata), što minimizira potrebu za skeniranjem SGAM stranice, koja se koristi za praćenje mješovitih ekstenta
- 1224 - Onemogućuje eskalaciju zaključavanja na osnovu broja zaključavanja. Međutim, prekomjerna upotreba memorije može pokrenuti eskalaciju zaključavanja
- 2371 - Mijenja fiksni prag automatskog ažuriranja statistike u prag za dinamičko automatsko ažuriranje statistike. Važno za ažuriranje planova upita za velike tabele, gde netačan broj zapisa dovodi do pogrešnih planova izvršenja
- 3226 - Suzbija poruke o uspjehu sigurnosne kopije u dnevniku grešaka
- 4199 - Uključuje promjene u optimizatoru upita objavljenom u CU i servisnim paketima SQL Servera
- 6532-6534 - Uključuje poboljšanja performansi za operacije upita na tipovima prostornih podataka
- 8048 - Konvertuje NUMA-particionirane memorijske objekte u CPU-particionirane
- 8780 - Omogućuje dodatnu dodjelu vremena za planiranje upita. Neki zahtjevi bez ove zastavice mogu biti odbijeni jer nemaju plan upita (vrlo rijetka greška)
- 8780 - 9389 - Omogućuje dodatni dinamički memorijski međuspremnik za naredbe batch moda, što omogućava operateru batch moda da zatraži više memorije i izbjegne premještanje podataka u tempdb ako je više memorije dostupno
Također prije 2016. godine, korisno je omogućiti oznaku praćenja 2301, koja omogućava 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 dugo ukupno vrijeme izvršavanja upita.
Takođe, za sisteme sa mnogo indeksa (na primer, za 1C baze podataka), preporučujem da omogućite zastavicu praćenja 2330, koja onemogućava prikupljanje korišćenja indeksa, što generalno ima pozitivan efekat na sistem.
Za više informacija o oznakama praćenja, pogledajte
Iz gornje veze, također je važno uzeti u obzir verzije i verzije MS SQL Servera, jer za novije verzije, neke oznake praćenja su omogućene po defaultu ili nemaju efekta.
Možete uključiti i isključiti oznaku praćenja pomoću naredbi DBCC TRACEON i DBCC TRACEOFF. Za više detalja pogledajte
Status zastavica praćenja možete dobiti pomoću naredbe DBCC TRACESTATUS:
Da bi oznake praćenja bile uključene u automatsko pokretanje usluge MS SQL Server, morate otići na SQL Server Configuration Manager i dodati ove oznake praćenja putem -T u svojstva usluge:
Ishodi
U ovom članku analizirani su neki aspekti praćenja MS SQL Servera, uz pomoć kojih možete brzo prepoznati nedostatak RAM-a i slobodnog CPU vremena, kao i niz drugih manje očiglednih problema. Pregledane su najčešće korištene oznake praćenja.
Izvori:
»
»
»
»
»
»
»
izvor: www.habr.com