Statistics

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsDatabase SQL in Microsoft Fabric

Query Optimizer usa le statistiche per creare piani di query che consentono di migliorare le prestazioni delle query. Per la maggior parte delle query, Query Optimizer genera già le statistiche necessarie per un piano di query di alta qualità. In alcuni casi, è necessario creare statistiche aggiuntive o modificare la progettazione della query per ottenere risultati ottimali. In questo articolo vengono illustrati i concetti relativi alle statistiche e vengono fornite linee guida per un utilizzo efficace delle statistiche di ottimizzazione delle query.

Componenti e concetti

Statistics

Le statistiche di ottimizzazione delle query sono oggetti binari di grandi dimensioni (BLOB) contenenti informazioni statistiche sulla distribuzione dei valori in una o più colonne di una tabella o di una vista indicizzata. Query Optimizer usa queste statistiche per la stima della cardinalità o del numero di righe nel risultato della query. Queste stime di cardinalità consentono a Query Optimizer di creare un piano di query di alta qualità. A seconda dei predicati, ad esempio, Query Optimizer può usare le stime della cardinalità per scegliere l'operatore Index Seek anziché l'operatore Index Scan che usa una maggior quantità di risorse, se questa opzione migliora le prestazioni delle query.

Ogni oggetto statistiche viene creato in un elenco di una o più colonne di tabella e include un istogramma in cui è visualizzata la distribuzione dei valori nella prima colonna. Negli oggetti statistiche su più colonne sono inoltre archiviate informazioni statistiche sulla correlazione dei valori tra le colonne. Queste statistiche sulla correlazione o densitàderivano dal numero di righe distinte di valori di colonna.

Histogram

Un istogramma misura la frequenza di occorrenza per ogni valore distinto in un set di dati. Il Query Optimizer calcola un istogramma sui valori della colonna nella prima colonna chiave dell'oggetto statistico, selezionando i valori della colonna mediante campionamento statistico delle righe oppure eseguendo una scansione completa di tutte le righe della tabella o della vista. Se l'istogramma viene creato da un insieme campionato di righe, i totali archiviati per il numero di righe e il numero di valori distinti sono stime e non è necessario che siano numeri interi completi.

Note

SQL Server compila istogrammi solo per una singola colonna, ovvero la prima colonna nel set di colonne chiave dell'oggetto statistiche.

Per creare l'istogramma, Query Optimizer ordina i valori di colonna, calcola il numero di valori che corrispondono a ogni valore distinct di colonna e quindi aggrega i valori di colonna in un massimo di 200 passaggi contigui dell'istogramma. Ogni passaggio dell'istogramma include un intervallo di valori della colonna, seguito da un valore della colonna che rappresenta il limite superiore. Nell'insieme sono inclusi tutti i possibili valori di colonna compresi tra i valori limite, esclusi questi ultimi. Il minore tra i valori di colonna ordinati costituisce il limite superiore per il primo intervallo dell'istogramma.

Più in dettaglio, SQL Server crea l'istogramma dal set ordinato di valori di colonna in tre passaggi:

• Inizializzazione dell'istogramma: nel primo passaggio viene elaborata una sequenza di valori a partire dall'inizio del set ordinato e vengono raccolti fino a 200 valori di range_high_key, equal_rows, range_rows e distinct_range_rows (in questo passaggio range_rows e distinct_range_rows sono sempre pari a zero). Il primo passaggio termina quando viene esaurito tutto l'input o quando vengono trovati 200 valori.
• Analisi con unione di bucket: ogni valore aggiuntivo della colonna iniziale della chiave delle statistiche viene elaborato nel secondo passaggio, in ordine ordinato. Ogni valore successivo viene aggiunto all'ultimo intervallo o viene creato un nuovo intervallo alla fine (questo ordinamento è possibile perché i valori di input sono ordinati). Se viene creato un nuovo intervallo, il processo comprime una coppia di intervalli adiacenti esistenti in un singolo intervallo. Questa coppia di intervalli viene selezionata per ridurre al minimo la perdita di informazioni. Questo metodo usa un algoritmo per il calcolo della differenza massima, per ridurre al minimo il numero di intervalli nell'istogramma, aumentando contemporaneamente la differenza tra i valori limite. Per tutta questa fase, il numero di passaggi dopo la compressione degli intervalli rimane pari a 200.
• consolidamento dell'istogramma: nel terzo passaggio è possibile comprimere più intervalli senza perdere una quantità significativa di informazioni. Il numero di passi dell'istogramma può essere inferiore al numero di valori distinti, anche per le colonne con meno di 200 punti di delimitazione. Pertanto, anche se la colonna ha più di 200 valori univoci, l'istogramma può avere meno di 200 intervalli. Per una colonna costituita solo da valori univoci, l'istogramma consolidato ha almeno tre passaggi.

Note

Se l'istogramma viene compilato usando un campione anziché fullscan, i valori di equal_rows, range_rows, distinct_range_rows e average_range_rows sono stime e pertanto non devono essere interi.

Nel diagramma seguente viene illustrato un istogramma con sei intervalli. L'area a sinistra del primo valore limite superiore è il primo gradino.

Per ogni passaggio dell'istogramma nell'esempio precedente:

• La riga in grassetto rappresenta il valore limite superiore (range_high_key) e il numero di volte in cui si verifica (equal_rows).

• L'area continua a sinistra di range_high_key rappresenta l'intervallo di valori di colonna e il numero medio di volte in cui si verifica ogni valore di colonna (average_range_rows). Il valore average_range_rows per il primo passaggio dell'istogramma è sempre 0.

• Le linee punteggiate rappresentano i valori campionati usati per stimare il numero totale di valori distinti nell'intervallo (distinct_range_rows) e il numero totale di valori nell'intervallo (range_rows). Query Optimizer usa range_rows e distinct_range_rows per calcolare average_range_rows e non archivia i valori campionati.

Vettore di densità

densità sono informazioni sul numero di duplicati in una determinata colonna o combinazione di colonne e viene calcolato come 1/(numero di valori distinti). Per ottimizzare le stime relative alla cardinalità per query che restituiscono più colonne della stessa tabella o vista indicizzata, Query Optimizer utilizza le densità. Man mano che la densità diminuisce, aumenta la selettività di un valore. Ad esempio, in una tabella che rappresenta automobili, molte automobili vengono prodotte dallo stesso costruttore, ma a ciascuna è assegnato un numero di identificazione univoco. Un indice basato sul numero di identificazione del veicolo è più selettivo rispetto all'indice basato sul produttore, perché il numero di identificazione del veicolo ha una densità minore rispetto al produttore.

Note

