Cosa deve comprendere un Health Check di un'istanza SQL Server - Seconda parte

Proseguiamo con la nostra panoramica dopo i controlli visti nell'articolo precedente.

Proprietà dei database presenti in un'istanza

Con il codice sotto andremo ad estrarre importanti proprietà di tutti i database presenti nell’istanza. Informazioni come il Recovery Model, il riutilizzo del Log, il compatibility level, sono alcuni importanti dettagli che la query ci mostra. Ad esempio nel caso di database in Recovery Model in FULL, è fortemente consigliato implementare un backup del transaction log, altrimenti il log crescerebbe fino a saturare il disco oppure lo spazio a disposizione impostato per il t-log. Analizzando gli altri risultati, possiamo scoprire se il t-log sta per riempirsi, oppure se sono attive le auto-statistiche o l’auto-skrink.

SELECT db.[name] AS [DatabaseName]
	,db.recovery_model_desc AS RecoveryModel
	,db.log_reuse_wait_desc AS [Log Reuse Wait Description]
	,ns.cntr_value AS [Log size KB]
	,nu.cntr_value AS [Log used KB]
	,CAST(CAST(nu.cntr_value AS FLOAT) / CAST(ns.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [LOG Used %]
	,db.compatibility_level AS [DB Compatibility Level]
	,db.page_verify_option_desc AS [Page Verify Option]
	,db.is_auto_update_stats_async_on
	,db.is_parameterization_forced
	,db.snapshot_isolation_state_desc
	,is_auto_shrink_on
	,is_auto_close_on
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS nu ON db.name = nu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ns ON db.name = ns.instance_name
WHERE nu.counter_name LIKE N'Log File(s) Used Size (KB)%'
	AND ns.counter_name LIKE N'Log File(s) Size (KB)%'
	AND ns.cntr_value > 0;

Volendo scoprire invece quali database sono maggiormente soggetti a blocchi i I/O, possiamo utilizzare:

SELECT DB_NAME(fs.database_id) AS [Database Name]
	,mf.physical_name
	,io_stall_read_ms
	,num_of_reads
	,CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS avg_read_stall_ms
	,io_stall_write_ms
	,num_of_writes
	,CAST(io_stall_Write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS avg_wrire_stall_ms
	,io_stall_read_ms + io_stall_write_ms AS [io_stalls]
	,num_of_reads + num_of_writes AS [total_io]
	,CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = fs.database_id
	AND fs.file_id = mf.file_id
ORDER BY avg_io_stall_ms DESC

L’output mostra il tempo di stallo I/O in millesecondi, di conseguenza è possibile determinare quale database ha impegnato più wait di attesa I/O. La query sopra mostra anche le attività di lettura e scrittura per tutti i database. Se si notano molti record riferiti allo stesso database in alto all’output dei risultati di questa query, potrebbe indicare dei colli di bottiglia riferiti all’I/O del disco; in tal caso per dare fondamenta solide al sospetto, occorre fare riferimento ai performance counter di windows come Avg Disk Sec/Write e Avg Sec/Read.

Utilizzo della CPU

SELECT DB_NAME(database_id) AS DatabaseName
	,COUNT(*) * 8 / 1024.0 AS [Cached Size MB]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4
	AND database_id <> 32767
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size MB] DESC
OPTION (RECOMPILE);

La query sopra elencherà l’utilizzo totale del buffer per ogni database utente in esecuzione sull’istanza corrente. Per ridurre l’utilizzo della memoria di un particolare database si può tentare di verificare eventuali indici mancanti su tabelle di grandi dimensioni che causano di conseguenza un gran numero di scansioni di indici o tabelle.

Nel caso si abbia a che fare con la versione Enterprise di SQL server, si può iniziare ad utilizzare la compressione dei dati di SQL Server su alcuni degli indici più grandi. Prima di fare ciò, è comunque necessario verificare che si tratti di una tabella statica di grandi dimensioni altamente comprimibile a causa dei tipi di dati e dei dati effettivi in tabella. Un indice compresso rimarrà compresso nel buffer pool a meno che i dati non vengano aggiornati.

Proseguendo con le analisi tramite la query successiva, è possibile estrarre dati sulla quantità di tempo di utilizzo dei processori da parte degli user database:

WITH DB_CPU_Stats
AS (
	SELECT DatabaseID
		,DB_NAME(databaseID) AS DATABASENAME
		,SUM(total_worker_time) AS CPU_TIME_MS
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY (
		SELECT CONVERT(INT, value) AS [DatabaseID]
		FROM sys.dm_exec_plan_attributes(qs.plan_handle)
		WHERE attribute = N'dbid'
		) AS F_DB
	GROUP BY DatabaseID
	)
SELECT ROW_NUMBER() OVER (
		ORDER BY CPU_TIME_MS DESC
		) AS Row_Num
	,DATABASENAME
	,CPU_TIME_MS
	,CAST([CPU_TIME_MS] * 1.0 / SUM([CPU_TIME_MS]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4
	AND DatabaseID <> 32767
ORDER BY Row_Num
OPTION (RECOMPILE);

L’utilizzo di questa query è limitato all’utilizzo del comando DBCC FLUSHPROCINDB(database_id), in quanto questo comando cancella la cache dei piani; di conseguenza viene eliminato anche l’utilizzo complessivo della CPU da parte dei database.

Statistiche di attesa

La query seguente invece prende in esame le principali statistiche di attesa cumulative dall’ultimo avvio di SQL Server o da quando le stesse sono state cancellate mediante l’utilizzo del comando DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR):

WITH [Waits]
AS (
	SELECT [wait_type]
		,[wait_time_ms] / 1000.0 AS [WaitS]
		,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
		,[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 [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'MEMORY_ALLOCATION_EXT'
			,N'ONDEMAND_TASK_QUEUE'
			,N'PARALLEL_REDO_DRAIN_WORKER'
			,N'PARALLEL_REDO_LOG_CACHE'
			,N'PARALLEL_REDO_TRAN_LIST'
			,N'PARALLEL_REDO_WORKER_SYNC'
			,N'PARALLEL_REDO_WORKER_WAIT_WORK'
			,N'PREEMPTIVE_OS_FLUSHFILEBUFFERS'
			,N'PREEMPTIVE_XE_GETTARGETSTATE'
			,N'PVS_PREALLOCATE'
			,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
			,N'PWAIT_DIRECTLOGCONSUMER_GETNEXT'
			,N'PWAIT_EXTENSIBILITY_CLEANUP_TASK'
			,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
			,N'QDS_ASYNC_QUEUE'
			,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
			,N'QDS_SHUTDOWN_QUEUE'
			,N'REDO_THREAD_PENDING_WORK'
			,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'SOS_WORK_DISPATCHER'
			,N'SP_SERVER_DIAGNOSTICS_SLEEP'
			,N'SQLTRACE_BUFFER_FLUSH'
			,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
			,N'SQLTRACE_WAIT_ENTRIES'
			,N'VDI_CLIENT_OTHER'
			,N'WAIT_FOR_RESULTS'
			,N'WAITFOR'
			,N'WAITFOR_TASKSHUTDOWN'
			,N'WAIT_XTP_RECOVERY'
			,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'
			)
		AND [waiting_tasks_count] > 0
	)
SELECT CAST(getdate() AS DATE) AS Capture_Data
	,MAX([W1].[wait_type]) AS [WaitType]
	,CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_Time_S]
	,MAX([W1].[WaitCount]) AS [WaitCount]
	,CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage]
	,CAST((MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95;

La query appena esposta aiuta a concentrarsi sui maggiori tempi di attesa di SQL Server. Bisogna ricordarsi che si tratta comunque di tempi di attesa registrati dall’ultimo riavvio di SQL Server e che non tutti i tempi di attesa sono dannosi per l’istanza. Nella clausola NOT IN della query sono stati esclusi i wait che risultano essere benigni e quindi possono essere ignorati.

A seconda della percentuale rilevata tramite la query sopra, si può decidere di indagare in maniera mirata sugli eventi che stanno scatenando un accumulo elevato del wait in questione.

Sempre collegato ai waits, SignalWait riguarda le attese legate alla CPU. La query seguente cerca di rilevare eventuali segnali di pressione sulla CPU. Un segnale di attesa che supera il 15%-20% è già un rilevatore di pressione per la CPU.

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%Signal (CPU) Waits]
	,CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%Resource waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK)
OPTION (RECOMPILE);

Ecco un esempio di output:

Riguardo le connessioni, la seguente query invece siamo in grado di verificare il numero di connessioni verso il database da parte delle singole login.

SELECT login_name
	,COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC
OPTION (RECOMPILE);

Rilevare il numero di connessioni per ciascun accesso permette di valutare eventuali carichi di lavoro che si discostano dalla normale routine quotidiana.

La seguente query invece mostra le attività correnti ed altre informazioni sull’I/O in sospeso.

SELECT AVG(current_tasks_count) AS [AVG Task Count]
	,AVG(runnable_tasks_count) AS [AVG Runnable Task Count]
	,AVG(pending_disk_io_count) AS [AVG pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255
OPTION (RECOMPILE);

A seconda dei valori ricevuti da questa query possiamo ricavare varie informazioni.

AVG Task Count: (per scheduler della CPU) è un buon indicatore del livello complessivo del carico di lavoro. Un numero elevato in questa colonna indica problemi causati da blocchi o conflitti di risorse.

AVG Runnable Task Count: indica quante attività sono in attesa della CPU su ciascuno scheduler. Valori elevati (superiore a 10), indicano pressione verso la CPU.

AVG Pending DiskIOCount: misura il numero di operazioni I/O in sospeso presenti su ogni scheduler della CPU, di conseguenza, valori superiori a 10 indicano pressione sull’I/O complessivo. Quest’ultimo valore, nel caso il sistema sia sottopressione, cambierà molto velocemente.

Se vogliamo utilizzare la query sopra per il nostro sistema di Health Check, ricordiamoci che dobbiamo prevedere un polling molto ravvicinato poiché, come prevedibile, i valori cambiano molto velocemente a seconda del carico di lavoro. Se uno dei 3 valori restituiti dalla query, rimane con un valore superiore a 10 per lunghi periodi, è il caso di sospettare che qualcosa non stia andando per il verso giusto.

Ancora sulla CPU la seguente query, mostra l’utilizzo della CPU negli ultimi 256 minuti ad intervalli di 1 minuto.

DECLARE @ts_now BIGINT = (
		SELECT cpu_ticks / (cpu_ticks / ms_ticks)
		FROM sys.dm_os_sys_info WITH (NOLOCK)
		);

SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
	,SystemIdle AS [System Idle Process]
	,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
	,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
	SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
		,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
		,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
		,[timestamp]
	FROM (
		SELECT [timestamp]
			,CONVERT(XML, record) AS [record]
		FROM sys.dm_os_ring_buffers WITH (NOLOCK)
		WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE N'%<SystemHealth>%'
		) AS x
	) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

L’output della query mostra, l’utilizzo delle CPU di SQL Server, Idle, degli altri processi e l’intervallo considerato.

Nella terza ed ultima parte vedremo come effettuare i controlli a livello di memoria ed indici.

di Luciano Maugeri, pubblicato il 29 aprile 2026

Cosa deve comprendere un Health Check di un'istanza SQL Server - Prima parte

I controlli preliminari e i controlli a livello di istanza

DATABASE OP CENTER

CONSULENZA

formazione

Cosa deve comprendere un Health Check di un'istanza SQL Server - Seconda parte

Proprietà dei database presenti in un'istanza

Utilizzo della CPU

Statistiche di attesa