Nem klaszterezett indexek finomhangolása hiányzó indexjavaslatok alapján

A következőkre vonatkozik:SQL Server Azure SQL Database Felügyelt Azure SQL-példány SQL-adatbázis a Microsoft Fabricben

A hiányzó indexek funkció egy egyszerű eszköz a hiányzó indexek megtalálásához, amelyek jelentősen javíthatják a lekérdezési teljesítményt. Ez a cikk azt ismerteti, hogyan használható a hiányzó indexjavaslatok az indexek hatékony finomhangolásához és a lekérdezési teljesítmény javításához.

A hiányzó indexfunkció korlátozásai

Amikor a lekérdezésoptimalizáló létrehoz egy lekérdezéstervet, elemzi, hogy mi a legjobb index egy adott szűrőfeltételhez. Ha a legjobb indexek nem léteznek, a lekérdezésoptimalizáló továbbra is létrehoz egy lekérdezéstervet az elérhető legkevésbé költséges hozzáférési módszerek használatával, de az indexekről is tárol információkat. A hiányzó indexek funkció lehetővé teszi, hogy hozzáférjen ezekhez az információkhoz a lehető legjobb indexekről, hogy eldönthesse, implementálják-e őket.

A lekérdezésoptimalizálás időérzékeny folyamat, ezért a hiányzó indexfunkcióra korlátozások vonatkoznak. A korlátozások a következők:

A hiányzó indexjavaslatok egy lekérdezés optimalizálása során, a lekérdezés végrehajtása előtt készített becsléseken alapulnak. A hiányzó indexjavaslatokat a rendszer nem teszteli vagy frissíti a lekérdezés végrehajtása után.
A hiányzó indexfunkció csak nem klaszterezett lemezalapú soros tárindexeket javasol. Az egyedi és szűrt indexek nem javasoltak .
A kulcsoszlopok javasoltak, de a javaslat nem adja meg az oszlopok sorrendjét. Az oszlopok sorrendjével kapcsolatos információkért tekintse meg a cikk hiányzó indexjavaslatok alkalmazása szakaszát.
A belefoglalt oszlopok javasoltak, de az SQL Server nem végez költség-haszon elemzést az eredményként kapott index méretéről, ha nagy számú belefoglalt oszlopot javasol.
A hiányzó indexkérelmek hasonló variációkat kínálhatnak ugyanazon a táblán és oszlop(ok)on a lekérdezések között. Fontos áttekinteni az indexjavaslatokat, és lehetőség szerint kombinálni.
A program nem tesz javaslatokat a triviális lekérdezési tervekre.
A költségadatok kevésbé pontosak a csak egyenlőtlenségi predikátumokat tartalmazó lekérdezések esetében.
A rendszer legfeljebb 600 hiányzó indexcsoportra vonatkozó javaslatokat gyűjt. A küszöbérték elérése után nincs több hiányzó indexcsoport-adat.

Ezen korlátozások miatt a hiányzó indexjavaslatok a legjobban az indexelemzés, a tervezés, a finomhangolás és a tesztelés során számos információforrás egyikeként kezelhetők. A hiányzó indexjavaslatok nem a javasolt módon hozhatók létre indexek.

Note

Az Azure SQL Database automatikus indexhangolást biztosít. Az automatikus indexhangolás gépi tanulással tanul horizontálisan az Azure SQL Database-ben lévő összes adatbázisból a mi-en keresztül, és dinamikusan fejleszti finomhangolási műveleteit. Az automatikus indexhangolás egy ellenőrzési folyamatot is tartalmaz, amely biztosítja, hogy a létrehozott indexek pozitívan javíthassák a számítási feladatok teljesítményét.

Hiányzó indexjavaslatok megtekintése

A hiányzó indexek funkció két összetevőből áll:

A MissingIndexesvégrehajtási tervek XML-jének eleme. Ez lehetővé teszi az indexek korrelálását, amelyeket a lekérdezésoptimalizáló hiányzónak tekint azokkal a lekérdezésekkel, amelyekhez hiányoznak.
Dinamikus felügyeleti nézetek (DMV-k) készlete, amelyek lekérdezhetők a hiányzó indexek adatainak visszaadásához. Ez lehetővé teszi az adatbázis összes hiányzó indexjavaslatának megtekintését.

