Statistiche della tabella per il pool SQL dedicato in Azure sinapsi AnalyticsTable statistics for dedicated SQL pool in Azure Synapse Analytics

In questo articolo sono disponibili indicazioni ed esempi per la creazione e l'aggiornamento delle statistiche di ottimizzazione delle query nelle tabelle nel pool SQL dedicato.In this article, you'll find recommendations and examples for creating and updating query-optimization statistics on tables in dedicated SQL pool.

Perché usare le statistiche?Why use statistics

Il pool SQL più dedicato conosce i dati, più rapidamente può eseguire query su di esso.The more dedicated SQL pool knows about your data, the faster it can execute queries against it. Dopo il caricamento dei dati in un pool SQL dedicato, la raccolta delle statistiche sui dati è una delle operazioni più importanti che è possibile eseguire per ottimizzare le query.After loading data into dedicated SQL pool, collecting statistics on your data is one of the most important things you can do to optimize your queries.

Il Query Optimizer del pool SQL dedicato è un ottimizzatore basato sui costi.The dedicated SQL pool query optimizer is a cost-based optimizer. Esegue un confronto fra i costi dei vari piani di query e poi sceglie quello che costa meno,It compares the cost of various query plans, and then chooses the plan with the lowest cost. che in molti casi è il piano eseguito più velocemente.In most cases, it chooses the plan that will execute the fastest.

Se, ad esempio, l'ottimizzatore stima che la data in base alla quale si sta filtrando la query restituirà una riga, verrà scelto un piano.For example, if the optimizer estimates that the date your query is filtering on will return one row it will choose one plan. Se invece stima che la data selezionata restituirà un milione righe, verrà restituito un piano diverso.If it estimates that the selected date will return 1 million rows, it will return a different plan.

Creazione automatica di statisticheAutomatic creation of statistic

Quando l'opzione database AUTO_CREATE_STATISTICS è on, il pool SQL dedicato analizza le query utente in ingresso per le statistiche mancanti.When the database AUTO_CREATE_STATISTICS option is on, dedicated SQL pool analyzes incoming user queries for missing statistics.

Se non sono presenti, Query Optimizer creerà statistiche su singole colonne nel predicato della query o nella condizione di join per migliorare le stime di cardinalità del piano di query.If statistics are missing, the query optimizer creates statistics on individual columns in the query predicate or join condition to improve cardinality estimates for the query plan.

Nota

Per impostazione predefinita, la creazione automatica di statistiche è attiva.Automatic creation of statistics is currently turned on by default.

È possibile verificare se il pool SQL dedicato è AUTO_CREATE_STATISTICS configurato eseguendo il comando seguente:You can check if your dedicated SQL pool has AUTO_CREATE_STATISTICS configured by running the following command:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Se il pool SQL dedicato non ha AUTO_CREATE_STATISTICS configurato, è consigliabile abilitare questa proprietà eseguendo il comando seguente:If your dedicated SQL pool doesn't have AUTO_CREATE_STATISTICS configured, we recommend you enable this property by running the following command:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Queste istruzioni attiveranno la creazione automatica delle statistiche:These statements will trigger automatic creation of statistics:

  • SELECTSELECT
  • INSERT SELECTINSERT-SELECT
  • CTASCTAS
  • UPDATEUPDATE
  • DELETEDELETE
  • EXPLAIN quando contengono un join o viene rilevata la presenza di un predicatoEXPLAIN when containing a join or the presence of a predicate is detected

Nota

L'opzione di creazione automatica di statistiche non crea statistiche in tabelle temporanee o esterne.Automatic creation of statistics are not created on temporary or external tables.

La creazione automatica di statistiche viene generata in modo sincrono; se nelle colonne non sono presenti tutte le statistiche, quindi, è possibile che si verifichi un leggero peggioramento delle prestazioni delle query.Automatic creation of statistics is done synchronously so you may incur slightly degraded query performance if your columns are missing statistics. Il tempo necessario per creare le statistiche relative a una colonna dipende dalle dimensioni della tabella.The time to create statistics for a single column depends on the size of the table.

