Lekérdezéstár használati forgatókönyvei

A következőkre vonatkozik: Sql Server 2016 (13.x) és újabb verziók Azure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse Analytics (csak dedikált SQL-készlet)SQL-adatbázis a Microsoft Fabricben

A Lekérdezéstár számos forgatókönyvben használható, ha a számítási feladatok kiszámítható teljesítményének nyomon követése és biztosítása kritikus fontosságú. Íme néhány példa, amelyeket megfontolhat:

• Lekérdezések rögzítése és javítása tervválasztási regressziókkal

• A legnagyobb erőforrás-igényű lekérdezések azonosítása és finomhangolása

• A/B tesztelés

• A teljesítmény stabilitásának megőrzése az újabb SQL Serverre való frissítés során

• Alkalmi számítási feladatok azonosítása és fejlesztése

• A lekérdezéstár konfigurálásáról és felügyeletéről további információt A lekérdezéstárhasználatával végzett teljesítményfigyelés című témakörben talál.

• A végrehajtható információk feltárásával és a lekérdezéstár teljesítményének finomhangolásával kapcsolatos információkért tekintse meg a Teljesítmény finomhangolása a Lekérdezéstárhasználatával című témakört.

• A lekérdezéstár Azure SQL Database-ben való működtetéséről további információt A lekérdezéstár üzemeltetése az Azure SQL Database-bencímű témakörben talál.

Lekérdezések rögzítése és javítása tervválasztási regressziókkal

A lekérdezésoptimalizáló a lekérdezések rendszeres végrehajtása során dönthet úgy, hogy egy másik tervet választ, mert a fontos bemenetek eltérőek lettek: az adatok számossága megváltozott, indexek jöttek létre, módosultak vagy elvettek, a statisztikák frissültek stb. Az új terv általában jobb, vagy nagyjából ugyanaz, mint a korábban használt terv. Vannak azonban olyan esetek, amikor az új terv jelentősen rosszabb - ezt a helyzetet tervválasztási változás regressziónak nevezzük. A Lekérdezéstár előtt nehéz volt azonosítani és kijavítani a problémát, mivel az SQL Server nem biztosított beépített adattárat, így a felhasználók megtekinthetik az idővel használt végrehajtási terveket.

A Lekérdezéstár segítségével gyorsan:

• Azonosítsa azokat a lekérdezéseket, amelyek végrehajtási metrikái csökkentek az érdeklődési időszakban (múlt óra, nap, hét stb.). Az SQL Server Management Studio visszalépett lekérdezései használatával felgyorsíthatja elemzését.

• A regressziós lekérdezések között könnyen megtalálhatja azokat a lekérdezéseket, amelyek több tervvel is voltak, és amelyek a rossz tervválasztás miatt csökkentek. A Tervösszegző panelt a Regressziós lekérdezések részben használja, hogy megjelenítse az összes tervet egy regressziós lekérdezéshez és azok időbeli teljesítményét.

• Kényszerítse ki az előző tervet a történelemből, ha jobbnak bizonyult. A Terv kényszerítése gombot a Regressed Query használatával kényszerítheti a lekérdezés kiválasztott tervét.

A forgatókönyv részletes leírását Lekérdezéstár: Az adatbázis repülési adatrögzítője blogban találja.

A legnagyobb erőforrás-igényű lekérdezések azonosítása és finomhangolása

Bár a számítási feladat több ezer lekérdezést generálhat, általában csak néhányuk használja ténylegesen a legtöbb rendszererőforrást, ezért figyelmet igényel. A legtöbb erőforrást használó lekérdezések között általában olyan lekérdezéseket talál, amelyek regressziós vagy további finomhangolással javíthatók.

A feltárás elindításának legegyszerűbb módja a Top Resource Consuming Queries megnyitása a Management Studióban. A felhasználói felület három panelre van elválasztva: a legfelső erőforrás-használó lekérdezéseket (balra), a kiválasztott lekérdezés tervösszegzőjét (jobb oldalon) és a kijelölt terv vizuális lekérdezési tervét (alul) ábrázoló hisztogram. Válassza a Konfigurálás elemet az elemezni kívánt lekérdezések és az időintervallum szabályozásához. Emellett választhat a különböző erőforrás-felhasználási dimenziók (időtartam, CPU, memória, I/O, végrehajtások száma) és az alapterv (Átlag, Min, Max, Összeg, Szórás) között.

Tekintse meg a jobb oldalon található terv összefoglalását a végrehajtási előzmények elemzéséhez, valamint a különböző tervek és azok futtatókörnyezeti statisztikáinak megismeréséhez. Az alsó panelen vizsgálja meg a különböző terveket, vagy hasonlítsa össze őket vizuálisan, egymás mellett renderelve (használja a Összehasonlítás gombot).

Ha egy optimálisnál alacsonyabb teljesítményű lekérdezést azonosít, a művelet a probléma jellegétől függ:

1. Ha a lekérdezés több tervvel lett végrehajtva, és az utolsó terv lényegesen rosszabb, mint az előző terv, a terv kényszerítési mechanizmusával biztosíthatja, hogy az SQL Server az optimális tervet használja a jövőbeli végrehajtásokhoz

2. Ellenőrizze, hogy az optimalizáló hiányzó indexeket javasol-e az XML-csomagban. Ha igen, hozza létre a hiányzó indexet, és a Lekérdezéstár használatával értékelje ki a lekérdezés teljesítményét az index létrehozása után

3. Győződjön meg arról, hogy a statisztikák up-to-date for the mögöttes táblák által használt lekérdezés.

4. Győződjön meg arról, hogy a lekérdezés által használt indexek nincsenek töredezve.

5. Fontolja meg a költséges lekérdezés újraírását. Használja ki például a lekérdezésparaméterezés előnyeit, és csökkentse a dinamikus SQL használatát. Optimális logika megvalósítása az adatok beolvasásakor (adatszűrés alkalmazása adatbázisoldalon, nem alkalmazásoldalon).

A/B tesztelés

A Lekérdezéstár használatával összehasonlíthatja a számítási feladatok teljesítményét az alkalmazás módosításai előtt és után.

Az alábbi lista számos példát tartalmaz, ahol a Lekérdezéstár használatával felmérheti a környezet vagy az alkalmazás számítási feladatok teljesítményére gyakorolt hatását:

• Új alkalmazásverzió bevezetése.

• Új hardver hozzáadása a kiszolgálóhoz.

• Hiányzó indexek létrehozása drága lekérdezések által hivatkozott táblákon.

• Szűrési szabályzat alkalmazása a sorszintű biztonsághoz. További információért lásd: Sorszintű biztonság optimalizálása a Lekérdezéstár használatával.

• Temporális rendszerverziósítás hozzáadása az OLTP-alkalmazások által gyakran módosított táblákhoz.

Az alábbi forgatókönyvek bármelyikében alkalmazza a következő munkafolyamatot:

1. Futtassa a számítási feladatot a Lekérdezéstárral a tervezett módosítás előtt a teljesítménykonfiguráció létrehozásához.

2. Alkalmazásmódosítás alkalmazása az ellenőrzött időpontban.

3. A számítási feladat futtatásának folytatása a rendszer teljesítményképének létrehozásához a módosítás után

4. Hasonlítsa össze az 1. és a 3. találatot.

   1. Nyissa meg teljes adatbázis-használat a teljes adatbázisra gyakorolt hatás meghatározásához.

   2. Nyissa meg erőforrás-fogyasztó lekérdezéseket (vagy futtassa a saját elemzését Transact-SQLhasználatával) a legfontosabb lekérdezések változásának hatásának elemzéséhez.

5. Döntse el, hogy megtartja-e a módosítást, vagy végrehajtja a visszaállítást abban az esetben, ha az új teljesítmény elfogadhatatlan.

Az alábbi ábrán a Lekérdezéstár elemzése (4. lépés) látható, ha hiányzik az index létrehozása. Nyissa meg leggyakoribb erőforrás-használó lekérdezéseket/Tervösszesítő panelt az index létrehozása által érintett lekérdezés nézetének lekéréséhez:

Emellett az index létrehozása előtt és után is összehasonlíthatja a terveket egymás mellett való megjelenítéssel. ("Hasonlítsa össze a kijelölt lekérdezés terveit egy külön ablakban" eszköztárbeállítás, amely piros négyzettel van megjelölve az eszköztáron.)

Az indexlétrehozás előtt (plan_id = 1, fent) hiányzik az indexmutató, és ellenőrizheti, hogy a fürtözött indexvizsgálat volt-e a lekérdezés legdrágább operátora (piros téglalap).

A hiányzó index létrehozása után (plan_id = 15; lásd alább) az Index keresés (nem fürtözött index) szerepel, amely csökkenti a lekérdezés összköltségét, és javítja a lekérdezés teljesítményét (zöld téglalap).

Az elemzés alapján valószínűleg megtartaná az indexet, mivel a lekérdezési teljesítmény javult.

A teljesítmény stabilitásának megőrzése az újabb SQL Serverre való frissítés során

Az SQL Server 2014 (12.x) előtt a felhasználók ki voltak téve a teljesítményregresszió kockázatának a legújabb platformverzióra való frissítés során. Ennek oka az volt, hogy a Lekérdezésoptimalizáló legújabb verziója azonnal aktívvá vált az új bitek telepítése után.

Az SQL Server 2014-től kezdve (12.x) minden lekérdezésoptimalizáló módosítás a legújabb adatbázis-kompatibilitási szinthezvan kötve, így a tervek nem változnak meg közvetlenül a frissítéskor, hanem csak akkor, amikor a felhasználó a COMPATIBILITY_LEVEL-t a legújabb szintre módosítja. Ez a funkció a Lekérdezéstárral kombinálva nagy mértékben szabályozhatja a lekérdezési teljesítményt a frissítési folyamat során. Az ajánlott frissítési munkafolyamat az alábbi képen látható:

1. Frissítse az SQL Servert az adatbázis kompatibilitási szintjének módosítása nélkül. Nem teszi elérhetővé a lekérdezésoptimalizáló legújabb módosításait, de továbbra is újabb SQL Server-funkciókat biztosít, beleértve a Lekérdezéstárat is.

2. Engedélyezze a lekérdezéstárat. További információ: Lekérdezéstár beállítása a számítási feladathoz.

3. A Lekérdezéstár lehetővé teszi a lekérdezések és tervek rögzítését, valamint a forrás-/korábbi adatbázis-kompatibilitási szinttel rendelkező teljesítménykonfiguráció létrehozását. Tartson ki ebben a lépésben elég ideig ahhoz, hogy rögzítse az összes tervet, és stabil alapszintet kapjon. Ez lehet egy termelési munkaterhelés szokásos üzleti ciklusának időtartama.

4. Lépjen a legújabb adatbáziskompatibilitási szintre: tegye ki munkaterhelését a legújabb lekérdezésoptimalizálónak, hogy potenciálisan új terveket hozhasson létre.

5. Elemzési és regressziós javításokhoz használja a Lekérdezéstárat: az új Lekérdezésoptimalizáló-fejlesztések általában jobb terveket eredményeznek. A Lekérdezéstár azonban könnyen azonosíthatja a tervválasztási regressziókat, és kijavíthatja őket egy terv kényszerítési mechanizmusával. Az SQL Server 2017 -től (14.x) kezdve az automatikus tervkorrekciós funkció használatakor ez a lépés automatikussá válik.

   a. Ha regressziók vannak, kényszerítse ki a korábban ismert jó tervet a Lekérdezéstárban.

   b. Ha vannak olyan lekérdezési tervek, amelyek nem kényszeríthetők ki, vagy ha a teljesítmény továbbra sem elegendő, fontolja meg a adatbázis kompatibilitási szintjének visszaállítását a korábbi beállításra, majd forduljon a Microsoft ügyfélszolgálatához.

Tip

Az SQL Server Management Studio Adatbázis frissítése feladatával frissítse az adatbázis adatbázis kompatibilitási szintjét. Részletekért tekintse meg Adatbázisok frissítését a Lekérdezéshangolási segéd használatával.

Alkalmi számítási feladatok azonosítása és fejlesztése

Egyes számítási feladatok nem rendelkeznek domináns lekérdezésekkel, amelyeket az alkalmazás általános teljesítményének javítása érdekében hangolhat. Ezeket a számítási feladatokat általában viszonylag nagy számú különböző lekérdezés jellemzi, amelyek mindegyike a rendszererőforrások egy részét használja fel. Az egyedi lekérdezéseket nagyon ritkán hajtják végre (általában csak egyszer, innen az ad hoc elnevezés), így a futásidejű terhelés nem kritikus fontosságú. Másrészt, mivel az alkalmazás folyamatosan hoz létre nettó új lekérdezéseket, a rendszererőforrások jelentős részét a lekérdezések fordítására fordítják, ami nem optimális. Ez a Lekérdezéstár számára sem ideális helyzet, mivel a sok lekérdezés és végrehajtási terv elárasztja a számára fenntartott területet, ami azt jelenti, hogy a Lekérdezéstár valószínűleg nagyon gyorsan írásvédett módba kerül. Ha aktiválta a Méretalapú törlési szabályzatot (erősen ajánlott annak érdekében, hogy a Lekérdezéstár mindig működőképes maradjon), akkor a háttérfolyamat az idő nagy részében a Lekérdezéstár struktúráinak tisztításával foglalkozik, ami jelentős rendszererőforrásokat igényel.

