prefazione
Molto spesso, utenti, sviluppatori e amministratori del DBMS MS SQL Server incontrano problemi di prestazioni del database o del DBMS nel suo insieme, quindi il monitoraggio di MS SQL Server è molto rilevante.
Questo articolo è un'aggiunta all'articolo e coprirà alcuni aspetti del monitoraggio di MS SQL Server, in particolare: come determinare rapidamente quali risorse mancano, nonché raccomandazioni per l'impostazione dei flag di traccia.
Affinché i seguenti script funzionino, è necessario creare uno schema inf nel database desiderato come segue:
Creazione di uno schema inf
use <имя_БД>;
go
create schema inf;
Metodo per rilevare la mancanza di RAM
Il primo indicatore della mancanza di RAM è il caso in cui un'istanza di MS SQL Server consuma tutta la RAM ad essa assegnata.
Per fare ciò, creeremo la seguente rappresentazione di inf.vRAM:
Creazione della vista 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;
Quindi puoi determinare che un'istanza di MS SQL Server consuma tutta la memoria ad essa assegnata dalla seguente query:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Se SQL_server_physical_memory_in_use_Mb è costantemente maggiore o uguale a SQL_server_committed_target_Mb, è necessario controllare le statistiche di attesa.
Per determinare la mancanza di RAM attraverso le statistiche di attesa, creiamo la vista inf.vWaits:
Creazione della vista 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];
In questo caso, puoi determinare la mancanza di RAM con la seguente query:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Qui devi prestare attenzione agli indicatori Percentuale e AvgWait_S. Se sono significativi nella loro totalità, allora c'è un'alta probabilità che non ci sia RAM sufficiente per l'istanza di MS SQL Server. I valori significativi sono determinati individualmente per ciascun sistema. Tuttavia, puoi iniziare con quanto segue: Percentage>=1 e AvgWait_S>=0.005.
Per inviare gli indicatori a un sistema di monitoraggio (ad esempio, Zabbix), puoi creare le seguenti due query:
- quanti tipi di attesa sono occupati dalla RAM in percentuale (la somma di tutti questi tipi di attesa):
select coalesce(sum([Percentage]), 0.00) as [Percentage] from [inf].[vWaits] where [WaitType] in ( 'PAGEIOLATCH_XX', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE' ); - quanti tipi di attesa RAM impiegano in millisecondi (il valore massimo di tutti i ritardi medi per tutti questi tipi di attesa):
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' );
Sulla base della dinamica dei valori ottenuti per questi due indicatori, possiamo concludere se c'è abbastanza RAM per un'istanza di MS SQL Server.
Metodo di rilevamento del sovraccarico della CPU
Per identificare la mancanza di tempo del processore, è sufficiente utilizzare la vista di sistema sys.dm_os_schedulers. In questo caso, se runnable_tasks_count è costantemente maggiore di 1, è molto probabile che il numero di core non sia sufficiente per l'istanza di MS SQL Server.
Per inviare un indicatore a un sistema di monitoraggio (ad esempio, Zabbix), puoi creare la seguente query:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
Sulla base della dinamica dei valori ottenuti per questo indicatore, possiamo concludere se il tempo del processore (il numero di core della CPU) è sufficiente per un'istanza di MS SQL Server.
Tuttavia, è importante tenere presente il fatto che le richieste stesse possono richiedere più thread contemporaneamente. E a volte l'ottimizzatore non può stimare correttamente la complessità della query stessa. Quindi alla richiesta potrebbero essere assegnati troppi thread che non possono essere elaborati contemporaneamente in un dato momento. E questo provoca anche un tipo di attesa associato alla mancanza di tempo del processore e alla crescita della coda per gli scheduler che utilizzano core della CPU specifici, ovvero l'indicatore runnable_tasks_count crescerà in tali condizioni.
In questo caso, prima di aumentare il numero di core della CPU, è necessario configurare correttamente le proprietà di parallelismo dell'istanza MS SQL Server stessa e, dalla versione 2016, configurare correttamente le proprietà di parallelismo dei database richiesti:
Qui dovresti prestare attenzione ai seguenti parametri:
- Max Degree of Parallelism - imposta il numero massimo di thread che possono essere assegnati a ciascuna richiesta (il valore predefinito è 0 - limitato solo dal sistema operativo stesso e dall'edizione di MS SQL Server)
- Soglia di costo per il parallelismo: costo stimato del parallelismo (il valore predefinito è 5)
- Max DOP - imposta il numero massimo di thread che possono essere assegnati a ciascuna query a livello di database (ma non superiore al valore della proprietà "Max Degree of Parallelism") (il valore predefinito è 0 - limitato solo dal sistema operativo stesso e l'edizione di MS SQL Server, nonché la restrizione sulla proprietà "Max Degree of Parallelism" dell'intera istanza di MS SQL Server)
Qui è impossibile fornire una ricetta altrettanto buona per tutti i casi, ad es. è necessario analizzare query pesanti.
In base alla mia esperienza, raccomando il seguente algoritmo di azioni per i sistemi OLTP per l'impostazione delle proprietà di parallelismo:
- disabilitare prima il parallelismo impostando il grado massimo di parallelismo a livello di istanza su 1
- analizzare le richieste più pesanti e selezionare il numero ottimale di thread per loro
- impostare il Max Degree of Parallelism al numero ottimale selezionato di thread ottenuto dal passaggio 2 e per database specifici impostare il valore Max DOP ottenuto dal passaggio 2 per ciascun database
- analizzare le richieste più pesanti e identificare l'effetto negativo del multithreading. In tal caso, aumentare la soglia di costo per il parallelismo.
Per sistemi come 1C, Microsoft CRM e Microsoft NAV, nella maggior parte dei casi è opportuno vietare il multithreading
Inoltre, se esiste un'edizione Standard, nella maggior parte dei casi il divieto del multithreading è adatto a causa del fatto che questa edizione è limitata nel numero di core della CPU.
Per i sistemi OLAP, l'algoritmo sopra descritto non è adatto.
In base alla mia esperienza, raccomando il seguente algoritmo di azioni per i sistemi OLAP per l'impostazione delle proprietà di parallelismo:
- analizzare le richieste più pesanti e selezionare il numero ottimale di thread per loro
- impostare il Max Degree of Parallelism al numero ottimale selezionato di thread ottenuto dal passaggio 1 e per database specifici impostare il valore Max DOP ottenuto dal passaggio 1 per ciascun database
- analizzare le query più pesanti e identificare l'effetto negativo della limitazione della concorrenza. In tal caso, abbassare il valore Soglia di costo per il parallelismo o ripetere i passaggi 1-2 di questo algoritmo
Cioè, per i sistemi OLTP passiamo dal single-threading al multi-threading, e per i sistemi OLAP, al contrario, passiamo dal multi-threading al single-threading. Pertanto, è possibile scegliere le impostazioni di parallelismo ottimali sia per un database specifico che per l'intera istanza di MS SQL Server.
È inoltre importante comprendere che le impostazioni delle proprietà di parallelismo devono essere modificate nel tempo, in base ai risultati del monitoraggio delle prestazioni di MS SQL Server.
Linee guida per l'impostazione dei flag di traccia
Dalla mia esperienza e dall'esperienza dei miei colleghi, per prestazioni ottimali, consiglio di impostare i seguenti flag di traccia a livello di esecuzione del servizio MS SQL Server per le versioni 2008-2016:
- 610 - Registrazione ridotta degli inserti nelle tabelle indicizzate. Può aiutare con inserimenti in tabelle con molti record e molte transazioni, con frequenti lunghe attese di WRITELOG per modifiche agli indici
- 1117 - Se un file in un filegroup soddisfa i requisiti della soglia di crescita automatica, tutti i file nel filegroup aumentano
- 1118 - Forza la localizzazione di tutti gli oggetti in estensioni diverse (divieto di estensioni miste), che riduce al minimo la necessità di eseguire la scansione della pagina SGAM, che viene utilizzata per tenere traccia delle estensioni miste
- 1224 - Disabilita l'escalation dei blocchi in base al numero di blocchi. Tuttavia, un utilizzo eccessivo della memoria può attivare l'escalation dei blocchi
- 2371 - Modifica la soglia fissa di aggiornamento automatico delle statistiche nella soglia dinamica di aggiornamento automatico delle statistiche. Importante per l'aggiornamento dei piani di query per tabelle di grandi dimensioni, in cui un conteggio errato dei record determina piani di esecuzione errati
- 3226 - Sopprime i messaggi di successo del backup nel log degli errori
- 4199 - Include le modifiche a Query Optimizer rilasciate nelle CU e nei Service Pack di SQL Server
- 6532-6534 - Include miglioramenti delle prestazioni per le operazioni di query sui tipi di dati spaziali
- 8048 - Converte oggetti di memoria partizionati NUMA in oggetti partizionati CPU
- 8780: consente un'allocazione di tempo aggiuntiva per la pianificazione delle query. Alcune richieste senza questo flag potrebbero essere rifiutate perché non hanno un piano di query (bug molto raro)
- 8780 - 9389 - Abilita un ulteriore buffer di memoria di concessione dinamica per le istruzioni in modalità batch, che consente all'operatore in modalità batch di richiedere memoria aggiuntiva ed evitare di spostare i dati in tempdb se è disponibile memoria aggiuntiva
Anche prima del 2016, è utile abilitare il flag di traccia 2301, che consente ottimizzazioni avanzate del supporto decisionale e quindi aiuta nella scelta di piani di query più corretti. Tuttavia, a partire dalla versione 2016, ha spesso un effetto negativo sui tempi complessivi di esecuzione delle query piuttosto lunghi.
Inoltre, per i sistemi con molti indici (ad esempio, per i database 1C), consiglio di abilitare il flag di traccia 2330, che disabilita la raccolta dell'utilizzo dell'indice, che generalmente ha un effetto positivo sul sistema.
Per ulteriori informazioni sui flag di traccia, vedere
Dal collegamento sopra, è anche importante considerare le versioni e le build di MS SQL Server, poiché per le versioni più recenti, alcuni flag di traccia sono abilitati per impostazione predefinita o non hanno alcun effetto.
È possibile attivare e disattivare il flag di traccia rispettivamente con i comandi DBCC TRACEON e DBCC TRACEOFF. Per maggiori dettagli vedere
È possibile ottenere lo stato dei flag di traccia utilizzando il comando DBCC TRACESTATUS:
Affinché i flag di traccia vengano inclusi nell'avvio automatico del servizio MS SQL Server, è necessario accedere a Gestione configurazione SQL Server e aggiungere questi flag di traccia tramite -T nelle proprietà del servizio:
Risultati di
In questo articolo sono stati analizzati alcuni aspetti del monitoraggio di MS SQL Server, con l'aiuto del quale è possibile identificare rapidamente la mancanza di RAM e tempo libero della CPU, oltre a una serie di altri problemi meno evidenti. I flag di traccia più comunemente utilizzati sono stati esaminati.
Fonti:
»
»
»
»
»
»
»
Fonte: habr.com