Per evitare una riduzione delle prestazioni, verificare che le statistiche siano già state create eseguendo il carico di lavoro di benchmark prima della profilatura del sistema.To avoid measurable performance degradation, you should ensure stats have been created first by executing the benchmark workload before profiling the system.

Nota

La creazione di statistiche verrà registrata sys.dm_pdw_exec_requests in un contesto utente diverso.The creation of stats will be logged in sys.dm_pdw_exec_requests under a different user context.

Le statistiche automatiche create sono nel formato: WA_Sys<id colonna a 8 cifre in hex><id tabella a 8 cifre in hex>.When automatic statistics are created, they will take the form: WA_Sys<8 digit column id in Hex><8 digit table id in Hex>. È possibile visualizzare le statistiche che sono già state create eseguendo il comando DBCC SHOW_STATISTICS :You can view stats that have already been created by running the DBCC SHOW_STATISTICS command:

DBCC SHOW_STATISTICS (<table_name>, <target>)

Il table_name è il nome della tabella che contiene le statistiche da visualizzare.The table_name is the name of the table that contains the statistics to display. Questa tabella non può essere una tabella esterna.This table can't be an external table. La destinazione è il nome dell'indice di destinazione, delle statistiche o della colonna per cui visualizzare le informazioni statistiche.The target is the name of the target index, statistics, or column for which to display statistics information.

Aggiornare le statisticheUpdate statistics

Una procedura consigliata consiste nell'aggiornare le statistiche sulle colonne data ogni giorno quando vengono aggiunte nuove date.One best practice is to update statistics on date columns each day as new dates are added. Ogni volta che vengono caricate nuove righe nel pool SQL dedicato, vengono aggiunte nuove date di caricamento o date di transazione.Each time new rows are loaded into the dedicated SQL pool, new load dates or transaction dates are added. Queste aggiunte modificano la distribuzione dei dati e rendono le statistiche obsolete.These additions change the data distribution and make the statistics out of date.

È possibile che non sia mai necessario aggiornare le statistiche relative a una colonna Country/Region in una tabella Customer perché la distribuzione dei valori non cambia in genere.Statistics on a country/region column in a customer table might never need to be updated since the distribution of values doesn't generally change. Supponendo che la distribuzione sia costante tra i clienti, l'aggiunta di nuove righe alla variazione di tabella non modificherà la distribuzione dei dati.Assuming the distribution is constant between customers, adding new rows to the table variation isn't going to change the data distribution.

Tuttavia, se il pool SQL dedicato contiene solo un paese e si importano i dati da un nuovo paese/area geografica, causando l'archiviazione di dati da più paesi o aree geografiche, è necessario aggiornare le statistiche nella colonna paese/area geografica.However, if your dedicated SQL pool only contains one country/region, and you bring in data from a new country/region, resulting in data from multiple countries/regions being stored, then you need to update statistics on the country/region column.

Di seguito sono forniti alcuni elementi consigliati per l'aggiornamento delle statistiche:The following are recommendations updating statistics:

Frequenza degli aggiornamenti delle statisticheFrequency of stats updates Conservativa: GiornalieraConservative: Daily
Dopo il caricamento o la trasformazione dei datiAfter loading or transforming your data
CampionamentoSampling Se inferiore a 1 miliardo di righe, usare il campionamento predefinito (20%).Less than 1 billion rows, use default sampling (20 percent).
Se superiore a 1 miliardo righe, usare il campionamento del 2%.With more than 1 billion rows, use sampling of two percent.

Quando si risolvono i problemi di una query è essenziale verificare prima di tutto che le statistiche siano aggiornate.One of the first questions to ask when you're troubleshooting a query is, "Are the statistics up to date?"

