CREATE STATISTICS (Transact-SQL)

Articolo
05/23/2024

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Endpoint di analisi SQL in Microsoft Fabric Warehouse 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 migliorare le prestazioni delle query.

Per altre informazioni, vedere Statistiche.

Nota

Per altre informazioni sulle statistiche in Microsoft Fabric, vedere Statistiche nel data warehousing di Fabric.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di Azure e Istanza gestita di SQL di Azure.

-- 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_constant ]

Sintassi per Azure Synapse Analytics and Analytics Platform System (PDW).

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 | = | <> | != | > | >= | !> | < | <= | !<

Sintassi per 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, vista indicizzata o tabella esterna in 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:

Non è possibile specificare colonne xml, full-text e FILESTREAM.
Le colonne calcolate possono essere specificate solo se le impostazioni del ARITHABORT database e QUOTED_IDENTIFIER sono 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 di filtro usa una logica di confronto semplice e non può fare riferimento a una colonna calcolata, a una colonna definito dall'utente, a una colonna di tipo di dati spaziale o a una colonna di tipo di dati hierarchyID . I confronti che usano NULL valori letterali non sono consentiti con gli operatori di confronto. Usare invece 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 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 e versioni successive

Consente di calcolare le statistiche analizzando tutte le righe. FULLSCAN e SAMPLE 100 PERCENT hanno gli stessi risultati. FULLSCAN non può essere usato 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 statistiche basate su colonna singola FULLSCAN e su colonna singola SAMPLE. Quando non è inclusa alcuna opzione, SAMPLE vengono create statistiche.

SAMPLE number { PERCENT | ROWS }

Specifica la percentuale approssimativa o il numero di righe nella tabella o nella vista indicizzata per Query Optimizer da usare quando crea statistiche. Per PERCENT, il numero può essere compreso tra 0 e 100 e per ROWS, il numero 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 casi speciali in cui il piano di query, in base al campionamento predefinito, non è ottimale. Nella maggior parte dei casi, non è necessario specificare SAMPLE perché Query Optimizer usa già il campionamento e determina le dimensioni del campione statisticamente significative per impostazione predefinita, come necessario per creare piani di query di alta qualità.

SAMPLE non può essere usato con l'opzione FULLSCAN. Quando SAMPLE o FULLSCAN non vengono specificati, Query Optimizer usa 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 statistiche basate su colonna singola FULLSCAN e su colonna singola SAMPLE. Quando non è inclusa alcuna opzione, FULLSCAN vengono create statistiche.

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.

Nota

Se la tabella viene troncata, per tutte le statistiche compilate in base alla struttura HoBT viene ripristinato l'uso della percentuale di campionamento predefinita.

STATS_STREAM = stats_stream

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

NORECOMPUTE

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 e quindi eseguire CREATE STATISTICS senza l'opzione NORECOMPUTE .

Avviso

Se si disabilita l'aggiornamento automatico delle statistiche, è possibile impedire a Query Optimizer di scegliere piani di esecuzione ottimali per le query che coinvolgono la tabella. È consigliabile usare questa opzione con moderazione e solo da un amministratore di database qualificato.

Per altre informazioni sull'opzione AUTO_STATISTICS_UPDATE , vedere Opzioni ALTER DATABASE SET. 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

Quando ON, le statistiche create sono per statistiche di partizione. Quando OFF, le statistiche vengono 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 tipi di statistiche seguenti:

Statistiche create con indici il cui partizionamento non è 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 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 e versioni successive

Esegue l'override dell'opzione di configurazione max degree of parallelism durante l'operazione di statistica. Per altre informazioni, vedere Configurare il massimo grado di parallelismo (opzione di configurazione del server). Usare MAXDOP per limitare il numero di processori usati in un'esecuzione di piano parallela. Il valore massimo è 64 processori.

max_degree_of_parallelism può essere:

1: elimina la generazione di piani paralleli.
>1: limita il numero massimo di processori utilizzati in un'operazione di indice parallelo al numero specificato.
0 (impostazione predefinita): usa il numero effettivo di processori o meno in base al carico di lavoro del sistema corrente.

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: SQL Server 2022 (16.x) e versioni successive e database SQL di Azure, Istanza gestita di SQL di Azure

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 AUTO_DROP proprietà 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.

