Statistieken voor geheugen-geoptimaliseerde tabellen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

De queryoptimalisatie gebruikt statistieken over kolommen om queryplannen te maken die de prestaties van query's verbeteren. Statistieken worden verzameld uit de tabellen in de database en opgeslagen in de metagegevens van de database.

Statistieken worden automatisch gemaakt, maar kunnen ook handmatig worden gemaakt. Statistieken worden bijvoorbeeld automatisch gemaakt voor indexsleutelkolommen wanneer de index wordt gemaakt. Zie Statistieken voor meer informatie over het maken van statistieken.

Tabelgegevens worden doorgaans na verloop van tijd gewijzigd wanneer rijen worden ingevoegd, bijgewerkt en verwijderd. Dit betekent dat statistieken periodiek moeten worden bijgewerkt. Statistieken voor tabellen worden standaard automatisch bijgewerkt wanneer de queryoptimalisatie bepaalt dat ze verouderd zijn.

Overwegingen voor statistieken over tabellen die zijn geoptimaliseerd voor geheugen:

• Vanaf SQL Server 2016 en in Azure SQL Database wordt automatische update van statistieken ondersteund voor tabellen die zijn geoptimaliseerd voor geheugen, bij gebruik van het databasecompatibiliteitsniveau van ten minste 130. Zie het compatibiliteitsniveau ALTER DATABASE (Transact-SQL). Als een database tabellen bevat die eerder zijn gemaakt met een lager compatibiliteitsniveau, moeten de statistieken handmatig één keer worden bijgewerkt om automatische updates van statistieken in te schakelen.

• Voor natuurlijk gecompileerde opgeslagen procedures worden uitvoeringsplannen voor query's in de procedure geoptimaliseerd wanneer de procedure wordt gecompileerd. Dit gebeurt op het creatiemoment. Ze worden niet automatisch opnieuw gecompileerd wanneer statistieken worden bijgewerkt. Daarom moeten de tabellen een representatieve set gegevens bevatten voordat de procedures worden gemaakt.

• Systeemeigen gecompileerde opgeslagen procedures kunnen handmatig opnieuw worden gecompileerd met behulp van sp_recompile (Transact-SQL), en ze worden automatisch opnieuw gecompileerd als de database offline wordt gehaald en weer online wordt gebracht, of als er een failover van de database of het opnieuw opstarten van de server is.

Automatische update van statistieken in bestaande tabellen inschakelen

Wanneer tabellen worden gemaakt in een database met een compatibiliteitsniveau van ten minste 130, wordt automatische update van statistieken ingeschakeld voor alle statistieken in die tabel en is er geen verdere actie nodig.

Als een database tabellen bevat die zijn geoptimaliseerd voor geheugen die zijn gemaakt in een eerdere versie van SQL Server of onder een lager compatibiliteitsniveau dan 130, moeten de statistieken handmatig worden bijgewerkt zodra automatische updates in de toekomst mogelijk zijn.

Voer de volgende stappen uit om de automatische bijwerking van statistieken te activeren voor geheugen-geoptimaliseerde tabellen die zijn gemaakt onder een ouder compatibiliteitsniveau:

1. Het compatibiliteitsniveau van de database bijwerken: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

2. Werk de statistieken van de tabellen die zijn geoptimaliseerd voor geheugen handmatig bij. Hieronder ziet u een voorbeeldscript dat hetzelfde uitvoert.

3. De systeemeigen gecompileerde opgeslagen procedures handmatig opnieuw compileren om te profiteren van de bijgewerkte statistieken.

Eenmalig script voor statistieken: Voor tabellen die zijn gemaakt onder een lager compatibiliteitsniveau, kunt u het volgende Transact-SQL script één keer uitvoeren om de statistieken van alle voor geheugen geoptimaliseerde tabellen bij te werken en vanaf dat moment automatische updates van statistieken in te schakelen (ervan uitgaande dat AUTO_UPDATE_STATISTICS is ingeschakeld voor de database):

-- Assuming AUTO_UPDATE_STATISTICS is already ON for your database:
-- ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON;

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;
GO
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT
      @sql += N'UPDATE STATISTICS '
         + quotename(schema_name(t.schema_id))
         + N'.'
         + quotename(t.name)
         + ';' + CHAR(13) + CHAR(10)
   FROM sys.tables AS t
   WHERE t.is_memory_optimized = 1 AND 
		t.object_id IN (SELECT object_id FROM sys.stats WHERE no_recompute=1)
;
EXECUTE sp_executesql @sql;
GO
-- Each row appended to @sql looks roughly like:
-- UPDATE STATISTICS [dbo].[MyMemoryOptimizedTable];

Controleren of automatisch bijwerken is ingeschakeld: Met het volgende script wordt gecontroleerd of automatische update is ingeschakeld voor statistieken over tabellen die zijn geoptimaliseerd voor geheugen. Na het uitvoeren van het vorige script zal 1 worden geretourneerd in de kolom auto-update enabled voor alle statistiekobjecten.

SELECT 
	quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) AS [table],
	s.name AS [statistics object],
	1-s.no_recompute AS [auto-update enabled]
FROM sys.stats s JOIN sys.tables o ON s.object_id=o.object_id
WHERE o.is_memory_optimized=1

Richtlijnen voor het implementeren van tabellen en procedures

Om ervoor te zorgen dat de queryoptimizer up-to-date statistieken heeft bij het maken van queryplannen: implementeert u geheugen-geoptimaliseerde tabellen en natuurlijk gecompileerde opgeslagen procedures die toegang hebben tot deze tabellen met behulp van deze vier stappen:

1. Zorg ervoor dat de database ten minste 130 compatibiliteitsniveau heeft. Zie het compatibiliteitsniveau ALTER DATABASE (Transact-SQL).

2. Tabellen en indexen maken. Indexen moeten inline worden opgegeven in de CREATE TABLE-instructies .

3. Laad gegevens in de tabellen.

4. Opgeslagen procedures maken die toegang hebben tot de tabellen.

Door systeemeigen gecompileerde opgeslagen procedures te maken nadat u de gegevens hebt geladen, zorgt u ervoor dat de optimizer statistieken bevat die beschikbaar zijn voor de tabellen die zijn geoptimaliseerd voor geheugen. Dit zorgt voor efficiënte queryplannen wanneer de procedure wordt gecompileerd.

Zie ook

Memory-Optimized tabellen