La frequenza è rappresentata dalle informazioni sull'occorrenza di ogni valore distinto nella prima colonna chiave dell'oggetto statistiche e viene calcolata con la formula row count * density. Nelle colonne con valori univoci è possibile trovare una frequenza massima pari a 1.

Il vettore di densità contiene una densità per ogni prefisso di colonna nell'oggetto statistiche. Ad esempio, se un oggetto statistiche ha le colonne CustomerIdchiave , ItemIde Price, la densità viene calcolata in ognuno dei prefissi di colonna seguenti.

Prefisso di colonna	Densità calcolata su
(CustomerId)	Righe con valori corrispondenti per CustomerId
(CustomerId, ItemId)	Righe con valori corrispondenti per CustomerId e ItemId
(CustomerId, ItemId, Price)	Righe con valori corrispondenti per CustomerId, ItemId e Price

Statistiche filtrate

Le statistiche filtrate possono migliorare le prestazioni di esecuzione delle query che effettuano la selezione da subset ben definiti di dati. Le statistiche filtrate utilizzano un predicato del filtro per selezionare il subset di dati incluso nelle statistiche. Statistiche filtrate progettate correttamente possono migliorare il piano di esecuzione delle query rispetto alle statistiche di tabella completa. Per altre informazioni sul predicato di filtro, vedere CREATE STATISTICS. Per altre informazioni su quando creare statistiche filtrate, vedere la sezione Quando creare le statistiche in questo articolo.

Opzioni statistiche

È possibile configurare le opzioni che influiscono su quando e su come il sistema crea e aggiorna le statistiche. È possibile impostare queste opzioni solo a livello di database.

opzione AUTO_CREATE_STATISTICS

Quando si attiva l'opzione di creazione automatica delle statistiche, AUTO_CREATE_STATISTICS, Query Optimizer crea statistiche su singole colonne nel predicato di query, se necessario, per migliorare le stime della cardinalità per il piano di query. Queste statistiche di colonna singola vengono create in colonne che ancora non hanno un istogramma in un oggetto statistiche esistente. L'opzione AUTO_CREATE_STATISTICS non determina se il database crea statistiche per gli indici. Questa opzione non genera anche statistiche filtrate. ma si applica esclusivamente alle statistiche di colonna singola per la tabella completa.

Quando Query Optimizer crea statistiche in seguito all'uso dell'opzione AUTO_CREATE_STATISTICS, il nome delle statistiche inizia con _WA. È possibile usare la query seguente per determinare se Query Optimizer ha creato statistiche per una colonna del predicato di query.

SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc
        ON s.stats_id = sc.stats_id
        AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;

opzione AUTO_UPDATE_STATISTICS

Quando si attiva l'opzione di aggiornamento automatico delle statistiche, AUTO_UPDATE_STATISTICS, Query Optimizer determina quando le statistiche potrebbero non essere più aggiornate e le aggiorna quando una query le utilizza. Questa azione è anche nota come ricompilazione delle statistiche. Le statistiche diventano obsolete in seguito a modifiche per operazioni di merge, inserimento, aggiornamento o eliminazione che modificano la distribuzione dei dati nella tabella o nella vista indicizzata. Query Optimizer conta il numero di modifiche alle righe dall'ultimo aggiornamento delle statistiche e confronta tale numero con una soglia per determinare se le statistiche potrebbero non essere aggiornate. La soglia si basa sulla cardinalità della tabella, ovvero il numero di righe nella tabella o nella vista indicizzata.

Contrassegnare le statistiche come non aggiornate in base alle modifiche di riga si verifica anche quando l'opzione AUTO_UPDATE_STATISTICS è OFF. Quando l'opzione AUTO_UPDATE_STATISTICS è OFF, il sistema non aggiorna le statistiche, anche quando le contrassegna come non aggiornate. I piani continuano a usare gli oggetti statistici obsoleti. Impostare AUTO_UPDATE_STATISTICS su OFF può causare piani di query non ottimali e un peggioramento delle prestazioni delle query. Impostare l'opzione AUTO_UPDATE STATISTICS su ON.

