Optimaliseren van niet-geclusterde indexen met suggesties voor ontbrekende indexen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

De functie ontbrekende indexen is een lichtgewicht hulpprogramma voor het vinden van ontbrekende indexen die de queryprestaties aanzienlijk kunnen verbeteren. In dit artikel wordt beschreven hoe u ontbrekende indexsuggesties gebruikt om indexen effectief af te stemmen en queryprestaties te verbeteren.

Beperkingen van de ontbrekende indexfunctie

Wanneer de queryoptimalisatie een queryplan genereert, wordt geanalyseerd wat de beste indexen zijn voor een bepaalde filtervoorwaarde. Als de beste indexen niet bestaan, genereert de queryoptimalisatie nog steeds een queryplan met behulp van de minst dure toegangsmethoden die beschikbaar zijn, maar worden ook gegevens over deze indexen opgeslagen. Met de functie ontbrekende indexen kunt u toegang krijgen tot die informatie over best mogelijke indexen, zodat u kunt bepalen of deze moeten worden geïmplementeerd.

Queryoptimalisatie is een tijdgevoelig proces, dus er zijn beperkingen voor de ontbrekende indexfunctie. Beperkingen zijn onder andere:

• Ontbrekende indexsuggesties zijn gebaseerd op schattingen die zijn gemaakt tijdens de optimalisatie van één query, vóór de uitvoering van de query. Ontbrekende indexsuggesties worden niet getest of bijgewerkt na de uitvoering van de query.
• De ontbrekende indexfunctie suggereert alleen niet-geclusterde, op schijven gebaseerde rowstore-indexen. Unieke en gefilterde indexen worden niet voorgesteld.
• Sleutelkolommen worden voorgesteld, maar de suggestie geeft geen volgorde op voor deze kolommen. Zie de sectie Ontbrekende indexsuggesties van dit artikel toepassen voor meer informatie over het ordenen van kolommen.
• Opgenomen kolommen worden voorgesteld, maar SQL Server voert geen kosten-batenanalyse uit met betrekking tot de grootte van de resulterende index wanneer een groot aantal opgenomen kolommen wordt voorgesteld.
• Ontbrekende indexaanvragen kunnen vergelijkbare variaties van indexen bieden voor dezelfde tabel en kolom(en) in query's. Het is belangrijk om indexsuggesties te controleren en waar mogelijk te combineren.
• Er worden geen suggesties gedaan voor triviale query-plannen.
• Kosteninformatie is minder nauwkeurig voor query's met alleen ongelijkheidspredicaten.
• Er worden suggesties verzameld voor maximaal 600 ontbrekende indexgroepen. Nadat deze drempelwaarde is bereikt, worden er geen ontbrekende indexgroepgegevens meer verzameld.

Vanwege deze beperkingen worden ontbrekende indexsuggesties het beste behandeld als een van de verschillende informatiebronnen bij het uitvoeren van indexanalyse, ontwerp, afstemming en testen. Ontbrekende indexsuggesties zijn geen recepten om indexen precies zoals voorgesteld te maken.

Aanbevelingen voor ontbrekende index weergeven

De functie ontbrekende indexen bestaat uit twee onderdelen:

• Het MissingIndexes element in de XML van uitvoeringsplannen. Hiermee kunt u indexen correleren die door de queryoptimalisatie als ontbrekend worden beschouwd met de query's waarvoor ze ontbreken.
• Een set dynamische beheerweergaven (DMV's) waarop query's kunnen worden uitgevoerd om informatie over ontbrekende indexen te retourneren. Hiermee kunt u alle aanbevelingen voor ontbrekende indexen voor een database bekijken.

Ontbrekende indexsuggesties in uitvoeringsplannen weergeven

Overzicht van het uitvoeringsplan kan op meerdere manieren worden gegenereerd of verkregen:

• Wanneer u een query schrijft of afstemt, kunt u SQL Server Management Studio (SSMS) gebruiken om het geschatte uitvoeringsplan weer te geven zonder de query uit te voeren of de query uit te voeren en een daadwerkelijk uitvoeringsplan weer te geven.
• Bewaak de prestaties met behulp van de Query Store, indien ingeschakeld, verzamelt uitvoeringsplannen.
• U kunt uitvoeringsplannen in de cache identificeren door een query uit te voeren op DMV's, zoals sys.dm_exec_text_query_plan.

U kunt bijvoorbeeld de volgende query gebruiken om ontbrekende indexaanvragen te genereren voor de AdventureWorks-voorbeelddatabases.

SELECT City, StateProvinceID, PostalCode
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;
GO

De ontbrekende indexaanvragen genereren en weergeven:

1. Open SSMS en verbind een sessie met uw kopie van de AdventureWorks-voorbeelddatabases.

2. Plak de query in de sessie en genereer een geschat uitvoeringsplan in SSMS voor de query door de werkbalkknop Geschat uitvoeringsplan weergeven te selecteren. Het uitvoeringsplan wordt weergegeven in een deelvenster in de huidige sessie. Bovenaan het grafische plan wordt een groene verklaring Ontbrekende index weergegeven.

   

   Eén uitvoeringsplan kan meerdere ontbrekende indexaanvragen bevatten, maar er kan slechts één ontbrekende indexaanvraag worden weergegeven in het grafische uitvoeringsplan. Een optie voor het weergeven van een volledige lijst met ontbrekende indexen voor een uitvoeringsplan is het weergeven van de XML van het uitvoeringsplan.

3. Klik met de rechtermuisknop op het uitvoeringsplan en selecteer XML van uitvoeringsplan weergeven... in het menu.

   

   De XML van het uitvoeringsplan wordt geopend als een nieuw tabblad in SSMS.

   Note

   Er wordt slechts één ontbrekende indexsuggesties weergegeven in de menuoptie Ontbrekende indexdetails... zelfs als er meerdere suggesties aanwezig zijn in de XML van het uitvoeringsplan. De ontbrekende indexsuggesties die worden weergegeven, zijn mogelijk niet de suggestie met de hoogste geschatte verbetering voor de query.

4. Het dialoogvenster Zoeken weergeven met de sneltoets Ctrl+f .

5. Zoek naar MissingIndex.

   

   In dit voorbeeld zijn er twee MissingIndex elementen.

   • De eerste ontbrekende index stelt voor dat de query een index kan gebruiken in de Person.Address tabel die ondersteuning biedt voor gelijkheid zoeken in de StateProvinceID kolom, die twee kolommen bevat en CityPostalCode'. Op het moment van optimalisatie geloofde de optimalisatie van de query dat deze index de geschatte kosten van de query met 34.2737%zou kunnen verlagen.
   • De tweede ontbrekende index suggereert dat de query een index kan gebruiken in de Person.Person tabel die ondersteuning biedt voor ongelijkheid zoeken in de kolom FirstName. Op het moment van optimalisatie geloofde de optimalisatie van de query dat deze index de geschatte kosten van de query met 18.1102%zou kunnen verlagen.

Elke niet-geclusterde index op basis van schijven in uw database neemt ruimte in beslag, voegt overhead toe voor invoegingen, updates en verwijderingen, en vereist mogelijk onderhoud. Daarom is het een best practice om alle ontbrekende indexaanvragen voor een tabel en de bestaande indexen in een tabel te controleren voordat u een index toevoegt op basis van een queryuitvoeringsplan.

Ontbrekende indexsuggesties weergeven in DMVs

U kunt informatie over ontbrekende indexen ophalen door een query uit te voeren op de dynamische beheerobjecten die worden vermeld in de volgende tabel.

De volgende query maakt gebruik van de ontbrekende index-DMV's om instructies te genereren CREATE INDEX . De instructies voor het maken van indexen zijn bedoeld om u te helpen bij het maken van uw eigen DDL na het onderzoeken van alle aanvragen voor de tabel, samen met bestaande indexen in de tabel.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' +
        CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' +
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Met deze query worden de suggesties gesorteerd op een kolom met de naam estimated_improvement. De geschatte verbetering is gebaseerd op een combinatie van:

• De geschatte querykosten van query's die zijn gekoppeld aan de ontbrekende indexaanvraag.
• De geschatte impact van het toevoegen van de index. Dit is een schatting van hoeveel de niet-geclusterde index de querykosten zou verlagen.
• De som van de uitvoeringen van queryoperators (zoekt en scant) die zijn uitgevoerd voor query's die zijn gekoppeld aan de ontbrekende indexaanvraag. Zoals we bespreken in persistente ontbrekende indexen met Query Store, wordt deze informatie periodiek gewist.

Bekijk beperkingen van de ontbrekende indexfunctie en hoe u ontbrekende indexsuggesties toepast voordat u indexen maakt en wijzig de indexnaam zodat deze overeenkomt met de naamconventie voor uw database.

Ontbrekende indexen behouden met Query Store

Ontbrekende indexsuggesties in DMV's worden gewist door gebeurtenissen zoals het opnieuw opstarten van exemplaren, failovers en het offline instellen van een database. Wanneer de metagegevens voor een tabel worden gewijzigd, worden bovendien alle ontbrekende indexinformatie over die tabel verwijderd uit deze dynamische beheerobjecten. Wijzigingen in tabelmetagegevens kunnen optreden wanneer kolommen worden toegevoegd of verwijderd uit een tabel, bijvoorbeeld wanneer een index wordt gemaakt in een kolom van een tabel. Als u een ALTER INDEX-bewerking uitvoert op een index in een tabel, worden ook ontbrekende indexaanvragen voor die tabel gewist.

Op dezelfde manier worden uitvoeringsplannen die zijn opgeslagen in de plancache gewist door gebeurtenissen zoals het opnieuw opstarten van exemplaren, failovers en het offline instellen van een database. Uitvoeringsplannen kunnen worden verwijderd uit de cache vanwege geheugendruk en hercompilaties.

Ontbrekende indexsuggesties in uitvoeringsplannen kunnen tijdens deze gebeurtenissen worden bewaard door de prestaties bewaken in te schakelen met behulp van de Query Store.

Met de volgende query worden de top 20 queryplannen met ontbrekende indexaanvragen uit Query Store opgehaald op basis van een ruwe schatting van het totale aantal logische leesbewerkingen voor de query. De gegevens zijn beperkt tot het uitvoeren van query's binnen de afgelopen 48 uur.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Ontbrekende indexsuggesties toepassen

Als u ontbrekende indexsuggesties effectief wilt gebruiken, volgt u de ontwerprichtlijnen voor niet-geclusterde indexen. Wanneer u niet-geclusterde indexen afstemt met ontbrekende indexsuggesties, controleert u de basistabelstructuur, combineert u indexen zorgvuldig, houdt u rekening met de volgorde van de sleutelkolom en controleert u de opgenomen kolomsuggesties.

De structuur van de basistabel controleren

Controleer de geclusterde index van de tabel voordat u niet-geclusterde indexen maakt op basis van ontbrekende indexsuggesties.

Een manier om te controleren op een geclusterde index is door de sp_helpindex systeem-opgeslagen procedure te gebruiken. We kunnen bijvoorbeeld een samenvatting van de indexen in de Person.Address tabel bekijken door de volgende instructie uit te voeren:

exec sp_helpindex 'Person.Address';
GO

Controleer de kolom index_description. Een tabel kan slechts één geclusterde index hebben. Als een geclusterde index is geïmplementeerd voor de tabel, bevat het index_description woord 'geclusterd'.

Als er geen geclusterde index aanwezig is, is de tabel een heap. Controleer in dit geval of de tabel opzettelijk als een heap is gemaakt om een specifiek prestatieprobleem op te lossen. De meeste tabellen profiteren van geclusterde indices: vaak worden tabellen per abuis geïmplementeerd als gestapelde structuren. Overweeg om een geclusterde index te implementeren op basis van de richtlijnen voor het ontwerpen van geclusterde indexen.

Ontbrekende indexen en bestaande indexen controleren op overlapping

Ontbrekende indexen kunnen vergelijkbare variaties van niet-geclusterde indexen bieden voor dezelfde tabel en kolom(en) in query's. Ontbrekende indexen kunnen ook vergelijkbaar zijn met bestaande indexen in een tabel. Voor optimale prestaties kunt u het beste ontbrekende indexen en bestaande indexen voor overlapping onderzoeken en voorkomen dat er dubbele indexen worden gemaakt.

Bestaande indexen uit een tabel genereren

Een manier om de definitie van bestaande indexen in een tabel te onderzoeken, is door de indexen te scripten met Objectverkenner-details:

1. Verbinding maken tussen Objectverkenner en uw exemplaar of database.
2. Vouw het knooppunt voor de betreffende database uit in Objectverkenner.
3. Vouw de map Tabellen uit.
4. Vouw de tabel uit waarvoor u scripts voor indexen wilt genereren.
5. Selecteer de map Indexen .
6. Als het deelvenster Details van Objectverkenner nog niet is geopend, selecteert u Objectverkennerdetails in het menu Beeld of drukt u op F7.
7. Selecteer alle indexen die worden weergegeven in het deelvenster Details van Objectverkenner met de sneltoets Ctrl+a.
8. Klik met de rechtermuisknop op een willekeurige plaats in de geselecteerde regio en selecteer de menuoptie Scriptindex als, vervolgens CREATE aan** en het venster Nieuwe queryeditor.

Indexen controleren en waar mogelijk combineren

Bekijk de aanbevelingen voor ontbrekende indexen voor een tabel als groep, samen met de definities van bestaande indexen in de tabel. Houd er rekening mee dat bij het definiëren van indexen over het algemeen gelijkheidskolommen vóór de ongelijkheidskolommen moeten worden geplaatst en dat ze samen de sleutel van de index moeten vormen. Als u een effectieve volgorde voor de gelijkheidskolommen wilt bepalen, rangschikt u ze op basis van hun selectiviteit: geef eerst de meest selectieve kolommen weer (meest links in de kolomlijst). Unieke kolommen zijn het meest selectief, terwijl kolommen met veel herhalende waarden minder selectief zijn.

Opgenomen kolommen moeten worden toegevoegd aan de CREATE INDEX instructie met behulp van de INCLUDE component. De volgorde van opgenomen kolommen heeft geen invloed op de queryprestaties. Bij het combineren van indexen kunnen opgenomen kolommen daarom worden gecombineerd zonder dat u zich zorgen hoeft te maken over de volgorde. Meer informatie vindt u in de richtlijnen voor opgenomen kolommen.

U hebt bijvoorbeeld een tabel, Person.Addressmet een bestaande index in de sleutelkolom StateProvinceID. Mogelijk ziet u aanbevelingen voor ontbrekende indexen voor de Person.Address tabel voor de volgende kolommen:

• GELIJKHEIDsfilters voor StateProvinceID en City
• GELIJKHEIDsfilters voor StateProvinceID en City, INCLUDEPostalCode

Als u de bestaande index wijzigt zodat deze overeenkomt met de tweede aanbeveling, zou een index met sleutels ingeschakeld StateProvinceID en City inclusief PostalCode, waarschijnlijk voldoen aan de query's die beide indexsuggesties hebben gegenereerd.

Compromissen komen vaak voor bij het afstemmen van indexen. Voor veel gegevenssets is de City kolom waarschijnlijk selectiever dan de StateProvinceID kolom. Als onze bestaande index StateProvinceID echter sterk wordt gebruikt en andere aanvragen grotendeels zoeken op beide StateProvinceID en City, is het een lagere overhead voor de database in het algemeen om één index te hebben met beide kolommen in de sleutel, wat leidt tot StateProvinceID, hoewel dit niet de meest selectieve kolom is.

Indexen kunnen op meerdere manieren worden gewijzigd:

• U kunt de CREATE INDEX bewerking gebruiken met de DROP_EXISTING clausule. Mogelijk wilt u de naam van de indexen wijzigen na de wijziging, zodat de naam de indexdefinitie nog steeds nauwkeurig beschrijft, afhankelijk van uw naamconventie.
• U kunt de DROP INDEX-instructie (Transact-SQL) gebruiken, gevolgd door een CREATE INDEX-instructie.

De volgorde van indexsleutels is belangrijk bij het combineren van de indexsuggesties: City als voorloopkolom verschilt van StateProvinceID als voorloopkolom. Meer informatie vindt u in ontwerprichtlijnen voor niet-geclusterde indexen.

Bij het maken van indexen kunt u overwegen om online indexbewerkingen te gebruiken wanneer deze beschikbaar zijn.

Hoewel indexen in sommige gevallen de queryprestaties aanzienlijk kunnen verbeteren, hebben indexen ook overhead- en beheerkosten. Bekijk algemene richtlijnen voor het ontwerpen van indexen om het voordeel van indexen te beoordelen voordat u deze maakt.

Controleer of de indexwijziging is geslaagd

Het is belangrijk om te controleren of uw indexwijzigingen zijn geslaagd: gebruikt u de queryoptimalisatie met behulp van uw indexen?

Een manier om uw indexwijzigingen te valideren, is door Query Store te gebruiken om query's te identificeren met ontbrekende indexaanvragen. Let op de query_id voor de query’s. Gebruik de weergave Bijgehouden query's in Query Store om te controleren of uitvoeringsplannen zijn gewijzigd voor een query en of de optimizer uw nieuwe of gewijzigde index gebruikt. Lees meer over Tracked Queries in Start met het oplossen van problemen met queryprestaties.

Verwante inhoud

• architectuur en ontwerphandleiding voor SQL Server- en Azure SQL-indexen
• sys.dm_db_missing_index_details (Transact-SQL)
• Prestaties controleren via de Query Store