Notitie
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen u aan te melden of de directory te wijzigen.
Voor toegang tot deze pagina is autorisatie vereist. U kunt proberen de mappen te wijzigen.
Van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric
De sys.dm_exec_query_plan dynamische beheerfunctie geeft het Showplan terug in XML-formaat voor de batch die door de planhandle is gespecificeerd. Het plan dat door de planhandle wordt gespecificeerd, kan zowel gecached zijn als momenteel uitgevoerd.
Het XML-schema voor het Showplan is gepubliceerd en beschikbaar op deze Microsoft-website. Het is ook beschikbaar in de map waar SQL Server is geïnstalleerd.
Transact-SQL syntaxis-conventies
Syntax
sys.dm_exec_query_plan(plan_handle)
Argumenten
plan_handle
Een token dat een queryuitvoeringsplan op unieke wijze identificeert voor een batch die is uitgevoerd en het bijbehorende plan zich in de plancache bevindt of momenteel wordt uitgevoerd.
plan_handleis varbinair(64).
De kan plan_handle worden verkregen uit de volgende dynamische beheerobjecten:
Tabel geretourneerd
| Kolomnaam | Gegevenstype | Beschrijving |
|---|---|---|
dbid |
smallint | Id van de contextdatabase die van kracht was toen de Transact-SQL instructie die overeenkomt met dit plan werd gecompileerd. Voor ad-hoc en voorbereide SQL-instructies, de id van de database waarin de instructies zijn gecompileerd. Kolom is null-waarde. |
objectid |
int | Id van het object (bijvoorbeeld opgeslagen procedure of door de gebruiker gedefinieerde functie) voor dit queryplan. Voor ad hoc- en voorbereide batches is NULLdeze kolom .Kolom is null-waarde. |
number |
smallint | Genummerde opgeslagen procedure geheel getal. Bijvoorbeeld, een groep procedures voor de orderapplicatie kan , orderproc;2, enzovoort hetenorderproc;1. Voor ad hoc- en voorbereide batches is NULLdeze kolom .Kolom is null-waarde. |
encrypted |
bit | Geeft aan of de bijbehorende opgeslagen procedure is versleuteld. 0 = niet versleuteld 1 = versleuteld De kolom is niet ongeldig te maken. |
query_plan |
xml | Bevat de compile-time Showplan-representatie van het query-uitvoeringsplan die is gespecificeerd met plan_handle. Showplan heeft een XML-indeling. Er wordt één plan gegenereerd voor elke batch die bijvoorbeeld ad hoc Transact-SQL instructies, opgeslagen procedureaanroepen en door de gebruiker gedefinieerde functieaanroepen bevat.Kolom is null-waarde. |
Opmerkingen
Onder de volgende voorwaarden wordt geen Showplan-uitvoer geretourneerd in de query_plan kolom van de geretourneerde tabel voor sys.dm_exec_query_plan:
Als het queryplan dat door gebruik wordt gespecificeerd
plan_handleuit de plancache is verwijderd, isNULLdequery_plankolom van de geretourneerde tabel . Deze situatie kan bijvoorbeeld optreden als er een tijdsvertraging is tussen het moment waarop het planhandvat werd vastgelegd en wanneer het werd gebruikt metsys.dm_exec_query_planSommige Transact-SQL-statements worden niet gecacht, zoals bulk operation statements of statements met stringliterals groter dan 8 KB. XML Showplans voor dergelijke statements kunnen niet worden opgehaald door te gebruiken
sys.dm_exec_query_plan, tenzij de batch momenteel wordt uitgevoerd, omdat ze niet in de cache bestaan.Als een Transact-SQL batch of opgeslagen procedure een aanroep bevat naar een door de gebruiker gedefinieerde functie of een aanroep naar dynamische SQL, bijvoorbeeld met EXEC (string), wordt de gecompileerde XML Showplan voor de door de gebruiker gedefinieerde functie niet opgenomen in de tabel die wordt
sys.dm_exec_query_planteruggegeven voor de batch of opgeslagen procedure. In plaats daarvan moet je een aparte aanroep maken voorsys.dm_exec_query_plande planhandle die overeenkomt met de door de gebruiker gedefinieerde functie.
Wanneer een ad hoc-query eenvoudige of geforceerde parametrisatie gebruikt, bevat de query_plan kolom alleen de statementtekst en niet het daadwerkelijke queryplan. Om het queryplan terug te geven, roep sys.dm_exec_query_plan je de planhandle van de voorbereide geparametriseerde query op. Je kunt bepalen of de query geparametriseerd is door te verwijzen naar de sql kolom van de sys.syscacheobjects-weergave of de tekstkolom van de sys.dm_exec_sql_text dynamische beheerweergave.
Note
Vanwege een beperking in het aantal geneste niveaus dat in het xml-datatype is toegestaan, sys.dm_exec_query_plan kunnen queryplannen niet worden teruggestuurd die voldoen aan of hoger zijn dan 128 niveaus van geneste elementen. In eerdere versies van SQL Server heeft deze voorwaarde voorkomen dat het queryplan retourneert en fout 6335 genereert. In SQL Server 2005 (9.x) Service Pack 2 en latere versies retourneert query_plande NULL kolom .
Je kunt de dynamische beheerfunctie sys.dm_exec_text_query_plan (Transact-SQL) gebruiken om de output van het queryplan in tekstformaat terug te geven.
Permissions
Om uit te voeren sys.dm_exec_query_planmoet een gebruiker lid zijn van de sysadmin-vaste serverrol of de VIEW SERVER STATE toestemming op de server hebben.
Rechten voor SQL Server 2022 en latere versies
Vereist VIEW SERVER PERFORMANCE STATE machtiging op de server.
Examples
De volgende voorbeelden laten zien hoe je de sys.dm_exec_query_plan dynamische beheerweergave kunt gebruiken.
Om de XML Showplans te bekijken, voer je de volgende queries uit in de Power Query-editor van SQL Server Management Studio en selecteer vervolgens ShowPlanXML in de query_plan kolom van de tabel die door sys.dm_exec_query_plan de XML wordt weergegeven door Showplan in het Management Studio samenvattingspaneel. Om de XML Showplan in een bestand op te slaan, klik met de rechtermuisknop op ShowPlanXML in de query_plan kolom, selecteer Resultaten opslaan als, en geef het bestand een naam in het formaat <file name>.sqlplan; bijvoorbeeld, MyXMLShowplan.sqlplan.
A. Haal het gecachte queryplan op voor een langzaam lopende Transact-SQL query of batch
Queryplannen voor verschillende typen Transact-SQL batches, zoals ad hoc batches, opgeslagen procedures en door de gebruiker gedefinieerde functies, worden opgeslagen in een geheugengebied dat de plancache wordt genoemd. Elk gecachte queryplan wordt geïdentificeerd door een unieke identificatie, een zogenaamde planhandvat. Je kunt deze planhandle specificeren met de sys.dm_exec_query_plan dynamische beheerweergave om het uitvoeringsplan voor een bepaald Transact-SQL query of batch op te halen.
Als een Transact-SQL query of batch lange tijd draait op een bepaalde verbinding met SQL Server, haal dan het uitvoeringsplan van die query of batch op om te ontdekken wat de vertraging veroorzaakt. Het volgende voorbeeld laat zien hoe je de XML Showplan kunt ophalen voor een langzaam lopende query of batch.
Note
Om dit voorbeeld uit te voeren, vervang je de waarden voor session_id en plan_handle door waarden die specifiek zijn voor je server.
Haal eerst de sessie-ID (SPID) op voor het proces dat de query of batch uitvoert door gebruik te maken van de sp_who opgeslagen procedure:
exec sp_who;
GO
De resultaatset die wordt teruggegeven door sp_who geeft aan dat de sessie-ID is 54. Je kunt de sessie-ID met de sys.dm_exec_requests dynamische beheerweergave gebruiken om de planhandle op te halen met de volgende query:
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO
De tabel die wordt teruggegeven door sys.dm_exec_requests geeft aan dat de planhandle voor de langzaam lopende query of batch , 0x06000100A27E7C1FA821B10600is , wat je als argument plan_handle kunt specificeren om sys.dm_exec_query_plan het uitvoeringsplan in XML-formaat op te halen, als volgt. Het uitvoeringsplan in XML-formaat voor de langzaam lopende query of batch is opgenomen in de query_plan kolom van de tabel die door sys.dm_exec_query_planwordt geretourneerd.
SELECT *
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO
B. Haal elk queryplan op uit de plancache
Om een snapshot van alle queryplannen in de plancache op te halen, haalt u de planhandles van alle queryplannen in de cache op door de sys.dm_exec_cached_plans dynamische beheerweergave te bevragen. De planhandvatten worden opgeslagen in de plan_handle kolom van sys.dm_exec_cached_plans. Gebruik vervolgens de operator CROSS APPLY om de planhandgrepen als volgt door sys.dm_exec_query_plan te geven. De XML Showplan-uitvoer voor elk plan dat zich momenteel in de plancache bevindt, bevindt zich in de query_plan kolom van de tabel die wordt geretourneerd.
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
C. Haal elk queryplan op waarvoor de server querystatistieken uit de plancache heeft verzameld
Om een snapshot op te halen van alle queryplannen waarvoor de server statistieken heeft verzameld die momenteel in de plancache staan, haal je de planhandles van deze plannen in de cache op door de sys.dm_exec_query_stats dynamische beheerweergave te bevragen. De planhandvatten worden opgeslagen in de plan_handle kolom van sys.dm_exec_query_stats. Gebruik vervolgens de operator CROSS APPLY om de planhandgrepen als volgt door sys.dm_exec_query_plan te geven. De XML Showplan-output voor elk plan waarvoor de server statistieken heeft verzameld die momenteel in de plancache liggen, staat in de query_plan kolom van de tabel die wordt teruggegeven.
SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO
D. Haal informatie op over de top vijf zoekopdrachten op basis van gemiddelde CPU-tijd
Het volgende voorbeeld geeft de plannen en de gemiddelde CPU-tijd voor de top vijf zoekopdrachten terug.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO