Delen via

Statistieken in Synapse SQL

  • Artikel

Dit artikel bevat aanbevelingen en voorbeelden voor het maken en bijwerken van statistieken voor queryoptimalisatie met behulp van de Synapse SQL-resources: toegewezen SQL-pool en serverloze SQL-pool.

Statistieken in toegewezen SQL-pool

Waarom statistieken gebruiken

Hoe meer toegewezen SQL-pool weet over uw gegevens, hoe sneller query's kunnen worden uitgevoerd. Na het laden van gegevens in een toegewezen SQL-pool is het verzamelen van statistieken over uw gegevens een van de belangrijkste dingen die u kunt doen voor queryoptimalisatie.

Het queryoptimalisatieprogramma voor toegewezen SQL-pools is een optimalisatieprogramma op basis van kosten. De kosten van verschillende queryplannen worden vergeleken en vervolgens wordt het abonnement met de laagste kosten gekozen. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.

Als het optimalisatieprogramma bijvoorbeeld schat dat de datum waarop uw query wordt gefilterd, één rij retourneert, wordt er één plan gekozen. Als wordt geschat dat de geselecteerde datum 1 miljoen rijen retourneert, wordt een ander plan geretourneerd.

Automatisch statistieken maken

De toegewezen SQL-poolengine analyseert binnenkomende gebruikersquery's op ontbrekende statistieken wanneer de optie database AUTO_CREATE_STATISTICS is ingesteld op ON. Als er statistieken ontbreken, maakt het queryoptimalisatieprogramma statistieken voor afzonderlijke kolommen in het querypredicaat of de joinvoorwaarde.

Deze functie wordt gebruikt om de schattingen van kardinaliteit voor het queryplan te verbeteren.

Belangrijk

Automatisch maken van statistieken is momenteel standaard ingeschakeld.

U kunt controleren of uw datawarehouse AUTO_CREATE_STATISTICS geconfigureerd door de volgende opdracht uit te voeren:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Als AUTO_CREATE_STATISTICS niet is ingeschakeld voor uw datawarehouse, raden we u aan deze eigenschap in te schakelen door de volgende opdracht uit te voeren:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Deze instructies activeren het automatisch maken van statistieken:

  • SELECT
  • INSERT-SELECT
  • CTAS
  • UPDATE
  • DELETE
  • UITLEGGEN wanneer er een join of de aanwezigheid van een predicaat wordt gedetecteerd

Notitie

Het automatisch maken van statistieken wordt niet gegenereerd voor tijdelijke of externe tabellen.

Het automatisch maken van statistieken wordt synchroon uitgevoerd. De queryprestaties kunnen dus enigszins verslechteren als er statistieken ontbreken in uw kolommen. De tijd voor het maken van statistieken voor één kolom is afhankelijk van de grootte van de tabel.

Om meetbare prestatievermindering te voorkomen, moet u ervoor zorgen dat er eerst statistieken zijn gemaakt door de benchmarkworkload uit te voeren voordat u het systeem profileert.

Notitie

Het maken van statistieken wordt geregistreerd in sys.dm_pdw_exec_requests onder een andere gebruikerscontext.

Wanneer automatische statistieken worden gemaakt, hebben ze de volgende vorm: WA_Sys<kolom-id van 8 cijfers in Hex>_<8-cijferige tabel-id in Hex>. U kunt al gemaakte statistieken weergeven door de opdracht DBCC SHOW_STATISTICS uit te voeren:

DBCC SHOW_STATISTICS (<table_name>, <target>)

De table_name is de naam van de tabel die de weer te geven statistieken bevat. Dit kan geen externe tabel zijn. Het doel is de naam van de doelindex, statistieken of kolom waarvoor statistische informatie moet worden weergegeven.

Statistieken bijwerken

Een best practice is om statistieken voor datumkolommen elke dag bij te werken wanneer er nieuwe datums worden toegevoegd. Telkens wanneer er nieuwe rijen in het datawarehouse worden geladen, worden nieuwe laad- of transactiedatums toegevoegd. Deze toevoegingen wijzigen de gegevensdistributie en maken de statistieken verouderd.

