Dela via

Statistik i Synapse SQL

  • Artikel

I den här artikeln finns rekommendationer och exempel för att skapa och uppdatera frågeoptimeringsstatistik med hjälp av Synapse SQL-resurser: dedikerad SQL-pool och serverlös SQL-pool.

Statistik i dedikerad SQL-pool

Varför använda statistik

Ju mer dedikerad SQL-pool som känner till dina data, desto snabbare kan den köra frågor. När du har hämtat data till en dedikerad SQL-pool är insamling av statistik om dina data en av de viktigaste sakerna du kan göra för frågeoptimering.

Frågeoptimeraren för dedikerad SQL-pool är en kostnadsbaserad optimering. Den jämför kostnaden för olika frågeplaner och väljer sedan planen med den lägsta kostnaden. I de flesta fall väljer den den plan som ska köras snabbast.

Om optimeraren till exempel uppskattar att det datum då frågan filtreras returnerar en rad, väljer den en plan. Om det valda datumet beräknas returnera 1 miljon rader returneras en annan plan.

Automatisk skapande av statistik

Den dedikerade SQL-poolmotorn analyserar inkommande användarfrågor för statistik som saknas när alternativet databas AUTO_CREATE_STATISTICS är inställt på ON. Om statistik saknas skapar frågeoptimeraren statistik för enskilda kolumner i frågepredikat- eller kopplingsvillkoret.

Den här funktionen används för att förbättra kardinalitetsuppskattningarna för frågeplanen.

Viktigt

Automatisk generering av statistik är för närvarande aktiverat som standard.

Du kan kontrollera om ditt informationslager har AUTO_CREATE_STATISTICS konfigurerats genom att köra följande kommando:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Om ditt informationslager inte har AUTO_CREATE_STATISTICS aktiverat rekommenderar vi att du aktiverar den här egenskapen genom att köra följande kommando:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Dessa instruktioner utlöser automatisk skapande av statistik:

  • SELECT
  • INSERT-SELECT
  • CTAS
  • UPDATE
  • DELETE
  • FÖRKLARA när en koppling eller förekomsten av ett predikat identifieras

Anteckning

Automatisk skapande av statistik genereras inte i tillfälliga eller externa tabeller.

Automatisk skapande av statistik görs synkront. Därför kan du få något sämre frågeprestanda om dina kolumner saknar statistik. Tiden för att skapa statistik för en enskild kolumn beror på tabellens storlek.

För att undvika mätbar prestandaförsämring bör du se till att statistik har skapats först genom att köra benchmark-arbetsbelastningen innan du profilerar systemet.

Anteckning

Skapandet av statistik loggas i sys.dm_pdw_exec_requests under en annan användarkontext.

När automatisk statistik skapas har de formatet: WA_Sys<kolumn-ID med 8 siffror i hex>_<8-siffrigt tabell-ID i Hex>. Du kan visa statistik som redan har skapats genom att köra kommandot DBCC SHOW_STATISTICS :

DBCC SHOW_STATISTICS (<table_name>, <target>)

Table_name är namnet på tabellen som innehåller den statistik som ska visas, vilket inte kan vara en extern tabell. Målet är namnet på målindexet, statistiken eller kolumnen som statistikinformationen ska visas för.

Uppdatera statistik

En bra idé är att uppdatera statistik för datumkolumner varje dag när nya datum läggs till. Varje gång nya rader läses in i informationslagret läggs nya inläsningsdatum eller transaktionsdatum till. Dessa tillägg ändrar datafördelningen och gör statistiken inaktuell.

Statistik för en kolumn i ett land eller en region i en kundtabell kanske aldrig behöver uppdateras eftersom fördelningen av värden vanligtvis inte ändras. Om distributionen är konstant mellan kunder kommer inte datadistributionen att ändras om du lägger till nya rader i tabellvarianten.

