Nota
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare ad accedere o modificare le directory.
L'accesso a questa pagina richiede l'autorizzazione. È possibile provare a modificare le directory.
In questo articolo sono riportati suggerimenti ed esempi per la creazione e l'aggiornamento delle statistiche di ottimizzazione delle query usando le risorse di Synapse SQL: pool SQL dedicato e pool SQL serverless.
Statistiche nel pool SQL dedicato
Perché usare le statistiche?
Il pool SQL più dedicato conosce i dati, più velocemente può eseguire query. Dopo il caricamento dei dati in un pool SQL dedicato, la raccolta di statistiche sui dati è una delle operazioni più importanti che è possibile eseguire per l'ottimizzazione delle query.
Query Optimizer del pool SQL dedicato è un ottimizzatore basato sui costi. Esegue un confronto fra i costi dei vari piani di query e poi sceglie quello che costa meno, Nella maggior parte dei casi, sceglie il piano che eseguirà il più veloce.
Ad esempio, se l'ottimizzatore stima che la data su cui la query sta filtrando restituirà una riga, sceglierà un piano. Se stima che la data selezionata restituirà 1 milione di righe, restituirà un piano diverso.
Creazione automatica di statistiche
Il motore del pool SQL dedicato analizzerà le query utente in ingresso per individuare le statistiche mancanti quando l'opzione AUTO_CREATE_STATISTICS del database è impostata su ON. Se non sono presenti, Query Optimizer creerà statistiche su singole colonne nel predicato della query o nella condizione di join,
Questa funzione è utilizzata per migliorare l'accuratezza delle stime di cardinalità nel piano di query.
Importante
Per impostazione predefinita, la creazione automatica di statistiche è attiva.
È possibile controllare se l'opzione AUTO_CREATE_STATISTICS è attiva nel data warehouse in uso con il comando seguente:
SELECT name, is_auto_create_stats_on
FROM sys.databases
Se il data warehouse non ha AUTO_CREATE_STATISTICS abilitato, è consigliabile abilitare questa proprietà eseguendo il comando seguente:
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Queste istruzioni attiveranno la creazione automatica delle statistiche:
- SELEZIONA
- INSERT-SELECT
- CTAS
- AGGIORNAMENTO
- ELIMINA
- EXPLAIN quando contengono un join o viene rilevata la presenza di un predicato
Annotazioni
La creazione automatica delle statistiche non viene generata in tabelle temporanee o esterne.
La creazione automatica delle statistiche viene eseguita in modo sincrono. Pertanto, è possibile che si verifichino prestazioni di query leggermente ridotte se le colonne non contengono statistiche. Il tempo necessario per creare le statistiche relative a una colonna dipende dalle dimensioni della tabella.
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.
Annotazioni
La creazione di statistiche viene registrata in sys.dm_pdw_exec_requests in un contesto utente diverso.
Quando vengono create statistiche automatiche, si presenteranno nella forma: WA_Sys<ID colonna a 8 cifre in Hex>_<ID tabella a 8 cifre in Hex>. È possibile visualizzare le statistiche già create eseguendo il comando DBCC SHOW_STATISTICS :
DBCC SHOW_STATISTICS (<table_name>, <target>)
Il table_name è il nome della tabella che contiene le statistiche da visualizzare, che non può essere una tabella esterna. La destinazione è il nome dell'indice di destinazione, delle statistiche o della colonna per cui visualizzare le informazioni statistiche.
Aggiornare le statistiche
Una procedura consigliata consiste nell'aggiornare le statistiche sulle colonne data ogni giorno quando vengono aggiunte nuove date. Ogni volta che vengono caricate nuove righe nel data warehouse, vengono aggiunte nuove date di caricamento o di transazione. Queste aggiunte modificano la distribuzione dei dati e rendono le statistiche obsolete.
Le statistiche relative a un paese o a una colonna di un'area geografica in una tabella cliente potrebbero non essere mai necessarie perché la distribuzione dei valori non cambia in genere. Supponendo che la distribuzione sia costante tra i clienti, l'aggiunta di nuove righe alla variazione di tabella non modificherà la distribuzione dei dati.
Tuttavia, quando il data warehouse contiene solo un paese o un'area geografica e si importano dati da un nuovo paese o area geografica, è necessario aggiornare le statistiche nella colonna paese o area geografica.
Di seguito sono fornite raccomandazioni per l'aggiornamento delle statistiche:
| TIPO | Raccomandazione |
|---|---|
| Frequenza degli aggiornamenti delle statistiche | Conservativa: giornaliera Dopo il caricamento o la trasformazione dei dati |
| Campionamento | Se inferiore a 1 miliardo di righe, usare il campionamento predefinito (20%).
Se superiore a 1 miliardo righe, usare il campionamento del 2%. |
Determinare l'ultimo aggiornamento delle statistiche
Una delle prime domande da porre quando si sta risolvendo una query è "Le statistiche sono aggiornate?"
Questa domanda non può essere risolta con l'età dei dati. Un oggetto statistiche aggiornato può essere vecchio se non sono state apportate modifiche sostanziali ai dati sottostanti. Quando il numero di righe è stato modificato in modo sostanziale o si verifica una modifica sostanziale della distribuzione dei valori per una colonna, è il momento di aggiornare le statistiche.
Non è disponibile una vista a gestione dinamica per determinare se i dati all'interno della tabella sono stati modificati dopo l'ultimo aggiornamento delle statistiche. Conoscere l'età delle statistiche può fornire parte del quadro.
È possibile usare la query seguente per determinare l'ultima volta che le statistiche sono state aggiornate in ogni tabella.
Annotazioni
Se si verifica una modifica sostanziale nella distribuzione dei valori per una colonna, è necessario aggiornare le statistiche indipendentemente dall'ultima volta in cui sono state aggiornate.
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 di data in un data warehouse, ad esempio, richiedono in genere aggiornamenti frequenti delle statistiche. Ogni volta che vengono caricate nuove righe nel data warehouse, vengono aggiunte nuove date di caricamento o di transazione. Queste aggiunte modificano la distribuzione dei dati e rendono le statistiche obsolete.
Le statistiche relative a una colonna di genere in una tabella cliente potrebbero non richiedere mai un aggiornamento. Supponendo che la distribuzione sia costante tra i clienti, l'aggiunta di nuove righe alla variazione di tabella non modificherà la distribuzione dei dati.
Tuttavia, se il data warehouse contiene un solo sesso e un nuovo requisito comporta più sessi, è necessario aggiornare le statistiche sulla colonna gender.
Per altre informazioni, vedere l'articolo Statistiche .
Implementare la gestione delle statistiche
È spesso consigliabile estendere il processo di caricamento dei dati per assicurare che le statistiche vengano aggiornate al termine del caricamento. 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. Il processo di caricamento costituisce quindi una posizione logica per implementare alcuni processi di gestione.
Di seguito sono disponibili i principi guida per l'aggiornamento delle statistiche durante il processo di caricamento:
- Assicurarsi che ogni tabella caricata includa almeno un oggetto statistiche aggiornato. Questo processo consente di aggiornare le informazioni sulle dimensioni delle tabelle (numero di righe e pagine) come parte dell'aggiornamento delle statistiche.
- Concentrarsi sulle colonne incluse nelle clausole JOIN, GROUP BY, ORDER BY e DISTINCT.
- 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.
- Prendere in considerazione una minore frequenza per l'aggiornamento delle colonne relative alla distribuzione statica.
- Occorre ricordare che ogni oggetto statistiche viene aggiornato in sequenza. La semplice implementazione di
UPDATE STATISTICS <TABLE_NAME>non è sempre ottimale, in particolare per tabelle di grandi dimensioni con molti oggetti statistiche.
Per altre informazioni, vedere Stima della cardinalità.
Esempi: Creare le statistiche
Questi esempi illustrano come usare diverse opzioni per la creazione delle statistiche. Le opzioni usate per ogni colonna dipendono dalle caratteristiche dei dati e dalla modalità di utilizzo della colonna nelle query.
Creare statistiche a colonna singola con opzioni predefinite
Per creare statistiche su una colonna, fornire un nome per l'oggetto statistiche e il nome della colonna. Questa sintassi usa tutte le opzioni predefinite. Per impostazione predefinita, il pool SQL dedicato campiona il 20 percento della tabella quando crea statistiche.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Per esempio:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1);
Creare statistiche a colonna singola esaminando ogni riga
La frequenza di campionamento del 20% è sufficiente per la maggior parte delle situazioni. È tuttavia possibile modificare la frequenza di campionamento. Per eseguire il campionamento dell'intera tabella, usare la sintassi seguente:
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Per esempio:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Creare statistiche a colonna singola specificando le dimensioni del campione
Un'altra opzione è quella di specificare le dimensioni del campione come percentuale:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH SAMPLE 50 PERCENT;
Creare statistiche a colonna singola solo su alcune righe
È anche possibile creare statistiche su una parte delle righe della tabella, denominata statistica filtrata.
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. Creando statistiche solo sui valori di partizione, l'accuratezza delle statistiche migliorerà. Si otterrà anche un miglioramento delle prestazioni delle query.
Questo esempio crea statistiche su un intervallo di valori. È possibile definire con facilità i valori in modo che corrispondano all'intervallo di valori in una partizione.
CREATE STATISTICS stats_col1
ON table1(col1)
WHERE col1 > '2000101' AND col1 < '20001231';
Annotazioni
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. Usando l'esempio precedente, la clausola WHERE della query deve specificare i valori col1 compresi tra 2000101 e 20001231.
Creare statistiche a colonna singola con tutte le opzioni
È anche possibile combinare le varie opzioni. L'esempio seguente crea un oggetto statistiche filtrato con una dimensione del campione personalizzata:
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.
Creare statistiche a più colonne
Per creare un oggetto statistiche a più colonne, usare gli esempi precedenti, specificando però più colonne.
Annotazioni
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.
In questo esempio l'istogramma è in product_category. Le statistiche tra colonne vengono calcolate su product_category e 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 su più colonne può essere utile se si accede contemporaneamente a queste colonne. Quando si esegue una query in questa tabella, le statistiche a più colonne miglioreranno le stime della cardinalità per join, aggregazioni GROUP BY, conteggi distinti e filtri WHERE (purché la colonna delle statistiche primarie faccia parte del filtro).
Creare statistiche su tutte le colonne in una tabella
Un modo per creare statistiche consiste nell'eseguire comandi CREATE STATISTICS dopo la creazione della tabella:
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);
Utilizzare una stored procedure per creare statistiche su tutte le colonne di un database
Il pool SQL non dispone di una procedura di sistema memorizzata equivalente a sp_create_stats in SQL Server. Questa stored procedure crea un oggetto statistico per singola colonna su ogni colonna del database che non ha già statistiche.
L'esempio seguente consente di iniziare a progettare il database. È possibile adattarlo alle proprie esigenze:
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.
EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;
Per creare statistiche su tutte le colonne della tabella usando un'analisi completa, chiamare questa procedura:
EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;
Per creare statistiche campionate su tutte le colonne della tabella, immettere 3 e la percentuale di campione. La procedura seguente usa una percentuale di campionamento del 20%.
EXEC [dbo].[prc_sqldw_create_stats] 3, 20;
Esempi: aggiornare le statistiche
Per aggiornare le statistiche, è possibile eseguire le operazioni seguenti:
- Aggiornare un oggetto statistiche. Specificare il nome dell'oggetto statistiche che si desidera aggiornare.
- Aggiornare tutti gli oggetti statistiche in una tabella. Specificare il nome della tabella invece di un oggetto statistiche specifico.
Aggiornare un oggetto statistiche specifico
Usare la sintassi seguente per aggiornare un oggetto statistiche specifico:
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Per esempio:
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. Questa azione richiede alcuni pensieri per selezionare gli oggetti statistiche migliori da aggiornare.
Aggiornamento di tutte le statistiche di una tabella
Di seguito è illustrato un semplice metodo di aggiornamento di tutti gli oggetti statistiche in una tabella.
UPDATE STATISTICS [schema_name].[table_name];
Per esempio:
UPDATE STATISTICS dbo.table1;
L'istruzione UPDATE STATISTICS è facile da usare. Ricorda semplicemente che aggiorna tutte le statistiche sulla tabella, richiedendo più lavoro di quanto necessario.
Se le prestazioni non sono un problema, questo metodo è il modo più semplice e completo per garantire che le statistiche siano aggiornate.
Annotazioni
Quando si aggiornano tutte le statistiche in una tabella, il pool SQL dedicato esegue un'analisi per campionare la tabella per ogni oggetto statistiche. 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à.
Per un'implementazione di una UPDATE STATISTICS procedura, vedere Tabelle temporanee. Il metodo di implementazione è leggermente diverso rispetto alla procedura CREATE STATISTICS precedente, ma il risultato è lo stesso.
Per la sintassi completa, vedere Aggiornare le statistiche.
Metadati delle statistiche
Esistono diverse visualizzazioni e funzioni di sistema che consentono di trovare informazioni sulle statistiche. Ad esempio, è possibile verificare se un oggetto statistiche potrebbe non essere aggiornato usando la funzione STATS_DATE(). STATS_DATE() consente di visualizzare quando le statistiche sono state create o aggiornate per l'ultima volta.
Viste del catalogo per le statistiche
Queste visualizzazioni di sistema forniscono informazioni sulle statistiche:
| Vista del catalogo | Descrizione |
|---|---|
| sys.columns | Una riga per ogni colonna. |
| sys.objects | Una riga per ogni oggetto nel database. |
| sys.schemas | Una riga per ogni schema nel database. |
| sys.stats | Una riga per ogni oggetto statistiche. |
| sys.stats_columns | Una riga per ogni colonna nell'oggetto statistiche. Collegamenti a sys.columns. |
| sys.tables | Una riga per ogni tabella (include tabelle esterne). |
| sys.table_types | Una riga per ogni tipo di dati. |
Funzioni di sistema per le statistiche
Queste funzioni di sistema sono utili per usare le statistiche:
| Funzione di sistema | Descrizione |
|---|---|
| STATS_DATE | Data dell'ultimo aggiornamento dell'oggetto statistiche. |
| DBCC SHOW_STATISTICS | Livello di riepilogo e informazioni dettagliate sulla distribuzione dei valori come compreso dall'oggetto statistiche. |
Combinare le colonne delle statistiche e le funzioni in un'unica visualizzazione
Questa vista riunisce le colonne correlate alle statistiche e ai risultati della funzione STATS_DATE().
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() mostra i dati contenuti in un oggetto statistiche. Questi dati sono costituiti da tre parti:
- Intestazione
- Vettore di densità
- Istogramma
L'intestazione è i metadati relativi alle statistiche. L'istogramma mostra la distribuzione dei valori nella prima colonna chiave dell'oggetto statistiche.
Il vettore di densità misura la correlazione tra le colonne. Il pool SQL dedicato calcola le stime di cardinalità con tutti i dati nell'oggetto statistiche.
Mostrare l'intestazione, la densità e l'istogramma
Questo semplice esempio mostra tutte e tre le parti di un oggetto statistiche:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Per esempio:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Mostrare una o più parti di DBCC SHOW_STATISTICS()
Se si è interessati a visualizzare solo parti specifiche, usare la clausola WITH e specificare le parti da visualizzare:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
WITH stat_header, histogram, density_vector
Per esempio:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
Differenze di DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() viene implementato in modo più rigoroso nel pool SQL dedicato rispetto a SQL Server:
- Non sono supportate funzionalità non documentate.
- Non è possibile usare Stats_stream.
- Non è possibile unire i risultati di specifici sottoinsiemi di dati statistici, Ad esempio, STAT_HEADER JOIN DENSITY_VECTOR.
- NO_INFOMSGS non può essere impostato per l'eliminazione dei messaggi.
- Non è possibile usare parentesi quadre per i nomi delle statistiche.
- Non è possibile usare i nomi di colonna per identificare gli oggetti statistiche.
- Non è supportato l'errore personalizzato 2767.
Statistiche nel pool SQL serverless
Le statistiche vengono create per ogni colonna specifica per un set di dati specifico (percorso di archiviazione).
Annotazioni
Impossibile creare statistiche per le colonne LOB.
Perché usare le statistiche?
Più il pool SQL serverless conosce i dati, più velocemente può eseguire query su di esso. La raccolta di statistiche sui dati è una delle operazioni più importanti che è possibile eseguire per ottimizzare le query.
Query Optimizer del pool SQL serverless è un ottimizzatore basato sui costi. Esegue un confronto fra i costi dei vari piani di query e poi sceglie quello che costa meno, Nella maggior parte dei casi, sceglie il piano che eseguirà il più veloce.
Ad esempio, se l'ottimizzatore stima che la data su cui viene filtrata la query restituirà una sola riga, verrà scelto un determinato piano. Se stima che la data selezionata restituirà 1 milione di righe, sceglierà un piano diverso.
Creazione automatica di statistiche
Il pool SQL serverless analizza le query utente in ingresso per individuare le statistiche mancanti. 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.
L'istruzione SELECT attiverà la creazione automatica di statistiche.
Annotazioni
Per la creazione automatica del campionamento delle statistiche viene usato e nella maggior parte dei casi la percentuale di campionamento sarà inferiore a 100%. Questo flusso è lo stesso per ogni formato di file. Tenere presente che quando si legge CSV con il campionamento parser versione 1.0 non è supportato e la creazione automatica delle statistiche non avviene con una percentuale di campionamento inferiore a 100%. Per le tabelle di piccole dimensioni con cardinalità (numero di righe) bassa stimata verrà attivata la creazione automatica delle statistiche con una percentuale di campionamento del 100%. Ciò significa fondamentalmente che l'analisi completa viene attivata e le statistiche automatiche vengono create anche per CSV con parser versione 1.0.
La creazione automatica delle statistiche viene eseguita in modo sincrono, pertanto è possibile che si verifichino prestazioni di query leggermente ridotte se le colonne mancano di statistiche. Il tempo necessario per creare le statistiche relative a una colonna dipende dalle dimensioni dei file di destinazione.
Creazione manuale delle statistiche
Il pool SQL serverless consente di creare le statistiche manualmente. Se si usa parser versione 1.0 con CSV, probabilmente sarà necessario creare le statistiche manualmente, perché questa versione del parser non supporta il campionamento. La creazione automatica delle statistiche in caso di parser versione 1.0 non verrà eseguita, a meno che la percentuale di campionamento non sia 100%.
Vedere gli esempi seguenti per istruzioni su come creare manualmente le statistiche.
Aggiornare le statistiche
Le modifiche apportate ai dati nei file, all'eliminazione e all'aggiunta di file comportano modifiche alla distribuzione dei dati e rendono le statistiche non aggiornate. In tal caso, le statistiche devono essere aggiornate.
Il pool SQL serverless ricrea automaticamente le statistiche per le colonne OPENROWSET se i dati vengono modificati in modo significativo. contemporaneamente, salva lo stato corrente del set di dati: percorsi di file, dimensioni, data dell'ultima modifica.
Quando le statistiche non sono aggiornate, verranno create nuove statistiche. L'algoritmo esamina i dati e lo confronta con lo stato corrente del set di dati. Se le dimensioni delle modifiche sono maggiori della soglia specifica, le statistiche precedenti vengono eliminate e verranno ricreate nel nuovo set di dati.
Le statistiche manuali non vengono mai dichiarate non aggiornati.
Annotazioni
Per la ricreazione automatica delle statistiche viene usato il campionamento e nella maggior parte dei casi la percentuale di campionamento sarà inferiore a 100%. Questo flusso è lo stesso per ogni formato di file. Tenere presente che quando si legge CSV con il campionamento parser versione 1.0 non è supportato e la ricreazione automatica delle statistiche non si verificherà con una percentuale di campionamento inferiore a 100%. In tal caso, è necessario eliminare e ricreare le statistiche manualmente. Controllare gli esempi seguenti su come eliminare e creare statistiche. Per le tabelle di piccole dimensioni con cardinalità bassa stimata (numero di righe) verrà attivata la ricreazione automatica delle statistiche con percentuale di campionamento pari a 100%. Ciò significa fondamentalmente che l'analisi completa viene attivata e le statistiche automatiche vengono create anche per CSV con parser versione 1.0.
Una delle prime domande da porre quando si sta risolvendo una query è "Le statistiche sono aggiornate?"
È necessario aggiornare le statistiche quando vengono apportate modifiche importanti al numero di righe o modifiche sostanziali alla distribuzione dei valori per una colonna specifica.
Annotazioni
Se si verifica una modifica sostanziale nella distribuzione dei valori per una colonna, è necessario aggiornare le statistiche indipendentemente dall'ultima volta in cui sono state aggiornate.
Implementare la gestione delle statistiche
È possibile estendere la pipeline di dati per assicurarsi che le statistiche vengano aggiornate quando i dati vengono modificati in modo significativo tramite l'aggiunta, l'eliminazione o la modifica dei file.
Di seguito vengono illustrati i principi guida per l'aggiornamento delle statistiche:
- Verificare che il set di dati disponga di almeno un oggetto statistiche aggiornato. Queste informazioni vengono aggiornate (conteggio righe e conteggio pagine) come parte dell'aggiornamento delle statistiche.
- Concentrarsi sulle colonne che partecipano alle clausole WHERE, JOIN, GROUP BY, ORDER BY e DISTINCT.
- Aggiornare le colonne "chiave crescente", ad esempio le date delle transazioni con maggiore frequenza, perché questi valori non verranno inclusi nell'istogramma delle statistiche.
- Aggiornare le colonne di distribuzione statiche meno frequentemente.
Per altre informazioni, vedere Stima della cardinalità.
Esempi: Creare statistiche per la colonna nel percorso OPENROWSET
Gli esempi seguenti illustrano come usare varie opzioni per la creazione di statistiche nei pool SQL serverless di Azure Synapse. Le opzioni usate per ogni colonna dipendono dalle caratteristiche dei dati e dalla modalità di utilizzo della colonna nelle query. Per altre informazioni sulle stored procedure usate in questi esempi, vedere sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics, che si applicano solo ai pool SQL serverless.
Annotazioni
È possibile creare statistiche a colonna singola solo in questo momento.
Per eseguire sp_create_openrowset_statistics e sp_drop_openrowset_statistics, sono necessarie le seguenti autorizzazioni: AMMINISTRARE OPERAZIONI IN BULK o AMMINISTRARE LE OPERAZIONI IN BULK DEL DATABASE.
La procedura memorizzata seguente viene usata per creare statistiche.
sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'
Argomenti: [ @stmt = ] N'statement_text': specifica un'istruzione Transact-SQL che restituirà i valori di colonna da usare per le statistiche. È possibile usare TABLESAMPLE per specificare esempi di dati da usare. Se TABLESAMPLE non è specificato, verrà usato FULLSCAN.
<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )
Annotazioni
Il campionamento CSV non funziona se si usa il parser versione 1.0, solo FULLSCAN è supportato per CSV con parser versione 1.0.
Creare statistiche a colonna singola esaminando ogni riga
Per creare statistiche su una colonna, specificare una query che restituisca la colonna per cui sono necessarie statistiche.
Per impostazione predefinita, se non si specifica diversamente quando si creano manualmente statistiche, il pool SQL serverless usa 100% dei dati forniti nel set di dati quando crea statistiche.
Ad esempio, per creare statistiche con le opzioni predefinite (FULLSCAN) per una colonna di popolamento del set di dati in base al file us_population.csv:
EXEC sys.sp_create_openrowset_statistics N'SELECT
population
FROM OPENROWSET(
BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
FORMAT = ''CSV'',
PARSER_VERSION = ''2.0'',
HEADER_ROW = TRUE)
AS [r]'
Creare statistiche a colonna singola specificando le dimensioni del campione
È possibile specificare le dimensioni del campione come percentuale:
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Esempi: aggiornare le statistiche
Per aggiornare le statistiche, è necessario prima eliminare e poi creare le statistiche. Per altre informazioni, vedere sys.sp_create_openrowset_statistics e sys.sp_drop_openrowset_statistics.
La sys.sp_drop_openrowset_statistics stored procedure viene usata per eliminare le statistiche:
sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'
Annotazioni
Per eseguire sp_create_openrowset_statistics e sp_drop_openrowset_statistics, sono necessarie le seguenti autorizzazioni: AMMINISTRARE OPERAZIONI IN BULK o AMMINISTRARE LE OPERAZIONI IN BULK DEL DATABASE.
Argomenti: [ @stmt = ] N'statement_text' - Specifica la stessa istruzione Transact-SQL utilizzata al momento della creazione delle statistiche.
Per aggiornare le statistiche per la colonna year nel set di dati, basato sul population.csv file, è necessario eliminare e creare statistiche:
EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
GO
/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO
CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = ''
GO
*/
EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
FORMAT = ''PARQUET''
) AS [nyc]
TABLESAMPLE(5 PERCENT)
'
Esempi: Creare statistiche per la colonna della tabella esterna
Gli esempi seguenti illustrano come usare varie opzioni per la creazione di statistiche. Le opzioni usate per ogni colonna dipendono dalle caratteristiche dei dati e dalla modalità di utilizzo della colonna nelle query.
Annotazioni
È possibile creare statistiche a colonna singola solo in questo momento.
Per creare statistiche su una colonna, fornire un nome per l'oggetto statistiche e il nome della colonna.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Argomenti: external_table specifica la tabella esterna da creare per le statistiche.
Calcola le statistiche con FULLSCAN eseguendo la scansione di tutte le righe. FULLSCAN e SAMPLE 100 PERCENT generano gli stessi risultati. Non è possibile usare FULLSCAN con l'opzione SAMPLE.
SAMPLE number PERCENT Specifica la percentuale o il numero approssimativo di righe nella tabella o nella vista indicizzata che il Query Optimizer utilizza quando crea statistiche. Il numero può essere compreso tra 0 e 100.
Non è possibile usare SAMPLE se viene specificata l'opzione FULLSCAN.
Annotazioni
Il campionamento CSV non funziona se si usa il parser versione 1.0, solo FULLSCAN è supportato per CSV con parser versione 1.0.
Creare statistiche a colonna singola esaminando ogni riga
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Creare statistiche a colonna singola specificando le dimensioni del campione
-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH SAMPLE 5 percent, NORECOMPUTE
Esempi: aggiornare le statistiche
Per aggiornare le statistiche, è necessario prima eliminare e poi creare le statistiche. Prima di tutto, eliminare le statistiche.
DROP STATISTICS census_external_table.sState
E creare statistiche:
CREATE STATISTICS sState
on census_external_table (STATENAME)
WITH FULLSCAN, NORECOMPUTE
Metadati delle statistiche
Esistono diverse visualizzazioni e funzioni di sistema che consentono di trovare informazioni sulle statistiche. Ad esempio, è possibile verificare se un oggetto statistiche potrebbe non essere aggiornato usando la funzione STATS_DATE(). STATS_DATE() consente di visualizzare quando le statistiche sono state create o aggiornate per l'ultima volta.
Annotazioni
I metadati delle statistiche sono disponibili solo per le colonne di tabella esterne. I metadati delle statistiche non sono disponibili per le colonne OPENROWSET.
Viste del catalogo per le statistiche
Queste visualizzazioni di sistema forniscono informazioni sulle statistiche:
| Vista del catalogo | Descrizione |
|---|---|
| sys.columns | Una riga per ogni colonna. |
| sys.objects | Una riga per ogni oggetto nel database. |
| sys.schemas | Una riga per ogni schema nel database. |
| sys.stats | Una riga per ogni oggetto statistiche. |
| sys.stats_columns | Una riga per ogni colonna nell'oggetto statistiche. Collegamenti a sys.columns. |
| sys.tables | Una riga per ogni tabella (include tabelle esterne). |
| sys.table_types | Una riga per ogni tipo di dati. |
Funzioni di sistema per le statistiche
Queste funzioni di sistema sono utili per usare le statistiche:
| Funzione di sistema | Descrizione |
|---|---|
| STATS_DATE | Data dell'ultimo aggiornamento dell'oggetto statistiche. |
Combinare le colonne delle statistiche e le funzioni in un'unica visualizzazione
Questa vista riunisce le colonne correlate alle statistiche e ai risultati della funzione STATS_DATE().
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 st.[user_created] = 1
;
Passaggi successivi
Per migliorare ulteriormente le prestazioni delle query per il pool SQL dedicato, vedere Monitorare il carico di lavoro e le procedure consigliate per il pool SQL dedicato.
Per migliorare ulteriormente le prestazioni delle query per il pool SQL serverless, vedere Procedure consigliate per il pool SQL serverless.