Statistieken voor een kolom land of regio in een klanttabel hoeven mogelijk nooit te worden bijgewerkt, omdat de verdeling van waarden meestal niet verandert. Ervan uitgaande dat de distributie tussen klanten constant is, verandert het toevoegen van nieuwe rijen aan de tabelvariatie de gegevensdistributie niet.

Wanneer uw datawarehouse echter slechts één land of regio bevat en u gegevens uit een nieuw land of een nieuwe regio binnenbrengt, moet u de statistieken voor de kolom land of regio bijwerken.

Hier volgen aanbevelingen voor het bijwerken van statistieken:

Type Aanbeveling
Frequentie van statistiekenupdates Conservatief: Dagelijks
na het laden of transformeren van uw gegevens
Steekproeven Gebruik standaardsampling (20 procent) van minder dan 1 miljard rijen.
Gebruik met meer dan 1 miljard rijen steekproeven van twee procent.

Laatste update van statistieken bepalen

Een van de eerste vragen die u moet stellen wanneer u problemen met een query wilt oplossen, is : 'Zijn de statistieken up-to-date?'

Deze vraag kan niet worden beantwoord door de leeftijd van de gegevens. Een up-to-date statistische object kan oud zijn als er geen belangrijke wijziging is aangebracht in de onderliggende gegevens. Wanneer het aantal rijen aanzienlijk is gewijzigd of wanneer er een belangrijke wijziging optreedt in de verdeling van waarden voor een kolom, is het tijd om de statistieken bij te werken.

Er is geen dynamische beheerweergave beschikbaar om te bepalen of de gegevens in de tabel zijn gewijzigd sinds de laatste keer dat statistieken zijn bijgewerkt. Als u de leeftijd van uw statistieken kent, kunt u een deel van de afbeelding krijgen.

U kunt de volgende query gebruiken om te bepalen wanneer uw statistieken voor het laatst zijn bijgewerkt voor elke tabel.

Notitie

Als er een wezenlijke wijziging is in de verdeling van waarden voor een kolom, moet u de statistieken bijwerken, ongeacht de laatste keer dat ze zijn bijgewerkt.

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;

Datumkolommen in een datawarehouse moeten bijvoorbeeld meestal regelmatig worden bijgewerkt. Telkens wanneer er nieuwe rijen in het datawarehouse worden geladen, worden nieuwe laad- of transactiedatums toegevoegd. Deze toevoegingen wijzigen de gegevensdistributie en maken de statistieken verouderd.

Statistieken over een geslachtskolom in een klanttabel hoeven mogelijk nooit te worden bijgewerkt. Ervan uitgaande dat de distributie tussen klanten constant is, verandert het toevoegen van nieuwe rijen aan de tabelvariatie de gegevensdistributie niet.

Maar als uw datawarehouse slechts één geslacht bevat en een nieuwe vereiste resulteert in meerdere geslachten, moet u de statistieken voor de geslachtskolom bijwerken.

Raadpleeg het artikel Statistieken voor meer informatie.

Statistiekenbeheer implementeren

Het is vaak een goed idee om uw proces voor het laden van gegevens uit te breiden om ervoor te zorgen dat statistieken aan het einde van de belasting worden bijgewerkt. De gegevensbelasting is wanneer tabellen het vaakst hun grootte, verdeling van waarden of beide wijzigen. Als zodanig is het laadproces een logische plaats om sommige beheerprocessen te implementeren.