Men när ditt informationslager bara innehåller ett land eller en region och du hämtar data från ett nytt land eller en ny region, måste du uppdatera statistiken i kolumnen land eller region.

Följande är rekommendationer för uppdatering av statistik:

Typ Rekommendation
Frekvens för statistikuppdateringar Konservativ: Varje dag
efter inläsning eller transformering av dina data
Sampling Mindre än 1 miljard rader använder standardsampling (20 procent).
Med mer än 1 miljard rader använder du sampling på två procent.

Fastställa senaste statistikuppdatering

En av de första frågorna att ställa när du felsöker en fråga är "Är statistiken uppdaterad?"

Den här frågan kan inte besvaras efter dataåldern. Ett uppdaterat statistikobjekt kan vara gammalt om det inte har skett någon väsentlig ändring av underliggande data. När antalet rader har ändrats väsentligt, eller om en väsentlig ändring i fördelningen av värden för en kolumn inträffar , är det dags att uppdatera statistiken.

Det finns ingen dynamisk hanteringsvy tillgänglig för att avgöra om data i tabellen har ändrats sedan statistiken senast uppdaterades. Om du känner till din statistiks ålder kan du få en del av bilden.

Du kan använda följande fråga för att fastställa den senaste gången statistiken uppdaterades i varje tabell.

Anteckning

Om det sker en väsentlig ändring i fördelningen av värden för en kolumn bör du uppdatera statistiken oavsett den senaste gången de uppdaterades.

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;

Datumkolumner i ett informationslager behöver till exempel vanligtvis frekventa statistikuppdateringar. Varje gång nya rader läses in i informationslagret läggs nya inläsningsdatum eller transaktionsdatum till. Dessa tillägg ändrar datafördelningen och gör statistiken inaktuell.

Statistik för en könskolumn i en kundtabell kanske aldrig behöver uppdateras. Om distributionen är konstant mellan kunder kommer inte datadistributionen att ändras om du lägger till nya rader i tabellvarianten.

Men om ditt informationslager bara innehåller ett kön och ett nytt krav resulterar i flera kön, måste du uppdatera statistiken för könskolumnen.

Mer information finns i artikeln Statistik .

Implementera statistikhantering

Det är ofta en bra idé att utöka datainläsningsprocessen för att säkerställa att statistiken uppdateras i slutet av belastningen. Datainläsningen är när tabeller oftast ändrar storlek, fördelning av värden eller båda. Därför är inläsningsprocessen en logisk plats för att implementera vissa hanteringsprocesser.

Följande riktlinjer finns för att uppdatera statistiken under inläsningsprocessen:

  • Kontrollera att minst ett statistikobjekt har uppdaterats för varje inläst tabell. Den här processen uppdaterar tabellstorleken (radantal och antal sidor) som en del av statistikuppdateringen.
  • Fokusera på kolumner som deltar i JOIN-, GROUP BY-, ORDER BY- och DISTINCT-satser.
  • Överväg att uppdatera kolumner med "stigande nyckel", till exempel transaktionsdatum oftare, eftersom dessa värden inte tas med i statistikhistogrammet.
  • Överväg att uppdatera statiska distributionskolumner mindre ofta.
  • Kom ihåg att varje statistikobjekt uppdateras i följd. Att bara implementera UPDATE STATISTICS <TABLE_NAME> är inte alltid idealiskt, särskilt inte för breda tabeller med många statistikobjekt.

Mer information finns i Kardinalitetsuppskattning.

Exempel: Skapa statistik

De här exemplen visar hur du använder olika alternativ för att skapa statistik. Vilka alternativ du använder för varje kolumn beror på egenskaperna för dina data och hur kolumnen ska användas i frågor.

Skapa statistik med en kolumn med standardalternativ

Om du vill skapa statistik för en kolumn anger du ett namn för statistikobjektet och namnet på kolumnen. Den här syntaxen använder alla standardalternativ. Som standard tar dedikerade SQL-poolexempel 20 procent av tabellen när den skapar statistik.

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

Exempel:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Skapa statistik med en kolumn genom att undersöka varje rad

Standardsamplingsfrekvensen på 20 procent är tillräcklig för de flesta situationer. Du kan dock justera samplingsfrekvensen. Om du vill ta exempel på den fullständiga tabellen använder du den här syntaxen:

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

Exempel:

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

Skapa statistik med en kolumn genom att ange exempelstorleken

Ett annat alternativ du har är att ange exempelstorleken som en procent:

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

Skapa statistik med en kolumn på endast några av raderna

Du kan också skapa statistik på en del av raderna i tabellen, vilket kallas filtrerad statistik.

Du kan till exempel använda filtrerad statistik när du planerar att fråga en specifik partition i en stor partitionerad tabell. Genom att bara skapa statistik för partitionsvärdena förbättras statistikens noggrannhet. Du får också en förbättring av frågeprestanda.

I det här exemplet skapas statistik för ett värdeintervall. Värdena kan enkelt definieras så att de matchar intervallet med värden i en partition.

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

Anteckning

För att frågeoptimeraren ska kunna överväga att använda filtrerad statistik när den väljer den distribuerade frågeplanen måste frågan få plats i definitionen av statistikobjektet. I föregående exempel måste frågans WHERE-sats ange col1-värden mellan 2000101 och 20001231.

Skapa statistik med en kolumn med alla alternativ

Du kan också kombinera alternativen tillsammans. I följande exempel skapas ett filtrerat statistikobjekt med en anpassad exempelstorlek:

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

Fullständig referens finns i SKAPA STATISTIK.

Skapa statistik för flera kolumner

Om du vill skapa ett statistikobjekt med flera kolumner använder du föregående exempel, men anger fler kolumner.

Anteckning

Histogrammet, som används för att uppskatta antalet rader i frågeresultatet, är bara tillgängligt för den första kolumnen som anges i definitionen av statistikobjektet.

I det här exemplet finns histogrammet på product_category. Statistik över flera kolumner beräknas på product_category och 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;

Eftersom det finns en korrelation mellan product_category och product_sub_category kan ett statistikobjekt med flera kolumner vara användbart om dessa kolumner används samtidigt. När du kör frågor mot den här tabellen förbättrar statistiken med flera kolumner kardinalitetsuppskattningar för kopplingar, GROUP BY-aggregeringar, distinkta antal och WHERE-filter (så länge den primära statistikkolumnen är en del av filtret).

Skapa statistik för alla kolumner i en tabell

Ett sätt att skapa statistik är att utfärda CREATE STATISTICS-kommandon när du har skapat tabellen:

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);

Använd en lagrad procedur för att skapa statistik för alla kolumner i en databas

SQL-poolen har ingen system lagrad procedur som motsvarar sp_create_stats i SQL Server. Den här lagrade proceduren skapar ett statistikobjekt med en kolumn i varje kolumn i databasen som inte redan har statistik.

I följande exempel får du hjälp att komma igång med databasdesignen. Du kan anpassa den efter dina behov:

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;

Om du vill skapa statistik för alla kolumner i tabellen med standardvärdena kör du den lagrade proceduren.

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

Om du vill skapa statistik för alla kolumner i tabellen med hjälp av en fullständig genomsökning anropar du den här proceduren:

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

Om du vill skapa samplad statistik för alla kolumner i tabellen anger du 3 och exempelprocenten. I proceduren nedan används en samplingsfrekvens på 20 procent.

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

Exempel: Uppdatera statistik

Om du vill uppdatera statistik kan du:

  • Uppdatera ett statistikobjekt. Ange namnet på statistikobjektet som du vill uppdatera.
  • Uppdatera alla statistikobjekt i en tabell. Ange namnet på tabellen i stället för ett specifikt statistikobjekt.

Uppdatera ett specifikt statistikobjekt

