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

Una volta verificata la memoria del S.O.m scendiamo più in profondita’ esaminando invece l’utilizzo della memoria interna di SQL Server, tramite la seguente query:

SELECT physical_memory_in_use_kb
	,locked_page_allocations_kb
	,page_fault_count
	,memory_utilization_percentage
	,available_commit_limit_kb
	,process_physical_memory_low
	,process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK)
OPTION (RECOMPILE);

Ecco l’output:

Quanto sopra ci indica la memoria effettivamente utilizzata da SQL Server, soprattutto se ci sono pagine bloccate in memoria, ed anche se la memoria fisica o virtuale risulta insufficiente.

Altro parametro che assume particolare importanza, sempre riguardo la memoria, è il PLE (page life expenctancy), ricavabile dalla query sotto:

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Manager%' -- Handles named instances 
	AND counter_name = N'Page life expectancy'
OPTION (RECOMPILE);

Il valore dell’output viene espresso in secondi e misura il tempo previsto di permanenza di una pagina di dati nella cache del buffer prima che venga scaricata o eliminata. Microsoft a suo tempo raccomandava un valore minimo di 300 secondi, sotto il quale si presumeva esserci pressione sulla memoria, ma con i moderni server i 300 secondi indicati non sono più consoni, quindi per il calcolo di un PLE efficiente si utilizza la formula:

PLE = (maxmemorySQLServer / 4) * 300 

Di conseguenza se ad esempio la nostra istanza è configurata con un maxmemory uguale a 96Gb di RAM, il PLE sarà uguale a: (96/4)*300 = 7200 sec.

La cattura del PLE ad intervalli regolari, ci permetterà di individuare facilmente i momenti di maggiore pressione della memoria.

Passiamo, con le due query seguenti, ad analizzare le concessioni di memoria in sospeso:

SELECT cntr_value AS [Memory Grants Outstanding]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances 
	AND counter_name = N'Memory Grants Outstanding'
OPTION (RECOMPILE);

Restituisce un valore che rappresenta il valore corrente delle concessioni di memoria in sospeso per l’istanza SQL Server. Il valore ottimale sarebbe zero: valori superiori indicano pressioni sulla memoria dovuta a query che utilizzano la memoria per ordinamento e l’hashing. 

SELECT cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances 
	AND counter_name = N'Memory Grants Pending'
OPTION (RECOMPILE);

Anche in questo caso, valori sopra lo zero indicano una forte pressione sulla memoria; nello specifico, la query qui sopra indica il numero totale di processi in attesa di una concessione di memoria. 

Se dalle query precedente si evidenziano valori anomali, con la query sotto si può indagare maggiormente sull’utilizzo complessivo della memoria in SQL Server:

SELECT TOP (10) [type] AS [Memory Clerk Type]
	,SUM(pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC
OPTION (RECOMPILE);

Questa query permette di verificare cosa utilizza grandi quantità di memoria (oltre alla cache del buffer). Voce a cui prestare particolare attenzione è CACHESTORE_SQLCP, che si occupa della memoria per i piani di query ad hoc. Molta memoria utilizzate dal memory Clerk CACHESTORE_SQLCP, indica un utilizzo molto elevato di memoria per i piani ad uno singoli per query ad hoc.

Per visualizzare il testo e le dimensioni della query dei piani di query ad hoc monouso, utilizzare la seguente query:

SELECT TOP (20) [text] AS [QueryText]
	,cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
	AND cp.objtype = N'Adhoc'
	AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
OPTION (RECOMPILE);

Controlli a livello di database

Per andare verso il completamento del nostro sistema di Health Check puntiamo adesso ai singoli database, utilizzando delle query mirate sui database presenti nella nostra istanza.

Cominciamo con le due query seguenti, che forniscono informazioni sui percorsi fisici dei datafiles e t-log e sulle loro dimensioni; 

SELECT name AS [File Name]
	,physical_name AS [Physical Name]
	,size / 128.0 AS [Total Size in MB]
	,size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS [Available Space In MB]
	,[file_id]
FROM sys.database_files WITH (NOLOCK)
OPTION (RECOMPILE);

La seconda query invece ci da informazioni più dettagliate sul t-log indicando lo spazio utilizzato, lo spazio totale e la percentuale di utilizzo. Il monitoraggio di queste informazione ci mette al riparo da eventuale riempimento del t-log. 

La query seguente permette invece di raccogliere informazioni sull’I/O del database:

SELECT DB_NAME(DB_ID()) AS [Database Name]
	,[file_id]
	,num_of_reads
	,num_of_writes
	,io_stall_read_ms
	,io_stall_write_ms
	,CAST(100. * io_stall_read_ms / (io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]
	,CAST(100. * io_stall_write_ms / (io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]
	,(num_of_reads + num_of_writes) AS [Writes + Reads]
	,num_of_bytes_read
	,num_of_bytes_written
	,CAST(100. * num_of_reads / (num_of_reads + num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct]
	,CAST(100. * num_of_writes / (num_of_reads + num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct]
	,CAST(100. * num_of_bytes_read / (num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct]
	,CAST(100. * num_of_bytes_written / (num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)
OPTION (RECOMPILE);

L’output mostrerà: il numero di letture e scritture per ciascun file del database, il numero dei byte letti e scritti, il numero di blocchi I/O di lettura e scrittura. 

Anche il numero dei VLF (Virtual Log File) è un indicatore della salute di un database; sono informazioni che è possibile estrarre tramite la seguente query:

DBCC LOGINFO;

Un numero elevato di VLF può influire sulle prestazioni in scrittura nel T-log ed inoltre può avere effetto sui tempi di ripristino di un database. Il numero dei VLF può essere controllato allocando preventivamente lo spazio del t-log in maniera ottimale, dipendente dalla grandezza del database. 

I VLF crescono ogni volta che il t-log si allarga; se la crescita è inferiore a 64 Mb, verranno aggiunti 4 VLF, se invece la crescita è tra i 64 Mb e 1 Gb, verranno aggiunti 8 VLF, mentre per crescite superiori a 1Gb, verranno aggiunti 16 VLF al T-log. Configurare il t-log in maniera ottimale per avere un numero di VLF non elevato dipende sostanzialmente dall’attività del database stesso e dal numero delle scritture che si prevedono. 

Passiamo con la prossima query ad esaminare le attività delle query sui nostri database:

SELECT qs.execution_count
	,qs.total_rows
	,qs.last_rows
	,qs.min_rows
	,qs.max_rows
	,qs.last_elapsed_time
	,qs.min_elapsed_time
	,qs.max_elapsed_time
	,SUBSTRING(qt.TEXT, qs.statement_start_offset / 2 + 1, (
			CASE 
				WHEN qs.statement_end_offset = - 1
					THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
				ELSE qs.statement_end_offset
				END - qs.statement_start_offset
			) / 2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

Restituisce le query memorizzate nella cache ordinate per numero di esecuzioni, con annesso lo statement.

La query seguente è similare alla precedente, ma prende in considerazione le SP in cache:

SELECT TOP (250) p.name AS [SP Name]
	,qs.execution_count
	,ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second]
	,qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
	,qs.total_worker_time AS [TotalWorkerTime]
	,qs.total_elapsed_time
	,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
	,qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

Controlli sugli indici

Informazioni che assumono particolare importanza sono quelle sugli indici; di seguito vedremo una serie di query che approfondiscono questo aspetto.

La prima query che vedremo riguarda gli indici non cluster che hanno più scritture che letture: 

SELECT OBJECT_NAME(s.[object_id]) AS [Table Name]
	,i.name AS [Index Name]
	,i.index_id
	,user_updates AS [Total Writes]
	,user_seeks + user_scans + user_lookups AS [Total Reads]
	,user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id]
	AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
	AND s.database_id = DB_ID()
	AND user_updates > (user_seeks + user_scans + user_lookups)
	AND i.index_id > 1
ORDER BY [Difference] DESC
	,[Total Writes] DESC
	,[Total Reads] ASC
OPTION (RECOMPILE);

Lo scopo di questa query è rilevare gli indici non cluster cha hanno un alto numero di scritture e un numero molto basso di letture; si tratta di indici praticamente inutili, poiché si paga un costo per tenerlo attivo, ma non si ottiene nessun beneficio. Occorre sottolineare che le informazioni estratte dalla query vengono azzerate con il riavvio dell’istanza: tale informazione va tenuta presente per poter analizzare i dati in maniera più coerente. Attenzione anche ad alcuni indici che vengono utilizzati raramente perché indispensabili solo per alcune procedure. 

La query seguente mostra invece gli indici mancanti in base al vantaggio offerto dall’indice:

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage]
	,migs.last_user_seek
	,mid.[statement] AS [Database.Schema.Table]
	,mid.equality_columns
	,mid.inequality_columns
	,mid.included_columns
	,migs.unique_compiles
	,migs.user_seeks
	,migs.avg_total_user_cost
	,migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID() -- Remove this to see for entire instance 
ORDER BY index_advantage DESC
OPTION (RECOMPILE);

La query mostra gli indici che SQL Server rileva come mancanti, in base al flusso di lavoro dei database ed in base al costo calcolato della query. Tuttavia prima di introdurre un indice, occorre verificare vari fattori: se la tabella è oggetto di molte scritture sarebbe meglio non aggiungere nuovi indici, poiché più indici rallenteranno le prestazioni di inserimento/aggiornamento/eliminazione sulla tabella. I campi last_user_seek e user_seek possono aiutare a scegliere se procedere con deploy dell’indice o meno; se il campo last_user_seek contiene un valore di pochi secondi o pochi minuti è probabile che effettivamente l’indice giovi al normale carico di lavoro. Se i valori sono più larghi, è probabile che l’indice non vada ad apportare alcun miglioramento. Il campo user_seek invece contiene un valore che identifica quante volte SQL Server ha stabilito che avrebbe avuto bisogno dell’indice. 

La seguente query, mostra gli indici mancanti per i piani memorizzati nella cache del database corrente:

SELECT TOP (25) OBJECT_NAME(objectid) AS [ObjectName]
	,query_plan
	,cp.objtype
	,cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
	AND dbid = DB_ID()
ORDER BY cp.usecounts DESC
OPTION (RECOMPILE);

Questa query restituisce informazioni sui piani di esecuzione memorizzati nella cache che presentano avvisi di “missing index”. Restituisce il nome della SP, l’execution plan ed il conteggio degli utilizzi per quel piano di esecuzione. 

La query sotto provvede a restituire informazioni sulle tabelle e sugli indici che utilizzano più spazio nel buffer pool di SQL Server:

SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName]
	,p.index_id
	,COUNT(*) / 128 AS [Buffer size(MB)]
	,COUNT(*) AS [BufferCount]
	,p.data_compression_desc AS [CompressionType]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id
WHERE b.database_id = CONVERT(INT, DB_ID())
	AND p.[object_id] > 100
GROUP BY p.[object_id]
	,p.index_id
	,p.data_compression_desc
ORDER BY [BufferCount] DESC
OPTION (RECOMPILE);

Per avere informazioni sullo stato di compressione dei dati di tutte le tabelle è possibile utilizzare la query seguente:

SELECT OBJECT_NAME(object_id) AS [ObjectName]
	,SUM(Rows) AS [RowCount]
	,data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any 
	AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
	AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
	AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%'
	AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
	AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
	AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
GROUP BY object_id
	,data_compression_desc
ORDER BY SUM(Rows) DESC
OPTION (RECOMPILE);

La seguente query invece mostra l’ultima volta che le statistiche sono state aggiornate:

SELECT o.name
	,i.name AS [Index Name]
	,STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]
	,s.auto_created
	,s.no_recompute
	,s.user_created
	,st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id]
	AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK) ON o.[object_id] = st.[object_id]
	AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC
OPTION (RECOMPILE);

La query restituisce il nome e altre proprietà di ogni indice cluster, ordinati in base alla data dell’ultimo aggiornamento delle statistiche. Statistiche non aggiornate potrebbero portare SQL Server Query Optimizer a scegliere un piano di esecuzione con prestazioni più scarse rispetto ad un altro. 

Particolare importanza assume anche la frammentazione degli indici, di conseguenza per scoprire quali indici hanno maggiore frammentazione, possiamo utilizzare la seguente query:

SELECT DB_NAME(database_id) AS [Database Name]
	,OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
	,i.name AS [Index Name]
	,ps.index_id
	,index_type_desc
	,avg_fragmentation_in_percent
	,fragment_count
	,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]
	AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
	AND page_count > 500