A leggyakoribb erőforrás-fogyasztó lekérdezések nézet elsőként jelzi a számítási feladat alkalmi jellegét:

A Végrehajtási szám metrikával elemezheti, hogy a leggyakoribb lekérdezések alkalmiak-e (ehhez a Lekérdezéstárat QUERY_CAPTURE_MODE = ALLkell futtatnia). A fenti ábrán látható, hogy legtöbb erőforrást igénylő lekérdezéseinek 90% csak egyszer kerül végrehajtásra.

Másik lehetőségként futtathat Transact-SQL szkriptet a lekérdezési szövegek, lekérdezések és tervek teljes számának lekéréséhez a rendszerben, és a query_hash és a query_plan_hashösszehasonlításával meghatározhatja, hogy ezek mennyire különböznek:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Ez az egyik lehetséges eredmény, amelyet eseti lekérdezésekkel rendelkező számítási feladatok esetén kaphat:

A lekérdezés eredménye azt mutatja, hogy a lekérdezéstárban található nagyszámú lekérdezés és tervek ellenére a query_hash és a query_plan_hash valójában nem különböznek. Az egyedi lekérdezési szövegek és az egyedi lekérdezési kivonatok közötti arány, amely sokkal nagyobb, mint 1, azt jelzi, hogy a számítási feladat jó választás a paraméterezéshez, mivel a lekérdezések között az egyetlen különbség a lekérdezésszöveg részeként megadott konstans (paraméter).

Ez a helyzet általában akkor fordul elő, ha az alkalmazás lekérdezéseket hoz létre (a tárolt eljárások vagy paraméteres lekérdezések meghívása helyett), vagy ha az alapértelmezés szerint lekérdezéseket generáló objektum-relációs leképezési keretrendszerekre támaszkodik.

Ha ön felügyeli az alkalmazáskódot, érdemes lehet újraírni az adatelérési réteget tárolt eljárások vagy paraméteres lekérdezések használatához. Ez a helyzet azonban jelentősen javítható alkalmazásmódosítások nélkül is, ha a lekérdezésparaméterezést a teljes adatbázisra (az összes lekérdezésre) vagy az azonos query_hashrendelkező egyes lekérdezéssablonokra kényszeríti.

Az egyes lekérdezéssablonokkal való megközelítéshez terv útmutató létrehozása szükséges:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

A tervvezetőkkel való megoldás pontosabb, de több munkát igényel.

Ha az összes lekérdezés (vagy azok többsége) automatikus paraméterezésre van kijelölve, fontolja meg a PARAMETERIZATION = FORCED konfigurálását a teljes adatbázishoz. További információ: Kényszerített paraméterezés használatának irányelvei.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

A lépések alkalmazása után a legnagyobb erőforrásigényű lekérdezések eltérő képet fog mutatni a terhelésről.

Bizonyos esetekben az alkalmazás számos különböző lekérdezést generálhat, amelyek nem alkalmasak az automatikus paraméterezésre. Ebben az esetben nagy számú lekérdezés jelenik meg a rendszerben, de az egyedi lekérdezések és az egyedi query_hash aránya valószínűleg közel 1- hez.

Ebben az esetben engedélyeznie kell a Az alkalmi számítási feladatok optimalizálása kiszolgálóbeállítást, hogy megakadályozza a gyorsítótár memóriájának elhasználását olyan lekérdezéseken, amelyek valószínűleg nem lesznek újra végrehajtva. Ha meg szeretné akadályozni a lekérdezések rögzítését a Lekérdezéstárban, állítsa QUERY_CAPTURE_MODEAUTOértékre.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Következő lépések

• Teljesítmény monitorozása a lekérdezéstár használatával
• Legjobb gyakorlat a Lekérdezéstár használata
• Adatbázisok frissítése a Lekérdezéshangolási asszisztens segítségével