Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Dit artikel bevat aanbevelingen en voorbeelden voor het maken en bijwerken van queryoptimalisatiestatistieken 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 over uw gegevens weet, hoe sneller query's kunnen worden uitgevoerd. Nadat u gegevens in een toegewezen SQL-pool hebt geladen, is het verzamelen van statistieken over uw gegevens een van de belangrijkste dingen die u kunt doen voor queryoptimalisatie.
De queryoptimalisatie voor toegewezen SQL-pools is een optimalisatie op basis van kosten. Het vergelijkt de kosten van verschillende queryplannen en kiest vervolgens het plan met de laagste kosten. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.
Als de optimizer bijvoorbeeld schat dat de datum waarop de query wordt gefilterd één rij retourneert, zal er één plan worden gekozen. Als het systeem inschat dat de geselecteerde datum 1 miljoen rijen oplevert, wordt een ander plan gegenereerd.
Automatisch statistieken maken
De toegewezen SQL-poolengine analyseert binnenkomende gebruikersquery's op ontbrekende statistieken wanneer de optie AUTO_CREATE_STATISTICS database 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 kardinaliteitschattingen voor het queryplan te verbeteren.
Belangrijk
Het automatisch maken van statistieken is momenteel standaard ingeschakeld.
U kunt controleren of uw datawarehouse is geconfigureerd voor AUTO_CREATE_STATISTICS door de volgende opdracht uit te voeren:
SELECT name, is_auto_create_stats_on
FROM sys.databases
Als uw datawarehouse geen AUTO_CREATE_STATISTICS heeft ingeschakeld, raden we u aan deze eigenschap in te schakelen door de volgende opdracht uit te voeren:
ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON
Met deze instructies wordt het automatisch maken van statistieken geactiveerd:
- SELECTEREN
- INSERT-SELECT (Een SQL-opdracht voor het invoegen van gegevens vanuit een andere selectie)
- CTAS
- Actualisatie
- Verwijderen
- UITLEGGEN wanneer een join of aanwezigheid van een predicaat wordt gedetecteerd
Notitie
De automatische aanmaak van statistieken vindt niet plaats voor tijdelijke of externe tabellen.
Het automatisch maken van statistieken wordt synchroon uitgevoerd. U kunt dus enigszins verslechterde queryprestaties ondervinden als er statistieken ontbreken in uw kolommen. De tijd die nodig is om statistieken voor één kolom te maken, 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 vastgelegd in sys.dm_pdw_exec_requests onder een andere gebruikerscontext.
Wanneer automatische statistieken worden gemaakt, krijgen ze de vorm: WA_Sys<8-cijferige kolom-id in Hex>_<8-cijferige tabel-id in Hex>. U kunt al gemaakte statistieken weergeven door de DBCC-opdracht 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 statistiekengegevens moeten worden weergegeven.
Statistieken bijwerken
Een best practice is om statistieken bij te werken op datumkolommen elke dag wanneer er nieuwe datums worden toegevoegd. Telkens wanneer nieuwe rijen in het datawarehouse worden geladen, worden nieuwe laaddatums 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 constant is tussen klanten, wordt de gegevensdistributie niet gewijzigd door nieuwe rijen toe te voegen aan de tabelvariatie.
Wanneer uw datawarehouse echter slechts één land of regio bevat en u gegevens uit een nieuw land of een nieuwe regio opbrengt, moet u de statistieken voor de kolom land of regio bijwerken.
Hieronder volgen aanbevelingen voor het bijwerken van statistieken:
| Typ | Aanbeveling |
|---|---|
| Frequentie van updates voor statistieken | Conservatief: Dagelijks na het laden of transformeren van uw gegevens |
| Steekproef | Minder dan 1 miljard rijen gebruiken standaardsampling (20 procent).
Bij meer dan 1 miljard rijen, gebruik een steekproef 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 is niet een vraag die kan worden beantwoord door de leeftijd van de gegevens. Een up-to-date statistiekenobject kan oud zijn als er geen materiële wijziging is aangebracht in de onderliggende gegevens. Wanneer het aantal rijen aanzienlijk is gewijzigd of een materiële wijziging in de verdeling van waarden voor een kolom plaatsvindt, is het tijd om statistieken bij te werken.
Er is geen dynamische beheerweergave beschikbaar om te bepalen of gegevens in de tabel zijn gewijzigd sinds de laatste keer dat statistieken zijn bijgewerkt. Als u de leeftijd van uw statistieken kent, kan dat u een deel van het inzicht geven.
U kunt de volgende query gebruiken om te bepalen wanneer uw statistieken voor het laatst zijn bijgewerkt in elke tabel.
Notitie
Als er een materiële wijziging is in de verdeling van waarden voor een kolom, moet u 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 hebben bijvoorbeeld regelmatig updates voor statistieken nodig. Telkens wanneer nieuwe rijen in het datawarehouse worden geladen, worden nieuwe laaddatums 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 constant is tussen klanten, wordt de gegevensdistributie niet gewijzigd door nieuwe rijen toe te voegen aan de tabelvariatie.
Maar als uw datawarehouse slechts één geslacht bevat en een nieuwe vereiste resulteert in meerdere geslachten, moet u statistieken voor de genderkolom 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 het laden worden bijgewerkt. De gegevensbelasting is wanneer tabellen de grootte, verdeling van waarden of beide het vaakst wijzigen. Als zodanig is het laadproces een logische plek om bepaalde beheerprocessen te implementeren.
De volgende richtlijnen worden gegeven 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 wordt de tabelgrootte (aantal rijen en paginatelling) bijgewerkt als onderdeel van de statistieken-update.
- Focus op kolommen die deelnemen aan JOIN, GROUP BY-, ORDER BY en DISTINCT-componenten.
- Overweeg kolommen met 'oplopende sleutel' zoals transactiedatums vaker bij te werken, omdat deze waarden niet worden opgenomen in het histogram voor statistieken.
- Overweeg om statische distributiekolommen minder vaak bij te werken.
- Onthoud dat elk statistiekobject op volgorde wordt bijgewerkt. Eenvoudig implementeren
UPDATE STATISTICS <TABLE_NAME>is niet altijd ideaal, met name voor brede tabellen met veel statistiekenobjecten.
Zie Kardinaliteitschattingvoor meer informatie.
Voorbeelden: Statistieken maken
In deze voorbeelden ziet u hoe u verschillende opties kunt gebruiken 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 voor een kolom wilt maken, geeft u een naam op voor het statistiekenobject en de naam van de kolom. Deze syntaxis maakt gebruik van alle standaardopties. Standaard neemt de toegewezen SQL-pool een steekproef van 20 procent van de tabel wanneer er statistieken worden gemaakt.
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name]);
Voorbeeld:
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. Als u een voorbeeld van de volledige tabel wilt gebruiken, gebruikt u deze syntaxis:
CREATE STATISTICS [statistics_name]
ON [schema_name].[table_name]([column_name])
WITH FULLSCAN;
Voorbeeld:
CREATE STATISTICS col1_stats
ON dbo.table1 (col1)
WITH FULLSCAN;
Statistieken met één kolom maken door de steekproefgrootte op te geven
Een andere optie is het opgeven van de steekproefgrootte 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 op de partitiewaarden te maken, wordt de nauwkeurigheid van de statistieken verbeterd. U ervaart ook een verbetering van 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
Opdat de queryoptimizer gefilterde statistieken gebruikt bij het kiezen van het gedistribueerde queryplan, moet de query binnen de definitie van het statistiekobject 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 voor de volledige referentie.
Statistieken met meerdere kolommen maken
Als u een statistiekenobject 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 definitie van het statistiekenobject.
In dit voorbeeld bevindt het histogram zich op product_category. Statistieken voor 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 handig zijn als deze kolommen tegelijkertijd worden geopend. Bij het uitvoeren van query's op deze tabel worden de kardinaliteitschattingen voor joins, GROUP BY-aggregaties, onderscheidende tellingen en WHERE-filters verbeterd, zolang de primaire statistiekkolom deel uitmaakt van het filter.
Statistieken maken voor alle kolommen in een tabel
Een manier om statistieken te maken, is het uitgeven van CREATE STATISTICS-opdrachten na het maken van de tabel:
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 maakt u één kolomstatistiekenobject op elke kolom van de database die nog geen statistieken heeft.
In het volgende voorbeeld kunt u aan de slag met uw databaseontwerp. U kunt het 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 standaardwaarden, 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 volledige scan, 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:
- Werk één statistiekenobject bij. Geef de naam op van het statistiekenobject dat u wilt bijwerken.
- Werk alle statistiekenobjecten in een tabel bij. Geef de naam van de tabel op in plaats van één specifiek statistiekenobject.
Eén specifiek statistiekenobject bijwerken
Gebruik de volgende syntaxis om een specifiek statistiekenobject bij te werken:
UPDATE STATISTICS [schema_name].[table_name]([stat_name]);
Voorbeeld:
UPDATE STATISTICS [dbo].[table1] ([stats_col1]);
Door specifieke statistiekenobjecten bij te werken, kunt u de tijd en resources minimaliseren die nodig zijn om statistieken te beheren. Voor deze actie moet u nadenken over het selecteren van de beste statistiekenobjecten die moeten worden bijgewerkt.
Alle statistieken in een tabel bijwerken
Een eenvoudige methode voor het bijwerken van alle statistiekenobjecten in een tabel is:
UPDATE STATISTICS [schema_name].[table_name];
Voorbeeld:
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
Bij het bijwerken van alle statistieken in een tabel voert een toegewezen SQL-pool een scan uit om de tabel voor elk statistiekenobject te samplen. Als de tabel groot is en veel kolommen en veel statistieken bevat, is het mogelijk efficiënter om afzonderlijke statistieken bij te werken op basis van behoefte.
Zie UPDATE STATISTICS voor een implementatie van een 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 statistiekenobject verouderd is met behulp van de functie STATS_DATE(). 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 statistiekenobject. |
| sys.stats_columns | Eén rij voor elke kolom in het statistiekenobject. Verwijst 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 statistiekenobject voor het laatst is bijgewerkt. |
| DBCC-SHOW_STATISTICS | Samenvattingsniveau en gedetailleerde informatie over de verdeling van waarden, zoals begrepen door het statistiekenobject. |
Statistiekenkolommen en -functies combineren in één weergave
Deze weergave bevat kolommen die betrekking hebben op statistieken en resultaten van de functie 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() voorbeelden
DBCC SHOW_STATISTICS() toont de gegevens die zijn opgeslagen in een statistiekenobject. Deze gegevens zijn in drie delen beschikbaar:
- Koptekst
- Dichtheidsvector
- Histogram
De header is de metagegevens over de statistieken. In het histogram wordt de verdeling van waarden weergegeven in de eerste sleutelkolom van het statistiekenobject.
De dichtheidsvector meet de correlatie tussen kolommen. Toegewezen SQL-pool berekent kardinaliteitschattingen met een van de gegevens in het statistiekenobject.
Koptekst, dichtheid en histogram weergeven
In dit eenvoudige voorbeeld ziet u alle drie de onderdelen van een statistiekenobject:
DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
Voorbeeld:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');
Een of meer onderdelen van DBCC weergeven SHOW_STATISTICS()
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
Voorbeeld:
DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
WITH histogram, density_vector
De verschillen in DBCC SHOW_STATISTICS()
DBCC SHOW_STATISTICS() wordt 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 statistiekengegevens. Bijvoorbeeld 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 statistiekenobjecten te identificeren.
- Aangepaste fout 2767 wordt niet ondersteund.
Statistieken in serverloze SQL-pool
Statistieken worden per specifieke kolom gemaakt voor een bepaalde gegevensset (opslagpad).
Notitie
Statistieken kunnen niet worden gemaakt voor LOB-kolommen.
Waarom statistieken gebruiken
Hoe meer serverloze SQL-pool over uw gegevens weet, hoe sneller query's 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.
De queryoptimalisatie voor serverloze SQL-pools is een kostengebaseerde optimalisatie. Het vergelijkt de kosten van verschillende queryplannen en kiest vervolgens het plan met de laagste kosten. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.
Als de optimizer bijvoorbeeld inschat dat de datum waarop je query filtert slechts één rij oplevert, zal er één specifiek plan worden 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 voor ontbrekende statistieken. Als er statistieken ontbreken, maakt de queryoptimizer statistieken voor afzonderlijke kolommen in de queryvoorwaarde of de verbindingstoestand om 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 minder 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 geschatte lage kardinaliteit (aantal rijen) wordt het automatisch maken van statistieken geactiveerd met een steekproefpercentage van 100%. Dat betekent in feite dat volledigescan wordt geactiveerd en automatische statistieken worden gemaakt, zelfs voor CSV met parserversie 1.0.
Het automatisch maken van statistieken wordt synchroon uitgevoerd, zodat u mogelijk enigszins verslechterde queryprestaties ondervindt als er statistieken ontbreken in uw kolommen. De tijd die nodig is om statistieken voor één kolom te maken, is afhankelijk van de grootte van de beoogde bestanden.
Handmatig maken van statistieken
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 gebeurt niet, tenzij het steekproefpercentage 100%is.
Zie de volgende voorbeelden voor instructies voor het handmatig maken van statistieken.
Statistieken bijwerken
Wijzigingen in gegevens in bestanden, verwijderen en toevoegen van bestanden resulteren in wijzigingen in gegevensdistributie en maken statistieken verouderd. In dat geval moeten statistieken worden bijgewerkt.
Serverloze SQL-pool maakt automatisch statistieken voor OPENROWSET-kolommen opnieuw als gegevens aanzienlijk worden gewijzigd. Telkens wanneer statistieken automatisch worden gemaakt, wordt de huidige status van de gegevensset ook 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 worden ze opnieuw gemaakt via 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 is het steekproefpercentage minder 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 automatische recreatie van statistieken niet plaatsvindt met een steekproefpercentage minder dan 100%. In dat geval moet u statistieken handmatig verwijderen en opnieuw maken. Bekijk de onderstaande voorbeelden voor het verwijderen en maken van statistieken. Voor kleine tabellen met geschatte lage kardinaliteit (aantal rijen) wordt automatische statistiekenrecreatie geactiveerd met een steekproefpercentage van 100%. Dat betekent in feite dat volledigescan 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 er is een materiële wijziging in de verdeling van waarden voor een kolom, is het tijd om statistieken bij te werken.
Notitie
Als er een materiële wijziging is in de verdeling van waarden voor een kolom, moet u 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 toevoeging, verwijdering of wijziging van bestanden.
De volgende richtlijnen worden gegeven voor het bijwerken van uw statistieken:
- Zorg ervoor dat de gegevensset ten minste één statistiekenobject heeft bijgewerkt. Dit werkt grootte-informatie (aantal rijen en pagina's) bij als onderdeel van de statistiekenupdate.
- Focus op kolommen die deelnemen aan WHERE-, JOIN-, GROUP BY-, ORDER BY- en DISTINCT-componenten.
- Werk kolommen met 'oplopende sleutel' zoals transactiedatums vaker bij, omdat deze waarden niet worden opgenomen in het histogram voor statistieken.
- Werk statische distributiekolommen minder vaak bij.
Zie Kardinaliteitschattingvoor meer informatie.
Voorbeelden: Statistieken maken voor kolom in OPENROWSET-pad
In de volgende voorbeelden ziet u hoe u verschillende opties kunt gebruiken voor het maken van statistieken in serverloze SQL-pools van Azure Synapse. 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 om sp_create_openrowset_statistics en sp_drop_openrowset_statistics uit te voeren: BULKBEWERKINGEN BEHEREN of DATABASE BULKBEWERKINGEN 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 waarmee kolomwaarden worden geretourneerd 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 voor een kolom wilt maken, geeft u een query op waarmee de kolom wordt geretourneerd waarvoor u statistieken nodig hebt.
Als u niet anders opgeeft wanneer u handmatig statistieken maakt, gebruikt een serverloze SQL-pool standaard 100% van de gegevens die in de gegevensset zijn opgegeven wanneer er statistieken worden gemaakt.
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 steekproefgrootte 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
Om statistieken bij te werken, moet u ze verwijderen en opnieuw aanmaken. 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 om sp_create_openrowset_statistics en sp_drop_openrowset_statistics uit te voeren: BULKBEWERKINGEN BEHEREN of DATABASE BULKBEWERKINGEN BEHEREN.
Argumenten: [ @stmt = ] N'statement_text': hiermee geeft u dezelfde Transact-SQL instructie op die wordt 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 de kolom externe tabel
In de volgende voorbeelden ziet u hoe u verschillende opties kunt gebruiken 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 voor een kolom wilt maken, geeft u een naam op voor het statistiekenobject en de naam van de kolom.
CREATE STATISTICS statistics_name
ON { external_table } ( column )
WITH
{ FULLSCAN
| [ SAMPLE number PERCENT ] }
, { NORECOMPUTE }
Argumenten: external_table specificeert de externe tabel waaraan de statistieken moeten worden gemaakt.
FULLSCAN bereken statistieken door alle rijen te scannen. FULLSCAN en SAMPLE 100 PROCENT hebben dezelfde resultaten. FULLSCAN kan niet worden gebruikt met de optie SAMPLE.
SAMPLE number PERCENT Specificeert het geschatte percentage of het aantal rijen in de tabel of geïndexeerde weergave voor de queryoptimizer om te gebruiken bij het maken van statistieken. Getal kan tussen 0 en 100 zijn.
VOORBEELD 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
Om statistieken bij te werken, moet u ze verwijderen en opnieuw aanmaken. 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 statistiekenobject verouderd is met behulp van de functie STATS_DATE(). 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 statistiekenobject. |
| sys.stats_columns | Eén rij voor elke kolom in het statistiekenobject. Verwijst 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 statistiekenobject voor het laatst is bijgewerkt. |
Statistiekenkolommen en -functies combineren in één weergave
Deze weergave bevat kolommen die betrekking hebben op statistieken en resultaten van de functie 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
;
Volgende stappen
Zie Uw workload bewaken en Aanbevolen procedures voor toegewezen SQL-pools om de queryprestaties van een toegewezen SQL-pool verder te verbeteren.
Zie De aanbevolen procedures voor een serverloze SQL-pool om de queryprestaties voor een serverloze SQL-pool verder te verbeteren.