Questa verifica non può essere basata sulla data di creazione dei dati.This question isn't one that can be answered by the age of the data. Un oggetto statistiche aggiornato può essere vecchio se non sono state apportate modifiche sostanziali ai dati sottostanti.An up-to-date statistics object might be old if there's been no material change to the underlying data. È necessario aggiornare le statistiche quando vengono apportate modifiche importanti al numero di righe o modifiche sostanziali alla distribuzione dei valori per una colonna specifica.When the number of rows has changed substantially, or there is a material change in the distribution of values for a column, then it's time to update statistics.

Non esiste alcuna vista a gestione dinamica per determinare se i dati all'interno della tabella sono cambiati dall'ultimo aggiornamento delle statistiche.There is no dynamic management view to determine if data within the table has changed since the last time statistics were updated. Le due query seguenti consentono di determinare se le statistiche non sono aggiornate.The following two queries can help you determine whether your statistics are stale.

Query 1: Individuare la differenza tra il conteggio delle righe dalle statistiche (stats_row_count) e il conteggio delle righe effettivo (actual_row_count).Query 1: Find out the difference between the row count from the statistics (stats_row_count) and the actual row count (actual_row_count).

select 
objIdsWithStats.[object_id], 
actualRowCounts.[schema], 
actualRowCounts.logical_table_name, 
statsRowCounts.stats_row_count, 
actualRowCounts.actual_row_count,
row_count_difference = CASE
    WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count THEN actualRowCounts.actual_row_count - statsRowCounts.stats_row_count
    ELSE statsRowCounts.stats_row_count - actualRowCounts.actual_row_count