Använd följande syntax för att uppdatera ett specifikt statistikobjekt:

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

Exempel:

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

Genom att uppdatera specifika statistikobjekt kan du minimera den tid och de resurser som krävs för att hantera statistik. Den här åtgärden kräver vissa tankar för att välja de bästa statistikobjekten att uppdatera.

Uppdatera all statistik i en tabell

En enkel metod för att uppdatera alla statistikobjekt i en tabell är:

UPDATE STATISTICS [schema_name].[table_name];

Exempel:

UPDATE STATISTICS dbo.table1;

Instruktionen UPDATE STATISTICS är enkel att använda. Kom bara ihåg att den uppdaterar all statistik i tabellen, vilket leder till mer arbete än vad som är nödvändigt.

Om prestanda inte är ett problem är den här metoden det enklaste och mest kompletta sättet att garantera att statistiken är uppdaterad.

Anteckning

När du uppdaterar all statistik i en tabell gör den dedikerade SQL-poolen en genomsökning för att sampla tabellen för varje statistikobjekt. Om tabellen är stor och har många kolumner och många statistik kan det vara mer effektivt att uppdatera individuell statistik baserat på behov.

En implementering av en UPDATE STATISTICS procedur finns i Temporära tabeller. Implementeringsmetoden skiljer sig något från föregående CREATE STATISTICS procedur, men resultatet är detsamma. Fullständig syntax finns i Uppdatera statistik.

Metadata för statistik

Det finns flera systemvyer och funktioner som du kan använda för att hitta information om statistik. Du kan till exempel se om ett statistikobjekt kan vara inaktuellt med hjälp av funktionen STATS_DATE(). STATS_DATE() låter dig se när statistik senast skapades eller uppdaterades.

Katalogvyer för statistik

Dessa systemvyer innehåller information om statistik:

Katalogvy Description
sys.columns En rad för varje kolumn.
sys.objects En rad för varje objekt i databasen.
sys.schemas En rad för varje schema i databasen.
sys.stats En rad för varje statistikobjekt.
sys.stats_columns En rad för varje kolumn i statistikobjektet. Länkar tillbaka till sys.columns.
sys.tables En rad för varje tabell (innehåller externa tabeller).
sys.table_types En rad för varje datatyp.

Systemfunktioner för statistik

Dessa systemfunktioner är användbara för att arbeta med statistik:

Systemfunktion Description
STATS_DATE Datum då statistikobjektet senast uppdaterades.
DBCC-SHOW_STATISTICS Sammanfattningsnivå och detaljerad information om fördelningen av värden som tolkas av statistikobjektet.

Kombinera statistikkolumner och funktioner i en vy

Den här vyn för samman kolumner som relaterar till statistik och resultat från funktionen 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
;

DBCC SHOW_STATISTICS()-exempel

DBCC SHOW_STATISTICS() visar data som lagras i ett statistikobjekt. Dessa data kommer i tre delar:

  • Huvud
  • Densitetsvektor
  • Histogram

Rubriken är metadata om statistiken. Histogrammet visar fördelningen av värden i den första nyckelkolumnen i statistikobjektet.

Densitetsvektorn mäter korrelation mellan kolumner. Dedikerad SQL-pool beräknar kardinalitetsuppskattningar med någon av data i statistikobjektet.

Visa sidhuvud, densitet och histogram

Det här enkla exemplet visar alla tre delarna i ett statistikobjekt:

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

Exempel:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

Visa en eller flera delar av DBCC SHOW_STATISTICS()

Om du bara är intresserad av att visa specifika delar använder WITH du satsen och anger vilka delar du vill se:

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

Exempel:

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

DBCC SHOW_STATISTICS() skillnader

