Användningsscenarier för Query Store
gäller för:
SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (endast dedikerad SQL-pool)SQL-databas i Microsoft Fabric
Query Store kan användas i många olika scenarier när det är viktigt att spåra och säkerställa förutsägbara arbetsbelastningsprestanda. Här följer några exempel som du kan tänka dig:
Hitta och åtgärda frågor med regressioner för planval
Identifiera och finjustera frågor som förbrukar mest resurser
A/B-testning
Behåll prestandastabilitet under uppgraderingen till nyare SQL Server
Identifiera och förbättra ad hoc-arbetsbelastningar
Mer information om hur du konfigurerar och administrerar med Query Store finns i Övervakningsprestanda med hjälp av Query Store-.
Information om hur du identifierar handlingsbar information och finjusterar prestanda med Query Store finns i Finjustera prestanda med hjälp av Query Store.
Information om hur du kör Query Store i Azure SQL Database finns i Använda Query Store i Azure SQL Database.
Hitta och åtgärda frågor med regressioner för planval
Under den vanliga frågekörningen kan Frågeoptimeraren välja en annan plan eftersom viktiga indata har ändrats: data kardinaliteten har ändrats, index har skapats, ändrats eller tagits bort, statistik har uppdaterats osv. Vanligtvis är den nya planen bättre, eller ungefär samma som den plan som användes tidigare. Det finns dock fall då den nya planen är betydligt värre - den här situationen kallas regression av planvalsändringar. Före Query Store var det ett problem som var svårt att identifiera och åtgärda eftersom SQL Server inte tillhandahåller inbyggt datalager, för användare att titta på för körningsplaner som har använts över tid.
Med Query Store kan du snabbt:
Identifiera alla frågor vars körningsmått har försämrats under den specificerade tidsperioden (senaste timmen, dagen, veckan osv.). Använd regresserade frågor i SQL Server Management Studio för att påskynda analysen.
Bland de regresserade frågorna är det enkelt att hitta de frågor som hade flera planer och som degraderades på grund av det felaktiga planvalet. Använd Planöversikt i Regresserade frågor för att visualisera alla planer för en regresserad fråga och deras frågeprestanda över tid.
Framtvinga den tidigare planen från historien, om den visade sig vara bättre. Använd knappen Force Plan i Regressed Querys för att framtvinga vald plan för frågan.
Detaljerad beskrivning av scenariot finns i Query Store: En flygdatainspelare för din databas blogg.
Identifiera och finjustera frågor som förbrukar mest resurser
Även om din arbetsbelastning kan generera tusentals frågor använder vanligtvis bara en handfull av dem det mesta av systemresurserna och kräver därför din uppmärksamhet. Bland de vanligaste resurskrävande frågorna hittar du vanligtvis frågor som antingen har regresserats eller frågor som kan förbättras med ytterligare justering.
Det enklaste sättet att börja utforska är att öppna de vanligaste resurskrävande frågorna i Management Studio. Användargränssnittet är indelat i tre fönster: Ett histogram som representerar de vanligaste resurskrävande frågorna (vänster), en plansammanfattning för vald fråga (höger) och en visuell frågeplan för vald plan (längst ned). Välj Konfigurera för att styra hur många frågor du vill analysera och tidsintervallet. Dessutom kan du välja mellan olika resursförbrukningsdimensioner (varaktighet, CPU, minne, I/O, antal körningar) och baslinjen (Genomsnitt, Min, Max, Total, Standardavvikelse).
Titta på plansammanfattningen till höger för att analysera utförandehistoriken och få insikt i de olika planerna och deras körningsstatistik. Använd den nedre rutan för att undersöka de olika planerna eller jämföra dem visuellt, renderade sida vid sida (använd knappen Jämför).
När du identifierar en fråga med suboptimal prestanda beror åtgärden på problemets art:
Om frågan kördes med flera planer och den senaste planen är betydligt sämre än tidigare plan kan du använda mekanismen för plantvingande för att säkerställa att SQL Server använder den optimala planen för framtida körningar
Kontrollera om optimeraren föreslår att det saknas index i XML-planen. Om ja skapar du det saknade indexet och använder Query Store för att utvärdera frågeprestanda när indexet har skapats
Kontrollera att statistiken är up-to-date för de underliggande tabeller som används av frågan.
Kontrollera att index som används av frågan är defragmenterade.
Överväg att skriva om dyr fråga. Du kan till exempel dra nytta av frågeparameterisering och minska användningen av dynamisk SQL. Implementera optimal logik när du läser data (tillämpa datafiltrering på databassidan, inte på programsidan).
A/B-testning
Använd Query Store för att jämföra arbetsbelastningens prestanda före och efter att programmet har ändrats.
Följande lista innehåller flera exempel där du kan använda Query Store för att utvärdera effekten av miljön eller programändringen av arbetsbelastningens prestanda:
Lansera ny programversion.
Lägga till ny maskinvara på servern.
Skapa saknade index i tabeller som refereras av krävande frågor.
Tillämpa filtreringsprincip för säkerhet på radnivå. Mer information finns i Optimera säkerhet på radnivå med Query Store.
Lägga till temporal systemversionering i tabeller som ofta ändras av dina OLTP-program.
I något av dessa scenarier tillämpar du följande arbetsflöde:
Kör din arbetsbelastning med Query Store före den planerade ändringen för att generera prestandabaslinje.
Tillämpa programändring vid kontrollerad tidpunkt.
Fortsätt att köra arbetsbelastningen tillräckligt länge för att generera en prestandabild av systemet efter ändringen
Jämför resultat från #1 och #3.
Öppna övergripande databasförbrukning för att fastställa påverkan på hela databasen.
Öppna mest resurskrävande sökfrågor (eller kör din egen analys med hjälp av Transact-SQL) för att analysera hur ändringen påverkar de viktigaste sökfrågorna.
Du ska bestämma om du vill behålla ändringen eller utföra en återställning om den nya prestandan är oacceptabel.
Följande bild visar Query Store-analys (steg 4) om indexet inte skapas. Öppna Mest resurskrävande frågor/Plansammanfattningspanel för att visa den här vyn för frågan som påverkas av indexskapandet.
Dessutom kan du jämföra planer före och efter skapandet av index genom att återge dem sida vid sida. ("Jämför planerna för den valda frågan i ett separat fönster" verktygsfältsalternativ, som är markerat med röd fyrkant i verktygsfältet.)
Planen innan indexet skapas (plan_id = 1 ovan) saknar indextips och du kan kontrollera att klustrad indexgenomsökning var den dyraste operatorn i frågan (röd rektangel).
Plan efter att saknade index skapas (plan_id = 15, nedan) har nu Index Seek (Nonclustered) som minskar den totala kostnaden för frågan och förbättrar dess prestanda (grön rektangel).
Baserat på analys skulle du förmodligen behålla indexet eftersom frågeprestandan har förbättrats.
Behåll prestandastabilitet under uppgraderingen till nyare SQL Server
Före SQL Server 2014 (12.x) utsattes användarna för risken för prestandaregression under uppgraderingen till den senaste plattformsversionen. Anledningen till detta var att den senaste versionen av Query Optimizer blev aktiv omedelbart när nya bitar har installerats.
Från och med SQL Server 2014 (12.x) är alla Query Optimizer-ändringar kopplade till den senaste databaskompatibilitetsnivån, så planer ändras inte direkt vid uppgraderingen, utan snarare när en användare ändrar COMPATIBILITY_LEVEL till den senaste. Den här funktionen i kombination med Query Store ger dig en bra kontroll över frågeprestanda i uppgraderingsprocessen. Rekommenderat uppgraderingsarbetsflöde visas i följande bild:
Uppgradera SQL Server utan att ändra databasens kompatibilitetsnivå. Den exponerar inte de senaste query optimizer-ändringarna, men innehåller fortfarande nyare SQL Server-funktioner, inklusive Query Store.
Aktivera Query Store. Mer information finns i Håll Query Store justerat efter din arbetsbelastning.
Tillåt Query Store att dokumentera frågor och planer och etablera en prestandabaslinje baserad på källans/tidigare databasens kompatibilitetsnivå. Stanna kvar i det här steget tillräckligt länge för att samla in alla planer och få en stabil baslinje. Detta kan vara varaktigheten för en vanlig konjunkturcykel för en produktionsarbetsbelastning.
Flytta till den senaste databaskompatibilitetsnivån: få din arbetsbelastning exponerad för den senaste frågeoptimeraren för att eventuellt skapa nya planer.
Använd Query Store för analys- och regressionskorrigeringar: vanligtvis bör de nya förbättringarna av Frågeoptimeraren ge bättre planer. Query Store är dock ett enkelt sätt att identifiera regressioner för planval och åtgärda dem med hjälp av en mekanism för plantvingande. Från och med SQL Server 2017 (14.x) blir det här steget automatiskt när du använder funktionen automatisk plankorrigering.
a. I fall där det finns regressioner, använd den tidigare kända bästa planen i Query Store.
b. Om det finns frågeplaner som inte kan framtvingas, eller om prestandan fortfarande är otillräcklig, kan du överväga att återställa databaskompatibilitetsnivå till den tidigare inställningen och sedan kontakta Microsofts kundsupport.
Tips
Använd SQL Server Management Studio-uppgiften Uppgradera databas för att uppgradera databaskompatibilitetsnivån av databasen. Mer information finns i Uppgradera databaser med hjälp av Frågejusteringsassistenten.
Identifiera och förbättra ad hoc-arbetsbelastningar
Vissa arbetsbelastningar har inte dominerande frågor som du kan justera för att förbättra programmets övergripande prestanda. Dessa arbetsbelastningar kännetecknas vanligtvis av relativt många olika frågor som var och en förbrukar en del av systemresurserna. Eftersom de är unika, körs dessa frågor mycket sällan (vanligtvis bara en gång, därav namnet ad hoc), så deras resursförbrukning under körning är inte kritisk. Å andra sidan, med tanke på att programmet genererar nya nettofrågor hela tiden, spenderas en betydande del av systemresurserna på frågekompilering, vilket inte är optimalt. Detta är inte en idealisk situation för Query Store eftersom ett stort antal frågor och planer översvämmar det utrymme som du har reserverat, vilket innebär att Query Store sannolikt kommer att hamna i skrivskyddat läge väldigt snabbt. Om du har aktiverat storleksbaserad rensningsprincip (rekommenderas starkt för att hålla Query Store alltid igång) rensar bakgrundsprocessen Query Store-strukturerna för det mesta och tar betydande systemresurser.
Vyn mest resurskrävande frågor ger dig en första indikation på arbetsbelastningens ad hoc-karaktär:
Använd mått på körningsfrekvens för att analysera om dina viktigaste sökfrågor är ad hoc (detta kräver att du kör Query Store med QUERY_CAPTURE_MODE = ALL). I diagrammet ovan ser du att 90% av dina mest resurskrävande frågor körs bara en gång.
Du kan också köra Transact-SQL skript för att få totalt antal frågetexter, frågor och planer i systemet och avgöra hur olika de är genom att jämföra query_hash och query_plan_hash:
--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;
Det här är ett potentiellt resultat som du kan få vid arbetsbelastning med ad hoc-frågor:
Frågeresultatet visar att även om det finns ett stort antal frågor och planer i Query Store, är deras query_hash och query_plan_hash faktiskt inte olika. Ett förhållande mellan unika frågetexter och unika frågehashvärden, som är mycket större än 1, är en indikation på att arbetsbelastningen är en bra kandidat för parameterisering, eftersom den enda skillnaden mellan frågorna är literalkonstanten (parametern) som tillhandahålls som en del av frågetexten.
Den här situationen inträffar vanligtvis om ditt program genererar frågor (i stället för att anropa lagrade procedurer eller parametriserade frågor) eller om det förlitar sig på ramverk för objektrelationsmappning som genererar frågor som standard.
Om du har kontroll över programkoden kan du överväga att skriva om dataåtkomstlagret för att använda lagrade procedurer eller parametriserade frågor. Den här situationen kan dock också förbättras avsevärt utan programändringar genom att tvinga fram frågeparameterisering för hela databasen (alla frågor) eller för de enskilda frågemallarna med samma query_hash.
Metoden med enskilda frågemallar kräver att planguiden skapas:
--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)';
Lösningen med planguider är mer exakt, men det krävs mer arbete.
Om alla dina frågor (eller de flesta) är kandidater för automatisk parameterisering kan du överväga att konfigurera PARAMETERIZATION = FORCED för hela databasen. Mer information finns i Riktlinjer för användning av tvingad parameterisering.
--Apply forced parameterization for entire database
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;
När du har tillämpat något av de här stegen de vanligaste resurskrävande frågorna visar en annan bild av din arbetsbelastning.
I vissa fall kan ditt program generera många olika frågor som inte är bra kandidater för automatisk parameterisering. I så fall ser du ett stort antal frågor i systemet, men förhållandet mellan unika frågor och unika query_hash är sannolikt nära 1.
I så fall kanske du vill aktivera alternativet Optimera för ad hoc-arbetsbelastningar server för att förhindra att cacheminne slösas bort på frågor som sannolikt inte kommer att köras igen. För att förhindra registrering av dessa frågor i Query Store ställer du in QUERY_CAPTURE_MODE till AUTO.
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);