ORDER BY avg_fragmentation_in_percent DESC
OPTION (RECOMPILE);

La query restituisce ogni tabella e indice del database, ordinato in base al grado di frammentazione medio e filtra gli indici che hanno meno di 500 pagine, poiché irrilevanti. Gli indici molto frammentati possono ridurre le prestazioni in termini di I/O e le prestazioni delle query, e possono contribuire anche ad aumentare lo spazio richiesto per il datafile. 

Conclusioni

Un Health Check ha comunque bisogno di un sistema che si occupi delle catture a intervalli di tempo regolari correlato da un ulteriori sistema di reporting, che mostri i risultati del polling e che permetta di agevolare il DBA nella successiva elaborazione dei dati.

Per quanto riguarda le cattura si potrebbe ad esempio utilizzare un SSIS Package che vada in polling sulle istanze della farm, associato a Reporting Services oppure a Power BI. Ci sono altre modalità con cui poter effettuare il polling nelle istanze: ognuno può dare libero sfogo alla sua fantasia su come raggiungere l’obiettivo. 

di Luciano Maugeri, pubblicato il 7 maggio 2026

Vuoi maggiori informazioni sul nostro Health Check per SQL Server ed Oracle?

Prenota un appuntamento con uno dei nostri esperti o contattaci direttamente.