Salta al contenuto > Blog > Amministrazione > Alcuni aspetti del monitoraggio di MS SQL Server. Raccomandazioni per la configurazione delle flag di tracciamento.

Alcuni aspetti del monitoraggio di MS SQL Server. Raccomandazioni per la configurazione delle flag di tracciamento.

Prefazione

Spesso gli utenti, gli sviluppatori e gli amministratori di database MS SQL Server si trovano ad affrontare problemi di prestazioni del database o del sistema di gestione del database in generale, quindi il monitoraggio di MS SQL Server è estremamente rilevante.
Questo articolo integra l'articolo Utilizzo di Zabbix per monitorare il database MS SQL Server e tratterà alcuni aspetti del monitoraggio di MS SQL Server, in particolare: come identificare rapidamente quali risorse mancano, oltre a raccomandazioni sulla configurazione dei flag di traccia.
Per eseguire i seguenti script, è necessario creare lo schema inf nel database desiderato nel seguente modo:
Creazione dello schema inf

use ;
go
create schema inf;

Metodo per identificare la mancanza di memoria RAM

Il primo indicatore della mancanza di memoria RAM è quando l'istanza di MS SQL Server utilizza tutta la RAM a lui dedicata.
Per questo, creeremo la seguente vista inf.vRAM:
Creazione della vista inf.vRAM

CREATE view [inf].[vRAM] as
select a.[TotalAvailOSRam_Mb]						--quantità di RAM libera sul server in MB
		 , a.[RAM_Avail_Percent]					--percentuale di RAM libera sul server
		 , a.[Server_physical_memory_Mb]				--totale di RAM sul server in MB
		 , a.[SQL_server_committed_target_Mb]			--totale di RAM riservata per MS SQL Server in MB
		 , a.[SQL_server_physical_memory_in_use_Mb] 		--totale di RAM utilizzata da MS SQL Server attualmente in MB
		 , a.[SQL_RAM_Avail_Percent]				--percentuale di RAM libera per MS SQL Server rispetto alla RAM totale riservata
		 , a.[StateMemorySQL]						--se è sufficiente RAM per MS SQL Server
		 , a.[SQL_RAM_Reserve_Percent]				--percentuale di RAM riservata per MS SQL Server rispetto alla RAM totale del server
		 --se la RAM è sufficiente per il server
		, (case when 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;

Pertanto, per determinare che l'istanza di MS SQL Server consuma tutta la memoria dedicata, è possibile 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 inferiore a SQL_server_committed_target_Mb, è necessario controllare le statistiche di attesa.
Per determinare la mancanza di memoria RAM attraverso le statistiche di attesa, creeremo la vista inf.vWaits:
Creazione della vista inf.vWaits

CREATE view [inf].[vWaits] as
WITH [Waits] AS
    (SELECT
        [wait_type], --nome del tipo di attesa
        [wait_time_ms] / 1000.0 AS [WaitS],--Tempo totale di attesa per questo tipo in millisecondi. Questo tempo include signal_wait_time_ms
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],--Tempo totale di attesa per questo tipo in millisecondi senza signal_wait_time_ms
        [signal_wait_time_ms] / 1000.0 AS [SignalS],--Differenza tra il tempo di segnalazione del thread in attesa e il tempo di inizio della sua esecuzione
        [waiting_tasks_count] AS [WaitCount],--Numero di attese per questo tipo. Questo contatore viene incrementato ogni volta che inizia un'attesa
        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],--Tempo totale di attesa per questo tipo in millisecondi. Questo tempo include signal_wait_time_ms
	    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],--Tempo totale di attesa per questo tipo in millisecondi senza signal_wait_time_ms
	    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],--Differenza tra il tempo di segnalazione del thread in attesa e il tempo di inizio della sua esecuzione
	    [W1].[WaitCount] AS [WaitCount],--Numero di attese per questo tipo. Questo contatore viene incrementato ogni volta che inizia un'attesa
	    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 -- soglia percentuale
)
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, per determinare la mancanza di memoria vivente, è possibile utilizzare 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 nella loro somma, esiste una grande probabilità che l'istanza di MS SQL Server sia a corto di memoria vivente. Valori significativi sono definiti individualmente per ogni sistema. Tuttavia, si può iniziare con il seguente indicatore: Percentage>=1 e AvgWait_S>=0.005.
Per inviare i valori a un sistema di monitoraggio (ad esempio, Zabbix) è possibile creare le seguenti due query:

  1. quantità in percentuale dei tipi di attesa in relazione alla 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'
  );
  2. quanto in millisecondi occupano i tipi di attesa in relazione alla RAM (massimo valore di tutte le attese medie 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'
  );

