Introduzione
Molto spesso, utenti, sviluppatori e amministratori di database MS SQL Server si trovano ad affrontare problemi di prestazioni del database o del sistema di gestione del database in generale; pertanto, il monitoraggio di MS SQL Server è molto rilevante.
Questo articolo è un complemento all'articolo e tratterà alcuni aspetti del monitoraggio di MS SQL Server, in particolare: come identificare rapidamente quali risorse mancano, oltre a raccomandazioni per la configurazione delle flag di tracciamento.
Per eseguire gli script seguenti, è necessario creare lo schema inf nel database richiesto come segue:
Creazione dello schema inf
use ;
go
create schema inf;
Metodo per identificare la mancanza di memoria RAM
Il primo indicatore di una carenza di memoria RAM è quando l'istanza di MS SQL Server utilizza tutta la memoria RAM a essa assegnata.
A tal fine, creiamo la seguente vista inf.vRAM:
Creazione della vista inf.vRAM
CREA vista [inf].[vRAM] come
select a.[TotalAvailOSRam_Mb] -- quanto RAM è disponibile sul server in MB
, a.[RAM_Avail_Percent] -- percentuale di RAM disponibile sul server
, a.[Server_physical_memory_Mb] -- totale di RAM sul server in MB
, a.[SQL_server_committed_target_Mb] -- totale di RAM allocato per MS SQL Server in MB
, a.[SQL_server_physical_memory_in_use_Mb] -- totale di RAM utilizzato da MS SQL Server in questo momento in MB
, a.[SQL_RAM_Avail_Percent] -- percentuale di RAM disponibile per MS SQL Server rispetto a tutta la RAM allocata per MS SQL Server
, a.[StateMemorySQL] -- è sufficiente la RAM per MS SQL Server
, a.[SQL_RAM_Reserve_Percent] -- percentuale di RAM riservata per MS SQL Server rispetto a tutta la RAM del server
-- è sufficiente la RAM per il server
, (case when a.[RAM_Avail_Percent]5 and a.[TotalAvailOSRam_Mb]<8192 then 'Attenzione' when a.[RAM_Avail_Percent]<=5 and a.[TotalAvailOSRam_Mb]<2048 then 'Pericolo' else 'Normale' 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 'Attenzione' else 'Normale' 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, per determinare che l'istanza MS SQL Server utilizza tutta la memoria a lui assegnata, si può utilizzare la seguente query:
select SQL_server_physical_memory_in_use_Mb, SQL_server_committed_target_Mb
from [inf].[vRAM];
Se il valore di SQL_server_physical_memory_in_use_Mb è costantemente minore di SQL_server_committed_target_Mb, è necessario controllare le statistiche di attesa.
Per determinare la carenza di memoria tramite le statistiche di attesa, creiamo la vista inf.vWaits:
Creazione della vista inf.vWaits
CREA vista [inf].[vWaits] come
CON [Waits] COME
(SELEZIONA
[wait_type], --nome del tipo di attesa
[wait_time_ms] / 1000.0 COME [WaitS],--Tempo totale di attesa di questo tipo in millisecondi. Questo tempo include signal_wait_time_ms
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 COME [ResourceS],--Tempo totale di attesa di questo tipo in millisecondi senza signal_wait_time_ms
[signal_wait_time_ms] / 1000.0 COME [SignalS],--Differenza tra il tempo di segnalazione del thread in attesa e il momento in cui inizia la sua esecuzione
[waiting_tasks_count] COME [WaitCount],--Numero di attese di questo tipo. Questo contatore aumenta ogni volta che inizia un'attesa
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() COME [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) COME [RowNum]
DA sys.dm_os_wait_stats
DOVE [waiting_tasks_count]>0
E [wait_type] NON 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 come (
SELEZIONA
[W1].[wait_type] COME [WaitType],
CAST ([W1].[WaitS] COMO DECIMAL (16, 2)) COME [Wait_S],--Tempo totale di attesa di questo tipo in millisecondi. Questo tempo include signal_wait_time_ms
CAST ([W1].[ResourceS] COMO DECIMAL (16, 2)) COME [Resource_S],--Tempo totale di attesa di questo tipo in millisecondi senza signal_wait_time_ms
CAST ([W1].[SignalS] COMO DECIMAL (16, 2)) COME [Signal_S],--Differenza tra il tempo di segnalazione del thread in attesa e il momento in cui inizia la sua esecuzione
[W1].[WaitCount] COME [WaitCount],--Numero di attese di questo tipo. Questo contatore aumenta ogni volta che inizia un'attesa
CAST ([W1].[Percentage] COME DECIMAL (5, 2)) COME [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) COME DECIMAL (16, 4)) COME [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) COME DECIMAL (16, 4)) COME [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) COME DECIMAL (16, 4)) COME [AvgSig_S]
DA [Waits] COME [W1]
INNER JOIN [Waits] COME [W2]
SU [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 -- soglia percentuale
)
SELEZIONA [WaitType]
,MAX([Wait_S]) come [Wait_S]
,MAX([Resource_S]) come [Resource_S]
,MAX([Signal_S]) come [Signal_S]
,MAX([WaitCount]) come [WaitCount]
,MAX([Percentage]) come [Percentage]
,MAX([AvgWait_S]) come [AvgWait_S]
,MAX([AvgRes_S]) come [AvgRes_S]
,MAX([AvgSig_S]) come [AvgSig_S]
DA ress
raggruppa per [WaitType];
In questo caso, è possibile determinare la mancanza di memoria operativa con la seguente query:
SELECT [Percentage]
,[AvgWait_S]
FROM [inf].[vWaits]
where [WaitType] in (
'PAGEIOLATCH_XX',
'RESOURCE_SEMAPHORE',
'RESOURCE_SEMAPHORE_QUERY_COMPILE'
);
Qui è necessario prestare attenzione ai valori di Percentage e AvgWait_S. Se sono significativi nel loro insieme, c'è un'alta probabilità che il server MS SQL stia riscontrando una mancanza di memoria operativa. I valori significativi vengono definiti individualmente per ogni sistema. Tuttavia, si può iniziare con il seguente indicatore: Percentage>=1 e AvgWait_S>=0.005.
Per visualizzare gli indicatori nel sistema di monitoraggio (ad esempio, Zabbix), è possibile creare le seguenti due query:
- quanto percentualmente occupano i tipi di attesa per la RAM (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' ); - quanto in millisecondi occupano i tipi di attesa per la RAM (valore massimo di tutti i tempi di attesa 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' );
In base alla dinamica dei valori ottenuti per questi due indicatori, è possibile concludere se c'è sufficiente RAM per il server MS SQL.
Metodo per identificare un carico eccessivo sulla CPU
Per rilevare la mancanza di tempo della CPU, è sufficiente utilizzare la vista di sistema sys.dm_os_schedulers. Qui, se il valore di runnable_tasks_count è costantemente superiore a 1, c'è una grande possibilità che il numero di core non sia sufficiente per il server MS SQL.
Per visualizzare l'indicatore nel sistema di monitoraggio (ad esempio, Zabbix), è possibile creare la seguente query:
select max([runnable_tasks_count]) as [runnable_tasks_count]
from sys.dm_os_schedulers
where scheduler_id<255;
In base alla dinamica dei valori ottenuti per questo indicatore, è possibile concludere se c'è sufficiente tempo della CPU (numero di core) per il server MS SQL.
Tuttavia, è importante ricordare che le query stesse possono richiedere più flussi contemporaneamente. E a volte l'ottimizzatore non riesce a valutare correttamente la complessità della query stessa. In quel caso, possono essere assegnati troppi flussi alla query, che in quel momento non possono essere elaborati simultaneamente. Questo causa anche un tipo di attesa legato alla carenza di tempo CPU e all'aumento della coda sui pianificatori che utilizzano specifici core della CPU, quindi l'indicatore runnable_tasks_count in tali condizioni aumenterà.
In tal caso, prima di aumentare il numero di core della CPU, è necessario configurare correttamente le proprietà di parallelismo dell'istanza MS SQL Server, e dalla versione 2016, configurare correttamente le proprietà di parallelismo dei database necessari:
Qui è importante prestare attenzione ai seguenti parametri:
- Max Degree of Parallelism: imposta il numero massimo di flussi che possono essere assegnati a ciascuna query (per impostazione predefinita è 0, limitato solo dal sistema operativo e dalla versione di MS SQL Server)
- Cost Threshold for Parallelism: costo stimato per il parallelismo (per impostazione predefinita è 5)
- Max DOP: imposta il numero massimo di flussi che possono essere assegnati a ciascuna query a livello di database (ma non superiore al valore della proprietà 'Max Degree of Parallelism') (per impostazione predefinita è 0, limitato solo dal sistema operativo e dalla versione di MS SQL Server, oltre al limite impostato dalla proprietà 'Max Degree of Parallelism' dell'intera istanza di MS SQL Server)
Qui non è possibile fornire una ricetta universale valida per tutti i casi, cioè è necessario analizzare le query più pesanti.
Dalla mia esperienza, consiglio il seguente algoritmo di azione per sistemi OLTP per configurare le proprietà di parallelismo:
- prima impedire il parallelismo impostando a livello dell'intera istanza il Max Degree of Parallelism a 1
- analizzare le query più pesanti e determinare il numero ottimale di flussi per esse
- impostare il Max Degree of Parallelism al numero ottimale di flussi stabilito al punto 2, e anche per i database specifici impostare il valore Max DOP ottenuto al punto 2 per ciascun database
- analizzare le query più pesanti e identificare gli effetti negativi del multithreading. Se presenti, aumentare il Cost Threshold for Parallelism.
Per sistemi come 1C, Microsoft CRM e Microsoft NAV, nella maggior parte dei casi sarà adeguato vietare il multithreading
Se la versione è Standard, nella maggior parte dei casi è sufficiente disabilitare il multithreading, considerando che questa versione ha un limite sul numero di core CPU.
Per i sistemi OLAP, l'algoritmo descritto sopra non è adatto.
In base alla mia esperienza personale, consiglio il seguente algoritmo di azioni per i sistemi OLAP per configurare le proprietà di parallelismo:
- analizzare le query più pesanti e determinare il numero ottimale di flussi per esse
- impostare il Max Degree of Parallelism su un numero ottimale di thread, ottenuto dal punto 1, e per database specifici impostare il valore Max DOP ottenuto dal punto 1 per ciascun database
- analizzare le query più pesanti e identificare l'effetto negativo della limitazione del parallelismo. Se presente, abbassare il valore del Cost Threshold for Parallelism o ripetere i passaggi 1-2 di questo algoritmo
Quindi, per i sistemi OLTP, si passa dal monothreading al multithreading, mentre per i sistemi OLAP si passa dal multithreading al monothreading. In questo modo si possono ottenere impostazioni di parallelismo ottimali sia per un singolo database che per l'intero'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.
Raccomandazioni per la configurazione dei flag di tracciamento
Dalla mia esperienza e da quella dei miei colleghi, per un funzionamento ottimale consiglio di impostare a livello di avvio del servizio MS SQL Server per le versioni 2008-2016 i seguenti flag di tracciamento:
- 610 — Riduce la registrazione delle inserzioni nelle tabelle indicizzate. Può aiutare con le inserzioni in tabelle con un gran numero di record e numerose transazioni, in caso di frequenti attese prolungate su WRITELOG per le modifiche agli indici
- 1117 — Se un file nel filegroup soddisfa i requisiti della soglia di aumento automatico, tutti i file nel filegroup vengono aumentati
- 1118 — Costringe tutti gli oggetti a trovarsi in extents diversi (divieto di extents misti), il che riduce al minimo la necessità di scansionare la pagina SGAM, utilizzata per monitorare gli extents misti
- 1224 — Disattiva l'aggregazione delle lock in base al numero di lock. Tuttavia, un utilizzo eccessivo della memoria può attivare l'aggregazione delle lock
- 2371 — Modifica la soglia dell'aggiornamento automatico fisso delle statistiche alla soglia dell'aggiornamento automatico dinamico delle statistiche. Importante per l'aggiornamento dei piani di query riguardanti grandi tabelle, dove la determinazione errata del numero di righe produce piani di esecuzione errati.
- 3226 — Sopprime i messaggi di successo dell'esecuzione del backup nel registro degli errori.
- 4199 — Abilita le modifiche nell'ottimizzatore delle query rilasciate negli aggiornamenti cumulativi e negli aggiornamenti di SQL Server.
- 6532-6534 — Attiva il miglioramento delle prestazioni delle operazioni di query con tipi di dati spaziali.
- 8048 — Converte gli oggetti di memoria partizionati secondo NUMA in partizionati secondo CPU.
- 8780 — Abilita l'ulteriore allocazione di tempo per la generazione del piano di query. Alcune query senza questo flag potrebbero essere rifiutate poiché non dispongono di un piano di query (errore molto raro).
- 8780 — 9389 — Abilita un buffer di memoria dinamico temporaneamente concesso per gli operatori in modalità batch, consentendo all'operatore di richiedere ulteriore memoria ed evitare il trasferimento di dati in tempdb, se è disponibile memoria aggiuntiva.
È anche utile attivare il flag di tracciamento 2301 nelle versioni fino al 2016, che attiva l'ottimizzazione del supporto esteso della decisione, aiutando così nella scelta di piani di query più appropriati. Tuttavia, a partire dalla versione 2016, di solito ha un effetto negativo sul tempo complessivo di esecuzione delle query.
Inoltre, per i sistemi con un numero molto elevato di indici (ad esempio, per i database 1C), consiglio di attivare il flag di tracciamento 2330, che disabilita la raccolta sull'uso degli indici, il che ha un effetto complessivamente positivo sul sistema.
Per dettagli più approfonditi sui flag di tracciamento è possibile consultare
Nel link sopra fornito, è importante considerare anche le versioni e le build di MS SQL Server, poiché nelle versioni più recenti alcuni flag di tracciamento sono attivati per impostazione predefinita o non hanno alcun effetto.
È possibile attivare e disattivare i flag di tracciamento utilizzando i comandi DBCC TRACEON e DBCC TRACEOFF rispettivamente. Per maggiori dettagli, vedere
È possibile ottenere lo stato dei flag di tracciamento utilizzando il comando DBCC TRACESTATUS:
Per abilitare i flag di tracciamento nell'avvio automatico del servizio MS SQL Server, è necessario accedere a SQL Server Configuration Manager e aggiungere i flag di tracciamento nelle proprietà del servizio utilizzando -T:
Conclusioni
In questo articolo sono stati esaminati alcuni aspetti del monitoraggio di MS SQL Server, che consentono di identificare rapidamente la carenza di RAM e di CPU disponibile, oltre a una serie di altri problemi meno evidenti. Sono stati considerati i flag di tracciamento più frequentemente utilizzati.
Fonti:
»
»
»
»
»
»
»
Fonte: habr.com
