Statistiche dell'indice
Data aggiornamento: 12 dicembre 2006
In SQL Server 2005 è possibile creare informazioni statistiche relative alla distribuzione di valori in una colonna. Tali informazioni vengono utilizzate in Query Optimizer per determinare il piano di query ottimale in base alla stima dei costi associati all'utilizzo di un indice per la valutazione della query.
Durante la creazione delle statistiche, Motore di database ordina i valori delle colonne su cui costruire le statistiche e crea un istogramma basato su un massimo di 200 valori, separati da intervalli. Nell'istogramma viene specificato il numero di righe che corrispondono esattamente a ciascun valore di intervallo, nonché il numero di righe comprese in un intervallo e un calcolo della densità dei valori, o dell'incidenza dei valori duplicati, all'interno di un intervallo.
In SQL Server 2005 vengono incluse ulteriori informazioni raccolte dalle statistiche create nelle colonne char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text e ntext. Tali informazioni, note come riepilogo delle stringhe, consentono a Query Optimizer di stimare la selettività dei predicati di query in modelli di stringa. Tramite il riepilogo delle stringhe è possibile eseguire stime più accurate delle dimensioni dei set di risultati e, di frequente, piani di query migliori quando in una query sono presenti condizioni LIKE, come ad esempio in WHERE ProductName LIKE '%Bike' e WHERE Name LIKE '[CS]heryl'.
[!NOTA] Le informazioni incluse nel riepilogo delle stringhe non vengono mantenute se le dimensioni del riepilogo di una colonna di esempio sono superiori a quelle gestibili in Motore di database. Un riepilogo delle stringhe non verrà ad esempio mantenuto in statistiche create utilizzando WITH FULLSCAN su una colonna varchar(80) univoca di una tabella che contiene 85.000 stringhe, ciascuna delle quali contiene 80 caratteri, e non è presente quasi nessuna similarità tra le stringhe. Per determinare se è stato archiviato un riepilogo delle stringhe per un oggetto statistiche specifico, utilizzare DBCC SHOW_STATISTICS (Transact-SQL).
Funzionamento delle statistiche automatiche
Durante la creazione di un indice Query Optimizer archivia automaticamente le informazioni statistiche sulle colonne indicizzate. Quando inoltre l'opzione di database AUTO_CREATE_STATISTICS è impostata su ON (impostazione predefinita), Motore di database crea automaticamente le statistiche per le colonne senza indici che vengono utilizzate in un predicato.
Quando i dati di una colonna vengono modificati, è possibile che le statistiche di indice e di colonna diventino obsolete e che per tale motivo Query Optimizer prenda decisioni non ottimali sull'esecuzione di una query. Se, ad esempio, si crea una tabella con una colonna indicizzata e 1.000 righe di dati, tutte con valori univoci nella colonna indicizzata, Query Optimizer considera la colonna indicizzata un metodo ottimale per recuperare i dati per una query. Se con l'aggiornamento dei dati della colonna vengono creati molti valori duplicati, la colonna non viene più considerata un candidato ideale da utilizzare in una query. In Query Optimizer viene tuttavia considerata un buon candidato in base alle statistiche di distribuzione dell'indice non aggiornate, che sono state create con i dati disponibili prima dell'aggiornamento.
[!NOTA] Le statistiche obsolete o mancanti sono indicate come avvisi (nome della tabella in rosso) quando il piano di esecuzione viene visualizzato graficamente utilizzando SQL Server Management Studio. Per ulteriori informazioni, vedere Visualizzazione dei piani di esecuzione grafici (SQL Server Management Studio). Il monitoraggio della classe di eventi Missing Column Statistics tramite SQL Server Profiler indica i casi in cui le statistiche risultano mancanti. Per ulteriori informazioni, vedere Categoria di eventi Errori e avvisi (Motore di database).
Quando l'opzione di database AUTO_UPDATE_STATISTICS è impostata su ON (valore predefinito), Query Optimizer aggiorna automaticamente le informazioni su base periodica non appena i dati delle tabelle vengono modificati. L'aggiornamento delle statistiche viene avviato ogni volta che il test delle statistiche correnti utilizzate in un piano di esecuzione di query ha esito negativo. Il campionamento è casuale nelle pagine di dati e riguarda la tabella o l'indice non cluster di dimensioni minori basato sulle colonne necessarie per le statistiche. Dopo la lettura di una pagina di dati dal disco, per aggiornare le informazioni statistiche verranno utilizzate tutte le righe incluse nella pagina di dati. Quasi sempre le informazioni statistiche vengono aggiornate quando viene modificato circa il 20% delle righe di dati. Query Optimizer garantisce sempre il campionamento di un numero minimo di righe. Viene inoltre sempre eseguita la scansione completa delle tabelle di dimensioni inferiori a 8 megabyte (MB) ai fini della raccolta delle statistiche.
Il campionamento dei dati, in alternativa all'analisi completa dei dati, consente di ridurre il costo dell'aggiornamento automatico delle statistiche. In determinate circostanze, il campionamento statistico non sarà in grado di rappresentare in modo accurato i dati in una tabella. È possibile controllare la quantità di dati campionati durante gli aggiornamenti manuali delle statistiche tabella per tabella utilizzando le clausole SAMPLE e FULLSCAN dell'istruzione UPDATE STATISTICS. Utilizzare la clausola FULLSCAN se si desidera che venga eseguita la scansione di tutti i dati della tabella ai fini della raccolta delle statistiche. Utilizzare invece la clausola SAMPLE per specificare la percentuale o il numero di righe da campionare.
Aggiornamenti asincroni delle statistiche
Una query che avvia un aggiornamento delle statistiche obsolete deve attendere l'aggiornamento delle statistiche prima di procedere con la compilazione e la restituzione di un set di risultati. Un tale comportamento può determinare tempi non prevedibili di risposta alle query, nonché errori in applicazioni con timeout particolarmente ridotti.
In SQL Server 2005 è possibile utilizzare l'opzione di database AUTO_UPDATE_STATISTICS_ASYNC per l'aggiornamento asincrono delle statistiche. Quando questa opzione è impostata su ON, per la compilazione delle query non è necessario attendere l'aggiornamento delle statistiche. Le statistiche obsolete vengono invece inserite in una coda per l'aggiornamento da un thread di lavoro di un processo in background. La query e le eventuali altre query simultanee vengono compilate immediatamente utilizzando le statistiche obsolete esistenti. I tempi di risposta alle query sono in questo caso prevedibili perché non è necessario attendere l'aggiornamento delle statistiche, tuttavia l'utilizzo di statistiche obsolete potrebbe comportare la scelta di un piano di query meno efficace in Query Optimizer. Per le query avviate al termine dell'aggiornamento verranno utilizzate le statistiche aggiornate. In tal caso potrebbe essere necessario ricompilare i piani memorizzati nella cache che dipendono dalla versione obsoleta delle statistiche. L'aggiornamento asincrono delle statistiche non può essere eseguito se alcune istruzioni DDL (Data Definition Language), tra cui CREATE, ALTER e DROP, sono presenti nella stessa transazione utente esplicita.
L'opzione AUTO_UPDATE_STATISTICS_ASYNC viene impostata a livello di database e determina il metodo di aggiornamento per tutte le statistiche del database. Può essere applicata solo all'aggiornamento delle statistiche e non per la creazione asincrona di statistiche. Se impostata su ON, questa opzione non ha alcun effetto a meno che anche l'opzione AUTO_UPDATE_STATISTICS sia impostata su ON. Per impostazione predefinita, l'opzione AUTO_UPDATE_STATISTICS_ASYNC è impostata su OFF. Per ulteriori informazioni sull'impostazione di questa opzione, vedere ALTER DATABASE (Transact-SQL).
Prima di impostare un database in modalità SINGLE_USER, verificare che l'opzione AUTO_UPDATE_STATISTICS_ASYNC sia impostata su OFF. Se l'opzione è impostata su ON, il thread in background utilizzato per aggiornare le statistiche stabilisce una connessione con il database che non sarà quindi accessibile in modalità utente singolo. Se l'opzione è impostata su ON, eseguire le operazioni seguenti:
- Impostare AUTO_UPDATE_STATISTICS_ASYNC su OFF.
- Verificare la presenza di processi asincroni attivi relativi alle statistiche eseguendo una query sulla vista a gestione dinamica sys.dm_exec_background_job_queue.
- Se sono presenti processi attivi, consentire il completamento di tali processi o terminarli manualmente utilizzando KILL STATS JOB.
Considerazioni sulle procedure consigliate
È consigliabile impostare l'opzione AUTO_UPDATE_STATISTICS_ASYNC su ON quando l'applicazione presenta le caratteristiche seguenti:
- Sono stati riscontrati timeout nelle richieste client causati da una o più query in attesa delle statistiche aggiornate.
- È necessario disporre di tempi di risposta prevedibili, anche a costo di eseguire di tanto in tanto query con piani meno efficienti causati dall'utilizzo di statistiche obsolete.
Visualizzazione delle proprietà di aggiornamento delle statistiche asincrone
Per visualizzare lo stato di attivazione (ON) o disattivazione (OFF) dell'opzione AUTO_UPDATE_STATISTICS_ASYNC, selezionare la colonna is_auto_update_stats_async_on della vista del catalogo sys.databases. Per ulteriori informazioni, vedere sys.databases (Transact-SQL).
Per sapere se le statistiche sono in coda per l'aggiornamento o in fase di aggiornamento, utilizzare la vista a gestione dinamica sys.dm_exec_background_job_queue. Ai fini delle statistiche, la colonna object_id1 visualizza l'ID della tabella o della vista, mentre la colonna object_id2 l'ID delle statistiche. Utilizzare la vista a gestione dinamica sys.dm_exec_background_job_queue_stats per visualizzare statistiche aggregate di tutte le code di processi in background, ad esempio il numero di richieste di processi in attesa di esecuzione, il numero delle richieste non riuscite e i tempi medi di esecuzione di richieste eseguite in precedenza.
Disattivazione delle statistiche automatiche
Per disattivare la generazione automatica delle statistiche per una colonna o un indice specifico, scegliere tra uno dei metodi seguenti:
- Utilizzare la stored procedure di sistema sp_autostats.
- Utilizzare la clausola STATISTICS_NORECOMPUTE dell'istruzione CREATE INDEX.
- Utilizzare la clausola NORECOMPUTE dell'istruzione UPDATE STATISTICS.
- Utilizzare la clausola NORECOMPUTE dell'istruzione CREATE STATISTICS.
- Impostare le opzioni di database AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS su OFF utilizzando l'istruzione ALTER DATABASE. Per ulteriori informazioni, vedere Impostazione delle opzioni di database.
Se la gestione automatica delle statistiche non è assegnata a Motore di database, le informazioni statistiche dovranno essere aggiornate manualmente.
[!NOTA] L'istruzione UPDATE STATISTICS consente di riattivare l'aggiornamento automatico delle statistiche a meno che non sia stata specificata la clausola NORECOMPUTE.
Creazione e aggiornamento manuali delle statistiche
Per creare le statistiche in base a tutte le colonne idonee di tutte le tabelle utente nel database corrente in una singola istruzione, è anche possibile utilizzare la stored procedure di sistema sp_createstats. È possibile creare statistiche in base a colonne specifiche di tabelle o viste utilizzando l'istruzione CREATE STATISTICS, nonché aggiornarle tramite l'istruzione UPDATE STATISTICS. Il numero massimo di statistiche che è possibile creare per una tabella o una vista indipendentemente da un indice è pari a 2000. Ai fini delle statistiche è possibile scegliere qualsiasi colonna o combinazione di colonne utilizzabile come chiave di indice, con le eccezioni seguenti:
- È possibile specificare colonne di tipo oggetto estese, ad eccezione di xml. È possibile specificare i tipi varchar(max), nvarchar(max), varbinary(max), image, text e ntext.
- La dimensione massima consentita dei valori combinati di colonna non può superare il limite di 900 imposto sul valore della chiave dell'indice.
È possibile eliminare l statistiche generate per una colonna se non si desidera più mantenerle e gestirle.
La creazione manuale di statistiche consente di includere nelle statistiche più densità di colonne, ovvero il numero medio di duplicati per la combinazione di colonne. Una query include ad esempio la clausola WHERE a = 7 and b = 9.
La creazione di statistiche manuali per entrambe le colonne contemporaneamente (a, b) consente a Motore di database di eseguire una stima più accurata per la query perché le statistiche includono anche il numero medio di valori distinct per la combinazione di colonne a e b.
Per creare statistiche per una colonna
CREATE STATISTICS (Transact-SQL)
Per creare statistiche per tutte le colonne idonee in tutte le tabelle utente
Per aggiornare manualmente le statistiche
UPDATE STATISTICS (Transact-SQL)
Per visualizzare le statistiche di una tabella
DBCC SHOW_STATISTICS (Transact-SQL)
Per eliminare le statistiche di una colonna
DROP STATISTICS (Transact-SQL)
Utilizzo delle statistiche dopo l'aggiornamento di un database a SQL Server 2005
Quando si aggiorna un database a SQL Server 2005 da una versione precedente di SQL Server, tutte le statistiche della versione precedente vengono considerate obsolete. In occasione del primo utilizzo, pertanto, le statistiche idonee per l'aggiornamento in base all'opzione di database AUTO_UPDATE_STATISTICS vengono aggiornate utilizzando la frequenza di campionamento predefinita. Questa funzionalità presenta rilevanti vantaggi e non richiede in genere alcun intervento da parte dell'utente. In rare circostanze, tuttavia, le statistiche possono risultare meno accurate se sono state calcolate manualmente in una versione precedente di SQL Server tramite FULLSCAN o con un'altra frequenza di campionamento elevata oppure se le dimensioni della tabella campionata sono superiori a 8 MB e la distribuzione dei dati non è casuale. In realtà, viene sempre riscontrata una riduzione della frequenza di campionamento nel caso di statistiche FULLSCAN per tabelle di dimensioni superiori a 8 MB quando si esegue AUTO_UPDATE_STATISTICS. L'aggiornamento iniziale delle statistiche può essere eseguito subito dopo l'aggiornamento alla nuova versione di SQL Server.
L'aggiornamento delle statistiche al formato di SQL Server 2005 presenta numerosi vantaggi. In SQL Server 2005 infatti le statistiche per una frequenza di campionamento specifica sono di qualità notevolmente superiore rispetto a quelle per SQL Server 2000 e versioni precedenti. Inoltre, come descritto in precedenza, in SQL Server 2005 vengono create speciali statistiche del riepilogo delle stringhe per le colonne di caratteri. Per ulteriori informazioni sulle statistiche in SQL Server 2005, vedere il sito Web Microsoft.
Considerazioni sulle procedure consigliate
Nella maggior parte dei casi dopo l'aggiornamento di un database non è necessario eseguire operazioni specifiche in relazione alle statistiche. Se, tuttavia, si dispone di un database esteso con particolari requisiti di prestazioni, dopo l'aggiornamento è consigliabile eseguire sp_updatestats (Transact-SQL) con l'opzione RESAMPLE. In tal modo le frequenze di campionamento precedenti verranno mantenute anche se tutte le statistiche verranno aggiornate al formato più recente. Tenere presente che per le statistiche ottenute durante la creazione dell'indice viene utilizzata la frequenza di campionamento FULLSCAN. Durante l'aggiornamento tramite AUTO_UPDATE_STATISTICS, per queste e per altre statistiche FULLSCAN verrà quindi utilizzata la frequenza di campionamento predefinita. Se si preferisce non aggiornare tutte le statistiche eseguendo sp_updatestats, utilizzare UPDATE STATISTICS per aggiornare in modo selettivo le statistiche per indici e altre statistiche FULLSCAN con la frequenza di campionamento FULLSCAN dopo l'aggiornamento del database.
Vedere anche
Concetti
Altre risorse
CREATE INDEX (Transact-SQL)
Ottimizzazione delle query
sp_autostats (Transact-SQL)
Guida in linea e informazioni
Cronologia modifiche
| Versione | Cronologia |
|---|---|
12 dicembre 2006 |
|
5 dicembre 2005 |
|