A partire dalla dinamica dei valori ottenuti su questi due indicatori, è possibile concludere se c'è sufficiente RAM per l'istanza di MS SQL Server.

Metodo per individuare un sovraccarico eccessivo sulla CPU

Per rilevare la mancanza di tempo di 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 probabilità che il numero di core non sia sufficiente per l'istanza di MS SQL Server.
Per riportare il valore a un sistema di monitoraggio (ad esempio, Zabbix) si può creare la seguente query:

seleziona max([runnable_tasks_count]) as [runnable_tasks_count]
dalla sys.dm_os_schedulers
dove scheduler_id<255;

Dalla dinamica dei valori ottenuti per questo indicatore, si può concludere se ci sia sufficiente tempo di CPU (numero di core CPU) per l'istanza di MS SQL Server.
Tuttavia, è importante ricordare che le stesse query possono richiedere più thread contemporaneamente. E talvolta l'ottimizzatore non riesce a valutare correttamente la complessità della query stessa. In tal caso, possono essere allocati troppi thread che in quel momento non possono essere elaborati simultaneamente. Questo causa anche un tipo di attesa legata alla mancanza di tempo di CPU e all'allungamento della coda sui pianificatori che utilizzano specifici core CPU, quindi l'indicatore runnable_tasks_count in tali condizioni aumenterà.
In tal caso, prima di aumentare il numero di core CPU, è necessario configurare correttamente le proprietà di parallelismo dell'istanza di MS SQL Server stesso e, dalla versione 2016, configurare correttamente le proprietà di parallelismo delle necessarie basi di dati:
Alcuni aspetti del monitoraggio di MS SQL Server. Raccomandazioni per la configurazione delle flag di tracciamento.

Alcuni aspetti del monitoraggio di MS SQL Server. Raccomandazioni per la configurazione delle flag di tracciamento.
Qui vale la pena prestare attenzione ai seguenti parametri:

  1. Max Degree of Parallelism - imposta il numero massimo di thread che possono essere assegnati a ciascuna query (per impostazione predefinita è 0 - limitato solo dal sistema operativo e dalla versione di MS SQL Server)
  2. Cost Threshold for Parallelism - costo di valutazione del parallelismo (per impostazione predefinita è 5)
  3. Max DOP - imposta il numero massimo di thread che possono essere assegnati a ciascuna query a livello di database (ma non più del valore della proprietà “Max Degree of Parallelism”) (per impostazione predefinita è 0 - limitato solo dal sistema operativo e dalla versione di MS SQL Server, nonché dalla proprietà “Max Degree of Parallelism” dell’intera istanza di MS SQL Server)

Non è possibile fornire una ricetta egualmente buona per tutti i casi, ovvero è necessario analizzare le query pesanti.
Dalla mia esperienza, consiglio il seguente algoritmo di azione per i sistemi OLTP per configurare le proprietà di parallelismo:

  1. innanzitutto vietare il parallelismo impostando, a livello dell'intera istanza, il Max Degree of Parallelism a 1
  2. analizzare le query più pesanti e scegliere il numero ottimale di thread per esse
  3. impostare il Max Degree of Parallelism al numero ottimale di thread scelto al punto 2, e inoltre per ciascuna delle basi di dati specifiche impostare il valore Max DOP ottenuto al punto 2
  4. analizzare le query più pesanti e identificare l'effetto negativo del multithreading. Se c'è, aumentare il Cost Threshold for Parallelism.
    Per sistemi come 1C, Microsoft CRM e Microsoft NAV, nella maggior parte dei casi è consigliabile vietare il multithreading.

Inoltre, se si utilizza la versione Standard, nella maggior parte dei casi è consigliabile vietare il multithreading, dato che questa versione è limitata nel numero di core CPU.
Per i sistemi OLAP, l'algoritmo sopra descritto non è adatto.
Dalla mia esperienza, consiglio il seguente algoritmo di azione per i sistemi OLAP per configurare le proprietà di parallelismo:

  1. analizzare le query più pesanti e scegliere il numero ottimale di thread per esse
  2. impostare il Max Degree of Parallelism al numero ottimale di thread scelto al punto 1, e inoltre per ciascuna delle basi di dati specifiche impostare il valore Max DOP ottenuto al punto 1
  3. analizzare le query più pesanti e identificare l'effetto negativo della limitazione del parallelismo. Se c'è, ridurre il valore del Cost Threshold for Parallelism, oppure ripetere i passaggi 1-2 di questo algoritmo.