DBCC SHOW_STATISTICS()implementeras striktare i en dedikerad SQL-pool jämfört med SQL Server:

  • Odokumenterade funktioner stöds inte.
  • Det går inte att använda Stats_stream.
  • Det går inte att koppla resultat för specifika delmängder av statistikdata. Till exempel STAT_HEADER JOIN DENSITY_VECTOR.
  • NO_INFOMSGS kan inte anges för undertryckning av meddelanden.
  • Hakparenteser runt statistiknamn kan inte användas.
  • Det går inte att använda kolumnnamn för att identifiera statistikobjekt.
  • Det anpassade felet 2767 stöds inte.

Statistik i en serverlös SQL-pool

Statistik skapas per viss kolumn för en viss datauppsättning (lagringssökväg).

Anteckning

Det går inte att skapa statistik för LOB-kolumner.

Varför använda statistik

Ju mer serverlös SQL-pool som känner till dina data, desto snabbare kan den köra frågor mot dem. Att samla in statistik om dina data är en av de viktigaste sakerna du kan göra för att optimera dina frågor.

Frågeoptimeraren för serverlös SQL-pool är en kostnadsbaserad optimerare. Den jämför kostnaden för olika frågeplaner och väljer sedan planen med den lägsta kostnaden. I de flesta fall väljer den den plan som ska köras snabbast.

Om optimeraren till exempel uppskattar att det datum då frågan filtreras returnerar en rad väljer den en plan. Om den beräknar att det valda datumet returnerar 1 miljon rader väljer den en annan plan.

Automatiskt skapande av statistik

Serverlös SQL-pool analyserar inkommande användarfrågor för statistik som saknas. Om statistik saknas skapar frågeoptimeraren statistik för enskilda kolumner i frågepredikatet eller kopplingsvillkoret för att förbättra kardinalitetsuppskattningarna för frågeplanen.

SELECT-instruktionen utlöser automatisk skapande av statistik.

Anteckning

För automatisk skapande av statistik används sampling och i de flesta fall är samplingsprocenten mindre än 100 %. Det här flödet är detsamma för varje filformat. Tänk på att när du läser CSV med parser version 1.0 stöds inte sampling och automatisk skapande av statistik sker inte med samplingsprocent mindre än 100 %. För små tabeller med uppskattad låg kardinalitet (antal rader) utlöses automatisk statistikgenerering med samplingsprocent på 100 %. Det innebär i princip att fullscan utlöses och automatisk statistik skapas även för CSV med parser version 1.0.

Automatisk skapande av statistik görs synkront så att du kan få något sämre frågeprestanda om dina kolumner saknar statistik. Tiden för att skapa statistik för en enda kolumn beror på storleken på de filer som är riktade.

Manuellt skapande av statistik

Med en serverlös SQL-pool kan du skapa statistik manuellt. Om du använder parser version 1.0 med CSV måste du förmodligen skapa statistik manuellt, eftersom den här parserversionen inte stöder sampling. Automatisk skapande av statistik vid parsning av version 1.0 sker inte, såvida inte samplingsprocenten är 100 %.

Se följande exempel för instruktioner om hur du skapar statistik manuellt.

Uppdatera statistik

Ändringar av data i filer, borttagning och tillägg av filer resulterar i datadistributionsändringar och gör statistiken inaktuell. I så fall måste statistiken uppdateras.

Serverlös SQL-pool återskapar automatiskt statistik om data ändras avsevärt. Varje gång statistik skapas automatiskt sparas även datamängdens aktuella tillstånd: filsökvägar, storlekar, senaste ändringsdatum.

När statistiken är inaktuell skapas nya. Algoritmen går igenom data och jämför dem med datauppsättningens aktuella tillstånd. Om storleken på ändringarna är större än det specifika tröskelvärdet tas gammal statistik bort och skapas på nytt över den nya datauppsättningen.

Manuell statistik förklaras aldrig inaktuell.

Anteckning