Hiányzó indexjavaslatok megtekintése végrehajtási tervekben

A végrehajtási terv áttekintése többféleképpen is létrehozható vagy lekérhető:

Lekérdezés írásakor vagy finomhangolásakor az SQL Server Management Studio (SSMS) használatával megjelenítheti a becsült végrehajtási tervet a lekérdezés futtatása nélkül, vagy végrehajthatja a lekérdezést, és megjeleníthet egy tényleges végrehajtási tervet.
A lekérdezéstár használatával figyelheti a teljesítményt, ha engedélyezve van, végrehajtási terveket gyűjt.
A gyorsítótárazott végrehajtási tervek az olyan DMV-k lekérdezésével azonosíthatók, mint például a sys.dm_exec_text_query_plan.

Az alábbi lekérdezéssel például hiányzó indexkéréseket hozhat létre az AdventureWorks mintaadatbázisaihoz.

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

A hiányzó indexkérelmek létrehozása és megtekintése:

Nyissa meg az SSMS-t, és csatlakoztassa a munkamenetet az AdventureWorks-mintaadatbázisok másolatához.
Illessze be a lekérdezést a munkamenetbe, és hozzon létre egy becsült végrehajtási tervet a lekérdezés SSMS-ben a Becsült végrehajtási terv megjelenítése eszköztár gombra kattintva. A végrehajtási terv az aktuális munkamenet egyik ablaktábláján jelenik meg. A grafikus terv tetején megjelenik egy zöld Hiányzó index utasítás.

Egy végrehajtási terv több hiányzó indexkérelmet tartalmazhat, de csak egy hiányzó indexkérelmet jeleníthet meg a grafikus végrehajtási tervben. A végrehajtási terv hiányzó indexeinek teljes listáját a végrehajtási terv XML-fájljának megtekintésével tekintheti meg.
Kattintson a jobb gombbal a végrehajtási tervre, és válassza a Végrehajtási terv XML-fájljának megjelenítése... lehetőséget a menüből.

A végrehajtási terv XML-fájlja új lapként nyílik meg az SSMS-ben.

Note

A Hiányzó index részletei... menüben csak egyetlen hiányzó indexjavaslat jelenik meg, még akkor is, ha több javaslat is szerepel a végrehajtási terv XML-fájljában. Előfordulhat, hogy a hiányzó indexjavaslat nem az, amely a lekérdezés becsült legnagyobb becsült javulásával rendelkezik.
A Keresés párbeszédpanel megjelenítése a CTRL+f billentyűparanccsal.
Keressen a MissingIndex kifejezésre.

Ebben a példában két MissingIndex elem található.
- Az első hiányzó index arra utal, hogy a lekérdezés egy olyan indexet használhat a Person.Address táblában, amely támogatja az egyenlőségi keresést az StateProvinceID oszlopon, amely további két oszlopot tartalmaz, City és PostalCode". Az optimalizáláskor a lekérdezésoptimalizáló úgy gondolta, hogy ez az index 34,2737%-tal csökkentheti a lekérdezés becsült költségét .
- A második hiányzó index arra utal, hogy a lekérdezés olyan indexet használhat a Person.Person táblában, amely támogatja a FirstName oszlop egyenlőtlenségi keresését. Az optimalizáláskor a lekérdezésoptimalizáló úgy gondolta, hogy ez az index 18.1102-zel csökkentheti a lekérdezés becsült költségét%.

Az adatbázis minden lemezalapú, nemclustered indexe helyet foglal el, többletterhelést okoz a beszúrásokhoz, frissítésekhez és törlésekhez, és karbantartást igényelhet. Ezen okok miatt ajánlott áttekinteni egy tábla összes hiányzó indexkérelmét és a tábla meglévő indexeit, mielőtt egy lekérdezés végrehajtási tervén alapuló indexet ad hozzá.