Quindi, per i sistemi OLTP si procede da un singolo thread a più thread, mentre per i sistemi OLAP si procede in senso opposto - da più thread a un singolo thread. In questo modo è possibile trovare le impostazioni ottimali di parallelismo sia per una singola base di dati che per l'intera istanza di MS SQL Server.
È anche 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 traccia

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 traccia:

  1. 610 - Riduzione della registrazione delle inserzioni in tabelle indicizzate. Può aiutare con le inserzioni in tabelle con un gran numero di record e molte transazioni, in caso di attese WRITELOG frequenti e prolungate a causa delle modifiche agli indici.
  2. 1117 - Se un file nel gruppo file soddisfa i requisiti di soglia per una crescita automatica, tutti i file nel gruppo file vengono aumentati.
  3. 1118 - Costringe tutti gli oggetti a trovarsi in estensioni diverse (divieto di estensioni miste), riducendo al minimo la necessità di scansionare la pagina SGAM, che viene utilizzata per tenere traccia delle estensioni miste.
  4. 1224 — Disattiva la fusione delle blocchi basata sul numero di blocchi. Tuttavia, un utilizzo eccessivo della memoria può attivare la fusione dei blocchi.
  5. 2371 — Cambia la soglia per l'aggiornamento automatico delle statistiche da fisso a dinamico. È importante per l'aggiornamento dei piani di query riguardanti tabelle di grandi dimensioni, dove una valutazione errata del numero di record porta a piani di esecuzione sbagliati.
  6. 3226 — Sopprime i messaggi di backup riusciti nel registro degli errori.
  7. 4199 — Attiva le modifiche all'ottimizzatore di query rilasciate negli aggiornamenti cumulativi e nei pacchetti di aggiornamento di SQL Server.
  8. 6532-6534 — Abilita il miglioramento delle prestazioni delle operazioni di query con tipi di dati spaziali.
  9. 8048 — Trasforma gli oggetti di memoria partizionati per NUMA in partizionati per CPU.
  10. 8780 — Abilita l'allocazione di tempo aggiuntivo per la creazione del piano di query. Alcune query senza questo flag possono essere rifiutate, poiché non hanno un piano di query (errore molto raro).
  11. 8780 — 9389 — Abilita un buffer di memoria dinamico temporaneamente fornito in aggiunta per gli operatori in modalità batch, consentendo all'operatore in modalità batch di richiedere ulteriore memoria ed evitare di spostare dati in tempdb, se è disponibile ulteriore memoria.

Fino alla versione 2016, è utile abilitare il flag di tracciamento 2301, che include l'ottimizzazione dell'estesa assistenza alle decisioni e quindi aiuta nella scelta di piani di query più appropriati. Tuttavia, a partire dalla versione 2016, ha frequentemente effetti negativi su lunghi tempi di esecuzione complessivi delle query.
Per sistemi con un numero elevato di indici (ad esempio, per database 1C), si consiglia di attivare il flag di tracciamento 2330, che disabilita la raccolta dell'utilizzo degli indici, il che influisce positivamente sul sistema.
Per maggiori dettagli sui flag di tracciamento, si può consultare qui
Nella link sopra, è importante considerare anche le versioni e le build di MS SQL Server, poiché per versioni più recenti alcuni flag di tracciamento sono abilitati 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 ulteriori dettagli, vedere qui
Per ottenere lo stato dei flag di tracciamento, utilizzare il comando DBCC TRACESTATUS: maggiori dettagli
Affinché i flag di tracciamento vengano abilitati all'avvio del servizio MS SQL Server, è necessario accedere a SQL Server Configuration Manager e nelle proprietà del servizio aggiungere i flag di tracciamento attraverso -T:
Alcuni aspetti del monitoraggio di MS SQL Server. Raccomandazioni per la configurazione delle flag di tracciamento.

Risultati

In questo articolo sono stati esaminati alcuni aspetti del monitoraggio di MS SQL Server, che consentono di rilevare tempestivamente carenze di RAM e tempo CPU disponibile, oltre ad alcune altre problematiche meno evidenti. Sono stati discussi i flag di tracciamento più comunemente utilizzati.

Fonti:

» Statistiche di attesa di SQL Server
» Statistiche delle attese di SQL Server o per favore dimmi dove fa male
» Vista di sistema sys.dm_os_schedulers
» Utilizzo di Zabbix per monitorare il database MS SQL Server
» Stile di vita SQL
» Flag di tracciamento
» sql.ru

Fonte: habr.com

Acquista hosting affidabile per siti web con protezione DDoS, server VPS VDS 🔥 Acquista hosting affidabile per siti web con protezione DDoS, server VPS VDS | ProHoster