De volgende richtlijnen worden geboden voor het bijwerken van uw statistieken tijdens het laadproces:

  • Zorg ervoor dat voor elke geladen tabel ten minste één statistiekenobject is bijgewerkt. Met dit proces worden de gegevens over de tabelgrootte (aantal rijen en pagina's) bijgewerkt als onderdeel van de update van de statistieken.
  • Focus op kolommen die deel uitmaken van JOIN-, GROUP BY-, ORDER BY- en DISTINCT-componenten.
  • Overweeg om kolommen met oplopende sleutels, zoals transactiedatums, vaker bij te werken, omdat deze waarden niet worden opgenomen in het histogram van de statistieken.
  • Overweeg statische distributiekolommen minder vaak bij te werken.
  • Houd er rekening mee dat elk statistiekobject opeenvolgend wordt bijgewerkt. Eenvoudig implementeren UPDATE STATISTICS <TABLE_NAME> is niet altijd ideaal, met name voor brede tabellen met veel statistische objecten.

Zie Kardinaliteitschatting voor meer informatie.

Voorbeelden: Statistieken maken

In deze voorbeelden ziet u hoe u verschillende opties gebruikt voor het maken van statistieken. De opties die u voor elke kolom gebruikt, zijn afhankelijk van de kenmerken van uw gegevens en hoe de kolom wordt gebruikt in query's.

Statistieken met één kolom maken met standaardopties

Als u statistieken wilt maken voor een kolom, geeft u een naam op voor het statistische object en de naam van de kolom. Deze syntaxis maakt gebruik van alle standaardopties. De toegewezen SQL-pool bevat standaard voorbeelden van 20 procent van de tabel wanneer er statistieken worden gemaakt.

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

Bijvoorbeeld:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Statistieken met één kolom maken door elke rij te bekijken

De standaardsampling van 20 procent is voldoende voor de meeste situaties. U kunt de steekproeffrequentie echter aanpassen. Gebruik deze syntaxis om een voorbeeld van de volledige tabel te nemen:

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

Bijvoorbeeld:

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

Statistieken met één kolom maken door de steekproefgrootte op te geven

Een andere optie die u hebt, is om de steekproefgrootte op te geven als een percentage:

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

Statistieken met één kolom maken voor slechts enkele rijen

U kunt ook statistieken maken voor een deel van de rijen in uw tabel. Dit wordt een gefilterde statistiek genoemd.

U kunt bijvoorbeeld gefilterde statistieken gebruiken wanneer u een query wilt uitvoeren op een specifieke partitie van een grote gepartitioneerde tabel. Door alleen statistieken te maken voor de partitiewaarden, wordt de nauwkeurigheid van de statistieken verbeterd. U ervaart ook een verbetering in de queryprestaties.

In dit voorbeeld worden statistieken gemaakt voor een bereik van waarden. De waarden kunnen eenvoudig worden gedefinieerd om overeen te komen met het bereik van waarden in een partitie.

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

Notitie

Als u wilt dat het queryoptimalisatieprogramma het gebruik van gefilterde statistieken overweegt bij het kiezen van het gedistribueerde queryplan, moet de query binnen de definitie van het statistische object passen. In het vorige voorbeeld moet de WHERE-component van de query col1-waarden opgeven tussen 2000101 en 20001231.

Statistieken met één kolom maken met alle opties

U kunt de opties ook combineren. In het volgende voorbeeld wordt een gefilterd statistiekenobject gemaakt met een aangepaste steekproefgrootte:

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

Zie CREATE STATISTICS (STATISTIEKEN MAKEN) voor het volledige overzicht.

Statistieken met meerdere kolommen maken

Als u een statistische object met meerdere kolommen wilt maken, gebruikt u de vorige voorbeelden, maar geeft u meer kolommen op.

Notitie

Het histogram, dat wordt gebruikt om het aantal rijen in het queryresultaat te schatten, is alleen beschikbaar voor de eerste kolom die wordt vermeld in de objectdefinitie statistieken.

In dit voorbeeld bevindt het histogram zich op product_category. Statistieken over meerdere kolommen worden berekend op product_category en 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;

Omdat er een correlatie bestaat tussen product_category en product_sub_category, kan een statistische object met meerdere kolommen nuttig zijn als deze kolommen tegelijkertijd worden geopend. Wanneer u een query uitvoert op deze tabel, verbeteren de statistieken met meerdere kolommen de schattingen van kardinaliteit voor joins, GROUP BY-aggregaties, afzonderlijke aantallen en WHERE-filters (zolang de kolom met primaire statistieken deel uitmaakt van het filter).

Statistieken maken voor alle kolommen in een tabel

Een manier om statistieken te maken, is door de opdrachten CREATE STATISTICS uit te voeren nadat u de tabel hebt gemaakt:

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

Een opgeslagen procedure gebruiken om statistieken te maken voor alle kolommen in een database

SQL-pool heeft geen door het systeem opgeslagen procedure die gelijk is aan sp_create_stats in SQL Server. Met deze opgeslagen procedure wordt een statistische object met één kolom gemaakt voor elke kolom van de database die nog geen statistieken bevat.

Het volgende voorbeeld helpt u om aan de slag te gaan met uw databaseontwerp. U kunt deze aanpassen aan uw behoeften:

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;

Als u statistieken wilt maken voor alle kolommen in de tabel met behulp van de standaardinstellingen, voert u de opgeslagen procedure uit.

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

Als u statistieken wilt maken voor alle kolommen in de tabel met behulp van een fullscan, roept u deze procedure aan:

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

Als u steekproefstatistieken wilt maken voor alle kolommen in de tabel, voert u 3 en het steekproefpercentage in. In de onderstaande procedure wordt een steekproeffrequentie van 20 procent gebruikt.

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

Voorbeelden: Statistieken bijwerken

Als u statistieken wilt bijwerken, kunt u het volgende doen:

  • Eén statistische object bijwerken. Geef de naam op van het statistische object dat u wilt bijwerken.
  • Alle statistiekenobjecten in een tabel bijwerken. Geef de naam van de tabel op in plaats van één specifiek statistische object.

Eén specifiek statistische object bijwerken

Gebruik de volgende syntaxis om een specifiek statistische object bij te werken:

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

Bijvoorbeeld:

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

Door specifieke statistiekenobjecten bij te werken, kunt u de benodigde tijd en resources voor het beheren van statistieken minimaliseren. Voor deze actie moet u nadenken over het selecteren van de beste statistiekenobjecten die u wilt bijwerken.

Alle statistieken voor een tabel bijwerken

Een eenvoudige methode voor het bijwerken van alle statistische objecten in een tabel is:

UPDATE STATISTICS [schema_name].[table_name];

Bijvoorbeeld:

UPDATE STATISTICS dbo.table1;

De instructie UPDATE STATISTICS is eenvoudig te gebruiken. Vergeet niet dat alle statistieken in de tabel worden bijgewerkt, waardoor er meer werk wordt gevraagd dan nodig is.

Als de prestaties geen probleem zijn, is deze methode de eenvoudigste en meest volledige manier om te garanderen dat statistieken up-to-date zijn.

Notitie

Wanneer u alle statistieken voor een tabel bijwerkt, scant de toegewezen SQL-pool de tabel voor elk statistische object. Als de tabel groot is en veel kolommen en statistieken bevat, is het mogelijk efficiënter om afzonderlijke statistieken bij te werken op basis van behoefte.

Zie Tijdelijke tabellen voor een implementatie van een UPDATE STATISTICS procedure. De implementatiemethode verschilt enigszins van de voorgaande CREATE STATISTICS procedure, maar het resultaat is hetzelfde. Zie Statistieken bijwerken voor de volledige syntaxis.

Metagegevens van statistieken

Er zijn verschillende systeemweergaven en functies die u kunt gebruiken om informatie over statistieken te vinden. U kunt bijvoorbeeld zien of een statistische object verouderd is met behulp van de functie STATS_DATE(). met STATS_DATE() kunt u zien wanneer statistieken voor het laatst zijn gemaakt of bijgewerkt.

Catalogusweergaven voor statistieken

Deze systeemweergaven bieden informatie over statistieken:

Catalogusweergave Beschrijving
sys.columns Eén rij voor elke kolom.
sys.objects Eén rij voor elk object in de database.
sys.schemas Eén rij voor elk schema in de database.
sys.stats Eén rij voor elk statistische object.
sys.stats_columns Eén rij voor elke kolom in het statistische object. Koppelingen terug naar sys.columns.
sys.tables Eén rij voor elke tabel (inclusief externe tabellen).
sys.table_types Eén rij voor elk gegevenstype.

Systeemfuncties voor statistieken

Deze systeemfuncties zijn handig voor het werken met statistieken:

Systeemfunctie Beschrijving
STATS_DATE Datum waarop het statistische object voor het laatst is bijgewerkt.
DBCC-SHOW_STATISTICS Samenvattingsniveau en gedetailleerde informatie over de verdeling van waarden zoals begrepen door het statistische object.

Statistische kolommen en functies combineren in één weergave

In deze weergave worden kolommen die betrekking hebben op statistieken en resultaten van de functie STATS_DATE() samengebracht.

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() voorbeelden

DBCC SHOW_STATISTICS() toont de gegevens die zijn opgeslagen in een statistics-object. Deze gegevens bestaan uit drie delen:

  • Header
  • Dichtheidsvector
  • Histogram

De header is de metagegevens van de statistieken. In het histogram wordt de verdeling van waarden in de eerste sleutelkolom van het statistische object weergegeven.

De dichtheidsvector meet de correlatie tussen kolommen. Toegewezen SQL-pool berekent schattingen van kardinaliteit met een van de gegevens in het statistische object.

Koptekst, dichtheid en histogram weergeven

In dit eenvoudige voorbeeld ziet u alle drie de onderdelen van een statistische object:

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

Bijvoorbeeld:

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

Een of meer onderdelen van DBCC SHOW_STATISTICS() weergeven

Als u alleen specifieke onderdelen wilt bekijken, gebruikt u de WITH -component en geeft u op welke onderdelen u wilt zien:

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

Bijvoorbeeld:

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

DBCC SHOW_STATISTICS() verschillen

DBCC SHOW_STATISTICS()is strikter geïmplementeerd in een toegewezen SQL-pool in vergelijking met SQL Server:

  • Niet-gedocumenteerde functies worden niet ondersteund.
  • Kan Stats_stream niet gebruiken.
  • Kan geen resultaten samenvoegen voor specifieke subsets van statistische gegevens. STAT_HEADER JOIN-DENSITY_VECTOR.
  • NO_INFOMSGS kan niet worden ingesteld voor berichtonderdrukking.
  • Vierkante haken rond namen van statistieken kunnen niet worden gebruikt.
  • Kan geen kolomnamen gebruiken om statistische objecten te identificeren.
  • Aangepaste fout 2767 wordt niet ondersteund.

Statistieken in serverloze SQL-pool

Statistieken worden gemaakt per specifieke kolom voor een bepaalde gegevensset (opslagpad).

Notitie

Er kunnen geen statistieken worden gemaakt voor LOB-kolommen.

Waarom statistieken gebruiken

Hoe meer serverloze SQL-pool op de hoogte is van uw gegevens, hoe sneller er query's op kunnen worden uitgevoerd. Het verzamelen van statistieken over uw gegevens is een van de belangrijkste dingen die u kunt doen om uw query's te optimaliseren.

Het queryoptimalisatieprogramma voor serverloze SQL-pools is een optimalisatieprogramma op basis van kosten. De kosten van verschillende queryplannen worden vergeleken en vervolgens wordt het abonnement met de laagste kosten gekozen. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.

Als het optimalisatieprogramma bijvoorbeeld schat dat de datum waarop uw query filtert, één rij retourneert, wordt er één plan gekozen. Als wordt geschat dat de geselecteerde datum 1 miljoen rijen retourneert, wordt een ander plan gekozen.

Automatisch statistieken maken

Serverloze SQL-pool analyseert binnenkomende gebruikersquery's op ontbrekende statistieken. Als statistieken ontbreken, maakt het queryoptimalisatieprogramma statistieken voor afzonderlijke kolommen in het querypredicaat of de joinvoorwaarde om de kardinaliteitschattingen voor het queryplan te verbeteren.

De SELECT-instructie activeert het automatisch maken van statistieken.

Notitie

Voor het automatisch maken van statistieken wordt steekproeven gebruikt en in de meeste gevallen is het steekproefpercentage lager dan 100%. Deze stroom is hetzelfde voor elke bestandsindeling. Houd er rekening mee dat bij het lezen van CSV met parserversie 1.0 steekproeven niet wordt ondersteund en dat het automatisch maken van statistieken niet plaatsvindt met een steekproefpercentage van minder dan 100%. Voor kleine tabellen met een geschatte lage kardinaliteit (aantal rijen) wordt het automatisch maken van statistieken geactiveerd met een steekproefpercentage van 100%. Dat betekent in feite dat fullscan wordt geactiveerd en automatische statistieken worden gemaakt, zelfs voor CSV met parserversie 1.0.

Het automatisch maken van statistieken wordt synchroon uitgevoerd, zodat de queryprestaties mogelijk enigszins verslechteren als er statistieken ontbreken in uw kolommen. De tijd voor het maken van statistieken voor één kolom is afhankelijk van de grootte van de gewenste bestanden.

Handmatig statistieken maken

Met een serverloze SQL-pool kunt u handmatig statistieken maken. Als u parserversie 1.0 met CSV gebruikt, moet u waarschijnlijk handmatig statistieken maken, omdat deze parserversie geen ondersteuning biedt voor steekproeven. Het automatisch maken van statistieken in het geval van parserversie 1.0 wordt niet uitgevoerd, tenzij het steekproefpercentage 100% is.

Zie de volgende voorbeelden voor instructies over het handmatig maken van statistieken.

Statistieken bijwerken

Wijzigingen in gegevens in bestanden, het verwijderen en toevoegen van bestanden leiden tot wijzigingen in de gegevensdistributie en maken statistieken verouderd. In dat geval moeten de statistieken worden bijgewerkt.

De serverloze SQL-pool maakt automatisch statistieken opnieuw als de gegevens aanzienlijk worden gewijzigd. Telkens wanneer statistieken automatisch worden gemaakt, wordt ook de huidige status van de gegevensset opgeslagen: bestandspaden, grootten, datums van laatste wijziging.

Wanneer statistieken verouderd zijn, worden er nieuwe gemaakt. Het algoritme doorloopt de gegevens en vergelijkt deze met de huidige status van de gegevensset. Als de grootte van de wijzigingen groter is dan de specifieke drempelwaarde, worden oude statistieken verwijderd en opnieuw gemaakt voor de nieuwe gegevensset.

Handmatige statistieken worden nooit verouderd gedeclareerd.

Notitie

Voor het automatisch recreëren van statistieken wordt steekproeven gebruikt en in de meeste gevallen zal het steekproefpercentage lager zijn dan 100%. Deze stroom is hetzelfde voor elke bestandsindeling. Houd er rekening mee dat bij het lezen van CSV met parserversie 1.0 steekproeven niet worden ondersteund en dat het automatisch opnieuw genereren van statistieken niet plaatsvindt met een steekproefpercentage van minder dan 100%. In dat geval moet u statistieken handmatig verwijderen en opnieuw maken. Bekijk de onderstaande voorbeelden over het verwijderen en maken van statistieken. Voor kleine tabellen met een geschatte lage kardinaliteit (aantal rijen) wordt automatische statistische recreatie geactiveerd met een steekproefpercentage van 100%. Dat betekent in feite dat fullscan wordt geactiveerd en automatische statistieken worden gemaakt, zelfs voor CSV met parserversie 1.0.

Een van de eerste vragen die u moet stellen wanneer u problemen met een query wilt oplossen, is : 'Zijn de statistieken up-to-date?'

Wanneer het aantal rijen aanzienlijk is gewijzigd of als er een belangrijke wijziging is in de verdeling van waarden voor een kolom, is het tijd om de statistieken bij te werken.

Notitie

Als er een wezenlijke wijziging is in de verdeling van waarden voor een kolom, moet u de statistieken bijwerken, ongeacht de laatste keer dat ze zijn bijgewerkt.

Statistiekenbeheer implementeren

U kunt uw gegevenspijplijn uitbreiden om ervoor te zorgen dat statistieken worden bijgewerkt wanneer gegevens aanzienlijk worden gewijzigd door het toevoegen, verwijderen of wijzigen van bestanden.

De volgende uitgangspunten worden geboden voor het bijwerken van uw statistieken:

  • Zorg ervoor dat de gegevensset ten minste één statistiekenobject heeft bijgewerkt. Deze informatie over de grootte van deze updates (aantal rijen en pagina's) als onderdeel van de update van de statistieken.
  • Richt u op kolommen die deelnemen aan WHERE-, JOIN-, GROUP BY-, ORDER BY- en DISTINCT-componenten.
  • Werk oplopende sleutelkolommen zoals transactiedatums vaker bij, omdat deze waarden niet worden opgenomen in het histogram voor statistieken.
  • Werk statische distributiekolommen minder vaak bij.

Zie Kardinaliteitschatting voor meer informatie.

Voorbeelden: Statistieken maken voor kolom in OPENROWSET-pad

In de volgende voorbeelden ziet u hoe u verschillende opties gebruikt voor het maken van statistieken in Azure Synapse serverloze SQL-pools. De opties die u voor elke kolom gebruikt, zijn afhankelijk van de kenmerken van uw gegevens en hoe de kolom wordt gebruikt in query's. Raadpleeg sys.sp_create_openrowset_statistics en sys.sp_drop_openrowset_statistics, die alleen van toepassing zijn op serverloze SQL-pools voor meer informatie over de opgeslagen procedures die in deze voorbeelden worden gebruikt.

Notitie

U kunt op dit moment alleen statistieken met één kolom maken.

De volgende machtigingen zijn vereist voor het uitvoeren sp_create_openrowset_statistics van en sp_drop_openrowset_statistics: BULKBEWERKINGEN BEHEREN of BULKBEWERKINGEN VOOR DE DATABASE BEHEREN.

De volgende opgeslagen procedure wordt gebruikt om statistieken te maken:

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

Argumenten: [ @stmt = ] N'statement_text': hiermee geeft u een Transact-SQL-instructie op die kolomwaarden retourneert die moeten worden gebruikt voor statistieken. U kunt TABLESAMPLE gebruiken om voorbeelden van gegevens op te geven die moeten worden gebruikt. Als TABLESAMPLE niet is opgegeven, wordt FULLSCAN gebruikt.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Notitie

CSV-steekproeven werken niet als u parserversie 1.0 gebruikt. Alleen FULLSCAN wordt ondersteund voor CSV met parserversie 1.0.

Statistieken met één kolom maken door elke rij te bekijken

Als u statistieken wilt maken voor een kolom, geeft u een query op die de kolom retourneert waarvoor u statistieken nodig hebt.

Als u geen andere gegevens opgeeft bij het handmatig maken van statistieken, gebruikt de serverloze SQL-pool standaard 100% van de gegevens die in de gegevensset zijn opgegeven bij het maken van statistieken.

Als u bijvoorbeeld statistieken wilt maken met standaardopties (FULLSCAN) voor een populatiekolom van de gegevensset op basis van het us_population.csv-bestand:


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

Statistieken met één kolom maken door de steekproefgrootte op te geven

U kunt de grootte van de steekproef opgeven als een percentage:

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

Voorbeelden: Statistieken bijwerken

Als u statistieken wilt bijwerken, moet u statistieken verwijderen en maken. Raadpleeg sys.sp_create_openrowset_statistics en sys.sp_drop_openrowset_statistics voor meer informatie.

De sys.sp_drop_openrowset_statistics opgeslagen procedure wordt gebruikt om statistieken te verwijderen:

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

Notitie

De volgende machtigingen zijn vereist voor het uitvoeren sp_create_openrowset_statistics van en sp_drop_openrowset_statistics: BULKBEWERKINGEN BEHEREN of BULKBEWERKINGEN VOOR DE DATABASE BEHEREN.

Argumenten: [ @stmt = ] N'statement_text': hiermee geeft u dezelfde Transact-SQL-instructie op die is gebruikt bij het maken van de statistieken.

Als u de statistieken voor de jaarkolom in de gegevensset wilt bijwerken, die is gebaseerd op het population.csv bestand, moet u statistieken verwijderen en maken:

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

Voorbeelden: Statistieken maken voor externe tabelkolom

In de volgende voorbeelden ziet u hoe u verschillende opties gebruikt voor het maken van statistieken. De opties die u voor elke kolom gebruikt, zijn afhankelijk van de kenmerken van uw gegevens en hoe de kolom wordt gebruikt in query's.

Notitie

U kunt op dit moment alleen statistieken met één kolom maken.

Als u statistieken wilt maken voor een kolom, geeft u een naam op voor het statistische object en de naam van de kolom.

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

Argumenten: external_table Hiermee geeft u de externe tabel op die statistieken moeten worden gemaakt.

FULLSCAN Rekenstatistieken door alle rijen te scannen. FULLSCAN en SAMPLE 100 PERCENT hebben dezelfde resultaten. FULLSCAN kan niet worden gebruikt met de optie SAMPLE.

SAMPLE number PERCENT Hiermee geeft u het percentage of het aantal rijen in de tabel of geïndexeerde weergave aan voor het queryoptimalisatieprogramma dat moet worden gebruikt bij het maken van statistieken. Getal kan van 0 tot en met 100 zijn.

SAMPLE kan niet worden gebruikt met de optie FULLSCAN.

Notitie

CSV-steekproeven werken niet als u parserversie 1.0 gebruikt. Alleen FULLSCAN wordt ondersteund voor CSV met parserversie 1.0.

Statistieken met één kolom maken door elke rij te bekijken

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

Statistieken met één kolom maken door de steekproefgrootte op te geven

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

Voorbeelden: Statistieken bijwerken

Als u statistieken wilt bijwerken, moet u statistieken verwijderen en maken. Statistieken eerst verwijderen:

DROP STATISTICS census_external_table.sState

En maak statistieken:

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

Metagegevens van statistieken

Er zijn verschillende systeemweergaven en functies die u kunt gebruiken om informatie over statistieken te vinden. U kunt bijvoorbeeld zien of een statistische object verouderd is met behulp van de functie STATS_DATE(). met STATS_DATE() kunt u zien wanneer statistieken voor het laatst zijn gemaakt of bijgewerkt.

Notitie

Metagegevens van statistieken zijn alleen beschikbaar voor externe tabelkolommen. Metagegevens van statistieken zijn niet beschikbaar voor OPENROWSET-kolommen.

Catalogusweergaven voor statistieken

Deze systeemweergaven bieden informatie over statistieken:

Catalogusweergave Beschrijving
sys.columns Eén rij voor elke kolom.
sys.objects Eén rij voor elk object in de database.
sys.schemas Eén rij voor elk schema in de database.
sys.stats Eén rij voor elk statistische object.
sys.stats_columns Eén rij voor elke kolom in het statistische object. Koppelingen terug naar sys.columns.
sys.tables Eén rij voor elke tabel (inclusief externe tabellen).
sys.table_types Eén rij voor elk gegevenstype.

Systeemfuncties voor statistieken

Deze systeemfuncties zijn handig voor het werken met statistieken:

Systeemfunctie Beschrijving
STATS_DATE Datum waarop het statistische object voor het laatst is bijgewerkt.

Statistische kolommen en functies combineren in één weergave

In deze weergave worden kolommen die betrekking hebben op statistieken en resultaten van de functie STATS_DATE() samengebracht.

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
;

Volgende stappen

Zie Uw workload bewaken en Best practices voor toegewezen SQL-pool om de queryprestaties voor een toegewezen SQL-pool verder te verbeteren.

Zie Best practices voor serverloze SQL-pool om de queryprestaties voor serverloze SQL-pools verder te verbeteren.