CREATE STATISTICS (Transact-SQL)

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Endpoint di analisi SQL di Azure Synapse Analyticsin Microsoft FabricWarehouse in Microsoft Fabric

Crea statistiche di ottimizzazione query per una o più colonne di una tabella o di una vista indicizzata o in una tabella esterna. 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 con CREATE STATISTICS o modificare la progettazione delle query per ottenere prestazioni migliori di esecuzione delle query.

Per altre informazioni, vedere Statistiche.

Convenzioni di sintassi Transact-SQL

Sintassi

-- Syntax for SQL Server and Azure SQL Database
-- Create statistics on an external table

CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WITH FULLSCAN ] ;
  
-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ ,...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
    ] ;
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<
    
<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_contant ]

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse 
  
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ ,...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]
  
<filter_predicate> ::=
    <conjunct> [AND <conjunct>]
  
<conjunct> ::=
    <disjunct> | <comparison>
  
<disjunct> ::=
        column_name IN (constant ,...)
  
<comparison> ::=
        column_name <comparison_op> constant
  
<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

-- Syntax for Microsoft Fabric
CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[;]

Argomenti

statistics_name

Nome delle statistiche da creare.

table_or_indexed_view_name

Nome della tabella, della vista indicizzata o della tabella esterna su cui creare le statistiche. Per creare statistiche su un altro database, specificare un nome di tabella completo.

column [ ,...n]

Una o più colonne da includere nelle statistiche. Le colonne devono essere in ordine di priorità da sinistra a destra. Per creare l'istogramma viene usata solo la prima colonna. Per le statistiche di correlazione tra colonne, denominate densità, vengono usate tutte le colonne.

È possibile specificare qualsiasi colonna utilizzabile come colonna chiave di indice, con le eccezioni seguenti:

• Impossibile specificare colonne XML, full-text e FILESTREAM.

• È possibile specificare colonne calcolate solo se le opzioni del database ARITHABORT e QUOTED_IDENTIFIER sono impostate su ON.

• È possibile specificare colonne di tipo CLR definito dall'utente se il tipo supporta l'ordinamento binario. È possibile specificare colonne calcolate definite come chiamate di metodi da una colonna con tipo definito dall'utente se tali metodi sono contrassegnati come deterministici.

WHERE <filter_predicate>

Specifica un'espressione per la selezione di un subset di righe da includere durante la creazione dell'oggetto statistiche. Le statistiche create con un predicato del filtro vengono definite statistiche filtrate. Il predicato del filtro usa una logica di confronto semplice e non può fare riferimento a una colonna calcolata, con tipo definito dall'utente (UDT), con tipo di dati spaziale o con tipo di dati hierarchyID. I confronti in cui vengono utilizzati valori letterali NULL non sono consentiti con gli operatori di confronto. In alternativa, utilizzare gli operatori IS NULL e IS NOT NULL.

Di seguito sono riportati alcuni esempi di predicati di filtro per la tabella Production.BillOfMaterials:

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Per altre informazioni sui predicati di filtro, vedere Creare indici filtrati.

FULLSCAN

Si applica a: SQL Server, a partire da SQL Server 2016 (13.x) SP1 CU4 e versioni successive, a partire da SQL Server 2017 (14.x) CU1

Consente di calcolare le statistiche analizzando tutte le righe. FULLSCAN e SAMPLE 100 PERCENT generano gli stessi risultati. Non è possibile utilizzare FULLSCAN con l'opzione SAMPLE.

Se omesso, SQL Server usa il campionamento per creare le statistiche e determina le dimensioni del campione necessarie per creare un piano di query di alta qualità.

In Warehouse in Microsoft Fabric sono supportate solo le statistiche basate su SAMPLE a colonna singola e FULLSCAN a colonna singola. Quando non è inclusa alcuna opzione, vengono create statistiche FULLSCAN.

SAMPLE number { PERCENT | ROWS }

Specifica la percentuale approssimativa o il numero di righe presenti nella tabella o nella vista indicizzata utilizzate da Query Optimizer durante la creazione delle statistiche. Per PERCENT, number può essere compreso tra 0 e 100, mentre per ROWS number può essere compreso tra 0 e il numero totale di righe. La percentuale effettiva o il numero di righe campionate da Query Optimizer potrebbero non corrispondere alla percentuale o al numero specificato. Query Optimizer analizza ad esempio tutte le righe in una pagina di dati.

SAMPLE è utile per i casi speciali in cui il piano di query, basato sul campionamento predefinito, non è ottimale. Nella maggior parte delle situazioni, non è necessario specificare SAMPLE perché Query Optimizer utilizza già il campionamento e determina le dimensioni del campione statisticamente significative per impostazione predefinita, come richiesto per creare piani di query di alta qualità.

Questa opzione non può essere utilizzata quando viene specificata l'opzione FULLSCAN. Se non si specifica né SAMPLE né FULLSCAN, Query Optimizer utilizza i dati campionati e calcola le dimensioni del campione per impostazione predefinita.

È consigliabile non specificare 0 PERCENT o 0 ROWS. Quando 0 PERCENT o 0 ROWS viene specificato, viene creato l'oggetto statistiche, ma non contiene dati statistici.

In Warehouse in Microsoft Fabric sono supportate solo le statistiche basate su SAMPLE a colonna singola e FULLSCAN a colonna singola. Quando non è inclusa alcuna opzione, vengono create statistiche FULLSCAN.

PERSIST_SAMPLE_PERCENT = { ON | OFF }

Quando on, le statistiche mantengono la percentuale di campionamento di creazione per gli aggiornamenti successivi che non specificano in modo esplicito una percentuale di campionamento. Quando OFF, la percentuale di campionamento delle statistiche viene reimpostata sul campionamento predefinito negli aggiornamenti successivi che non specificano in modo esplicito una percentuale di campionamento. Il valore predefinito è OFF.

STATS_STREAM = stats_stream

Identificato solo a scopo informativo. Non supportato. Non è garantita la compatibilità con le versioni future.

NORECOMPUTE

Consente di disabilitare l'opzione di aggiornamento automatico delle statistiche AUTO_STATISTICS_UPDATE per statistics_name. Se viene specificata questa opzione, Query Optimizer completa gli aggiornamenti delle statistiche in corso per statistics_name e disabilita gli aggiornamenti futuri.

Per riabilitare gli aggiornamenti delle statistiche, rimuovere le statistiche con DROP STATISTICS ed eseguire CREATE STATISTICS senza l'opzione NORECOMPUTE.

Per altre informazioni sull'opzione AUTO_STATISTICS_UPDATE, vedere Opzioni ALTER DATABASE SET (Transact-SQL). Per altre informazioni sulla disabilitazione e sulla riabilitazione degli aggiornamenti delle statistiche, vedere Statistiche.

INCREMENTAL = { ON | OFF }

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

Se è specificato ON, le statistiche create sono statistiche per partizione. Se OFF, le statistiche sono combinate per tutte le partizioni. Il valore predefinito è OFF.

Se le statistiche per partizione non sono supportate, viene generato un errore. Le statistiche incrementali non sono supportate per i seguenti tipi di statistiche:

• Statistiche create con indici che non hanno il partizionamento allineato con la 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 per le viste.
• Statistiche create per le tabelle interne.
• Statistiche create con indici spaziali o indici XML.

MAXDOP = max_degree_of_parallelism

Si applica a: SQL Server, a partire da SQL Server 2016 (13.x) SP2 e SQL Server 2017 (14.x) CU3.

Esegue l'override dell'opzione di configurazione max_degree_of_parallelism per la durata dell'operazione statistica. Per altre informazioni, vedere Configurare l'opzione di configurazione del server max degree of parallelism. Utilizzare MAXDOP per limitare il numero di processori utilizzati durante l'esecuzione di un piano parallelo. Il valore massimo è 64 processori.

max_degree_of_parallelism può essere:

1
Disattiva la generazione di piani paralleli.

>1
Consente di limitare al valore specificato, o a un valore più basso in base al carico di lavoro corrente del sistema, il numero massimo di processori usati in un'operazione parallela statistica.

0 (predefinito)
Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

update_stats_stream_option

Identificato solo a scopo informativo. Non supportato. Non è garantita la compatibilità con le versioni future.

AUTO_DROP = { ON | OFF }

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

Prima di SQL Server 2022 (16.x), se le statistiche vengono create manualmente da un utente o da uno strumento di terze parti in un database utente, tali oggetti statistiche possono bloccare o interferire con le modifiche dello schema che il cliente potrebbe desiderare.

A partire da SQL Server 2022 (16.x), l'opzione AUTO_DROP è abilitata per impostazione predefinita in tutti i database nuovi ed migrati. La proprietà AUTO_DROP consente la creazione di oggetti statistiche in una 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 AUTO_DROP abilitato si comportano come le statistiche create automaticamente.

Per altre informazioni, vedere opzione AUTO_DROP.

Autorizzazioni

Richiede una di queste autorizzazioni:

• MODIFICA TABELLA
• L'utente è il proprietario della tabella
• Appartenenza al ruolo predefinito del database db_ddladmin

Osservazioni:

SQL Server può usare tempdb per ordinare le righe campionate prima di compilare statistiche.

Statistiche per le tabelle esterne