Hiányzó indexjavaslatok megtekintése DMV-kben

A hiányzó indexekről az alábbi táblázatban felsorolt dinamikus felügyeleti objektumok lekérdezésével tájékozódhat.

Dinamikus felügyeleti nézet	Visszaadott információk
sys.dm_db_missing_index_group_stats	Összegző információkat ad vissza a hiányzó indexcsoportokról, például a hiányzó indexek egy adott csoportjának implementálásával megszerezhető teljesítménybeli fejlesztésekről.
sys.dm_db_missing_index_groups	A hiányzó indexek adott csoportjára, például a csoportazonosítóra és az adott csoportban található összes hiányzó index azonosítójára vonatkozó információkat adja vissza.
sys.dm_db_missing_index_details	Részletes információkat ad vissza egy hiányzó indexről; Visszaadja például annak a táblának a nevét és azonosítóját, ahol az index hiányzik, valamint a hiányzó indexet alkotó oszlopokat és oszloptípusokat.
sys.dm_db_missing_index_columns	Az indexet hiányzó adatbázistábla-oszlopokkal kapcsolatos információkat adja vissza.

Az alábbi lekérdezés a hiányzó index DMV-ket használja az utasítások létrehozásához CREATE INDEX . Az indexlétrehozási utasítások célja, hogy segítséget nyújtson a saját DDL elkészítésében, miután megvizsgálta a tábla összes kérését, valamint a tábla meglévő indexeit.

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

Ez a lekérdezés a javaslatokat egy nevű oszlop szerint rendeli meg estimated_improvement. A becsült javulás a következők kombinációján alapul:

A hiányzó indexkéréshez társított lekérdezések becsült lekérdezési költsége.
Az index hozzáadásának becsült hatása. Ez a becslés azt mutatja, hogy a nemclustered index mennyivel csökkentené a lekérdezés költségeit.
A hiányzó indexkéréshez társított lekérdezésekhez futtatott lekérdezési operátorok (keresések és vizsgálatok) végrehajtásának összege. A hiányzó indexek lekérdezéstárban való tárolása kapcsán, ezeket az információkat rendszeresen törli a rendszer.

Note

A Microsoft Tiger Toolbox index-létrehozási szkriptje megvizsgálja a hiányzó index DMV-ket, és automatikusan eltávolítja a feleslegesen javasolt indexeket, elemzi az alacsony hatású indexeket, és indexlétrehozó szkripteket hoz létre a felülvizsgálathoz. A fenti lekérdezéshez hasonlóan az indexlétrehozás parancsait is végrehajtja NOT . Az Index-Létrehozás szkript az SQL Serverhez és a felügyelt Azure SQL-példányhoz használható. Az Azure SQL Database esetében fontolja meg az automatikus indexhangolás implementálását.

Az indexek létrehozása előtt tekintse át a hiányzó indexfunkció korlátozásait és a hiányzó indexjavaslatok alkalmazásának módját, és módosítsa az index nevét az adatbázis elnevezési konvenciójának megfelelően.

Hiányzó indexek megőrzése a Lekérdezéstárban

A DMV-k hiányzó indexjavaslatait olyan események törlik, mint például a példányok újraindítása, a feladatátvételek és az adatbázis offline beállítása. Ezenkívül ha egy tábla metaadatai megváltoznak, a rendszer törli a tábláról hiányzó összes indexinformációt ezekből a dinamikus felügyeleti objektumokból. A tábla metaadatainak változásai akkor fordulhatnak elő, ha oszlopokat adnak hozzá vagy elvetnek egy táblából, például amikor indexet hoznak létre egy tábla oszlopán. Ha alter INDEX műveletet hajt végre egy táblán egy indexen, azzal törli az adott tábla hiányzó indexkérelmeit is.

Hasonlóképpen, a tervgyorsítótárban tárolt végrehajtási terveket olyan események törlik, mint például a példányok újraindítása, a feladatátvételek és az adatbázis offline beállítása. A végrehajtási tervek memóriaterhelés és újrafordítás miatt eltávolíthatók a gyorsítótárból.