END,
percent_deviation_from_actual = CASE
    WHEN actualRowCounts.actual_row_count = 0 THEN statsRowCounts.stats_row_count
    WHEN statsRowCounts.stats_row_count = 0 THEN actualRowCounts.actual_row_count
    WHEN actualRowCounts.actual_row_count >= statsRowCounts.stats_row_count THEN CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (actualRowCounts.actual_row_count - statsRowCounts.stats_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
    ELSE CONVERT(NUMERIC(18, 0), CONVERT(NUMERIC(18, 2), (statsRowCounts.stats_row_count - actualRowCounts.actual_row_count)) / CONVERT(NUMERIC(18, 2), actualRowCounts.actual_row_count) * 100)
END
from
(
    select distinct object_id from sys.stats where stats_id > 1
) objIdsWithStats
left join
(
    select object_id, sum(rows) as stats_row_count from sys.partitions group by object_id
) statsRowCounts
on objIdsWithStats.object_id = statsRowCounts.object_id 
left join
(
    SELECT sm.name [schema] ,
    tb.name logical_table_name ,
    tb.object_id object_id ,
    SUM(rg.row_count) actual_row_count
    FROM sys.schemas sm
    INNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id
    INNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id
    INNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name
    INNER JOIN sys.dm_pdw_nodes_db_partition_stats rg
    ON rg.object_id = nt.object_id
    AND rg.pdw_node_id = nt.pdw_node_id
    AND rg.distribution_id = nt.distribution_id
    WHERE 1 = 1
    GROUP BY sm.name, tb.name, tb.object_id
) actualRowCounts
on objIdsWithStats.object_id = actualRowCounts.object_id

Query 2: Verificare l'età delle statistiche controllando l'ultima volta in cui le statistiche sono state aggiornate in ogni tabella.Query 2: Find out the age of your statistics by checking the last time your statistics were updated on each table.

Nota

Se è stata apportata una modifica sostanziale nella distribuzione dei valori per una colonna, è necessario aggiornare le statistiche a prescindere dalla data dell'ultimo aggiornamento.If there is a material change in the distribution of values for a column, you should update statistics regardless of the last time they were updated.

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

Le colonne della data in un pool SQL dedicato, ad esempio, richiedono in genere aggiornamenti frequenti delle statistiche.Date columns in a dedicated SQL pool, for example, usually need frequent statistics updates. Ogni volta che vengono caricate nuove righe nel pool SQL dedicato, vengono aggiunte nuove date di caricamento o date di transazione.Each time new rows are loaded into the dedicated SQL pool, new load dates or transaction dates are added. Queste aggiunte modificano la distribuzione dei dati e rendono le statistiche obsolete.These additions change the data distribution and make the statistics out of date.

Al contrario, è possibile che non sia mai necessario aggiornare le statistiche relative alla colonna del sesso in una tabella clienti.Conversely, statistics on a gender column in a customer table might never need to be updated. Supponendo che la distribuzione sia costante tra i clienti, l'aggiunta di nuove righe alla variazione di tabella non modificherà la distribuzione dei dati.Assuming the distribution is constant between customers, adding new rows to the table variation isn't going to change the data distribution.

Se il pool SQL dedicato contiene un solo sesso e un nuovo requisito produce più sessi, è necessario aggiornare le statistiche sulla colonna Gender.If your dedicated SQL pool contains only one gender and a new requirement results in multiple genders, then you need to update statistics on the gender column.

Per ulteriori informazioni, vedere indicazioni su Statistiche.For more information, see general guidance for Statistics.

Implementazione della gestione delle statisticheImplementing statistics management

Spesso è consigliabile estendere il processo di caricamento dei dati per assicurare che le statistiche vengano aggiornate al termine del caricamento per evitare o ridurre al minimo il blocco o la contesa di risorse tra le query simultanee.It is often a good idea to extend your data-loading process to ensure that statistics are updated at the end of the load to avoid/minimize blocking or resource contention between concurrent queries.

Il caricamento dei dati è la fase in cui si verifica con maggiore frequenza una modifica delle dimensioni e/o della distribuzione dei valori delle tabelle.The data load is when tables most frequently change their size and/or their distribution of values. Il caricamento dei dati è una posizione logica per implementare alcuni processi di gestione.Data-loading is a logical place to implement some management processes.

Di seguito vengono illustrati i principi guida per l'aggiornamento delle statistiche:The following guiding principles are provided for updating your statistics:

  • Assicurarsi che ogni tabella caricata includa almeno un oggetto statistiche aggiornato.Ensure that each loaded table has at least one statistics object updated. Ciò permette di aggiornare le informazioni sulle dimensioni delle tabelle (numero di righe e pagine) come parte dell'aggiornamento delle statistiche.This updates the table size (row count and page count) information as part of the statistics update.
  • Concentrarsi sulle colonne incluse nelle clausole JOIN, GROUP BY, ORDER BY e DISTINCT.Focus on columns participating in JOIN, GROUP BY, ORDER BY, and DISTINCT clauses.
  • Prendere in considerazione una maggiore frequenza per l'aggiornamento delle colonne "chiave crescente", ad esempio le date delle transazioni, poiché questi valori non verranno inclusi nell'istogramma delle statistiche.Consider updating "ascending key" columns such as transaction dates more frequently, because these values will not be included in the statistics histogram.
  • Prendere in considerazione una minore frequenza per l'aggiornamento delle colonne relative alla distribuzione statica.Consider updating static distribution columns less frequently.
  • Occorre ricordare che ogni oggetto statistiche viene aggiornato in sequenza.Remember, each statistic object is updated in sequence. La semplice implementazione di UPDATE STATISTICS <TABLE_NAME> non è sempre ottimale, in particolare per tabelle di grandi dimensioni con molti oggetti statistiche.Simply implementing UPDATE STATISTICS <TABLE_NAME> isn't always ideal, especially for wide tables with lots of statistics objects.

Per ulteriori informazioni, vedere Stima della cardinalità.For more information, see Cardinality Estimation.

Esempi: Creare le statisticheExamples: Create statistics

Questi esempi illustrano come usare diverse opzioni per la creazione delle statistiche.These examples show how to use various options for creating statistics. Le opzioni usate per ogni colonna dipendono dalle caratteristiche dei dati e dal modo in cui la colonna verrà usata nelle query.The options that you use for each column depend on the characteristics of your data and how the column will be used in queries.

Creare statistiche a colonna singola con opzioni predefiniteCreate single-column statistics with default options

Per creare statistiche su una colonna, fornire un nome per l'oggetto statistiche e il nome della colonna.To create statistics on a column, provide a name for the statistics object and the name of the column.

Questa sintassi usa tutte le opzioni predefinite.This syntax uses all of the default options. Per impostazione predefinita, durante la creazione delle statistiche viene campionato il 20% della tabella.By default, 20 percent of the table is sampled when creating statistics.

CREATE STATISTICS [statistics_name] ON [schema_name].[table_name]([column_name]);

Ad esempio:For example:

CREATE STATISTICS col1_stats ON dbo.table1 (col1);

Creare statistiche a colonna singola esaminando ogni rigaCreate single-column statistics by examining every row

La frequenza di campionamento del 20% è sufficiente per la maggior parte delle situazioni.The default sampling rate of 20 percent is sufficient for most situations. È tuttavia possibile modificare la frequenza di campionamento.However, you can adjust the sampling rate.

Per eseguire il campionamento dell'intera tabella, usare la sintassi seguente:To sample the full table, use this syntax:

CREATE STATISTICS [statistics_name] ON [schema_name].[table_name]([column_name]) WITH FULLSCAN;

Ad esempio:For example:

CREATE STATISTICS col1_stats ON dbo.table1 (col1) WITH FULLSCAN;

Creare statistiche a colonna singola specificando le dimensioni del campioneCreate single-column statistics by specifying the sample size

In alternativa, è possibile specificare le dimensioni del campione sotto forma di percentuale:Alternatively, you can specify the sample size as a percent:

CREATE STATISTICS col1_stats ON dbo.table1 (col1) WITH SAMPLE = 50 PERCENT;

Creare statistiche a colonna singola solo su alcune righeCreate single-column statistics on only some of the rows

È anche possibile creare statistiche su una parte delle righe della tabella.You can also create statistics on a portion of the rows in your table. Questa opzione è definita statistica filtrata.This is called a filtered statistic.

Ad esempio, è possibile usare le statistiche filtrate quando si pianifica di eseguire una query in una partizione specifica di una tabella partizionata di grandi dimensioni.For example, you can use filtered statistics when you plan to query a specific partition of a large partitioned table. Se si creano statistiche solo sui valori della partizione, la precisione delle statistiche migliorerà e miglioreranno quindi le prestazioni delle query.By creating statistics on only the partition values, the accuracy of the statistics will improve, and therefore improve query performance.

Questo esempio crea statistiche su un intervallo di valori.This example creates statistics on a range of values. È possibile definire con facilità i valori in modo che corrispondano all'intervallo di valori in una partizione.The values can easily be defined to match the range of values in a partition.

CREATE STATISTICS stats_col1 ON table1(col1) WHERE col1 > '2000101' AND col1 < '20001231';

Nota

Per fare in modo che Query Optimizer prenda in considerazione l'uso delle statistiche filtrate quando sceglie il piano di query distribuite, è necessario che la query rientri nella definizione dell'oggetto statistiche.For the query optimizer to consider using filtered statistics when it chooses the distributed query plan, the query must fit inside the definition of the statistics object. Usando l'esempio precedente, la clausola WHERE della query deve specificare i valori col1 compresi tra 2000101 e 20001231.Using the previous example, the query's WHERE clause needs to specify col1 values between 2000101 and 20001231.

Creare statistiche a colonna singola con tutte le opzioniCreate single-column statistics with all the options

È anche possibile combinare le varie opzioni.You can also combine the options together. L'esempio seguente crea un oggetto statistiche filtrato con una dimensione del campione personalizzata:The following example creates a filtered statistics object with a custom sample size:

CREATE STATISTICS stats_col1 ON table1 (col1) WHERE col1 > '2000101' AND col1 < '20001231' WITH SAMPLE = 50 PERCENT;

Per i riferimenti completi, vedere CREAZIONE DELLE STATISTICHE.For the full reference, see CREATE STATISTICS.

Creare statistiche a più colonneCreate multi-column statistics

Per creare un oggetto statistiche a più colonne, usare gli esempi precedenti, specificando però più colonne.To create a multi-column statistics object, use the previous examples, but specify more columns.

Nota

L'istogramma, che viene usato per stimare il numero di righe nei risultato della query, è disponibile solo per la prima colonna elencata nella definizione dell'oggetto statistiche.The histogram, which is used to estimate the number of rows in the query result, is only available for the first column listed in the statistics object definition.

In questo esempio l'istogramma è disponibile su product_category.In this example, the histogram is on product_category. Le statistiche sulle colonne vengono calcolate su product_category e product_sub_category:Cross-column statistics are calculated on product_category and product_sub_category:

CREATE STATISTICS stats_2cols ON table1 (product_category, product_sub_category) WHERE product_category > '2000101' AND product_category < '20001231' WITH SAMPLE = 50 PERCENT;

Poiché esiste una correlazione tra product_category e product_sub_category, un oggetto statistiche a più colonne può essere utile se si accede contemporaneamente a queste colonne.Because there is a correlation between product_category and product_sub_category, a multi-column statistics object can be useful if these columns are accessed at the same time.

Creare statistiche su tutte le colonne in una tabellaCreate statistics on all columns in a table

Un modo per creare le statistiche consiste nell'emettere comandi CREATE STATISTICS dopo la creazione della tabella:One way to create statistics is to issue CREATE STATISTICS commands after creating the table:

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

Usare un stored procedure per creare statistiche su tutte le colonne in un pool SQLUse a stored procedure to create statistics on all columns in a SQL pool

Il pool SQL dedicato non dispone di un stored procedure di sistema equivalente a sp_create_stats in SQL Server.Dedicated SQL pool does not have a system stored procedure equivalent to sp_create_stats in SQL Server. Questo stored procedure crea un oggetto statistiche a colonna singola su ogni colonna di un pool SQL che non dispone già di statistiche.This stored procedure creates a single column statistics object on every column in a SQL pool that doesn't already have statistics.

L'esempio seguente consente di iniziare a usare la progettazione del pool SQL.The following example will help you get started with your SQL pool design. È possibile adattare l'operazione alle proprie esigenze.Feel free to adapt it to your needs.

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default 2 Fullscan 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Per creare statistiche su tutte le colonne della tabella usando le impostazioni predefinite, chiamare la stored procedure.To create statistics on all columns in the table using the defaults, execute the stored procedure.

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

Per creare statistiche su tutte le colonne della tabella usando un FULLSCAN, chiamare questa procedura.To create statistics on all columns in the table using a fullscan, call this procedure.

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

Per creare statistiche campionate su tutte le colonne della tabella, immettere 3 e la percentuale di campionamento.To create sampled statistics on all columns in the table, enter 3, and the sample percent. Questa procedura usa una frequenza di campionamento del 20%.This procedure uses a 20 percent sample rate.

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

Esempi: Aggiornare le statisticheExamples: Update statistics

Per aggiornare le statistiche, è possibile eseguire le operazioni seguenti:To update statistics, you can:

  • Aggiornare un oggetto statistiche.Update one statistics object. Specificare il nome dell'oggetto statistiche che si desidera aggiornare.Specify the name of the statistics object you want to update.
  • Aggiornare tutti gli oggetti statistiche in una tabella.Update all statistics objects on a table. Specificare il nome della tabella invece di un oggetto statistiche specifico.Specify the name of the table instead of one specific statistics object.

Aggiornare un oggetto statistiche specificoUpdate one specific statistics object

Usare la sintassi seguente per aggiornare un oggetto statistiche specifico:Use the following syntax to update a specific statistics object:

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

Ad esempio:For example:

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

L'aggiornamento di oggetti statistiche specifici permette di ridurre al minimo il tempo e le risorse necessari per gestire le statistiche.By updating specific statistics objects, you can minimize the time and resources required to manage statistics. In questo modo è necessario scegliere gli oggetti statistiche migliori da aggiornare.Doing so requires some thought to choose the best statistics objects to update.

Aggiornamento di tutte le statistiche di una tabellaUpdate all statistics on a table

Di seguito è illustrato un semplice metodo di aggiornamento di tutti gli oggetti statistiche in una tabella.A simple method for updating all the statistics objects on a table is:

UPDATE STATISTICS [schema_name].[table_name];

Ad esempio:For example:

UPDATE STATISTICS dbo.table1;

L'istruzione UPDATE STATISTICS è facile da usare.The UPDATE STATISTICS statement is easy to use. Occorre ricordare che aggiorna tutte le statistiche nella tabella e che quindi potrebbe eseguire più lavoro del necessario.Just remember that it updates all statistics on the table, and therefore might perform more work than is necessary. Se le prestazioni non sono un problema, questo è il modo più semplice e più completo per garantire che le statistiche siano aggiornate.If performance is not an issue, this is the easiest and most complete way to guarantee that statistics are up to date.

Nota

Quando si aggiornano tutte le statistiche in una tabella, il pool SQL dedicato esegue un'analisi per campionare la tabella per ogni oggetto statistiche.When updating all statistics on a table, dedicated SQL pool does a scan to sample the table for each statistics object. Se si tratta di una tabella di grandi dimensioni, che include molte colonne e molte statistiche, potrebbe risultare più efficiente aggiornare le singole statistiche in base alle necessità.If the table is large and has many columns and many statistics, it might be more efficient to update individual statistics based on need.

Per un'implementazione di una UPDATE STATISTICS procedura, vedere tabelle temporanee.For an implementation of an UPDATE STATISTICS procedure, see Temporary Tables. Il metodo di implementazione è leggermente diverso rispetto alla procedura CREATE STATISTICS precedente, ma il risultato è lo stesso.The implementation method is slightly different from the preceding CREATE STATISTICS procedure, but the result is the same.

Per la sintassi completa, vedere Update Statistics.For the full syntax, see Update Statistics.

Metadati delle statisticheStatistics metadata

Esistono diverse visualizzazioni e funzioni di sistema che consentono di trovare informazioni sulle statistiche.There are several system views and functions that you can use to find information about statistics. Ad esempio, è possibile verificare se un oggetto statistiche non è aggiornato usando la funzione stats-date per vedere la data di creazione o dell'ultimo aggiornamento delle statistiche.For example, you can see if a statistics object might be out of date by using the stats-date function to see when statistics were last created or updated.

Viste del catalogo per le statisticheCatalog views for statistics

Queste visualizzazioni di sistema forniscono informazioni sulle statistiche:These system views provide information about statistics:

Vista del catalogoCatalog view DescrizioneDescription
sys.columnssys.columns Una riga per ogni colonna.One row for each column.
sys.objectssys.objects Una riga per ogni oggetto del database.One row for each object in the database.
sys.schemassys.schemas Una riga per ogni schema del database.One row for each schema in the database.
sys.statssys.stats Una riga per ogni oggetto statistiche.One row for each statistics object.
sys.stats_columnssys.stats_columns Una riga per ogni colonna nell'oggetto statistiche.One row for each column in the statistics object. Si collega a sys.columns.Links back to sys.columns.
sys.tablessys.tables Una riga per ogni tabella (include le tabelle esterne).One row for each table (includes external tables).
sys.table_typessys.table_types Una riga per ogni tipo di dati.One row for each data type.

Funzioni di sistema per le statisticheSystem functions for statistics

Queste funzioni di sistema sono utili per usare le statistiche:These system functions are useful for working with statistics:

Funzioni di sistemaSystem function DescrizioneDescription
STATS_DATESTATS_DATE Data dell'ultimo aggiornamento dell'oggetto statistiche.Date the statistics object was last updated.
DBCC SHOW_STATISTICSDBCC SHOW_STATISTICS Riepilogo e informazioni dettagliate sulla distribuzione di valori riconosciuti dall'oggetto statistiche.Summary level and detailed information about the distribution of values as understood by the statistics object.

Combinare le colonne delle statistiche e le funzioni in un'unica visualizzazioneCombine statistics columns and functions into one view

Questa visualizzazione riunisce le colonne relative alle statistiche e ai risultati della funzione STATS_DATE().This view brings columns that relate to statistics and results from the STATS_DATE() function together.

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON  co.[object_id]        = tb.[object_id]
JOIN    sys.schemas         AS sm ON  tb.[schema_id]        = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

Esempi di DBCC SHOW_STATISTICS()DBCC SHOW_STATISTICS() examples

DBCC SHOW_STATISTICS() mostra i dati inclusi in un oggetto statistiche.DBCC SHOW_STATISTICS() shows the data held within a statistics object. Questi dati sono costituiti da tre parti:This data comes in three parts:

  • IntestazioneHeader
  • Vettore di densitàDensity vector
  • IstogrammaHistogram

Metadati di intestazione sulle statistiche.The header metadata about the statistics. L'istogramma mostra la distribuzione dei valori nella prima colonna chiave dell'oggetto statistiche.The histogram displays the distribution of values in the first key column of the statistics object. Il vettore di densità misura la correlazione tra le colonne.The density vector measures cross-column correlation.

Nota

Il pool SQL dedicato calcola le stime della cardinalità con tutti i dati nell'oggetto statistiche.Dedicated SQL pool computes cardinality estimates with any of the data in the statistics object.

Mostrare l'intestazione, la densità e l'istogrammaShow header, density, and histogram

Questo semplice esempio mostra tutte e tre le parti di un oggetto statistiche:This simple example shows all three parts of a statistics object:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Ad esempio:For example:

DBCC SHOW_STATISTICS (dbo.table1, stats_col1);

Mostrare una o più parti di DBCC SHOW_STATISTICS()Show one or more parts of DBCC SHOW_STATISTICS()

Se si è interessati a visualizzare solo parti specifiche, usare la clausola WITH e specificare le parti da visualizzare:If you're only interested in viewing specific parts, use the WITH clause and specify which parts you want to see:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>) WITH stat_header, histogram, density_vector

Ad esempio:For example:

DBCC SHOW_STATISTICS (dbo.table1, stats_col1) WITH histogram, density_vector

Differenze di DBCC SHOW_STATISTICS()DBCC SHOW_STATISTICS() differences

DBCC SHOW_STATISTICS () viene implementato in modo più rigoroso nel pool SQL dedicato rispetto a SQL Server:DBCC SHOW_STATISTICS() is more strictly implemented in dedicated SQL pool compared to SQL Server:

  • Le funzionalità non documentate non sono supportate.Undocumented features are not supported.
  • Non è possibile usare Stats_stream.Cannot use Stats_stream.
  • Non è possibile unire i risultati per sottoinsiemi specifici di dati statistici.Cannot join results for specific subsets of statistics data. ad esempio: STAT_HEADER JOIN DENSITY_VECTOR.For example, STAT_HEADER JOIN DENSITY_VECTOR.
  • NO_INFOMSGS non può essere impostato per l'eliminazione del messaggio.NO_INFOMSGS cannot be set for message suppression.
  • Non è possibile usare le parentesi quadre per i nomi delle statistiche.Square brackets around statistics names cannot be used.
  • Non è possibile usare i nomi di colonna per identificare gli oggetti statistiche.Cannot use column names to identify statistics objects.
  • L'errore personalizzato 2767 non è supportato.Custom error 2767 is not supported.

Passaggi successiviNext steps

Per ottimizzare ulteriormente le prestazioni delle query, vedere Monitorare il carico di lavoroFor further improve query performance, see Monitor your workload