Quando si creano statistiche per le tabelle esterne, SQL Server importa la tabella esterna in una tabella di SQL Server temporanea e quindi crea le statistiche. Per le statistiche di campioni, vengono importate solo le righe campionate. Se si dispone di una tabella esterna di grandi dimensioni, è più veloce usare il campionamento predefinito anziché l'opzione analisi completa.

Quando la tabella esterna usa DELIMITEDTEXT, CSV, PARQUETo DELTA come tipi di dati, le tabelle esterne supportano solo le statistiche per una colonna per CREATE STATISTICS comando.

Statistiche con una condizione filtrata

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 nella clausola WHERE per selezionare il subset di dati incluso nelle statistiche.

Quando usare CREATE STATISTICS

Per altre informazioni sulle situazioni in cui usare CREATE STATISTICS, vedere Statistiche.

Dipendenze di riferimento per le statistiche filtrate

La vista del catalogo sys.sql_expression_dependencies registra ogni colonna nel predicato delle statistiche filtrate come dipendenza di riferimento. Tenere presente le operazioni eseguite sulle colonne della tabella prima di creare statistiche filtrate poiché non è possibile eliminare, rinominare o modificare la definizione di una colonna della tabella definita in un predicato delle statistiche filtrate.

Limitazioni e restrizioni

• L'aggiornamento delle statistiche non è supportato per le tabelle esterne. Per aggiornare le statistiche in una tabella esterna, eliminare e ricreare le statistiche.
• È possibile elencare fino a 64 colonne per ogni oggetto statistiche.
• L'opzione MAXDOP non è compatibile con le opzioni STATS_STREAM, ROWCOUNT e PAGECOUNT.
• L'opzione MAXDOP è limitata dall'impostazione MAX_DOP del gruppo di carico di lavoro di Resource Governor, se in uso.
• CREATE e DROP STATISTICS su tabelle esterne non sono supportate nel database SQL di Azure.

Esempi

Gli esempi usano il AdventureWorks database.

R. Usare CREATE STATISTICS con il numero DI CAMPIONE PERCENT

Nell'esempio seguente vengono create le ContactMail1 statistiche usando un campione casuale del 5% delle BusinessEntityID colonne e EmailPromotion della Person tabella del database AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Usare CREATE STATISTICS con FULLSCAN e NORECOMPUTE

Nell'esempio seguente vengono create le statistiche NamePurchase per tutte le righe nelle colonne BusinessEntityID e EmailPromotion della tabella Person e viene disabilitato il ricalcolo automatico delle statistiche.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Usare CREATE STATISTICS per creare statistiche filtrate

Nell'esempio seguente vengono create le statistiche filtrate ContactPromotion1. Il motore di database usa come campione il 50% dei dati, quindi seleziona le righe in cui EmailPromotion è uguale a 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Creare statistiche per una tabella esterna

L'unica decisione necessaria quando si creano statistiche per una tabella esterna, oltre a specificare l'elenco delle colonne, è se creare le statistiche tramite campionamento delle righe o tramite l'analisi di tutte le righe. CREATE e DROP STATISTICS su tabelle esterne non sono supportate nel database SQL di Azure.

Poiché SQL Server importa dati dalla tabella esterna in una tabella temporanea per creare statistiche, l'opzione di analisi completa richiede molto più tempo. Per una tabella di grandi dimensioni, il metodo predefinito, corrispondente al campionamento, è in genere sufficiente.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);
  
--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Usare CREATE STATISTICS con FULLSCAN e PERSIST_SAMPLE_PERCENT

L'esempio seguente crea le statistiche NamePurchase per tutte le righe nelle colonne BusinessEntityID e EmailPromotion della tabella Person e imposta una percentuale di campionamento pari al 100% per tutti gli aggiornamenti successivi che non specificano in modo esplicito una percentuale di campionamento.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Esempi che usano il database AdventureWorksDW

F. Creare statistiche per due colonne

L'esempio seguente crea le statistiche CustomerStats1 in base alle colonne CustomerKey e EmailAddress della tabella DimCustomer. Le statistiche vengono create in base a un campione statisticamente significativo delle righe nella tabella Customer.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Creare statistiche tramite un'analisi completa

L'esempio seguente crea le statistiche CustomerStatsFullScan in base all'analisi di tutte le righe della tabella DimCustomer.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Creare statistiche specificando la percentuale di campionamento

L'esempio seguente crea le statistiche CustomerStatsSampleScan in base all'analisi del 50% delle righe della tabella DimCustomer.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Usare CREATE STATISTICS con AUTO_DROP

Per usare le statistiche di rilascio automatico, è sufficiente aggiungere quanto segue alla clausola "WITH" delle statistiche create o aggiornate.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

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

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

Passaggi successivi

• Statistica
• Statistiche in Microsoft Fabric
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)