A végrehajtási tervekben hiányzó indexjavaslatok ezen események között megmaradhatnak a figyelő teljesítményének a Lekérdezéstár használatával történő engedélyezésével.

Az alábbi lekérdezés lekéri a lekérdezéstárból hiányzó indexkéréseket tartalmazó 20 legfontosabb lekérdezési tervet a lekérdezés teljes logikai olvasásának hozzávetőleges becslése alapján. Az adatok az elmúlt 48 órában végrehajtott lekérdezésekre korlátozódnak.

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

Hiányzó indexjavaslatok alkalmazása

A hiányzó indexjavaslatok hatékony használatához kövesse a nem konkredens indextervezési irányelveket. Amikor nem clusterezett indexeket hangol hiányzó index javaslatokkal, vizsgálja meg az alaptábla felépítését, gondosan egyesítse az indexeket, fontolja meg a kulcsoszlopok sorrendjét, és tekintse át a belefoglalt oszlopjavaslatokat.

Az alaptábla szerkezetének áttekintése

Mielőtt nem kizárólagos indexeket hoz létre egy táblán hiányzó indexjavaslatok alapján, tekintse át a tábla fürtözött indexét.

A fürtözött indexek keresésének egyik módja a sp_helpindex rendszer által tárolt eljárás használata. A Person.Address táblában lévő indexek összegzését például a következő utasítás végrehajtásával tekinthetjük meg:

exec sp_helpindex 'Person.Address';
GO

Tekintse át a index_description oszlopot. Egy táblának csak egy klaszterezett indexe lehet. Ha a táblához fürtözött indexet implementáltak, a index_description a 'fürtözött' szót fogja tartalmazni.

Ha nincs klaszterezett index, a tábla egy halom. Ebben az esetben ellenőrizze, hogy a tábla szándékosan halomként lett-e létrehozva egy adott teljesítményproblémának a megoldásához. A legtöbb tábla kihasználja a fürtözött indexeket: a táblákat gyakran véletlenül halomként implementálják. Fontolja meg egy klaszteres index implementálását a klaszteres index tervezési irányelvek alapján.

Hiányzó indexek és meglévő indexek áttekintése átfedés esetén

A hiányzó indexek hasonló variációkat kínálhatnak ugyanazon a táblán és oszlop(ok)on a lekérdezések között. A hiányzó indexek a tábla meglévő indexeihez is hasonlóak lehetnek. Az optimális teljesítmény érdekében érdemes megvizsgálni a hiányzó indexeket és a meglévő indexeket az átfedés érdekében, és elkerülni a duplikált indexek létrehozását.

Meglévő indexek szkriptelése egy táblán

A tábla meglévő indexeinek definíciójának vizsgálatára az egyik módszer az indexek szkriptelése az Object Explorer részleteivel:

Csatlakoztassa a Object Explorert a példányához vagy az adatbázishoz.
Bontsa ki a kérdéses adatbázis csomópontját az Object Explorerben.
Nyissa meg a Táblák mappát.
Bontsa ki azt a táblát, amelyhez indexeket szeretne kiszkriptelni.
Válassza ki az Indexek mappát.
Ha az Object Explorer Részletek ablaktáblája még nincs megnyitva, a Nézet menüben válassza az Object Explorer Details (Objektumkezelő részletei) lehetőséget , vagy nyomja le az F7 billentyűt.
Jelölje ki az Objektumkezelő részletei panelen felsorolt összes indexet a CTRL+a billentyűparanccsal.
Kattintson a jobb gombbal a kijelölt régió tetszőleges pontjára, és válassza a Parancsprogram indexe parancsot, majd CREATE a To** és az Új lekérdezésszerkesztő ablakát.

Indexek áttekintése és kombinálás lehetőség szerint

