Adatbázisok frissítése a Lekérdezéshangolási segéd használatával

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók

Ha az SQL Server egy régebbi verziójáról az SQL Server 2014-es (12.x) vagy újabb verzióira migrál, és az adatbázis kompatibilitási szintjét a legújabb rendelkezésre állóra frissíti, előfordulhat, hogy egy számítási feladat ki van téve a teljesítményregresszió kockázatának. Ez kisebb mértékben is lehetséges az SQL Server 2014 (12.x) és bármely újabb verzió közötti frissítéskor.

Az SQL Server 2014 (12.x) és újabb verzióiban a lekérdezésoptimalizáló minden módosítása a legújabb adatbáziskompatibilitási szintre kerül, így a végrehajtási tervek nem módosulnak közvetlenül a frissítéskor, hanem akkor, amikor a felhasználó az COMPATIBILITY_LEVEL adatbázis-beállítást a legújabb elérhetőre módosítja. Az SQL Server 2014-ben (12.x) bevezetett lekérdezésoptimalizáló változásokról további információt a Számosságbecslés (SQL Server) című témakörben talál. A kompatibilitási szintekről és azok frissítési hatásáról további információt kompatibilitási szintek és adatbázismotor-frissítésekcímű témakörben talál.

Ez az adatbáziskompatibilitási szint által biztosított gating funkció a Lekérdezéstárral együtt nagy mértékben szabályozza a lekérdezési teljesítményt a frissítési folyamatban, ha a frissítés a következő ábrán látható ajánlott munkafolyamatot követi. A kompatibilitási szint frissítéséhez javasolt munkafolyamatról további információt az adatbázis kompatibilitási szintjének módosítása és a Lekérdezéstár használata című témakörben talál.

Az SQL Server 2017 (14.x) használatával tovább fejlesztették a frissítések szabályozását, ahol bevezették az automatikus hangolást , és lehetővé teszik az ajánlott munkafolyamat utolsó lépésének automatizálását.

Az SQL Server Management Studio 18-as verziójától kezdve a Lekérdezéshangolási segéd (QTA) funkció végigvezeti a felhasználókat az ajánlott munkafolyamaton, hogy megőrizze a teljesítménystabilitást az újabb SQL Server-verziókra való frissítés során, ahogyan azt a Lekérdezéstár-használati forgatókönyvekújabb SQL Serverre való frissítés során a teljesítménystabilitás megőrzése című szakaszban is bemutatjuk. A QTA azonban nem áll vissza egy korábban jól ismert tervre, ahogyan az az ajánlott munkafolyamat utolsó lépésében látható. Ehelyett a QTA nyomon követi a Lekérdezéstár regressziós lekérdezések nézetében található regressziókat, és iterálja át az alkalmazható optimalizálómodell-változatok lehetséges permutációit, hogy egy új, jobb tervet lehessen létrehozni.

Fontos

A QTA nem hoz létre felhasználói számítási feladatot. Ha olyan környezetben futtatja a QTA-t, amelyet az alkalmazások nem használnak, győződjön meg arról, hogy a célzott SQL Server adatbázismotoron továbbra is végrehajthat reprezentatív tesztterhelést más módon.

A Lekérdezéshangolási asszisztens munkafolyamata

A QTA kiindulási pontja feltételezi, hogy az SQL Server egy korábbi verziójából származó adatbázist ( adatbázis csatolása vagy VISSZAÁLLÍTÁSi utasítások) az SQL Server adatbázismotor újabb verziójára helyezi át, és a frissítés előtti adatbázis-kompatibilitási szint nem változik azonnal. A QTA végigvezeti az alábbi lépéseket:

1. Konfigurálja a Lekérdezéstárat a felhasználó által beállított számítási feladatok időtartamára (napokban) vonatkozó ajánlott beállítások szerint. Gondolja át, hogy a számítási feladatok időtartama megfelel-e a tipikus üzleti ciklusnak.

2. Kérje meg a szükséges számítási feladat elindítását, hogy a Lekérdezéstár összegyűjthesse a számítási feladatok adatainak alapkonfigurációját (ha még nincs ilyen).

3. Frissítsen a felhasználó által választott céladatbázis kompatibilitási szintjére.

4. Kérje meg a számítási feladatok adatainak második menetét az összehasonlításhoz és a regresszió észleléséhez.

5. Iterálja át a Lekérdezéstár Regressded Query nézet alapján talált regressziókat, kísérletezzen úgy, hogy futásidejű statisztikákat gyűjt az alkalmazható optimalizálómodell-változatok lehetséges permutációiról, és mérje meg az eredményt.

6. Jelentés a mért fejlesztésekről, és opcionálisan lehetővé teszi a módosítások megőrzését terv útmutatóinakhasználatával.

Az adatbázis csatolásáról további információt az Adatbázis leválasztása és csatolásacímű témakörben talál.

Az alábbi ábra azt mutatja be, hogy a QTA hogyan módosítja az ajánlott munkafolyamat utolsó lépéseit a kompatibilitási szint frissítéséhez a Korábban látott Lekérdezéstár használatával. A jelenleg nem hatékony végrehajtási terv és az utolsó ismert jó végrehajtási terv közötti választás helyett a QTA a kiválasztott regressziós lekérdezésekre jellemző hangolási lehetőségeket nyújt, hogy egy új továbbfejlesztett állapotot hozzon létre finomhangolt végrehajtási tervekkel.

QTA – Belső keresési terület finomhangolása

A QTA csak SELECT lekérdezéseket céloz meg, amelyek a Lekérdezéstárból végrehajthatók. A paraméteres lekérdezések akkor alkalmazhatók, ha a kompilált paraméter ismert. A futásidejű szerkezetektől, például ideiglenes tábláktól vagy táblaváltozóktól függő lekérdezések jelenleg nem jogosultak.

A QTA a számosságbecslés (SQL Server) verzióinak változásai miatt a lekérdezési regresszió ismert lehetséges mintáit célozza meg. Ha például egy adatbázist az SQL Server 2012 -ről (11.x) és 110-es adatbázis-kompatibilitási szintről SQL Server 2017-re (14.x) és 140-es adatbázis-kompatibilitási szintre frissít, egyes lekérdezések visszafejlődhetnek, mert kifejezetten az SQL Server 2012 -ben (11.x) (CE 70) létező CE-verzióval való együttműködésre lettek tervezve. Ez nem jelenti azt, hogy a CE 140-ről a CE 70-re való visszaállítás az egyetlen lehetőség. Ha a regressziót csak az újabb verzió egy adott módosítása mutatja be, akkor arra utalhat, hogy a lekérdezés csak az előző CE-verzió megfelelő részét használja, amely jobban működött az adott lekérdezéshez, miközben továbbra is az újabb CE-verziók összes többi továbbfejlesztését használja. Emellett lehetővé teszi a számítási feladatokban lévő, nem regressziós lekérdezések számára, hogy kihasználják az újabb CE-fejlesztéseket.

A QTA által keresett CE-minták a következők:

• Függetlenség és korreláció: Ha a függetlenség feltételezése jobb becslést ad az adott lekérdezéshez, akkor a lekérdezési tipp USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') arra készteti az SQL Servert, hogy a minimális szelektivitást használva hozzon létre végrehajtási tervet, amikor a szűrők predikátumait becsüli AND meg a korreláció figyelembe vételével. További információ: USE HINT lekérdezési tippek és A CEverziói.

• Egyszerű elszigetelés és alapszintű elszigetelés: Ha egy másik illesztési elszigetelés jobb becslést ad az adott lekérdezéshez, akkor a lekérdezési tipp USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') azt eredményezi, hogy az SQL Server végrehajtási tervet hoz létre az egyszerű elszigetelési feltételezéssel az alapértelmezett alapelszigetelési feltételezés helyett. További információ: USE HINT lekérdezési tippek és A CEverziói.

• Többutas táblaértékű függvény (MSTVF) rögzített kardinalitás-becslés 100 sor vs. 1 sor: Ha a TVF-ek esetében a 100 soros alapértelmezett rögzített becslés nem eredményez hatékonyabb tervet, mint az 1 soros rögzített becslés (ami megfelel az SQL Server 2008 R2 (10.50.x) és korábbi verziók lekérdezésoptimalizáló CE-modelljének alapértelmezett értékének), akkor a QUERYTRACEON 9488 lekérdezési tippet használjuk a végrehajtási terv létrehozásához. További információ az MSTVF-ekről: Felhasználó által definiált függvények (adatbázismotor) létrehozása.

Végső megoldásként, ha a szűk hatókörű tippek nem adnak elég jó eredményt a jogosult lekérdezési mintákhoz, akkor a CE 70 teljes használatát is figyelembe veszi a lekérdezési tipp USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') használatával végrehajtási terv létrehozásához.