För automatisk återskapning av statistik används sampling och i de flesta fall är samplingsprocenten mindre än 100 %. Det här flödet är detsamma för varje filformat. Tänk på att när du läser CSV med parser version 1.0 sampling stöds inte och automatisk rekreation av statistik kommer inte att ske med samplingsprocent mindre än 100%. I så fall måste du ta bort och återskapa statistik manuellt. Kontrollera exemplen nedan om hur du släpper och skapar statistik. För små tabeller med uppskattad låg kardinalitet (antal rader) utlöses automatisk statistik med samplingsprocent på 100 %. Det innebär i princip att fullscan utlöses och automatisk statistik skapas även för CSV med parser version 1.0.

En av de första frågorna att ställa när du felsöker en fråga är "Är statistiken uppdaterad?"

När antalet rader har ändrats avsevärt, eller om det sker en väsentlig ändring i fördelningen av värden för en kolumn, är det dags att uppdatera statistiken.

Anteckning

Om det sker en väsentlig ändring i fördelningen av värden för en kolumn bör du uppdatera statistiken oavsett den senaste gången de uppdaterades.

Implementera statistikhantering

Du kanske vill utöka din datapipeline för att säkerställa att statistiken uppdateras när data ändras avsevärt genom tillägg, borttagning eller ändring av filer.

Följande riktlinjer finns för att uppdatera din statistik:

  • Kontrollera att datauppsättningen har minst ett statistikobjekt uppdaterat. Den här uppdateringsstorleken (radantal och antal sidor) som en del av statistikuppdateringen.
  • Fokusera på kolumner som deltar i WHERE-, JOIN-, GROUP BY-, ORDER BY- och DISTINCT-satser.
  • Uppdatera kolumner med "stigande nyckel", till exempel transaktionsdatum oftare eftersom dessa värden inte tas med i statistik histogrammet.
  • Uppdatera statiska distributionskolumner mindre ofta.

Mer information finns i Kardinalitetsuppskattning.

Exempel: Skapa statistik för kolumnen i OPENROWSET-sökvägen

I följande exempel visas hur du använder olika alternativ för att skapa statistik i Azure Synapse serverlösa SQL-pooler. Vilka alternativ du använder för varje kolumn beror på egenskaperna för dina data och hur kolumnen ska användas i frågor. Mer information om lagrade procedurer som används i dessa exempel finns i sys.sp_create_openrowset_statistics och sys.sp_drop_openrowset_statistics, som endast gäller för serverlösa SQL-pooler.

Anteckning

Du kan bara skapa statistik med en kolumn just nu.

Följande behörigheter krävs för att köra sp_create_openrowset_statistics och sp_drop_openrowset_statistics: ADMINISTRERA BULKÅTGÄRDER eller ADMINISTRERA MASSÅTGÄRDER FÖR DATABASER.

Följande lagrade procedur används för att skapa statistik:

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

Argument: [ @stmt = ] N'statement_text' – Anger en Transact-SQL-instruktion som returnerar kolumnvärden som ska användas för statistik. Du kan använda TABLESAMPLE för att ange exempel på data som ska användas. Om TABLESAMPLE inte har angetts används FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Anteckning

CSV-sampling fungerar inte om du använder parser version 1.0, endast FULLSCAN stöds för CSV med parser version 1.0.

Skapa statistik med en kolumn genom att undersöka varje rad

Om du vill skapa statistik för en kolumn anger du en fråga som returnerar den kolumn som du behöver statistik för.

Om du inte anger något annat när du skapar statistik manuellt använder serverlös SQL-pool som standard 100 % av de data som anges i datauppsättningen när den skapar statistik.

Om du till exempel vill skapa statistik med standardalternativ (FULLSCAN) för en populationskolumn i datauppsättningen baserat på filen 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]'

Skapa statistik med en kolumn genom att ange exempelstorleken

Du kan ange exempelstorleken som procent:

/* 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)
'

Exempel: Uppdatera statistik

Om du vill uppdatera statistik måste du ta bort och skapa statistik. Mer information finns i sys.sp_create_openrowset_statistics och sys.sp_drop_openrowset_statistics.

Den sys.sp_drop_openrowset_statistics lagrade proceduren används för att ta bort statistik:

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

Anteckning

Följande behörigheter krävs för att köra sp_create_openrowset_statistics och sp_drop_openrowset_statistics: ADMINISTRERA BULKÅTGÄRDER eller ADMINISTRERA MASSÅTGÄRDER FÖR DATABASER.

Argument: [ @stmt = ] N'statement_text' – Anger samma Transact-SQL-instruktion som användes när statistiken skapades.

Om du vill uppdatera statistiken för kolumnen year i datauppsättningen population.csv , som baseras på filen, måste du ta bort och skapa statistik:

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)
'

Exempel: Skapa statistik för extern tabellkolumn

I följande exempel visas hur du använder olika alternativ för att skapa statistik. Vilka alternativ du använder för varje kolumn beror på egenskaperna för dina data och hur kolumnen ska användas i frågor.

Anteckning

Du kan bara skapa statistik med en kolumn just nu.

Om du vill skapa statistik för en kolumn anger du ett namn för statistikobjektet och namnet på kolumnen.

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

Argument: external_table Anger extern tabell som statistik ska skapas.

FULLSCAN Compute-statistik genom att genomsöka alla rader. FULLSCAN och SAMPLE 100 PROCENT har samma resultat. FULLSCAN kan inte användas med alternativet EXEMPEL.

EXEMPELnummer PROCENT Anger den ungefärliga procentandelen eller antalet rader i tabellen eller den indexerade vyn som frågeoptimeraren ska använda när den skapar statistik. Talet kan vara mellan 0 och 100.

EXEMPEL kan inte användas med alternativet FULLSCAN.

Anteckning

CSV-sampling fungerar inte om du använder parser version 1.0, endast FULLSCAN stöds för CSV med parser version 1.0.

Skapa statistik med en kolumn genom att undersöka varje rad

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Skapa statistik med en kolumn genom att ange exempelstorleken

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

Exempel: Uppdatera statistik

Om du vill uppdatera statistiken måste du ta bort och skapa statistik. Ta bort statistik först:

DROP STATISTICS census_external_table.sState

Och skapa statistik:

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Metadata för statistik

Det finns flera systemvyer och funktioner som du kan använda för att hitta information om statistik. Du kan till exempel se om ett statistikobjekt kan vara inaktuellt med hjälp av funktionen STATS_DATE(). STATS_DATE() låter dig se när statistik senast skapades eller uppdaterades.

Anteckning

Metadata för statistik är endast tillgängliga för externa tabellkolumner. Metadata för statistik är inte tillgängliga för OPENROWSET-kolumner.

Katalogvyer för statistik

Dessa systemvyer innehåller information om statistik:

Katalogvy Description
sys.columns En rad för varje kolumn.
sys.objects En rad för varje objekt i databasen.
sys.schemas En rad för varje schema i databasen.
sys.stats En rad för varje statistikobjekt.
sys.stats_columns En rad för varje kolumn i statistikobjektet. Länkar tillbaka till sys.columns.
sys.tables En rad för varje tabell (innehåller externa tabeller).
sys.table_types En rad för varje datatyp.

Systemfunktioner för statistik

Dessa systemfunktioner är användbara för att arbeta med statistik:

Systemfunktion Description
STATS_DATE Datum då statistikobjektet senast uppdaterades.

Kombinera statistikkolumner och funktioner i en vy

Den här vyn för samman kolumner som relaterar till statistik och resultat från funktionen 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
;

Nästa steg

Mer information om hur du ytterligare förbättrar frågeprestanda för dedikerad SQL-pool finns i Övervaka din arbetsbelastning och Metodtips för dedikerad SQL-pool.

Mer information om hur du ytterligare förbättrar frågeprestanda för serverlös SQL-pool finns i Metodtips för serverlös SQL-pool.