Tekintse át a tábla mint csoport hiányzó indexjavaslatait, valamint a tábla meglévő indexeinek definícióit. Ne feledje, hogy az indexek meghatározásakor az egyenlőségi oszlopokat általában az egyenlőtlenségi oszlopok elé kell helyezni, és együtt kell alkotniuk az index kulcsát. Az egyenlőségi oszlopok tényleges sorrendjének meghatározásához rendezze őket a szelektivitásuk alapján: először a legválogatottabb oszlopokat listázzuk (az oszloplistában a bal szélsőt). Az egyedi oszlopok a legválogatottabbak, míg a sok ismétlődő értékkel rendelkező oszlopok kevésbé szelektívek.

A záradék használatával belefoglalt oszlopokat kell hozzáadni az CREATE INDEX utasításhoz INCLUDE . A belefoglalt oszlopok sorrendje nem befolyásolja a lekérdezés teljesítményét. Ezért az indexek kombinálásakor a belefoglalt oszlopok kombinálhatók anélkül, hogy a sorrend miatt kellene aggódniuk. További információ a belefoglalt oszlopokra vonatkozó irányelvekben.

Lehet például, hogy van egy táblája, Person.Addressamely egy meglévő indexet tartalmaz a kulcsoszlopon StateProvinceID. Az alábbi oszlopokhoz hiányzó indexjavaslatok jelenhetnek meg a Person.Address táblához:

Az EGYENLŐSÉG szűrői StateProvinceID és City számára
EGYENLŐSÉG szűrők és StateProvinceIDCity, INCLUDEPostalCode

Ha módosítja a meglévő indexet úgy, hogy megfeleljen a második javaslatnak, azaz kulcsokat StateProvinceID és City tartalmazó indexet, amely tartalmazza PostalCode-t is, valószínűleg kielégíti azokat a lekérdezéseket, amelyek mindkét indexjavaslatot generálták.

Az indexhangolásban gyakoriak a kompromisszumok. Valószínű, hogy sok adathalmaz esetében az City oszlop szelektívebb, mint az StateProvinceID oszlop. Ha azonban a meglévő indexet StateProvinceID erősen használják, és az egyéb kérések nagyrészt mindkettőben StateProvinceID keresnek, és Cityaz adatbázis általában alacsonyabb terhelést jelent, ha egyetlen index van a kulcs mindkét oszlopával, amely a StateProvinceIDkulcsban vezet, bár nem ez a legválogatottabb oszlop.

Az indexek többféleképpen is módosíthatók:

A CREATE INDEX utasítást a DROP_EXISTING záradékkal használhatja. A módosítást követően érdemes lehet átnevezni az indexeket , hogy a név az elnevezési konvenciótól függően továbbra is pontosan írja le az indexdefiníciót.
Használhatja a DROP INDEX (Transact-SQL) utasítást, majd egy CREATE INDEX Utasítást.

Az indexkulcsok sorrendje az indexjavaslatok kombinálásakor számít: City vezető oszlopként eltér az első oszloptól StateProvinceID . További információ a nem konklúziós indextervezési irányelvekben.

Indexek létrehozásakor érdemes lehet online indexelési műveleteket használni, ha elérhetők.

Bár az indexek bizonyos esetekben jelentősen javíthatják a lekérdezési teljesítményt, az indexek többletterheléssel és felügyeleti költségekkel is járnak. Az indexek létrehozásának megkezdése előtt tekintse át az indexek általános tervezési irányelveit , amelyek segítenek felmérni az indexek előnyeit.

Ellenőrizze, hogy az index módosítása sikeres-e

Fontos ellenőrizni, hogy az indexmódosítások sikeresek-e: a lekérdezésoptimalizáló használja-e az indexeket?

Az indexváltozások ellenőrzésének egyik módja, ha a Lekérdezéstár használatával azonosítja a hiányzó indexkérelmet tartalmazó lekérdezéseket. Jegyezze fel a lekérdezések query_id-t. A Lekérdezéstár Korrektúrák nézetével ellenőrizheti, hogy módosultak-e a végrehajtási tervek egy lekérdezéshez, és hogy az optimalizáló használja-e az új vagy módosított indexet. További információ a nyomon követett lekérdezésekről a lekérdezési teljesítmény hibaelhárításával kezdődően.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-11-18