Forord
Ganske ofte støder brugere, udviklere og administratorer af MS SQL Server DBMS på ydeevneproblemer i databasen eller DBMS som helhed, så MS SQL Server-overvågning er meget relevant.
Denne artikel er en tilføjelse til artiklen og det vil dække nogle aspekter af overvågning af MS SQL Server, især: hvordan man hurtigt kan bestemme, hvilke ressourcer der mangler, samt anbefalinger til indstilling af sporingsflag.
For at følgende scripts skal fungere, skal du oprette et inf-skema i den ønskede database som følger:
Oprettelse af et inf-skema
use <имя_БД>;
go
create schema inf;
Metode til at opdage mangel på RAM
Den første indikator for manglen på RAM er tilfældet, når en forekomst af MS SQL Server spiser al den RAM, der er allokeret til den.
For at gøre dette vil vi oprette følgende repræsentation af inf.vRAM:
Oprettelse af inf.vRAM-visningen
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;
Derefter kan du bestemme, at en forekomst af MS SQL Server bruger al den hukommelse, der er allokeret til den, ved følgende forespørgsel:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Hvis SQL_server_physical_memory_in_use_Mb konsekvent er større end eller lig med SQL_server_committed_target_Mb, skal ventestatistikken kontrolleres.
For at bestemme manglen på RAM gennem ventestatistikker, lad os oprette visningen inf.vWaits:
Oprettelse af inf.vWaits-visningen
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];
I dette tilfælde kan du bestemme manglen på RAM med følgende forespørgsel:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Her skal du være opmærksom på indikatorerne Procent og AvgWait_S. Hvis de er signifikante i deres helhed, så er der meget stor sandsynlighed for, at der ikke er nok RAM til MS SQL Server-instansen. Væsentlige værdier bestemmes individuelt for hvert system. Du kan dog starte med følgende: Procent>=1 og AvgWait_S>=0.005.
For at udlæse indikatorer til et overvågningssystem (for eksempel Zabbix), kan du oprette følgende to forespørgsler:
- hvor mange typer ventetider er optaget af RAM i procent (summen af alle sådanne typer ventetider):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - hvor mange RAM-ventetyper tager i millisekunder (den maksimale værdi af alle gennemsnitlige forsinkelser for alle sådanne ventetyper):
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' );
Baseret på dynamikken i de opnåede værdier for disse to indikatorer kan vi konkludere, om der er nok RAM til en forekomst af MS SQL Server.
Metode til registrering af CPU-overbelastning
For at identificere manglen på processortid er det nok at bruge systemvisningen sys.dm_os_schedulers. Her, hvis runnable_tasks_count konstant er større end 1, så er der stor sandsynlighed for, at antallet af kerner ikke er nok til MS SQL Server-instansen.
For at sende en indikator til et overvågningssystem (for eksempel Zabbix), kan du oprette følgende forespørgsel:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Baseret på dynamikken i de opnåede værdier for denne indikator kan vi konkludere, om der er nok processortid (antallet af CPU-kerner) til en forekomst af MS SQL Server.
Det er dog vigtigt at huske på, at anmodninger i sig selv kan anmode om flere tråde på én gang. Og nogle gange kan optimeringsværktøjet ikke korrekt vurdere kompleksiteten af selve forespørgslen. Så kan anmodningen blive tildelt for mange tråde, som ikke kan behandles på samme tid på det givne tidspunkt. Og dette forårsager også en type ventetid forbundet med mangel på processortid og vækst i køen for planlæggere, der bruger specifikke CPU-kerner, dvs. runnable_tasks_count-indikatoren vil vokse under sådanne forhold.
I dette tilfælde, før antallet af CPU-kerner øges, er det nødvendigt at konfigurere parallelitetsegenskaberne for selve MS SQL Server-forekomsten korrekt og fra 2016-versionen korrekt konfigurere parallelitetsegenskaberne for de nødvendige databaser:
Her skal du være opmærksom på følgende parametre:
- Max Degree of Parallelism - indstiller det maksimale antal tråde, der kan allokeres til hver anmodning (standarden er 0 - begrænset kun af selve operativsystemet og udgaven af MS SQL Server)
- Omkostningstærskel for parallelisme - estimerede omkostninger ved parallelisme (standard er 5)
- Max DOP - indstiller det maksimale antal tråde, der kan allokeres til hver forespørgsel på databaseniveau (men ikke mere end værdien af egenskaben "Max Degree of Parallelism") (standard er 0 - kun begrænset af selve operativsystemet og udgaven af MS SQL Server, såvel som begrænsningen af egenskaben "Max Degree of Parallelism" for hele forekomsten af MS SQL Server)
Her er det umuligt at give en lige god opskrift for alle sager, dvs. du skal analysere tunge forespørgsler.
Fra min egen erfaring anbefaler jeg følgende handlingsalgoritme for OLTP-systemer til opsætning af parallelitetsegenskaber:
- deaktiver først parallelisme ved at indstille den instansomfattende Max Degree of Parallelism til 1
- analysere de tungeste anmodninger og vælge det optimale antal tråde til dem
- indstil Max Degree of Parallelism til det valgte optimale antal tråde opnået fra trin 2, og for specifikke databaser indstil Max DOP-værdien opnået fra trin 2 for hver database
- analysere de tungeste anmodninger og identificere den negative effekt af multithreading. Hvis det er, så øg omkostningstærsklen for parallelisme.
For systemer som 1C, Microsoft CRM og Microsoft NAV er det i de fleste tilfælde egnet til at forbyde multithreading
Også, hvis der er en standardudgave, er forbuddet mod multithreading i de fleste tilfælde egnet på grund af det faktum, at denne udgave er begrænset i antallet af CPU-kerner.
Til OLAP-systemer er den ovenfor beskrevne algoritme ikke egnet.
Fra min egen erfaring anbefaler jeg følgende handlingsalgoritme for OLAP-systemer til opsætning af parallelitetsegenskaber:
- analysere de tungeste anmodninger og vælge det optimale antal tråde til dem
- indstil Max Degree of Parallelism til det valgte optimale antal tråde opnået fra trin 1, og for specifikke databaser indstil Max DOP-værdien opnået fra trin 1 for hver database
- analysere de tungeste forespørgsler og identificere den negative effekt af at begrænse samtidighed. Hvis det er det, skal du enten sænke omkostningstærsklen for parallelisme eller gentage trin 1-2 i denne algoritme
Det vil sige, at vi for OLTP-systemer går fra single-threading til multi-threading, og for OLAP-systemer går vi tværtimod fra multi-threading til single-threading. Du kan således vælge de optimale parallelitetsindstillinger for både en specifik database og hele instansen af MS SQL Server.
Det er også vigtigt at forstå, at indstillingerne for parallelitetsegenskaberne skal ændres over tid, baseret på resultaterne af overvågningen af MS SQL Servers ydeevne.
Retningslinjer for indstilling af sporingsflag
Ud fra min egen erfaring og mine kollegers erfaring anbefaler jeg for optimal ydeevne at indstille følgende sporingsflag på kørselsniveauet for MS SQL Server-tjenesten for version 2008-2016:
- 610 - Reduceret logning af indstik i indekserede tabeller. Kan hjælpe med indsættelser i tabeller med mange poster og mange transaktioner, med hyppige lange WRITELOG-venter på ændringer i indekser
- 1117 - Hvis en fil i en filgruppe opfylder kravene til autovæksttærskel, vokser alle filer i filgruppen
- 1118 - Tvinger alle objekter til at blive lokaliseret i forskellige udstrækninger (forbud mod blandet omfang), hvilket minimerer behovet for at scanne SGAM-siden, som bruges til at spore blandet omfang
- 1224 - Deaktiverer låseeskalering baseret på antallet af låse. Overdreven hukommelsesbrug kan dog udløse låseeskalering
- 2371 - Ændrer den faste tærskel for automatisk statistikopdatering til den dynamiske tærskel for automatisk statistikopdatering. Vigtigt til opdatering af forespørgselsplaner for store tabeller, hvor en forkert optælling af poster resulterer i fejlagtige eksekveringsplaner
- 3226 - Undertrykker sikkerhedskopieringssuccesmeddelelser i fejlloggen
- 4199 - Inkluderer ændringer til forespørgselsoptimering frigivet i CU'er og SQL Server Service Packs
- 6532-6534 - Indeholder ydeevneforbedringer for forespørgselsoperationer på geografiske datatyper
- 8048 - Konverterer NUMA-partitionerede hukommelsesobjekter til CPU-partitionerede
- 8780 - Muliggør yderligere tidsallokering til planlægning af forespørgsler. Nogle anmodninger uden dette flag kan blive afvist, fordi de ikke har en forespørgselsplan (meget sjælden fejl)
- 8780 - 9389 - Aktiverer yderligere dynamisk tildelingshukommelsesbuffer til batch-tilstandsudsagn, som gør det muligt for batch-tilstandsoperatøren at anmode om mere hukommelse og undgå at flytte data til tempdb, hvis mere hukommelse er tilgængelig
Også før 2016 er det nyttigt at aktivere sporingsflag 2301, som muliggør forbedrede beslutningsstøtteoptimeringer og dermed hjælper med at vælge mere korrekte forespørgselsplaner. Fra version 2016 har det dog ofte en negativ effekt på ret lange overordnede forespørgselsudførelsestider.
For systemer med mange indekser (for eksempel til 1C-databaser) anbefaler jeg også at aktivere sporingsflag 2330, som deaktiverer indsamling af indeksbrug, hvilket generelt har en positiv effekt på systemet.
For mere information om sporingsflag, se
Fra linket ovenfor er det også vigtigt at overveje versioner og builds af MS SQL Server, da for nyere versioner er nogle sporingsflag aktiveret som standard eller har ingen effekt.
Du kan slå sporingsflaget til og fra med henholdsvis DBCC TRACEON og DBCC TRACEOFF kommandoerne. For flere detaljer se
Du kan få status for sporingsflagene ved hjælp af kommandoen DBCC TRACESTATUS:
For at sporingsflag kan inkluderes i autostarten af MS SQL Server-tjenesten, skal du gå til SQL Server Configuration Manager og tilføje disse sporingsflag via -T i tjenesteegenskaberne:
Resultaterne af
I denne artikel blev nogle aspekter af overvågning af MS SQL Server analyseret, ved hjælp af hvilke du hurtigt kan identificere manglen på RAM og ledig CPU-tid, samt en række andre mindre åbenlyse problemer. De mest brugte sporflag er blevet gennemgået.
Kilder:
»
»
»
»
»
»
»
Kilde: www.habr.com