Nota

Il tentativo di impostare o annullare l'impostazione della proprietà Auto_Drop sulle statistiche create automaticamente potrebbe generare errori. Per le statistiche create automaticamente è sempre prevista l'opzione di eliminazione automatica. Alcuni backup, se ripristinati, potrebbero avere questa proprietà impostata in modo non corretto fino alla successiva aggiornamento dell'oggetto statistiche (manualmente o automaticamente). Tuttavia, le statistiche con eliminazione automatica si comportano sempre come statistiche create automaticamente. 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à delle statistiche AUTO_DROP .

Per altre informazioni, vedere opzione AUTO_DROP.

Autorizzazioni

Richiede una di queste autorizzazioni:

ALTER TABLE
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 si usa 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. Prendere in considerazione le operazioni eseguite sulle colonne della tabella prima di creare statistiche filtrate. Non è possibile eliminare, rinominare o modificare la definizione di una colonna di tabella definita in un predicato delle statistiche filtrate.

Limiti

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 STATS_STREAMle opzioni , ROWCOUNTe PAGECOUNT .
L'opzione MAXDOP è limitata dall'impostazione MAX_DOP del gruppo di carico di lavoro di Resource Governor, se in uso.
CREATEe DROP STATISTICS nelle tabelle esterne non sono supportate in database SQL di Azure.

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

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. CREATEe DROP STATISTICS nelle tabelle esterne non sono supportate in 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

Nell'esempio seguente vengono create le NamePurchase statistiche per tutte le righe della BusinessEntityID tabella e Person EmailPromotion e viene impostata una percentuale di campionamento del 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;

Condividi tramite

CREATE STATISTICS (Transact-SQL)

Sintassi

Argomenti

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Autorizzazioni

Osservazioni:

Statistiche per le tabelle esterne

Statistiche con una condizione filtrata

Quando usare CREATE STATISTICS

Dipendenze di riferimento per le statistiche filtrate

Limiti

Esempi

R. Usare CREATE STATISTICS con il numero DI CAMPIONE PERCENT

B. Usare CREATE STATISTICS con FULLSCAN e NORECOMPUTE

C. Usare CREATE STATISTICS per creare statistiche filtrate

D. Creare statistiche per una tabella esterna

E. Usare CREATE STATISTICS con FULLSCAN e PERSIST_SAMPLE_PERCENT

Esempi che usano il database AdventureWorksDW

F. Creare statistiche per due colonne

G. Creare statistiche tramite un'analisi completa

H. Creare statistiche specificando la percentuale di campionamento

I. Usare CREATE STATISTICS con AUTO_DROP

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

CREATE STATISTICS (Transact-SQL)

Sintassi

Argomenti

statistics_name

table_or_indexed_view_name

column [ ,...n]

WHERE <filter_predicate>

FULLSCAN

SAMPLE number { PERCENT | ROWS }

PERSIST_SAMPLE_PERCENT = { ON | OFF }

STATS_STREAM = stats_stream

NORECOMPUTE

INCREMENTAL = { ON | OFF }

MAXDOP = max_degree_of_parallelism

update_stats_stream_option

AUTO_DROP = { ON | OFF }

Autorizzazioni

Osservazioni:

Statistiche per le tabelle esterne

Statistiche con una condizione filtrata

Quando usare CREATE STATISTICS

Dipendenze di riferimento per le statistiche filtrate

Limiti

Esempi

R. Usare CREATE STATISTICS con il numero DI CAMPIONE PERCENT

B. Usare CREATE STATISTICS con FULLSCAN e NORECOMPUTE

C. Usare CREATE STATISTICS per creare statistiche filtrate

D. Creare statistiche per una tabella esterna

E. Usare CREATE STATISTICS con FULLSCAN e PERSIST_SAMPLE_PERCENT

Esempi che usano il database AdventureWorksDW

F. Creare statistiche per due colonne

G. Creare statistiche tramite un'analisi completa

H. Creare statistiche specificando la percentuale di campionamento

I. Usare CREATE STATISTICS con AUTO_DROP

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive