Ajánlott eljárások a lekérdezéstár kezeléséhez

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Felügyelt Példány

Ez a cikk az SQL Server Lekérdezéstár kezelését és a környező funkciókat ismerteti.

• 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.

Jegyzet

Az SQL Server 2022 -ben (16.x) a lekérdezéstár alapértelmezés szerint engedélyezve van az összes újonnan létrehozott SQL Server-adatbázishoz, hogy jobban nyomon lehessen követni a teljesítményelőzményeket, elhárítsa a lekérdezési tervvel kapcsolatos problémákat, és új lekérdezésfeldolgozó képességeket engedélyezhessen.

A Lekérdezéstár alapértelmezései az Azure SQL Database-ben

Ez a szakasz az Azure SQL Database optimális konfigurációs alapértelmezett beállításait ismerteti, amelyek a lekérdezéstár és a függő funkciók megbízható működésének biztosítására lettek kialakítva. Az alapértelmezett konfiguráció folyamatos adatgyűjtésre van optimalizálva, azaz az OFF/READ_ONLY állapotokban töltött minimális idő. További információ az összes elérhető lekérdezéstár-beállításról: ALTER DATABASE SET beállítások (Transact-SQL).

Konfiguráció	Leírás	Alapértelmezett	Megjegyzés
MAX_STORAGE_SIZE_MB	Megadja a lekérdezéstár által az ügyféladatbázison belül igénybe vehető adattér korlátját	100 az SQL Server 2019 előtt (15.x) 1000 az SQL Server 2019-től kezdve (15.x)	Új adatbázisokra érvényesítve
INTERVALLUM_HOSSZA_PERCEKBEN	Meghatározza annak az időablaknak a méretét, amely során a lekérdezéstervek összegyűjtött futtatókörnyezeti statisztikái összesítve és megőrizve lesznek. Minden aktív lekérdezési terv legfeljebb egy sorral rendelkezik az ezzel a konfigurációval meghatározott ideig	60	Új adatbázisokra érvényesítve
STALE_QUERY_THRESHOLD_DAYS	A fenntartott futtatókörnyezeti statisztikák és inaktív lekérdezések megőrzési idejét szabályozó időalapú törlési szabályzat	30	Új adatbázisoknál és az előző alapértelmezéssel rendelkező adatbázisoknál érvényesítve (367)
Méret-alapú tisztítási mód	Megadja, hogy az automatikus adattisztítás akkor történjen-e, amikor a Lekérdezéstár adatmérete megközelíti a korlátot	AUTÓ	Minden adatbázisra kényszerítve
LEKÉRÉSI_RÖGZÍTÉSI_MÓD	Meghatározza, hogy az összes lekérdezés vagy csak a lekérdezések egy részhalmaza nyomon legyen-e követve	AUTÓ	Minden adatbázisra kényszerítve
ADAT_FLUSH_INTERVALLUM_MÁSODPERCBEN	Meghatározza a rögzített futásidejű statisztikák memóriában való tartásának maximális időtartamát a lemezre való kiürítés előtt	900	Új adatbázisok esetén kötelező

Fontos

Ezeket az alapértelmezett értékeket a rendszer automatikusan alkalmazza a Lekérdezéstár aktiválásának utolsó szakaszában egy Azure SQL Database-ben. A beállítás engedélyezése után az Azure SQL Database nem módosítja az ügyfelek által beállított konfigurációs értékeket, kivéve, ha negatívan befolyásolják a lekérdezéstár elsődleges számítási feladatait vagy megbízható műveleteit.

Jegyzet

A Lekérdezéstár nem tiltható le az Azure SQL Database-ben és az Elasztikus Készletben. A ALTER DATABASE [database] SET QUERY_STORE = OFF végrehajtása visszaadja a figyelmeztetést 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Ha meg szeretné tartani az egyéni beállításokat, használja ALTER DATABASE lekérdezéstár-beállításokkal a konfiguráció előző állapotba való visszaállításához. Tekintse meg a lekérdezéstár ajánlott eljárásait, hogy megtudja, hogyan választhatja ki az optimális konfigurációs paramétereket.

A lekérdezéstár optimális rögzítési módjának beállítása

Tartsa meg a legfontosabb adatokat a Lekérdezéstárban. Az alábbi táblázat az egyes lekérdezéstár-rögzítési módok tipikus forgatókönyveit ismerteti:

Lekérdezéstár rögzítési módja	Forgatókönyv
Minden	Alaposan elemezze a számítási feladatot a lekérdezések alakzatai, azok végrehajtási gyakorisága és egyéb statisztikák szempontjából. Új lekérdezések azonosítása a számítási feladatban. Észleli, hogy a felhasználó vagy az automatikus paraméterezés lehetőségeinek azonosítására alkalmi lekérdezéseket használnak-e. Megjegyzés: Ez az sql server 2016 (13.x) és az SQL Server 2017 (14.x) alapértelmezett rögzítési módja.
automatikus	Összpontosítsa a figyelmet a releváns és végrehajtható lekérdezésekre. Ilyenek például azok a lekérdezések, amelyek rendszeresen futnak, vagy amelyek jelentős erőforrás-felhasználást igényelnek. Megjegyzés: Az SQL Server 2019 (15.x) és újabb verzióiban ez az alapértelmezett rögzítési mód.
Nincs	Már rögzítette a futtatókörnyezetben figyelni kívánt lekérdezéskészletet, és meg szeretné szüntetni a más lekérdezések által esetlegesen előforduló zavaró tényezőket. Egyik sem alkalmas tesztelésre és teljesítményértékelésre. Egyik sem megfelelő azoknak a szoftvergyártóknak, akik a lekérdezéstár konfigurációját az alkalmazás számítási feladatainak monitorozására konfigurálva küldik. Egyiket sem érdemes körültekintően használni, mert előfordulhat, hogy elmulasztja a fontos új lekérdezések nyomon követésének és optimalizálásának lehetőségét. Kerülje a Nincs használatát, kivéve, ha van egy konkrét forgatókönyve, amelyhez szükség van rá.
egyéni	Az SQL Server 2019 (15.x) egyéni rögzítési módot vezetett be a ALTER DATABASE ... SET QUERY_STORE parancs alatt. Bár az Automatikus beállítás alapértelmezett és ajánlott, ha továbbra is fennáll a probléma a lekérdezéstár terhelésével kapcsolatban, az adatbázisgazdák egyéni rögzítési szabályzatokkal tovább hangolhatják a Lekérdezéstár rögzítési viselkedését. További információkért és javaslatokért tekintse meg jelen cikk egyéni rögzítési szabályzatait. Erről a szintaxisról további információt ALTER DATABASE SET Optionscímű témakörben talál.

Jegyzet

A kurzorok, a tárolt eljárásokban található lekérdezések és a nativ módon fordított lekérdezések mindig rögzítésre kerülnek, ha a Lekérdezéstár rögzítési módja minden, automatikusvagy egyedi. A nativ módon lefordított lekérdezések rögzítéséhez engedélyezze a lekérdezésenkénti statisztikák gyűjtését a sys.sp_xtp_control_query_exec_statshasználatával.

A legrelevánsabb adatok megőrzése a Lekérdezéstárban

Konfigurálja úgy a Lekérdezéstárat, hogy csak a releváns adatokat tartalmazza, hogy azok folyamatosan fussanak, és nagyszerű hibaelhárítási élményt nyújt, és minimális hatással legyen a normál számítási feladatra.

Az alábbi táblázat az ajánlott eljárásokat ismerteti:

Ajánlott eljárás	Beállítás
A megtartott előzményadatok korlátozása.	Konfigurálja az időalapú szabályzatot az autocleanup aktiválásához.
Szűrje ki a nemlevens lekérdezéseket.	Állítsa be a Lekérdezéstár rögzítési módjátAutomatikus-ra.
A maximális méret elérésekor törölje a kevésbé releváns lekérdezéseket.	Méretalapú törlési szabályzat aktiválása.

Egyéni rögzítési szabályzatok

Ha engedélyezve van az EGYÉNI lekérdezéstár rögzítési módja, további lekérdezéstár-konfigurációk érhetők el egy új lekérdezéstár-rögzítési házirend-beállításban az adott kiszolgálón végzett adatgyűjtés finomhangolásához.

Az új egyéni beállítások határozzák meg, hogy mi történik a belső rögzítési szabályzat időküszöbe alatt. Ez egy időhatár, amely során a konfigurálható feltételek kiértékelése történik, és ha vannak ilyenek, a lekérdezést a Lekérdezéstár rögzítheti.

A Lekérdezéstár rögzítési módja a lekérdezéstár lekérdezésrögzítési szabályzatát határozza meg.

• Minden: Az összes lekérdezést rögzíti. Ez a beállítás az SQL Server 2016 (13.x) és az SQL Server 2017 (14.x) alapértelmezett beállítása.
• Automatikus: A rendszer figyelmen kívül hagyja a ritkán végzett lekérdezéseket és a jelentéktelen fordítási és végrehajtási időtartamú lekérdezéseket. A végrehajtások számának, fordításának és futásidejű időtartamának küszöbértékei belsőleg vannak meghatározva. Az SQL Server 2019 -től kezdve (15.x) ez az alapértelmezett beállítás.
• Nincs: A Lekérdezéstár leállítja az új lekérdezések rögzítését.
• Egyéni: Lehetővé teszi a további vezérlést és az adatgyűjtési szabályzat finomhangolását. Az új egyéni beállítások határozzák meg, hogy mi történik a belső rögzítési szabályzat időküszöbe alatt. Ez egy időhatár, amely során a konfigurálható feltételek kiértékelése történik, és ha vannak ilyenek, a lekérdezést a Lekérdezéstár rögzítheti.

A környezet megfelelő egyéni rögzítési szabályzatának finomhangolását a következő esetekben érdemes figyelembe venni:

• Az adatbázis nagyon nagy.
• Az adatbázis számos egyedi, alkalmi lekérdezéssel rendelkezik.
• Az adatbázis méret- vagy növekedési korlátozásokkal rendelkezik.

SSMS

Az SQL Server Management Studio (SSMS) legújabb verziójának letöltése

Az aktuális beállítások megtekintése a Management Studióban:

1. Az SQL Server Management Studio Object Explorerben kattintson a jobb gombbal az adatbázisra.
2. Válassza a Tulajdonságokelemet.
3. Válassza Lekérdezéstárlehetőséget. A Lekérdezéstár lapon ellenőrizze, hogy a Üzemmód (kért)Írás és olvasás.
4. Lekérdezéstár rögzítési módjának megváltoztatása módra egyéni.
5. Vegye figyelembe, hogy a Lekérdezéstár rögzítési szabályzatának négy rögzítési szabályzatmezője mostantól engedélyezve és konfigurálható.

T-SQL

Használja az ALTER DATABASE parancsot... Állítsd be a szintaxist QUERY_CAPTURE_MODE = CUSTOM-ra, majd add meg a QUERY_CAPTURE_POLICYopcióit. Az egyéni rögzítési házirend lehetőségeiről bővebben az alábbi T-SQL példák és az ALTER DATABASE SET opciókkal (Transact-SQL) olvashat.

Példa egyedi rögzítési irányelvek

Az alábbi példa automatikusra állítja QUERY_CAPTURE_MODE, és egyéni rögzítési módot állít be. Az alábbiak mindegyike az egyéni rögzítési szabályzatokat az SQL Server 2022 alapértelmezett értékére állítja (16.x). Érdemes módosítani ezeket az értékeket a rögzített lekérdezések számának csökkentése érdekében, és ezáltal csökkenteni a lekérdezéstár lemezen elfoglalt helyigényét. Javasoljuk, hogy ezeket az értékeket fokozatosan, kis lépésekben módosítsa.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Az alábbi minta lekérdezés egy meglévő lekérdezéstárat módosít egy egyéni rögzítési szabályzat használatára, amely felülírja EXECUTION_COUNT és TOTAL_COMPILE_CPU_TIME_MSalapértelmezett beállításait.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Lekérdezéstár maximális mérete

A lekérdezéstár alapértelmezett maximális mérete 1000 MB, az SQL Server 2019-től kezdve (15.x). A korábbi verziókban az alapértelmezett érték 100 MB volt. A lekérdezéstár maximális méretkorlátjának növelése megfelelő a sok egyedi lekérdezési csomaggal rendelkező foglalt adatbázisban. A rögzítési szabályzat módosítása (lásd az előző szakaszt) sokkal fontosabb szempont a lekérdezéstár lemezméretének korlátozásához és a lekérdezéstár READ_ONLY módba való belépésének megakadályozásához. Bár a Lekérdezéstár lekérdezéseket, végrehajtási terveket és statisztikákat gyűjt, az adatbázis mérete mindaddig nő, amíg el nem éri ezt a korlátot. Ha ez történik, a Lekérdezéstár automatikusan READ_ONLY üzemmódra módosítja a műveleti módot, és leállítja az új adatok gyűjtését, ami azt jelenti, hogy a teljesítményelemzés már nem pontos.

• Az SQL Serverben és a felügyelt Azure SQL-példányban a MAX_STORAGE_SIZE_MB korlát nincs szigorúan kényszerítve.
• Az Azure SQL Database-ben az engedélyezett maximális MAX_STORAGE_SIZE_MB érték 10 240 MB.

A tárterület méretét csak akkor ellenőrzi a rendszer, ha a Lekérdezéstár adatokat ír lemezre. Ezt az időközt a DATA_FLUSH_INTERVAL_SECONDS beállítás vagy a Management Studio Lekérdezéstár párbeszédpanel adatöblítési időközállítja be.

• Az intervallum alapértelmezett értéke 900 másodperc (vagy 15 perc).
• Ha a lekérdezéstár túllépte a tárméret-ellenőrzések közötti MAX_STORAGE_SIZE_MB korlátot, akkor írásvédett üzemmódra vált.
• Ha SIZE_BASED_CLEANUP_MODE engedélyezve van, a MAX_STORAGE_SIZE_MB korlátot kényszerítő törlési mechanizmus is aktiválódik.
  • Ha már nincs elég hely, a Lekérdezéstár mód automatikusan visszaáll READ_WRITE módra.

További részletekért lásd: ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Adatöblítési időköz (perc)

Az adatöblítési időköz határozza meg az összegyűjtött futásidejű statisztikák lemezen való megőrzésének gyakoriságát. Az SQL Server Management Studióban az érték percekben van kifejezve, de Transact-SQL másodpercben van kifejezve. Az alapértelmezett érték 15 perc (900 másodperc).

• Az adatöblítési időköz növelése csökkentheti a lekérdezéstár teljes I/O-hatását, de a tárolási I/O-számítási feladat nagyobb tüskés, kevesebb, de nagyobb hatással van a lemezkihasználtságra. Érdemes lehet magasabb értéket használni, ha a számítási feladat nem hoz létre nagy számú különböző lekérdezést és csomagot, vagy ha hosszabb ideig bírja az adatok megőrzését az adatbázis leállítása előtt.
• Az adatöblítési időköz csökkentése csökkenti a lekérdezéstár azon adatainak mennyiségét, amelyek leállítás, áramkimaradás vagy feladatátvétel esetén elvesznének. A lekérdezéstár tárolási I/O-hatását is kisimíthatja, ha gyakrabban ír lemezre, de kevesebb adattal.

Jegyzet

A 7745-ös nyomkövetési jelző használatával a lekérdezéstár adatai nem írhatók lemezre feladatátvételi vagy leállítási parancs esetén. További információ: Lekérdezéstár használata kritikus fontosságú kiszolgálókon.

A Lekérdezéstár alapértelmezéseinek módosítása

Konfigurálja a Lekérdezéstárat a számítási feladat és a teljesítmény hibaelhárítási követelményei alapján. Az alapértelmezett paraméterek elég jók a kezdéshez, de figyelnie kell, hogy a Lekérdezéstár hogyan viselkedik az idő függvényében, és ennek megfelelően állítsa be a konfigurációját.

A Lekérdezéstár aktuális beállításainak megtekintése

Tekintse meg a lekérdezéstár aktuális beállításait az SQL Server Management Studióban (SSMS) vagy a T-SQL-ben.

SSMS-

Az SQL Server Management Studio (SSMS) legújabb verziójának letöltése

Az aktuális beállítások megtekintése a Management Studióban:

1. Az SQL Server Management Studio Object Explorerben kattintson a jobb gombbal az adatbázisra.
2. Válassza a Tulajdonságoklehetőséget.
3. Válassza Lekérdezéstár.

T-SQL

Hajtsa végre a következő T-SQL-szkriptet a lekérdezéstár méretével kapcsolatos legfrissebb információk lekéréséhez:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

A következő szkript új értéket állít be maximális méret (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Használja az SQL Server Management Studio-t vagy a Transact-SQL-t, hogy beállítson egy különböző értéket a adatöblítési időköz-hez.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Statisztikai adatgyűjtési időköz: Meghatározza az összegyűjtött futásidejű statisztikák részletességi szintjét, percekben kifejezve. Az alapértelmezett érték 60 perc. Érdemes lehet alacsonyabb értéket használni, ha finomabb részletességre vagy kevesebb időre van szüksége a problémák észleléséhez és megoldásához. Ne feledje, hogy az érték közvetlenül befolyásolja a lekérdezéstár adatainak méretét. Az SQL Server Management Studio vagy a Transact-SQL használatával állítson be egy másik értéket Statisztikai adatgyűjtési időköz:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Megszámlált lekérdezési küszöbérték (napok): Idő alapú tisztítási szabályzat, amely a fenntartott futásidejű teljesítménystatisztikák és inaktív lekérdezések megőrzési időtartamot szabályozza, napokban kifejezve. Alapértelmezés szerint a Lekérdezéstár úgy van konfigurálva, hogy 30 napig tárolja az adatokat, ami szükségtelenül hosszú lehet a forgatókönyvhöz.

Kerülje a nem tervezett előzményadatok megőrzését. Ez a gyakorlat csökkenti a módosításokat írásvédett állapotra. A lekérdezéstár adatainak mérete és a probléma észlelésének és elhárításának ideje kiszámíthatóbb lesz. Az időalapú törlési szabályzat konfigurálásához használja a Management Studiót vagy a következő szkriptet:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Méretalapú törlési mód: Azt határozza meg, hogy az automatikus adattisztítás akkor történjen-e, amikor a Lekérdezéstár adatmérete megközelíti a korlátot. A méretalapú törlés aktiválásával győződjön meg arról, hogy a Lekérdezéstár mindig olvasási-írási módban fut, és összegyűjti a legújabb adatokat. A nagy számítási feladatok esetén nincs garancia arra, hogy a Lekérdezéstár karbantartása folyamatosan fenntartja az adatméretet a korlát alatt. Előfordulhat, hogy az automatikus adattisztítás lemarad, és (ideiglenesen) csak olvasható módba vált.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Lekérdezéstár rögzítési módja: A lekérdezéstár lekérdezésrögzítési szabályzatát adja meg.

• Minden: Az összes lekérdezést rögzíti. Ez a beállítás az SQL Server 2016 (13.x) és az SQL Server 2017 (14.x) alapértelmezett beállítása.
• Automatikus: A rendszer figyelmen kívül hagyja a ritkán végzett lekérdezéseket és a jelentéktelen fordítási és végrehajtási időtartamú lekérdezéseket. A végrehajtások számának, fordításának és futásidejű időtartamának küszöbértékei belsőleg vannak meghatározva. Az SQL Server 2019 -től kezdve (15.x) ez az alapértelmezett beállítás.
• Nincs: A Lekérdezéstár leállítja az új lekérdezések rögzítését.
• egyéni: Lehetővé teszi a további vezérlést és az adatgyűjtési szabályzat finomhangolását. Az új egyéni beállítások határozzák meg, hogy mi történik a belső rögzítési szabályzat időküszöbe alatt. Ez egy időhatár, amely során a konfigurálható feltételek kiértékelése történik, és ha vannak ilyenek, a lekérdezést a Lekérdezéstár rögzítheti.

Fontos

A kurzorok, a tárolt eljárásokon belüli lekérdezések és a natívan lefordított lekérdezések mindig akkor lesznek rögzítve, ha a Lekérdezéstár rögzítési módja az összes , automatikusvagy egyénivan beállítva. A natívan lefordított lekérdezések rögzítéséhez engedélyezze a lekérdezésenkénti statisztikák gyűjtését a sys.sp_xtp_control_query_exec_statshasználatával.

A következő szkript a QUERY_CAPTURE_MODE értékét AUTO-ra állítja.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Példák

Az alábbi példa automatikusra állítja QUERY_CAPTURE_MODE, és beállítja az SQL Server 2016 egyéb ajánlott beállításait (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

Az alábbi példa automatikusra állítja QUERY_CAPTURE_MODE és az SQL Server 2017 egyéb ajánlott beállításait (14.x) a várakozási statisztikák belefoglalásához:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Az alábbi példa az EGYÉNI rögzítési szabályzatot az SQL Server 2019 (15.x) alapértelmezett értékére állítja az új alapértelmezett automatikus rögzítési mód helyett. Az egyéni rögzítési szabályzat beállításairól és alapértelmezett beállításairól további információt a <query_capture_policy_option_list>című témakörben talál.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Lekérdezéstár karbantartása

Ez a szakasz néhány útmutatást tartalmaz a Lekérdezéstár funkció kezelésével kapcsolatban.

Lekérdezéstár állapota

A Lekérdezéstár a felhasználói adatbázisban tárolja az adatait, ezért van méretkorlátja (MAX_STORAGE_SIZE_MBkonfigurálva). Ha a Lekérdezéstárban lévő adatok elérik a korlátot, a Lekérdezéstár automatikusan írás-olvasásról csak olvasásra vált, és leállítja az új adatok gyűjtését.

Kérdezze le a sys.database_query_store_options paramétert annak megállapításához, hogy a Lekérdezéstár jelenleg aktív-e, és hogy gyűjt-e futásidejű statisztikákat.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

A lekérdezéstár állapotát a actual_state oszlop határozza meg. Ha eltér a kívánt állapottól, a readonly_reason oszlop további információt adhat. Ha a Lekérdezéstár mérete meghaladja a kvótát, a funkció csak-olvasási módra vált, és indokot biztosít. Az okokról további információt a sys.database_query_store_optionsrészben talál.

Lekérdezéstár beállításainak lekérése

A Lekérdezéstár állapotának részletes információiért hajtsa végre a következő lépéseket egy felhasználói adatbázisban.

SELECT * FROM sys.database_query_store_options;

A lekérdezéstár időközének beállítása

Felülbírálhatja a lekérdezési futásidejű statisztikák összesítésének időközét (az alapértelmezett érték 60 perc). Az intervallum új értéke sys.database_query_store_options nézetben jelenik meg.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

A INTERVAL_LENGTH_MINUTESnem engedélyezettek tetszőleges értékek. Használja az alábbi időközök egyikét: 1, 5, 10, 15, 30, 60 vagy 1440 perc.

Jegyzet

Az Azure Synapse Analytics esetében nem támogatott a Lekérdezéstár konfigurációs beállításainak testreszabása, ahogyan az ebben a szakaszban is látható.

Lekérdezéstár területhasználata

A lekérdezéstár aktuális méretének és korlátjának ellenőrzéséhez hajtsa végre a következő utasítást a felhasználói adatbázisban.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Ha a Lekérdezéstár tárterülete megtelt, használja az alábbi utasítást a tárterület kibővítéséhez.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Lekérdezéstár beállításainak megadása

Egyszerre több lekérdezéstár-beállítást is megadhat egyetlen ALTER DATABASE utasítással.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

A konfigurációs beállítások teljes listájáért lásd ALTER DATABASE SET Options (Transact-SQL).

A tér megtisztítása

A lekérdezéstár belső táblái az ELSŐDLEGES fájlcsoportban jönnek létre az adatbázis létrehozása során, és a konfiguráció később nem módosítható. Ha elfogy a szabad terület, az alábbi utasítással törölheti a régebbi Lekérdezéstár-adatokat.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Azt is megteheti, hogy csak az alkalmi lekérdezési adatokat szeretné törölni, mivel az kevésbé releváns a lekérdezésoptimalizáláshoz és a tervelemzéshez, de ugyanannyi helyet foglal el.

Az Azure Synapse Analyticsben a lekérdezéstár törlése nem érhető el. Az adatok az elmúlt hét napban automatikusan megmaradnak.

Alkalmi lekérdezések törlése

Ez törli az alkalmi és belső lekérdezéseket a Lekérdezéstárból, hogy a lekérdezéstár ne fogyjon el a szabad területből, és távolítsa el azokat a lekérdezéseket, amelyeket valóban nyomon kell követnünk.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

A már nem kívánt adatok törléséhez különböző logikával határozhatja meg a saját eljárását.

Az előző példa a sp_query_store_remove_query kiterjesztett tárolt eljárást használja a szükségtelen adatok eltávolításához. A következőket is megteheti:

• A sp_query_store_reset_exec_stats használatával törölheti egy adott terv futtatókörnyezeti statisztikáit.
• Egyetlen terv eltávolításához használja a sp_query_store_remove_plan.

Kapcsolódó tartalom

• ALTER DATABASE SET BEÁLLÍTÁSOK (Transact-SQL)
• Lekérdezéstár katalógusnézetei (Transact-SQL)
• Lekérdezéstár tárolt eljárásai (Transact-SQL)
• Használja a Lekérdezéstárat a In-Memory OLTP-vel
• lekérdezésfeldolgozási architektúra útmutatója
• Lekérdezéstár útmutatások
• Teljesítmény figyelése a Lekérdezéstár használatával
• Teljesítmény finomhangolása a Lekérdezéstárral
• Korábbi lekérdezések tárolása és elemzése az Azure Synapse Analytics