• Fino a SQL Server 2014 (12.x), il motore di database usa una soglia di ricompilazione in base al numero di righe nella tabella o nella vista indicizzata determinato al momento della valutazione delle statistiche. La soglia è diversa a seconda che una tabella sia temporanea o permanente.

  Tipo di tabella	Cardinalità della tabella (n)	Soglia di ricompilazione (# modifiche)
  Temporary	n< 6	6
  Temporary	6 <= n<= 500	500
  Permanent	N<= 500	500
  Temporanea o permanente	n> 500	500 + (0,20 * n)

  Ad esempio, se la tabella contiene 20.000 righe, il calcolo è 500 + (0.2 * 20,000) = 4,500 e le statistiche vengono aggiornate ogni 4.500 modifiche.

• A partire da SQL Server 2016 (13.x) e con il livello di compatibilità del database 130, il motore di database usa una soglia di ricompilazione delle statistiche dinamiche decrescente che regola in base alla cardinalità della tabella al momento della valutazione delle statistiche. Con questa modifica, le statistiche sulle tabelle di grandi dimensioni vengono aggiornate più frequentemente. Tuttavia, se un database ha un livello di compatibilità inferiore a 130, si applicano le soglie di SQL Server 2014 (12,x).

  Tipo di tabella	Cardinalità della tabella (n)	Soglia di ricompilazione (# modifiche)
  Temporary	n < 6	6
  Temporary	6 <= n <= 500	500
  Permanent	n <= 500	500
  Temporanea o permanente	n > 500	MIN ( 500 + (0.20 * n), SQRT(1,000 * n) )

  Ad esempio, se la tabella contiene 2 milioni di righe, il calcolo è il minimo di 500 + (0.20 * 2,000,000) = 400,500 e SQRT(1,000 * 2,000,000) = 44,721. Ciò significa che le statistiche vengono aggiornate ogni 44.721 modifiche.

Important

In SQL Server 2008 R2 (10.50.x) fino a SQL Server 2014 (12.x) o in SQL Server 2016 (13.x) e versioni successive con il livello di compatibilità del database inferiore 120, abilitare il flag di traccia 2371 in modo che SQL Server usi una soglia di aggiornamento delle statistiche dinamiche decrescente.

Sebbene sia consigliata per tutti gli scenari, l'abilitazione del flag di traccia 2371 è facoltativa. Tuttavia, è possibile usare le indicazioni seguenti per abilitare il flag di traccia 2371 nell'ambiente precedente a SQL Server 2016 (13.x):

• Se stai utilizzando un sistema SAP, abilita questa traccia. Per ulteriori informazioni, consulta questo blog sul trace flag 2371.
• Se devi affidarti a un job notturno per aggiornare le statistiche perché l'attuale aggiornamento automatico non viene attivato con frequenza sufficiente, valuta di abilitare il trace flag 2371 per adeguare la soglia alla cardinalità della tabella.

Query Optimizer controlla la presenza di statistiche non aggiornate prima di compilare una query e prima di eseguire un piano di query memorizzato nella cache. Prima di compilare una query, Query Optimizer usa le colonne, le tabelle e le viste indicizzate nel predicato di query per determinare quali statistiche potrebbero non essere aggiornate. Prima di eseguire un piano di query memorizzato nella cache, il motore di database verifica che il piano di query faccia riferimento alle statistiche up-to-date.

L'opzione AUTO_UPDATE_STATISTICS si applica agli oggetti statistiche creati per indici, colonne singole nei predicati di query e statistiche create con l'istruzione CREATE STATISTICS . Questa opzione si applica anche alle statistiche filtrate.

È possibile usare il sys.dm_db_stats_properties per tenere traccia accuratamente del numero di righe modificate in una tabella e decidere se aggiornare le statistiche manualmente.

AUTO_UPDATE_STATISTICS è sempre OFF per le tabelle ottimizzate per la memoria.

AUTO_UPDATE_STATISTICS_ASYNC

L'opzione relativa all'aggiornamento asincrono delle statistiche, AUTO_UPDATE_STATISTICS_ASYNC, determina se Query Optimizer usa gli aggiornamenti sincroni o asincroni delle statistiche. Per impostazione predefinita, l'opzione di aggiornamento delle statistiche asincrone è OFFe Query Optimizer aggiorna le statistiche in modo sincrono. L'opzione AUTO_UPDATE_STATISTICS_ASYNC si applica agli oggetti statistiche creati per indici, colonne singole nei predicati di query e statistiche create con l'istruzione CREATE STATISTICS .

Note

Per impostare l'opzione di aggiornamento delle statistiche asincrone in SQL Server Management Studio, nella pagina Opzioni della finestra Proprietà database impostare sia Aggiornamento automatico statistiche che Aggiornamento automatico statistiche in modo asincrono su True.

Gli aggiornamenti delle statistiche possono essere sincroni (impostazione predefinita) o asincroni.

• Con gli aggiornamenti sincroni delle statistiche, le query vengono sempre compilate ed eseguite con statistiche aggiornate. Quando le statistiche sono obsolete, Query Optimizer attende le statistiche aggiornate prima di compilare ed eseguire la query.

• Con gli aggiornamenti asincroni delle statistiche, le query vengono compilate con le statistiche esistenti anche se non sono aggiornate. Query Optimizer potrebbe scegliere un piano di query non ottimale se le statistiche non sono aggiornate al momento della compilazione della query. Le statistiche vengono in genere aggiornate subito dopo. Le query che vengono compilate dopo il completamento dell'aggiornamento delle statistiche traggono vantaggio dall'uso delle statistiche aggiornate.

Valutare l'uso di statistiche sincrone quando si eseguono operazioni che modificano la distribuzione dei dati, ad esempio il troncamento di una tabella o l'esecuzione di un aggiornamento in blocco di un'alta percentuale delle righe. Se non si aggiornano manualmente le statistiche dopo aver completato l'operazione, l'uso dell'aggiornamento sincrono delle statistiche garantisce che siano aggiornate prima che le query ne abbiano bisogno.

Utilizzare le statistiche asincrone per ottenere tempi di risposta alle query più stimabili per gli scenari seguenti:

• L'applicazione esegue frequentemente la stessa query, query simili o piani di esecuzione delle query simili memorizzati nella cache. È possibile che gli aggiornamenti asincroni delle statistiche consentano di ottenere tempi di risposta alle query più stimabili rispetto agli aggiornamenti sincroni delle statistiche perché Query Optimizer può eseguire le query in entrata senza attendere le statistiche aggiornate. Ciò evita di ritardare alcune query e non altre.

• L'applicazione ha subito timeout nelle richieste client causati da una o più query in attesa di statistiche aggiornate. In alcuni casi, l'attesa di statistiche sincrone potrebbe causare errori nelle applicazioni con timeout aggressivi.

Note

Le statistiche sulle tabelle temporanee locali vengono sempre aggiornate in modo sincrono indipendentemente dall'opzione AUTO_UPDATE_STATISTICS_ASYNC . Le statistiche sulle tabelle temporanee globali vengono aggiornate in modo sincrono o asincrono in base all'opzione AUTO_UPDATE_STATISTICS_ASYNC impostata per il database utente.

L'aggiornamento asincrono delle statistiche viene eseguito da una richiesta in background. Quando è pronta per scrivere le statistiche aggiornate nel database, la richiesta tenta di acquisire un blocco di modifica dello schema sull'oggetto dei metadati delle statistiche. Se in una sessione diversa è già presente un blocco sullo stesso oggetto, l'aggiornamento asincrono delle statistiche viene bloccato fino a quando non è possibile acquisire il blocco di modifica dello schema. Analogamente, le sessioni che devono acquisire un blocco di stabilità (Sch-S) dello schema sull'oggetto dei metadati delle statistiche per compilare una query possono essere bloccate dalla sessione di aggiornamento asincrono delle statistiche in background, la quale include già o è in attesa di acquisire il blocco di modifica dello schema. Pertanto, per i carichi di lavoro con compilazioni delle query molto frequenti e frequenti aggiornamenti delle statistiche, l'uso dell'aggiornamento asincrono delle statistiche può aumentare la probabilità di problemi di concorrenza a causa del blocco dovuto ai lock.

In database SQL di Azure, Istanza gestita di SQL di Azure e a partire da SQL Server 2022 (16.x), è possibile evitare potenziali problemi di concorrenza usando l'aggiornamento asincrono delle statistiche se si abilita la ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITYconfigurazione con ambito database. Con questa configurazione abilitata, la richiesta in background attende di acquisire il blocco della modifica dello schema (Sch-M) e rendere persistenti le statistiche aggiornate in una coda separata con priorità bassa, consentendo ad altre richieste di continuare a compilare query con statistiche esistenti. Una volta che nessun'altra sessione mantiene un blocco sull'oggetto metadati delle statistiche, la richiesta in background acquisisce il blocco di modifica dello schema e aggiorna le statistiche. Nel caso improbabile che la richiesta in background non riesca ad acquisire il blocco entro un periodo di timeout di diversi minuti, l'aggiornamento asincrono delle statistiche viene interrotto e le statistiche non vengono aggiornate finché non viene attivato un altro aggiornamento automatico delle statistiche o fino a quando le statistiche non vengono aggiornate manualmente.

Note

L'opzione ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY di configurazione con ambito database è disponibile in database SQL di Azure, Istanza gestita di SQL di Azure e in SQL Server a partire da SQL Server 2022 (16.x).

opzione AUTO_DROP

Si applica a: Database SQL di Azure, Istanza gestita di SQL di Azure e, a partire da SQL Server 2022 (16.x)

In SQL Server prima di SQL Server 2022 (16.x), se si creano manualmente statistiche o si usa uno strumento di terze parti in un database utente, tali oggetti statistiche possono bloccare o interferire con le modifiche dello schema.

A partire da SQL Server 2022 (16.x), l'opzione di eliminazione automatica è abilitata per impostazione predefinita in tutti i database nuovi e sottoposti a migrazione. Se si abilita la AUTO_DROP proprietà , il database crea oggetti statistiche in modalità in modo che una modifica dello schema successiva non venga bloccata dall'oggetto statistica, ma le statistiche vengono eliminate in base alle esigenze. In questo modo, le statistiche create manualmente con l'opzione di eliminazione automatica si comportano come le statistiche create automaticamente.

In database SQL di Azure, Istanza gestita di SQL di Azure e SQL Server 2022 (16.x) e versioni successive, le statistiche create automaticamente si comportano sempre come se il AUTO_DROP sia abilitato.

Note

Il tentativo di definire o di annullare l'impostazione della proprietà di eliminazione automatica in statistiche create automaticamente può generare errori. Per le statistiche create automaticamente è sempre prevista l'opzione di eliminazione automatica. In alcuni backup, quando ripristinati, questa proprietà potrebbe rimanere impostata in modo non corretto fino al successivo aggiornamento dell'oggetto statistiche (manuale o automatico). Tuttavia, le statistiche create automaticamente si comportano sempre come statistiche a eliminazione automatica. Quando si ripristina un database in SQL Server 2022 (16.x) da una versione precedente, è consigliabile eseguire sp_updatestats nel database, impostando i metadati appropriati per la funzionalità di rilascio automatico delle statistiche.

Ad esempio, per creare manualmente un oggetto di statistiche nella tabella dbo.DatabaseLog:

CREATE STATISTICS [mystats]
    ON [dbo].[DatabaseLog]([DatabaseLogID], [PostTime], [DatabaseUser])
    WITH AUTO_DROP = ON;

Ad esempio, per aggiornare un'impostazione di eliminazione automatica di un oggetto di statistiche nella tabella dbo.DatabaseLog:

UPDATE STATISTICS [dbo].[DatabaseLog] ([mystats])
    WITH AUTO_DROP = ON;

Per valutare l'impostazione di eliminazione automatica sulle statistiche esistenti, usare la colonna auto_drop in sys.stats:

SELECT object_id,
       [name],
       auto_drop
FROM sys.stats;

Per altre informazioni, vedere AUTO_DROP.

INCREMENTAL

Si applica a: SQL Server 2014 (12.x) e versioni successive.

Quando si imposta l'opzione INCREMENTAL di CREATE STATISTICS su ON, si creano statistiche per partizione. Quando si imposta su OFF, il database elimina l'albero delle statistiche e ricompila le statistiche. Il valore predefinito è OFF. Questa impostazione sostituisce la proprietà a livello di database INCREMENTAL.

• Per altre informazioni sulla creazione di statistiche incrementali, vedere CREATE STATISTICS.
• Per altre informazioni sulla creazione automatica delle statistiche per partizione, vedere Proprietà database (pagina Opzioni) e ALTER DATABASE SET opzioni.

Quando si aggiungono nuove partizioni a una tabella di grandi dimensioni, è necessario aggiornare le statistiche per includere le nuove partizioni. Tuttavia, il tempo necessario per analizzare l'intera tabella (FULLSCAN o SAMPLE opzioni) può essere lungo. Inoltre, l'analisi dell'intera tabella non è necessaria in quanto occorrono solo le statistiche sulle nuove partizioni. L'opzione incrementale crea e archivia le statistiche per ogni partizione e, se aggiornata, aggiorna solo le statistiche su tali partizioni che richiedono nuove statistiche.

Se le statistiche per partizione non sono supportate, il database ignora l'opzione e genera un avviso. Le statistiche incrementali non sono supportate per i tipi di statistiche seguenti:

• Statistiche create con indici non allineati alle partizioni della tabella di base.
• Statistiche create per i database secondari leggibili Always On.
• Statistiche create per i database di sola lettura.
• Statistiche create per gli indici filtrati.
• Statistiche create nelle viste.
• Statistiche create su tabelle interne.
• Statistiche create con indici spaziali o indici XML.

Quando creare le statistiche

Query Optimizer crea già le statistiche nelle modalità seguenti:

1. Quando si crea un indice in tabelle o viste, Query Optimizer crea statistiche per gli indici. Tali statistiche vengono create nelle colonne chiave dell'indice. Se l'indice è filtrato, Query Optimizer crea statistiche filtrate nello stesso subset di righe specificato per l'indice filtrato. Per altre informazioni sugli indici filtrati, vedere Creare indici filtrati e CREATE INDEX.

   Note

   In SQL Server 2014 (12.x) e versioni successive, il database non crea statistiche analizzando tutte le righe nella tabella quando si crea o si ricompila un indice partizionato. Query Optimizer usa invece l'algoritmo di campionamento predefinito per generare statistiche. Dopo avere aggiornato un database con gli indici partizionati, è possibile notare una differenza nei dati dell'istogramma relativamente a tali indici. Tale cambiamento potrebbe non influire sulle prestazioni di query. Per ottenere statistiche sugli indici partizionati analizzando tutte le righe della tabella, usare CREATE STATISTICS o UPDATE STATISTICS con la FULLSCAN clausola .

2. Quando AUTO_CREATE_STATISTICS è impostata su ON, Query Optimizer crea statistiche per le singole colonne nei predicati di query.

Per la maggior parte delle query, questi due metodi per la creazione di statistiche garantiscono un piano di query di alta qualità. In alcuni casi, è possibile migliorare i piani di query creando statistiche aggiuntive usando l'istruzione CREATE STATISTICS . Queste statistiche aggiuntive possono acquisire correlazioni statistiche che Query Optimizer non tiene conto quando crea statistiche per indici o colonne singole. È possibile che nell'applicazione siano disponibili correlazioni statistiche aggiuntive nei dati della tabella che, se calcolate in un oggetto statistiche, possono consentire a Query Optimizer di migliorare i piani di query. Ad esempio, le statistiche filtrate su un sottoinsieme di righe di dati o le statistiche multicolonna sulle colonne dei predicati della query potrebbero migliorare il piano di esecuzione della query.

Quando si creano statistiche usando l'istruzione CREATE STATISTICS , mantenere l'opzione AUTO_CREATE_STATISTICSON in modo che Query Optimizer continui a creare regolarmente statistiche a colonna singola per le colonne del predicato di query. Per ulteriori informazioni sui predicati di query, consultare Condizione di ricerca.

Valutare la possibilità di creare statistiche usando l'istruzione CREATE STATISTICS quando si applica una delle condizioni seguenti:

• In Ottimizzazione guidata motore di database viene suggerito di creare statistiche
• Il predicato di query contiene più colonne correlate che non sono già chiavi nello stesso indice.
• La query effettua la selezione da un subset di dati.
• La query presenta statistiche mancanti.

Note

Per informazioni specifiche per le tabelle e le statistiche correlate a OLTP in memoria, vedere Statistiche per le tabelle ottimizzate per la memoria.

Il predicato di query contiene più colonne correlate

Quando il predicato di una query contiene più colonne correlate e dipendenti tra loro, è possibile che la creazione di statistiche per più colonne consenta di migliorare il piano di query. Le statistiche su più colonne contengono statistiche di correlazione tra colonne, denominate densità , non disponibili nelle statistiche a colonna singola. Le densità possono migliorare le stime della cardinalità qualora i risultati della query dipendano da relazioni tra i dati di più colonne.

Se le colonne si trovano già nello stesso indice, l'oggetto statistiche multicolumn esiste già e non è necessario crearlo manualmente. Se le colonne non si trovano già nello stesso indice, è possibile creare statistiche multicolonna creando un indice nelle colonne o usando l'istruzione CREATE STATISTICS . La gestione di un indice richiede più risorse di sistema rispetto a un oggetto statistiche. Se l'applicazione non richiede l'indice multicolonna, si possono economizzare le risorse di sistema creando l'oggetto delle statistiche senza creare l'indice.

Quando si creano statistiche multicolonna, l'ordine delle colonne nella definizione dell'oggetto statistiche influisce sull'efficacia delle densità per eseguire stime della cardinalità. L'oggetto statistiche archivia le densità per ciascun prefisso delle colonne chiave nella definizione dell'oggetto statistiche. Per altre informazioni sulle densità, vedere la sezione Densità in questo articolo.

Per creare densità utili per le stime della cardinalità, è necessario che le colonne nel predicato di query corrispondano a uno dei prefissi delle colonne nella definizione dell'oggetto statistiche. L'esempio seguente crea ad esempio un oggetto statistiche multicolonna per le colonne LastName, MiddleName e FirstName.

USE AdventureWorks2022;
GO

IF EXISTS (SELECT name
           FROM sys.stats
           WHERE name = 'LastFirst'
                 AND object_ID = OBJECT_ID('Person.Person'))
    DROP STATISTICS Person.Person.LastFirst;
GO

CREATE STATISTICS LastFirst
    ON Person.Person(LastName, MiddleName, FirstName);
GO

In questo esempio, l'oggetto statistiche LastFirst dispone delle densità per i prefissi di colonna seguenti: (LastName), (LastName, MiddleName) e (LastName, MiddleName, FirstName). La densità non è disponibile per (LastName, FirstName). Se la query usa LastName e FirstName senza usare MiddleName, la densità non è disponibile per le stime della cardinalità.

Query seleziona da un subset di dati

La creazione di statistiche per indici e colonne singole in Query Optimizer implica la creazione di statistiche per i valori in tutte le righe. Quando le query effettuano la selezione da un subset di righe che dispone di una distribuzione dei dati univoca, le statistiche filtrate possono migliorare i piani di query. È possibile creare statistiche filtrate usando l'istruzione CREATE STATISTICS con la clausola WHERE per definire l'espressione del predicato di filtro.

Ad esempio, usando AdventureWorks2025, ogni prodotto nella Production.Product tabella appartiene a una delle quattro categorie della Production.ProductCategory tabella: Bikes, Components, Clothinge Accessories. Ciascuna categoria presenta una distribuzione dei dati del peso diversa: i pesi delle biciclette vanno da 13,77 a 30,0, i pesi dei componenti vanno da 2,12 a 1050,00 con alcuni valori NULL, i pesi dell’abbigliamento sono tutti NULL e i pesi degli accessori sono anch’essi NULL.

Usando Bikes come esempio, le statistiche filtrate su tutti i pesi delle biciclette forniscono statistiche più accurate per l'ottimizzatore di interrogazioni e possono migliorare la qualità del piano di interrogazione rispetto alle statistiche su tutta la tabella o alle statistiche inesistenti nella colonna Peso. La colonna del peso delle biciclette è un buon candidato per le statistiche filtrate, ma non necessariamente per un indice filtrato se il numero di ricerche del peso è relativamente ridotto. È possibile che i vantaggi derivanti dai miglioramenti alle prestazioni delle ricerche offerti da un indice filtrato siano inferiori rispetto agli svantaggi derivanti dai costi di manutenzione e archiviazione supplementari dovuti all'aggiunta di un indice filtrato al database.

L'istruzione seguente crea le statistiche filtrate BikeWeights su tutte le sottocategorie di Bikes. L'espressione di predicato filtrata definisce le biciclette enumerando tutte le sottocategorie di biciclette con il confronto Production.ProductSubcategoryID IN (1,2,3). Il predicato non può usare il nome della categoria Bikes perché è archiviato nella tabella Production.ProductCategory e tutte le colonne nell'espressione di filtro devono trovarsi nella stessa tabella.

USE AdventureWorks2022;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

L'Query Optimizer può usare le statistiche filtrate di BikeWeights per migliorare il piano di query per la query seguente, che seleziona tutte le biciclette che pesano più di 25.

SELECT P.Weight AS Weight,
       S.Name AS BikeName
FROM Production.Product AS P
     INNER JOIN Production.ProductSubcategory AS S
         ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
      AND P.Weight > 25
ORDER BY P.Weight;
GO

L'interrogazione identifica le statistiche mancanti

Se un errore o un altro evento impedisce la creazione di statistiche da parte di Query Optimizer, il piano di query viene creato senza usare statistiche. Query Optimizer contrassegna le statistiche come mancanti e tenta di rigenerare le statistiche alla successiva esecuzione della query.

Le statistiche mancanti sono indicate come avvisi (nome tabella in rosso) quando il piano di esecuzione di una query viene visualizzato graficamente utilizzando SQL Server Management Studio. Il monitoraggio della classe di eventi Missing Column Statistics con SQL Server Profiler indica anche i casi in cui le statistiche risultano mancanti. Per altre informazioni, vedere Categoria di eventi Errori e avvisi (Motore di database).

In caso di statistiche mancanti, effettuare quanto segue:

• Verificare che AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS siano impostate su ON.
• Verificare che il database non sia di sola lettura. Se il database è di sola lettura, non è possibile salvare un nuovo oggetto statistiche.
• Creare le statistiche mancanti usando l'istruzione CREATE STATISTICS .

Statistiche temporanee

Quando le statistiche su uno snapshot o un database di sola lettura sono mancanti o non aggiornate, il motore di database crea e gestisce statistiche temporanee in tempdb. Quando il motore di database crea statistiche temporanee, al nome delle statistiche viene aggiunto il suffisso _readonly_database_statistic per differenziare le statistiche temporanee da quelle permanenti. Il suffisso _readonly_database_statistic è riservato alle statistiche generate dal motore di database. È possibile creare ed eseguire script per le statistiche temporanee in un database di lettura/scrittura. Quando viene creato uno script, Management Studio modifica il suffisso del nome delle statistiche da _readonly_database_statistic a _readonly_database_statistic_scripted.

Solo il motore di database può creare e aggiornare le statistiche temporanee. È tuttavia possibile eliminare le statistiche temporanee e monitorare le relative proprietà utilizzando gli stessi strumenti utilizzati per le statistiche permanenti:

• Eliminare le statistiche temporanee usando l'istruzione DROP STATISTICS .
• Monitorare le statistiche mediante le viste del catalogo sys.stats e sys.stats_columns. La vista dle catalogo del sistema sys.stats include la colonna is_temporary che indica quali statistiche sono permanenti e quali invece temporanee.

Poiché le statistiche temporanee vengono archiviate in tempdb, un riavvio del motore di database rimuove tutte le statistiche temporanee.

Analogamente a tutte le statistiche, la creazione e l'aggiornamento delle statistiche temporanee richiedono un blocco di modifica dello schema (Sch-M) per l'oggetto . Questo blocco potrebbe bloccare altre query e processi, incluso il processo di redo del sistema nelle repliche secondarie che applicano le transazioni dalla replica primaria. Se questo blocco influisce sui carichi di lavoro di query o sulla propagazione dei dati, è possibile disabilitare rispettivamente la creazione automatica e l'aggiornamento delle statistiche temporanee usando le READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATEREADABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE e .

Quando aggiornare le statistiche

Il Query Optimizer determina quando le statistiche potrebbero essere obsolete e quindi le aggiorna, quando necessario, per generare un piano di query. In alcuni casi, è possibile migliorare il piano di query e quindi migliorare le prestazioni delle query aggiornando le statistiche più frequentemente rispetto a quando AUTO_UPDATE_STATISTICS è ON. È possibile aggiornare le statistiche usando l'istruzione UPDATE STATISTICS o la stored procedure sp_updatestats.

L'aggiornamento delle statistiche garantisce che le query vengano compilate utilizzando statistiche aggiornate. L'aggiornamento delle statistiche tramite qualsiasi processo può causare la ricompilazione automatica dei piani di query. Non aggiornare manualmente le statistiche troppo frequentemente perché esiste un compromesso tra il miglioramento dei piani di query e il tempo necessario per ricompilare le query. Tale equilibrio dipende dall'applicazione in uso.

Quando si aggiornano le statistiche usando UPDATE STATISTICS o sp_updatestats, mantenere AUTO_UPDATE_STATISTICS impostato su ON in modo che Query Optimizer aggiorni regolarmente le statistiche.

• Per altre informazioni su come aggiornare le statistiche in una colonna, un indice, una tabella o una vista indicizzata, vedere UPDATE STATISTICS.

• Per informazioni su come aggiornare le statistiche per tutte le tabelle definite dall'utente e interne nel database, consultare la stored procedure sp_updatestats.

• Per altre informazioni sulle soglie degli aggiornamenti automatici delle statistiche, vedere opzione AUTO_UPDATE_STATISTICS.

Quando si imposta AUTO_UPDATE_STATISTICS su OFF, la ricompilazione del piano può comunque verificarsi per vari altri motivi, ma non si verifica automaticamente a causa degli aggiornamenti di statistiche non aggiornate. Quando si imposta su AUTO_UPDATE_STATISTICSOFF, gli aggiornamenti delle statistiche vengono eseguiti solo tramite altri processi pianificati manualmente, ad esempio i piani di manutenzione. Impostare AUTO_UPDATE_STATISTICS su OFF può quindi causare piani di query non ottimali e prestazioni delle query degradate.

Rilevare statistiche non aggiornate

Per determinare la data dell'ultimo aggiornamento delle statistiche, usare le funzioni sys.dm_db_stats_properties o STATS_DATE.

Aggiornare le statistiche nei seguenti casi:

• I tempi di esecuzione delle query sono lenti.
• Si verificano operazioni di inserimento in colonne chiave crescenti o decrescenti.
• In seguito a operazioni di manutenzione.

Per esempi di aggiornamento manuale delle statistiche, vedere UPDATE STATISTICS.

I tempi di esecuzione delle query sono lenti

Se i tempi di risposta alle query sono troppo lunghi o imprevedibili, assicurarsi che le query dispongano di statistiche aggiornate prima di eseguire ulteriori procedure di risoluzione dei problemi.

Si verificano operazioni di inserimento in colonne chiave crescenti o decrescenti

Le statistiche sulle colonne chiave in ordine crescente o decrescente, ad esempio IDENTITY o sulle colonne timestamp in tempo reale, potrebbero richiedere aggiornamenti delle statistiche più frequenti di quelli eseguiti da Query Optimizer. Le operazioni di inserimento accodano nuovi valori alle colonne crescenti o decrescenti. È possibile che il numero di righe aggiunte non sia sufficiente per attivare un aggiornamento delle statistiche. Se le statistiche non sono up-to-date e le query selezionano tra le righe aggiunte più di recente, le statistiche correnti non hanno stime di cardinalità per questi nuovi valori. Questa condizione può comportare stime di cardinalità imprecise e prestazioni lente delle query.

Ad esempio, una query che seleziona dalle date più recenti dell'ordine di vendita ha stime di cardinalità imprecise se le statistiche non vengono aggiornate per includere stime di cardinalità per le date degli ordini di vendita più recenti.

In seguito a operazioni di manutenzione

Valutare l'aggiornamento delle statistiche dopo aver eseguito procedure di manutenzione che modificano la distribuzione dei dati, ad esempio il troncamento di una tabella o l'esecuzione di un inserimento in blocco di una grande percentuale delle righe. L'aggiornamento proattivo delle statistiche può evitare ritardi futuri nell'elaborazione delle query mentre le query attendono gli aggiornamenti automatici delle statistiche.

Operazioni come la ricompilazione, la deframmentazione o la riorganizzazione di un indice non modificano la distribuzione dei dati. Non è quindi necessario aggiornare le statistiche dopo l'esecuzione ALTER INDEXdi operazioni REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE. Query Optimizer aggiorna le statistiche in seguito alla ricompilazione di un indice in una tabella o una vista mediante ALTER INDEX REBUILD o DBCC DBREINDEX. Tale aggiornamento delle statistiche è tuttavia il risultato della ricostruzione dell'indice. Query Optimizer non aggiorna le statistiche dopo operazioni DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE.

Tip

A partire da SQL Server 2016 (13.x) SP1 CU4, usare l'opzione PERSIST_SAMPLE_PERCENT di CREATE STATISTICS o UPDATE STATISTICS per impostare e mantenere una percentuale di campionamento specifica per gli aggiornamenti delle statistiche successivi che non specificano in modo esplicito una percentuale di campionamento.

Gestione automatica dell'indice e delle statistiche

Usare soluzioni intelligenti come la deframmentazione dell'indice adattativo per gestire automaticamente la deframmentazione dell'indice e gli aggiornamenti delle statistiche per uno o più database. Questa procedura sceglie automaticamente se ricompilare o riorganizzare un indice in base al livello di frammentazione, tra gli altri parametri e aggiorna le statistiche con una soglia lineare.

Determinare le statistiche usate da Query Optimizer

È possibile trovare gli oggetti statistiche usati da Query Optimizer quando compila una query esaminando un piano di esecuzione stimato o effettivo. Quando si esamina un piano di esecuzione, l'elemento OptimizerStatsUage contiene StatisticsInfo elementi che contengono informazioni sugli oggetti statistiche caricati da Query Optimizer durante la compilazione. Gli StatisticsInfo elementi contengono il nome dell'oggetto statistiche, il database, lo schema e la tabella a cui appartiene, il conteggio delle modifiche in fase di compilazione, la percentuale di campionamento e l'ultimo aggiornamento.

Usare una delle tecniche seguenti per esaminare il piano di esecuzione:

• In SQL Server Management Studio selezionare Includi piano di esecuzione effettivo (CTRL+M) prima di eseguire la query. Nella scheda Piano di esecuzione visualizzata con i risultati è possibile:
  • Fare clic con il pulsante destro del mouse all'interno del piano grafico e selezionare Mostra XML piano di esecuzione. Cercate l'elemento OptimizerStatsUsage e ciascun elemento figlio StatisticsInfo.
  • Selezionare l'operatore finale (più a sinistra). Nel caso di una SELECT query, questo operatore è un SELECT nodo. Nella finestra Proprietà espandere il nodo OptimizerStatsUsage e visualizzare le informazioni sugli oggetti statistiche usati nella query.
• Eseguire SETSET STATISTICS XML ON prima di eseguire la query. Selezionare il collegamento ipertestuale visualizzato con i risultati per visualizzare il codice XML del piano di esecuzione.
• Eseguire query sys.dm_exec_query_plan o sys.dm_exec_query_statistics_xml per le query recenti.
• Leggere un piano precedentemente acquisito da Query Store tramite sys.query_store_plan.

Ogni StatisticsInfo elemento è simile al frammento XML seguente di una query nel AdventureWorks2022 database di esempio:

<StatisticsInfo 
    Database="[AdventureWorks2022]" 
    Schema="[Sales]" 
    Table="[SalesOrderDetail]" 
    Statistics="[IX_SalesOrderDetail_ProductID]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2025-09-07T15:32:16.89" />

Attribute	Meaning
Database, Schema, Table	Oggetto a cui appartiene la statistica.
Statistics	Nome dell'oggetto statistiche nel database. Usare questo nome con DBCC SHOW_STATISTICS o sys.stats per esaminare l'istogramma e il vettore di densità.
ModificationCount	Numero di modifiche ai dati dall'ultimo aggiornamento della statistica, al momento della compilazione del piano. Un valore elevato rispetto alle dimensioni della tabella indica che la statistica non è aggiornata durante la compilazione.
SamplingPercent	Percentuale di righe campionate per compilare la statistica. I valori inferiori possono produrre istogrammi meno accurati per i dati asimmetrici.
LastUpdate	Timestamp dell'ultimo aggiornamento delle statistiche. Se l'opzione AUTO_UPDATE_STATISTICS è abilitata nel database, il database aggiorna automaticamente le statistiche quando necessario.

Note

StatisticsInfo riflette le statistiche disponibili e considerate durante la compilazione del piano. Se manca una voce StatisticsInfo per una colonna usata come filtro nella query, il Query Optimizer non ha identificato statistiche rilevanti, il che può causare prestazioni insufficienti.

Per controllare i conteggi correnti di aggiornamento e modifica per un oggetto statistica, utilizzare sys.dm_db_stats_properties. Ad esempio, la query seguente fornisce le metriche correnti per un oggetto statistica denominato IX_SalesOrderDetail_ProductID nella tabella Sales.SalesOrderDetail:

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    s.name                          AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')
  AND s.name = N'IX_SalesOrderDetail_ProductID';

Per determinare se le opzioni di creazione e aggiornamento automatiche del database corrente sono abilitate, utilizzare:

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE [name] = DB_NAME();

Query che utilizzano efficacemente le statistiche

Alcune implementazioni delle query, quali le variabili locali e le espressioni complesse nel predicato di query, possono comportare la definizione di piani di query non ottimali. Per evitare questi problemi, seguire le linee guida per la progettazione delle query per l'uso efficace delle statistiche. Per ulteriori informazioni sui predicati di query, consultare Condizione di ricerca.

È possibile migliorare i piani di query applicando le linee guida relative alla progettazione delle query che prevedono un utilizzo efficace delle statistiche, al fine di migliorare le stime della cardinalità per espressioni, variabili e funzioni usate nei predicati di query. Quando Query Optimizer non conosce il valore di un'espressione, di una variabile o di una funzione, non conosce il valore da cercare nell'istogramma e pertanto non può recuperare la stima della cardinalità migliore dall'istogramma. Invece, il Query Optimizer basa la stima della cardinalità sul numero medio di righe per ogni valore distinto per tutte le righe campionate nell'istogramma. Questa situazione porta a stime della cardinalità non ottimali e può danneggiare le prestazioni delle query. Per altre informazioni sugli istogrammi, vedere la sezione istogramma in questo articolo o sys.dm_db_stats_histogram.

Nelle linee guida seguenti viene indicato come scrivere query per migliorare i piani di query grazie all'ottimizzazione delle stime della cardinalità.

Migliorare le stime della cardinalità per le espressioni

Per migliorare le stime della cardinalità per le espressioni, attenersi alle seguenti linee guida:

• Quando possibile, semplificare le espressioni che contengono costanti. Query Optimizer non valuta tutte le funzioni e le espressioni che contengono costanti prima di determinare le stime della cardinalità. Semplificare ad esempio l'espressione ABS(-100) in 100.
• Se l'espressione utilizza più variabili, creare una colonna calcolata per l'espressione, quindi creare le statistiche o un indice nella colonna calcolata. È ad esempio possibile che la stima della cardinalità del predicato di query WHERE PRICE + Tax > 100 sia migliore se si crea una colonna calcolata per l'espressione Price + Tax.

Migliorare le stime della cardinalità per variabili e funzioni

Per migliorare le stime della cardinalità per variabili e funzioni, seguire queste linee guida:

• Se nel predicato di query viene utilizzata una variabile locale, riscrivere la query in modo da utilizzare un parametro anziché una variabile locale. Query Optimizer non conosce il valore di una variabile locale quando crea il piano di esecuzione della query. Quando una query usa un parametro, Query Optimizer usa la stima della cardinalità per il primo valore effettivo del parametro ricevuto dalla stored procedure.

• È consigliabile usare una tabella standard o una tabella temporanea per contenere i risultati delle funzioni con valori di tabella multistatement. Query Optimizer non crea statistiche per le funzioni con valori di tabella multistatement. Usando questo approccio, Query Optimizer può creare statistiche sulle colonne della tabella e usarle per creare un piano di query migliore.

• Utilizzare una tabella standard o una tabella temporanea in sostituzione delle variabili di tabella. Query Optimizer non crea statistiche per le variabili di tabella. Usando questo approccio, Query Optimizer può creare statistiche sulle colonne della tabella e usarle per creare un piano di query migliore. Ci sono dei compromessi nel decidere se usare una tabella temporanea o una variabile di tabella. Le variabili di tabella usate nelle stored procedure causano un minor numero di ricompilazione della stored procedure rispetto alle tabelle temporanee. In base all'applicazione in uso, è possibile che l'utilizzo di una tabella temporanea al posto di una variabile di tabella non comporti un miglioramento delle prestazioni.

• Se una stored procedure contiene una query che utilizza un parametro passato, evitare di modificare il valore del parametro all'interno della stored procedure prima di utilizzarlo nella query. Le stime della cardinalità per la query sono basate sul valore del parametro passato, non sul valore aggiornato. Per evitare di modificare il valore del parametro, è possibile riscrivere la query in modo da utilizzare due stored procedure.

  Ad esempio, la procedura memorizzata seguente Sales.GetRecentSales modifica il valore del parametro @date quando @date è NULL.

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

  Se la prima chiamata alla stored procedure Sales.GetRecentSales passa un NULL per il parametro @date, il Query Optimizer compila la stored procedure con la stima della cardinalità per @date = NULL anche se il predicato di query non è chiamato con @date = NULL. Questa stima della cardinalità potrebbe essere significativamente diversa dal numero di righe nel risultato effettivo della query. Query Optimizer potrebbe quindi scegliere un piano di query non ottimale. Per evitare questo problema, è possibile riscrivere la stored procedure in due procedure come indicato di seguito:

  USE AdventureWorks2022;
  GO
  
  IF OBJECT_ID('Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNullRecentSales
  @date DATETIME
  AS
  BEGIN
      IF @date IS NULL
          SET @date = DATEADD(MONTH, -3,
              (SELECT MAX(ORDERDATE)
              FROM Sales.SalesOrderHeader));
      EXECUTE Sales.GetNonNullRecentSales @date;
  END
  GO
  
  IF OBJECT_ID('Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  
  CREATE PROCEDURE Sales.GetNonNullRecentSales
  @date DATETIME
  AS
  BEGIN
      SELECT *
      FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
      WHERE h.SalesOrderID = d.SalesOrderID
          AND h.OrderDate > @date;
  END
  GO
  

Migliorare le stime della cardinalità con gli hint di query

Per migliorare le stime di cardinalità per le variabili locali, utilizzare gli hint di query OPTIMIZE FOR <value> o OPTIMIZE FOR UNKNOWN con RECOMPILE. Per ulteriori informazioni, vedere i suggerimenti di query .

In alcune applicazioni, la ricompilazione della query ogni volta che viene eseguita può richiedere tempi troppo lunghi. L'hint per la query OPTIMIZE FOR può risultare utile anche se non si usa l'opzione RECOMPILE. È possibile ad esempio aggiungere un'opzione OPTIMIZE FOR alla stored procedure Sales.GetRecentSales per indicare una data specifica. Nell'esempio seguente viene aggiunta l'opzione OPTIMIZE FOR alla procedura Sales.GetRecentSales.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO

CREATE PROCEDURE Sales.GetRecentSales
@date DATETIME
AS
BEGIN
    IF @date IS NULL
        SET @date = DATEADD(MONTH, -3,
            (SELECT MAX(ORDERDATE)
            FROM Sales.SalesOrderHeader));
    SELECT *
    FROM Sales.SalesOrderHeader AS h, Sales.SalesOrderDetail AS d
    WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate > @date
    OPTION (OPTIMIZE FOR (@date = '2004-05-01 00:00:00.000'));
END
GO

Migliorare le stime di cardinalità con le guide di piano

Per alcune applicazioni, le linee guida per la progettazione delle query potrebbero non essere applicabili perché non è possibile modificare la query o l'hint per la query RECOMPILE potrebbe causare troppe ricompilazioni. Usare le guide del piano per specificare altri hint, ad esempio USE PLAN, per controllare il comportamento della query durante l'analisi delle modifiche apportate all'applicazione con il fornitore dell'applicazione. Per altre informazioni sulle guide di piano, vedere Guide di piano.

Nel database SQL di Azure considerare la possibilità di usare, anziché le guide di piano, gli hint di Query Store per forzare i piani. Per altre informazioni, vedere Suggerimenti di Query Store.

Contenuti correlati

• Statistiche per tabelle con ottimizzazione per la memoria
• CREATE STATISTICS (Transact-SQL)
• UPDATE UPDATE STATISTICS (Transact-SQL)
• sys.sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• ALTER DATABASE SET opzioni (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• Creare indici filtrati
• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)
• Adaptive Index Defrag (Deframmentazione dell'indice adattativo)