Fontos

Minden tipp kényszerít bizonyos viselkedéseket, amelyek a jövőbeli SQL Server-frissítésekben kezelhetők. Azt javasoljuk, hogy csak akkor alkalmazzon tippeket, ha nincs más lehetőség, és tervezze meg, hogy minden új frissítéssel újra megismétlje a javasolt kódot. A viselkedés kényszerítésével előfordulhat, hogy a számítási feladat nem élvezi az SQL Server újabb verzióiban bevezetett fejlesztések előnyeit.

Lekérdezéshangolási segéd indítása adatbázis-frissítésekhez

A QTA egy munkamenet-alapú szolgáltatás, amely a munkamenet-állapotot annak a felhasználói adatbázisnak a msqta sémájában tárolja, ahol első alkalommal jön létre munkamenet. Egyszerre több hangolási munkamenet is létrehozható egyetlen adatbázisban, de egy adott adatbázishoz csak egy aktív munkamenet létezhet.

Adatbázis-frissítési munkamenet létrehozása

1. Az SQL Server Management Studióban nyissa meg az Object Explorert, és csatlakozzon az adatbázismotorhoz.

2. Az adatbázis kompatibilitási szintjének frissítésére szánt adatbázis esetében kattintson a jobb gombbal az adatbázis nevére, válassza a Feladatoklehetőséget, válassza Adatbázis-frissítésilehetőséget, majd válassza Új adatbázis-frissítési munkamenetlehetőséget.

3. A QTA varázsló ablakában két lépésre van szükség egy munkamenet konfigurálásához:

   1. A beállítási ablakban konfigurálja a Lekérdezéstárat úgy, hogy a számítási feladatok adatainak egy teljes üzleti ciklusának megfelelőt rögzítse az elemzéshez és a finomhangoláshoz.

      • Adja meg a számítási feladatok várható időtartamát napokban (minimum 1 nap). Ez az ajánlott lekérdezéstár-beállítások javaslatára szolgál, amelyek feltételesen lehetővé teszik a teljes alapkonfiguráció gyűjtését. A jó alapkonfiguráció rögzítése azért fontos, hogy az adatbázis kompatibilitási szintjének módosítása után talált regressziós lekérdezések elemezhetők legyenek.

      • A QTA-munkafolyamat befejezése után adja meg a céladatbázis kompatibilitási szintjét, amelynek a felhasználói adatbázisnak meg kell lennie.

      Ha elkészült, válassza a Következőlehetőséget.

      

   2. A Beállítások ablakban két oszlopban látható a lekérdezéstár aktuális állapota a céladatbázisban, valamint az Ajánlott beállítások.

      • A javasolt beállítások alapértelmezés szerint ki vannak jelölve, de ha az Aktuális oszlopon a választógombot választja, elfogadja az aktuális beállításokat, és lehetővé teszi az aktuális lekérdezéstár konfigurációjának finomhangolását is.

      • A javasolt állott lekérdezési küszöbérték napokban számított érték, amely a várható számítási feladat időtartam kétszeresének felel meg. Ennek az az oka, hogy a Lekérdezéstárnak az alapkonfigurációs számítási feladatra és az adatbázis-frissítés utáni számítási feladatra vonatkozó információkat kell tárolnia.

      Ha elkészült, válassza a Következőlehetőséget.

      

      Fontos

      A javasolt maximális méret egy tetszőleges érték, amely alkalmas lehet egy rövid időre beállított számítási feladathoz. Előfordulhat azonban, hogy nem elegendő az alapkonfigurációs és az adatbázis-frissítés utáni számítási feladatokra vonatkozó információk tárolására az intenzív számítási feladatok esetében, nevezetesen akkor, ha számos különböző csomag hozható létre. Ha előre látható, hogy ez a helyzet, adjon meg egy megfelelőbb értéket.

4. A hangolási ablak befejezi a munkamenet-konfigurációt, és a munkamenet megnyitásának és folytatásának következő lépéseire utasítja. Ha végzett, válassza Befejezéslehetőséget.

   

Az adatbázis-frissítési munkafolyamat végrehajtása

1. Az adatbázis kompatibilitási szintjének frissítésére szánt adatbázis esetében kattintson a jobb gombbal az adatbázis nevére, válassza a Feladatoklehetőséget, válassza Adatbázis-frissítési, majd válassza Munkamenetek figyeléselehetőséget.

2. A munkamenet-kezelési lap a hatókörben lévő adatbázis aktuális és korábbi munkameneteit sorolja fel. Válassza ki a kívánt munkamenetet, és kattintson a Részletekgombra.

   Jegyzet

   Ha az aktuális munkamenet nem jelenik meg, válassza a Frissítés gombot.

   A lista a következő információkat tartalmazza:

   • munkamenet-azonosító

   • munkamenet neve: A rendszer által létrehozott név, amely az adatbázis nevéből, dátumából és a munkamenet létrehozásának időpontjából áll.

   • Állapot: A munkamenet állapota (aktív vagy lezárt).

   • Leírás: A rendszer által létrehozott egység tartalmazza a felhasználó által kiválasztott céladatbázis kompatibilitási szintjét és az üzleti ciklus feldolgozásához szükséges napok számát.

   • kezdési idő: A munkamenet létrehozásának dátuma és időpontja.

   

   Jegyzet

   Munkamenet törlése törli a kijelölt munkamenethez tárolt adatokat. A lezárt munkamenetek törlése azonban nem törölni a korábban üzembe helyezett terv segédvonalakat. Ha töröl egy olyan munkamenetet, amely üzembe helyezett terv-útmutatókat, akkor nem használhatja a QTA-t a visszaállításra. Ehelyett a sys.plan_guides rendszertáblában keressen terv útmutatókat, és manuálisan törölje őket a sp_control_plan_guidehasználatával.

3. Egy új munkamenet belépési pontja az adatgyűjtési lépés.

   Jegyzet

   A Munkamenetek gomb visszakerül a munkamenet-kezelési lapra, így az aktív munkamenet as-ismarad.

   Ez a lépés három részhalmazból áll:

   1. Alapadatgyűjtési kéri a felhasználót, hogy futtassa a reprezentatív munkaterhelés ciklusát, hogy a Lekérdezéstár összegyűjthesse a kiindulási alapot. A számítási feladat befejeződése után ellenőrizze a Kész a számítási feladat futtatásával lehetőséget, és válassza a Tovább gombot.

      Jegyzet

      A QTA-ablak bezárható a számítási feladat futtatása közben. Az aktív állapotú munkamenetre való visszatérés egy későbbi időpontban ugyanabból a lépésből folytatódik, ahol abbahagyták.

      

   2. Az Adatbázis frissítése engedélyt kér az adatbázis kompatibilitási szintjének a kívánt célra való frissítéséhez. A következő részlépéshez válassza az Igenlehetőséget.

      

      Az alábbi oldal megerősíti, hogy az adatbázis kompatibilitási szintje sikeresen frissítve lett.

      

   3. A megfigyelt adatgyűjtés arra kéri a felhasználót, hogy futtassa újra a reprezentatív számítási feladat ciklusát, hogy a Lekérdezéstár összegyűjthesse az optimalizálási lehetőségek kereséséhez használt összehasonlító alapkonfigurációt. A számítási feladat végrehajtásakor használja a Frissítés gombot a regressziós lekérdezések listájának frissítéséhez, ha vannak ilyenek. Módosítsa a lekérdezéseket érték megjelenítésére a megjelenített lekérdezések számának korlátozásához. A lista sorrendjét a Metrika (Időtartam vagy CpuTime) és összesítés (az átlag az alapértelmezett) befolyásolja. Azt is megadhatja, hogy hány lekérdezés jelenjen meg. A számítási feladat befejeződése után ellenőrizze a Kész a számítási feladat futtatásával lehetőséget, és válassza a Tovább gombot.

      

      A lista a következő információkat tartalmazza:

      • lekérdezésazonosító

      • Lekérdezésszöveg: Transact-SQL utasítás, amely kibontható a ... gombbal.

      • Futtat: Megjeleníti a lekérdezés végrehajtásának számát a teljes számítási feladatgyűjteményhez.

      • Alapmetrika: Az adatbázis-kompatibilitás frissítése előtt az ms-ben kiválasztott metrika (Időtartam vagy CPU-idő).

      • Megfigyelt metrika: Az adatbázis kompatibilitásának frissítése után kiválasztott metrika (Időtartam vagy CpuTime) milliszekundumban.

      • %módosítása: A kijelölt metrika százalékos változása az adatbázis-kompatibilitás frissítése előtti és utáni állapot között. A negatív szám a lekérdezés mért regressziójának összegét jelöli.

      • Hangolható: Igaz vagy Hamis attól függően, hogy a lekérdezés jogosult-e kísérletezésre.

