Förord
Ganska ofta möter användare, utvecklare och administratörer av MS SQL Server DBMS prestandaproblem för databasen eller DBMS som helhet, så övervakning av MS SQL Server är mycket relevant.
Den här artikeln är ett tillägg till artikeln och den kommer att undersöka några aspekter av övervakning av MS SQL Server, särskilt: hur man snabbt kan avgöra vilka resurser som saknas, samt rekommendationer för att sätta upp spårningsflaggor.
För att följande skript ska fungera måste du skapa ett inf-schema i den önskade databasen enligt följande:
Skapar inf-schema
use <имя_БД>;
go
create schema inf;
Metod för att upptäcka brist på RAM
Den första indikatorn på brist på RAM är när en instans av MS SQL Server äter upp allt RAM som tilldelats den.
För att göra detta, skapa följande representation inf.vRAM:
Skapa inf.vRAM-vyn
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;
Sedan kan du fastställa att en instans av MS SQL Server förbrukar allt minne som tilldelats den med hjälp av följande fråga:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Om SQL_server_physical_memory_in_use_Mb-indikatorn ständigt inte är mindre än SQL_server_committed_target_Mb, måste du kontrollera väntestatistiken.
För att fastställa bristen på RAM genom väntestatistik, låt oss skapa en vy inf.vWaits:
Skapa vyn 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];
I det här fallet kan du fastställa bristen på RAM med hjälp av följande fråga:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Här måste du vara uppmärksam på indikatorerna Percentage och AvgWait_S. Om de är betydande i sin helhet, så är det mycket stor sannolikhet att MS SQL Server-instansen inte har tillräckligt med RAM. Väsentliga värden bestäms individuellt för varje system. Du kan dock börja med följande indikator: Procent>=1 och AvgWait_S>=0.005.
För att mata ut indikatorer till ett övervakningssystem (till exempel Zabbix), kan du skapa följande två frågor:
- Hur stor är procentandelen väntetyper för RAM (summa för alla sådana väntetyper):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - hur många RAM-väntetyper tar i millisekunder (det maximala värdet av alla genomsnittliga fördröjningar för alla sådana väntetyper):
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' );
Baserat på dynamiken i de erhållna värdena för dessa två indikatorer kan vi dra slutsatsen om det finns tillräckligt med RAM för MS SQL Server-instansen.
Metod för att upptäcka överdriven CPU-belastning
För att identifiera en brist på CPU-tid, använd bara systemvyn sys.dm_os_schedulers. Här, om runnable_tasks_count-indikatorn ständigt är större än 1, är det stor sannolikhet att antalet kärnor inte är tillräckligt för MS SQL Server-instansen.
För att visa en indikator i ett övervakningssystem (till exempel Zabbix), kan du skapa följande begäran:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Baserat på dynamiken i de erhållna värdena för denna indikator kan vi dra slutsatsen om det finns tillräckligt med processortid (antal CPU-kärnor) för MS SQL Server-instansen.
Det är dock viktigt att komma ihåg att frågorna själva kan fråga flera trådar samtidigt. Och ibland kan optimeraren inte korrekt uppskatta komplexiteten i själva frågan. Då kan begäran tilldelas för många trådar, som vid en given tidpunkt inte kan behandlas samtidigt. Och detta orsakar också en typ av väntan förknippad med brist på processortid, och ökningen av kön för schemaläggare som använder specifika CPU-kärnor, det vill säga runnable_tasks_count-indikatorn kommer att öka under sådana förhållanden.
I det här fallet, innan du ökar antalet CPU-kärnor, måste du korrekt konfigurera parallellitetsegenskaperna för själva MS SQL Server-instansen, och från version 2016, korrekt konfigurera parallellitetsegenskaperna för de önskade databaserna:
Här bör du vara uppmärksam på följande parametrar:
- Max Degree of Parallelism – ställer in det maximala antalet trådar som kan allokeras till varje begäran (standard är 0 – begränsas endast av själva operativsystemet och utgåvan av MS SQL Server)
- Kostnadströskel för parallellism - uppskattad kostnad för parallellism (standard är 5)
- Max DOP – ställer in det maximala antalet trådar som kan allokeras till varje fråga på databasnivå (men inte mer än värdet för egenskapen “Max Degree of Parallelism”) (som standard är det 0 – begränsat endast av operativsystemet sig själv och utgåvan av MS SQL Server, samt en begränsning av egenskapen "Max Degree of Parallelism" för hela MS SQL Server-instansen)
Det är omöjligt att ge ett lika bra recept för alla fall, det vill säga du behöver analysera svåra frågor.
Baserat på min egen erfarenhet rekommenderar jag följande algoritm för åtgärder för OLTP-system för att konfigurera parallellitetsegenskaper:
- först inaktivera parallellism genom att ställa in Max Degree of Parallelism till 1 på nivån för hela instansen
- analysera de tyngsta frågorna och välj det optimala antalet trådar för dem
- ställ in Max Degree of Parallelism till det valda optimala antalet trådar som erhållits från steg 2, och även för specifika databaser ställ in Max DOP-värdet som erhålls från steg 2 för varje databas
- analysera de tyngsta frågorna och identifiera den negativa effekten av multithreading. Om så är fallet, höj sedan kostnadströskeln för parallellism.
För system som 1C, Microsoft CRM och Microsoft NAV är det i de flesta fall lämpligt att förbjuda multithreading
Dessutom, om du har Standard-utgåvan, är i de flesta fall ett förbud mot multi-threading lämpligt på grund av det faktum att denna utgåva är begränsad i antalet CPU-kärnor.
Algoritmen som beskrivs ovan är inte lämplig för OLAP-system.
Baserat på min egen erfarenhet rekommenderar jag följande algoritm för åtgärder för OLAP-system för att konfigurera parallellitetsegenskaper:
- analysera de tyngsta frågorna och välj det optimala antalet trådar för dem
- ställ in Max Degree of Parallelism till det valda optimala antalet trådar som erhållits från steg 1, och även för specifika databaser ställ in Max DOP-värdet som erhålls från steg 1 för varje databas
- analysera de tyngsta frågorna och identifiera den negativa effekten av att begränsa samtidighet. Om det är det, sänk antingen kostnadströskeln för parallellism eller upprepa steg 1-2 i denna algoritm
Det vill säga, för OLTP-system går vi från enkeltrådning till flertrådig, och för OLAP-system går vi tvärtom från flertrådig till enkeltrådig. På så sätt kan du välja de optimala parallellitetsinställningarna både för en specifik databas och för hela MS SQL Server-instansen.
Det är också viktigt att förstå att inställningarna för samtidighetsegenskaper måste ändras över tiden, baserat på resultaten av övervakningen av MS SQL Servers prestanda.
Rekommendationer för att sätta spårflaggor
Av min egen erfarenhet och mina kollegors erfarenhet, för optimal prestanda, rekommenderar jag att du ställer in följande spårningsflaggor på MS SQL Server-tjänstens körningsnivå för 2008-2016 versioner:
- 610 - Minska loggning av inlägg i indexerade tabeller. Kan hjälpa till med insättningar i tabeller med ett stort antal poster och många transaktioner, med frekventa långa WRITELOG-väntningar på ändringar i index
- 1117 - Om en fil i en filgrupp når tröskeln för automatisk tillväxt, växer alla filer i filgruppen
- 1118 - Tvingar alla objekt att lokaliseras i olika omfattningar (tillåter inte blandade omfattningar), vilket minimerar behovet av att skanna SGAM-sidan, som används för att spåra blandade omfattningar
- 1224 - Inaktiverar låseskalering baserat på låsantal. Däremot kan överdriven minnesanvändning möjliggöra låseskalering
- 2371 - Ändrar den fasta tröskeln för automatisk statistikuppdatering till den dynamiska tröskeln för automatisk statistikuppdatering. Viktigt för uppdatering av frågeplaner på stora tabeller där felaktig definiering av antalet poster resulterar i felaktiga exekveringsplaner
- 3226 - Undertrycker framgångsmeddelanden för säkerhetskopiering i felloggen
- 4199 - Inkluderar ändringar av frågeoptimeraren som släppts i SQL Server-uppdateringssamlingar och service pack
- 6532-6534 - Innehåller prestandaförbättringar för frågor med rumsliga datatyper
- 8048 - Konverterar NUMA-partitionerade minnesobjekt till CPU-partitionerade
- 8780 - Möjliggör ytterligare tidsallokering för frågeplanering. Vissa förfrågningar utan denna flagga kan avvisas eftersom de inte har en frågeplan (ett mycket sällsynt fel)
- 8780 - 9389 - Aktiverar en extra dynamisk temporär minnesbuffert för batchlägesoperatörer, vilket gör att batchlägesoperatören kan begära ytterligare minne och undvika att överföra data till tempdb om ytterligare minne finns tillgängligt
Det är också användbart att aktivera spårningsflagga 2016 före version 2301, vilket möjliggör avancerad optimering av beslutsstöd och därmed hjälper till att välja bättre frågeplaner. Men sedan version 2016 har det ofta en negativ effekt på ganska långa totala frågekörningstider.
Dessutom, för system med många index (till exempel för 1C-databaser), rekommenderar jag att du aktiverar spårningsflagga 2330, vilket inaktiverar insamling av indexanvändning, vilket generellt har en positiv effekt på systemet.
Du kan lära dig mer om spårningsflaggor
Från länken ovan är det också viktigt att ta hänsyn till versionerna och versionerna av MS SQL Server, eftersom för nyare versioner är vissa spårningsflaggor aktiverade som standard eller har ingen effekt.
Du kan aktivera eller inaktivera spårningsflaggan med kommandona DBCC TRACEON respektive DBCC TRACEOFF. Se mer information
Du kan få status för spårningsflaggor med kommandot DBCC TRACESTATUS:
För att spårningsflaggor ska inkluderas i autostarten av MS SQL Server-tjänsten måste du gå till SQL Server Configuration Manager och lägga till dessa spårningsflaggor via -T i tjänstens egenskaper:
Resultat av
Den här artikeln undersökte några aspekter av övervakning av MS SQL Server, med hjälp av vilken du snabbt kan identifiera brist på RAM och ledig CPU-tid, samt ett antal andra mindre uppenbara problem. De mest använda spårningsflaggorna granskades.
Källor:
»
»
»
»
»
»
»
Källa: will.com