A Lekérdezéstár felfedezése
Az SQL Server Lekérdezéstár egy adatbázisonkénti funkció, amely automatikusan rögzíti a lekérdezések, tervek és futtatókörnyezeti statisztikák előzményeit, leegyszerűsítve a teljesítmény hibaelhárítását és a lekérdezések finomhangolását. Emellett betekintést nyújt az adatbázis-használati mintákba és az erőforrás-használatba.
A Lekérdezéstár három tárolóból áll:
- Tervtároló: Tárolja a végrehajtási terv becsült adatait.
- Futtatókörnyezeti statisztikák tárolója: A végrehajtási statisztikák adatait tárolja.
- Várakozási statisztikák tárolása: Megőrzi a várakozási statisztikák adatait.
A lekérdezéstár engedélyezése
A Lekérdezéstár alapértelmezés szerint engedélyezve van az Azure SQL-adatbázisokban. Ha az SQL Serverrel és az Azure Synapse Analyticsszel szeretné használni, először engedélyeznie kell. A Lekérdezéstár funkció engedélyezéséhez használja a környezetére érvényes alábbi lekérdezést:
-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
Hogyan gyűjti a lekérdezéstár az adatokat?
A Lekérdezéstár több fázisban integrálható a lekérdezésfeldolgozó folyamattal. Minden integrációs ponton a rendszer összegyűjti az adatokat a memóriában, és aszinkron módon írja a lemezre az I/O-terhelés minimalizálása érdekében. Az integrációs pontok a következők:
Amikor egy lekérdezés első alkalommal hajt végre, a lekérdezés szövege és a kezdeti becsült végrehajtási terv a lekérdezéstárba kerül, és megmarad.
A terv a lekérdezések újrafordításakor frissül a Lekérdezéstárban. Ha az újrafordítás egy újonnan létrehozott végrehajtási tervet eredményez, az a lekérdezéstárban is megmarad az előző tervek kibővítéséhez. Emellett a Lekérdezéstár nyomon követi az egyes lekérdezéstervek végrehajtási statisztikáit összehasonlítás céljából.
A fordítás és az újrafordítási fázisok ellenőrzése során a lekérdezéstár azonosítja, hogy van-e kényszerített terv a lekérdezés végrehajtásához. A lekérdezés újrafordításra kerül, ha a Lekérdezéstár a tervtől eltérő kényszerített tervet biztosít az eljárásgyorsítótárban.
Egy lekérdezés végrehajtásakor a futtatókörnyezet statisztikái a Lekérdezéstárban maradnak. A Lekérdezéstár összesíti ezeket az adatokat, hogy minden lekérdezési terv pontos legyen.
Ha többet szeretne megtudni arról, hogy a Lekérdezéstár hogyan gyűjt adatokat, olvassa el a Lekérdezéstár adatgyűjtési módját.
Gyakori forgatókönyvek
Az SQL Server Query Store értékes betekintést nyújt az adatbázis-műveletek teljesítményébe. Gyakori forgatókönyvek a következők:
- A teljesítményregressziók azonosítása és javítása a lekérdezés-végrehajtási terv rosszabb kiválasztása miatt.
- A legnagyobb erőforrás-felhasználású lekérdezések azonosítása és finomhangolása.
- A/B tesztelés az adatbázis- és alkalmazásváltozások hatásainak kiértékeléséhez.
- Az SQL Server frissítései utáni teljesítménystabilitás biztosítása.
- A leggyakrabban használt lekérdezések meghatározása.
- Lekérdezési tervek előzményeinek naplózása.
- A nem tervezett számítási feladatok azonosítása és javítása.
- Az adatbázis gyakori várakozási kategóriáinak, valamint a várakozási időt befolyásoló közreműködő lekérdezések és tervek megismerése.
- Adatbázis-használati minták elemzése az erőforrás-felhasználás (CPU, I/O, Memória) tekintetében.
A Lekérdezéstár nézeteinek felderítése
Ha a Lekérdezéstár engedélyezve van egy adatbázisban, a Lekérdezéstár mappa látható az adatbázis számára az Object Explorerben. Az Azure Synapse Analytics esetében a Lekérdezéstár nézetei a Rendszernézetek területen jelennek meg. A Lekérdezéstár nézetei összesített, gyors elemzéseket nyújtanak az SQL Server-adatbázis teljesítménybeli jellemzőiről.
Regressziós lekérdezések
A regressziós lekérdezések teljesítménycsökkenést tapasztalnak a végrehajtási terv változásai miatt. A becsült végrehajtási tervek különböző tényezők miatt változhatnak, beleértve a sémaváltozásokat, a statisztikák változásait és az index változásait. Az eljárásgyorsítótár vizsgálata lehet az első ösztön, de csak a legújabb végrehajtási tervet tárolja egy lekérdezéshez, és a tervek kiüríthetők a rendszer memóriaigényei alapján. A Lekérdezéstár azonban minden lekérdezéshez több végrehajtási tervet is tárol, lehetőséget biztosítva arra, hogy a tervmódosítások okozta lekérdezés teljesítményregresszió kezelésére egy adott tervet tervlekényszerítéssel válasszunk ki.
A regressziós lekérdezések nézet képes meghatározni azokat a lekérdezéseket, amelyek végrehajtási metrikái egy adott időkereten belül a végrehajtási terv változásai miatt regressziós állapotba kerülnek. Ez a nézet lehetővé teszi a szűrést egy kiválasztott metrika (például időtartam, processzoridő, sorszám és egyebek) és egy statisztika (összeg, átlag, perc, max vagy szórás) alapján. Ezután felsorolja a 25 legjobb regressziós lekérdezést a megadott szűrő alapján. Alapértelmezés szerint a lekérdezések grafikus sávdiagram nézete jelenik meg, de lehetőség van a lekérdezések rácsformátumban való megtekintésére is.
Miután kiválasztott egy lekérdezést a bal felső lekérdezési panelről, a tervösszesítő panel megjeleníti a lekérdezéshez tartozó, az idő függvényében megmaradó lekérdezési terveket. Ha kiválaszt egy lekérdezéstervet a Terv összegzése panelen, az alsó panelen megjelenik egy grafikus lekérdezésterv. A tervösszegző és a grafikus lekérdezésterv panel eszköztárgombjai lehetővé teszik a kijelölt lekérdezés kijelölt tervének kényszerítéséhez. Ezt a panelstruktúrát és -viselkedést a rendszer minden SQL Query-nézetben következetesen használja.
Másik lehetőségként használhatja a tárolt eljárást a sp_query_store_force_plan terv kényszerítésére.
EXEC sp_query_store_force_plan @query_id=73, @plan_id=79
Összesített erőforrás-felhasználás
A Teljes erőforrás-felhasználás nézet lehetővé teszi a teljes erőforrás-felhasználás elemzését több végrehajtási metrika esetében (például a végrehajtások száma, időtartama, várakozási idő stb.) egy adott időkerethez. A renderelt diagramok interaktívak; ha egy mértéket választ ki az egyik diagramból, egy részletezési nézet jelenik meg, amely a kiválasztott mértékhez társított lekérdezéseket jeleníti meg egy új lapon.
A részletek nézet a kiválasztott metrikához hozzájáruló 25 erőforrás-fogyasztó lekérdezését tartalmazza. Ez a részletes nézet a konzisztens felületet használja, amely lehetővé teszi a kapcsolódó lekérdezések és azok részleteinek vizsgálatát, a mentett becsült lekérdezési tervek kiértékelését, és opcionálisan a terv kényszerítését a teljesítmény javítása érdekében. Ez a nézet akkor hasznos, ha a rendszererőforrás-versengés problémává válik, például amikor a processzorhasználat eléri a kapacitást.
Az erőforrásokat a legnagyobb mértékben használó lekérdezések
A Leggyakoribb erőforrás-fogyasztó lekérdezések nézet hasonló a Teljes erőforrás-felhasználás nézet részletes részletezéséhez. Lehetővé teszi egy metrika és egy statisztika szűrőként való kiválasztását is. A megjelenített lekérdezések azonban a kiválasztott szűrő és időkeret alapján a 25 leghatásosabb lekérdezés.
A leggyakoribb erőforrás-fogyasztó lekérdezések nézet a számítási feladat nem tervezett jellegének első jelzését nyújtja a nem tervezett számítási feladatok azonosítása és javítása során. Az alábbi képen például a Végrehajtás száma metrika és a Teljes statisztika van kiválasztva annak felfedéséhez, hogy a legtöbb erőforrást használó lekérdezés körülbelül 90% csak egyszer lesz végrehajtva.
Kényszerített csomagokat tartalmazó lekérdezések
A Kényszerített tervekkel rendelkező lekérdezések nézet gyorsan áttekinti a kényszerített lekérdezési tervekkel rendelkező lekérdezéseket. Ez a nézet akkor válik relevánssá, ha egy kényszerített terv már nem a várt módon működik, és újra kell értékelni. Ez a nézet lehetővé teszi, hogy áttekintse egy kiválasztott lekérdezés összes becsült végrehajtási tervét, így könnyen megállapíthatja, hogy egy másik terv jobban megfelel-e a teljesítménynek. Ha igen, az eszköztárgombok szükség szerint feloldják a csomag végrehajtásának feloldását.
Nagy variációjú lekérdezések
A lekérdezés teljesítménye végrehajtásonként eltérő lehet. A Nagy változat nézettel rendelkező lekérdezések olyan lekérdezések elemzését tartalmazzák, amelyek a legmagasabb variációval vagy szórással rendelkeznek egy kiválasztott metrikához. A felület összhangban van a legtöbb Lekérdezéstár nézettel, amely lehetővé teszi a lekérdezés részleteinek vizsgálatát, a végrehajtási terv kiértékelését, és opcionálisan egy adott terv kényszerítését. Ezzel a nézetben konzisztensebb teljesítménymintába hangolhatja a kiszámíthatatlan lekérdezéseket.
Lekérdezés várakozási statisztikái
A Lekérdezési várakozási statisztikák nézet elemzi az adatbázis legaktívabb várakozási kategóriáit, és megjelenít egy diagramot. Ez a diagram interaktív; a várakozási kategória kiválasztása részletezi a várakozási idő statisztikához hozzájáruló lekérdezések részleteit.
A részletek nézet felülete a legtöbb lekérdezéstár nézettel is összhangban van, amely lehetővé teszi a lekérdezés részleteinek vizsgálatát, a végrehajtási terv kiértékelését és opcionálisan egy adott terv kényszerítését. Ez a nézet segít azonosítani azokat a lekérdezéseket, amelyek hatással vannak a felhasználói élményre az alkalmazásokban.
Nyomkövetési lekérdezés
A Nyomkövetési lekérdezés nézet lehetővé teszi egy adott lekérdezés elemzését egy megadott lekérdezésazonosító-érték alapján. A futtatás után a nézet a lekérdezés teljes végrehajtási előzményeit tartalmazza. A végrehajtásra vonatkozó pipa azt jelzi, hogy kényszerített tervet használtak. Ez a nézet betekintést nyújt az olyan lekérdezésekbe, mint a kényszerített tervekkel rendelkező lekérdezések, amelyek ellenőrzik, hogy a lekérdezési teljesítmény stabil-e.
Lekérdezési várakozások keresése a Lekérdezéstár használatával
Amikor egy rendszer teljesítménye csökkenni kezd, érdemes lekérdezési várakozási statisztikákat tanulmányozni, hogy esetleg azonosítani lehessen az okot. A hangolásra szoruló lekérdezések azonosítása mellett a potenciális infrastruktúra-frissítéseket is megvilágíthatja, amelyek hasznosak lehetnek.
Az SQL Query Store biztosítja a Lekérdezési várakozási statisztikák nézetet, hogy betekintést nyújtson az adatbázis legfontosabb várakozási kategóriáiba. Jelenleg 23 várakozási kategória létezik.
A sávdiagram az adatbázis leghatásosabb várakozási kategóriáit jeleníti meg a Lekérdezési várakozási statisztikák nézet megnyitásakor. Emellett a várakozási kategóriák panel eszköztárán található szűrő lehetővé teszi a várakozási statisztikák kiszámítását a teljes várakozási idő (alapértelmezett), az átlagos várakozási idő, a minimális várakozási idő, a maximális várakozási idő vagy a szórási várakozási idő alapján.
Ha kiválaszt egy várakozási kategóriát, az részletezi az adott várakozási kategóriához hozzájáruló lekérdezések részleteit. Ebből a nézetben megvizsgálhatja az egyes lekérdezéseket, amelyek a leghatásosabbak. A tervösszesítő panelen megjelenítendő becsült végrehajtási tervekhez a lekérdezés panelen lévő lekérdezés kiválasztásával férhet hozzá. Ha kiválaszt egy lekérdezéstervet a Terv összegző panelről, az alsó panelen megjelenik a grafikus lekérdezésterv. Ebből a nézetben kényszerítheti vagy lekényszerítheti a lekérdezésre vonatkozó lekérdezéstervet a teljesítmény javítása érdekében.
Automatikus tervkorrekció
Az SQL Server 2017 és az Azure SQL Database a Lekérdezéstárban lévő adatok elemzésével bevezette az automatikus tervkorrekció koncepcióját. Ha az SQL Server 2017 (vagy újabb) verzióban és az Azure SQL Database-ben engedélyezett a Lekérdezéstár egy adatbázisban, az SQL Server motorja lekérdezésterv-regressziókat keres, és javaslatokat ad. Ezeket a javaslatokat a sys.dm_db_tuning_recommendations dinamikus felügyeleti nézetben (DMV) tekintheti meg. Ezek közé a javaslatok közé tartoznak azok a T-SQL-utasítások, amelyekkel manuálisan kényszeríthető egy lekérdezésterv, ha a teljesítmény jó állapotban volt.
Ha ezek a javaslatok elnyerték a bizalmát, engedélyezheti, hogy az SQL Server automatikusan kényszerítsen terveket, amikor regressziókat talál. Automatikus tervkorrekció engedélyezése a használatával ALTER DATABASE és az argumentum használatával AUTOMATIC_TUNING .
Azure SQL Database esetében az automatikus tervkorrekciót az Azure Portal vagy a REST API-k automatikus hangolási beállításaival is engedélyezheti. Az automatikus tervkorrekcióval kapcsolatos javaslatok mindig engedélyezve vannak minden olyan adatbázis esetében, amelyben engedélyezve van a Lekérdezéstár (ami az Azure SQL Database és a felügyelt Azure SQL-példányok esetében alapértelmezett). Új adatbázisok esetén alapértelmezés szerint engedélyezve van az automatikus tervkorrekció (FORCE_PLAN) az Azure SQL Database-ben.