4. Nézetelemzés lehetővé teszi a lekérdezések kísérletezésre való kiválasztását és az optimalizálási lehetőségek keresését. A értéket megjelenítő lekérdezések a kísérletezéshez használható lekérdezések hatókörévé válnak. A kívánt lekérdezések ellenőrzése után válassza a Következő lehetőséget a kísérletezéshez.

   Olyan lekérdezések, amelyekben a Tunable értéke False, nem választhatók ki kísérleti célokra.

   Fontos

   A parancssor azt tanácsolja, hogy ha a QTA a kísérletezési fázisba lép, nem lehet visszatérni a Nézetelemzés lapra. Ha nem választja ki az összes jogosult lekérdezést, mielőtt a kísérletezési fázisra lépne, egy későbbi időpontban létre kell hoznia egy új munkamenetet, és meg kell ismételnie a munkafolyamatot. Ehhez alaphelyzetbe kell állítani az adatbázis kompatibilitási szintjét az előző értékre.

   

5. Megnézhető megállapítások lehetővé teszi a lekérdezések kiválasztását, amelyekre a javasolt optimalizálás útmutatóként alkalmazható.

   A lista a következő információkat tartalmazza:

   • lekérdezésazonosító

   • Lekérdezésszöveg: Transact-SQL utasítás, amely kibontható a ... gombbal.

   • Állapot: Megjeleníti a lekérdezés aktuális kísérletezési állapotát.

   • alapmetrika: A lekérdezés ms-ben kiválasztott metrikája (Időtartam vagy CpuTime) a 2. lépés 3.. lépésében végrehajtott lekérdezéshez, amely az adatbázis kompatibilitásának frissítése után a regressziós lekérdezést jelöli.

   • Megfigyelt metrika: Az ms-ben (Időtartam vagy CpuTime) kiválasztott metrika a lekérdezéshez a kísérletezés után, egy megfelelő javasolt optimalizáció eléréséhez.

   • % Változás: A kiválasztott metrikának a kísérletezés előtti és utáni állapota közötti százalékos változását adja meg, amely a lekérdezés mért javulásának mértékét jelöli a javasolt optimalizálással.

   • lekérdezési beállítás: Hivatkozás a javasolt tippre, amely javítja a lekérdezések végrehajtási metrikáit.

   • Üzembe helyezhető: Igaz vagy Hamis attól függően, hogy a javasolt lekérdezésoptimalizálás üzembe helyezhető-e terv útmutatóként.

   

6. Érvényesítés megjeleníti a munkamenethez korábban kiválasztott lekérdezések üzembe helyezési állapotát. Az ezen az oldalon található lista az előző oldaltól abban különbözik, hogy a Üzembe helyezhető oszlopot a Visszaállíthatóoszlopra cserélték. Ez az oszlop lehet Igaz vagy Hamis attól függően, hogy az üzembe helyezett lekérdezésoptimalizálás visszaállítható-e, és a terv útmutatója eltávolítható-e.

   

   Ha egy későbbi időpontban vissza kell állítani egy javasolt optimalizálást, válassza ki a megfelelő lekérdezést, és válassza a Visszaállítás lehetőséget. A lekérdezésterv útmutatója el lesz távolítva, és a lista frissül a visszaállított lekérdezés eltávolításához. Jegyezze fel az alábbi képen, hogy a 8. lekérdezés el lett távolítva.

   

   Jegyzet

   A lezárt munkamenetek törlése nem törölni a korábban üzembe helyezett terv segédvonalakat. Ha töröl egy olyan munkamenetet, amely üzembe helyezett terv-útmutatókat, akkor nem használhatja a QTA-t a visszaállításra. Ehelyett a sys.plan_guides rendszertáblában keressen terv útmutatókat, és manuálisan törölje őket a sp_control_plan_guidehasználatával.

Engedélyek

A db_owner szerepkör tagsága szükséges.

Kapcsolódó tartalom

• kompatibilitási szintek és adatbázismotor-frissítések
• Teljesítményfigyelési és hangolási eszközök
• A teljesítmény figyelése a Lekérdezéstár segítségével
• Az adatbázis kompatibilitási szintjének módosítása és a Lekérdezéstár használata
• Nyomkövetési jelzők beállítása a DBCC TRACEON használatával (Transact-SQL)
• USE HINT lekérdezési tippek
• számosság becslése (SQL Server)
• Automatikus hangolás
• Az SQL Server lekérdezéshangolási segédjének használata