sys.dm_exec_text_query_plan (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Retourneert de Showplan in tekstformaat voor een Transact-SQL batch of voor een specifieke verklaring binnen de batch. Het queryplan dat door de planhandle wordt gespecificeerd, kan zowel gecached zijn als momenteel uitgevoerd. Deze tabelwaardige functie lijkt op sys.dm_exec_query_plan (Transact-SQL), maar heeft de volgende verschillen:

• De output van het queryplan wordt in tekstformaat teruggegeven.
• De output van het queryplan is niet beperkt in omvang.
• Individuele statements binnen de batch kunnen worden gespecificeerd.

Van toepassing op: SQL Server (SQL Server 2008 (10.0.x en later), Azure SQL Database.

Transact-SQL syntaxis-conventies

Syntaxis

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)  

Arguments

plan_handle
Is een token dat uniek een query-uitvoeringsplan identificeert voor een batch die is uitgevoerd en waarvan het plan zich in de plancache bevindt, of momenteel uitvoert. plan_handle is varbinair(64).

De plan_handle kan worden verkregen uit de volgende dynamische beheerobjecten:

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

statement_start_offset | 0 | VERSTEK
Geeft in bytes de beginpositie aan van de query die de rij beschrijft binnen de tekst van zijn batch of behouden object. statement_start_offset is int. Een waarde van 0 geeft het begin van de batch aan. De standaardwaarde is 0.

De statement start-offset kan worden verkregen uit de volgende dynamische beheerobjecten:

• sys.dm_exec_query_stats

• sys.dm_exec_requests

statement_end_offset | -1 | VERSTEK
Geeft in bytes de eindpositie aan van de query die de rij beschrijft binnen de tekst van zijn batch of behouden object.

statement_start_offset is int.

Een waarde van -1 duidt het einde van de batch aan. De standaardwaarde is 1.

Tabel geretourneerd

Kolomnaam	Gegevenstype	Description
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.
Object	int	Id van het object (bijvoorbeeld opgeslagen procedure of door de gebruiker gedefinieerde functie) voor dit queryplan. Voor ad-hoc- en voorbereide batches is deze kolom null. Kolom is null-waarde.
number	smallint	Genummerde opgeslagen procedure geheel getal. Een groep procedures voor de orderapplicatie kan bijvoorbeeld orderproc worden genoemd; 1, orderproc; 2, enzovoort. Voor ad-hoc- en voorbereide batches is deze kolom null. Kolom is null-waarde.
Gecodeerde	bit	Geeft aan of de bijbehorende opgeslagen procedure is versleuteld. 0 = niet versleuteld 1 = versleuteld De kolom is niet ongeldig te maken.
query_plan	nvarchar(max)	Bevat de compile-time Showplan-representatie van het query-uitvoeringsplan dat met plan_handle is gespecificeerd. De Showplan is in tekstformaat. 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 er geen Showplan-output teruggegeven in de plankolom van de teruggestuurde tabel voor sys.dm_exec_text_query_plan:

• Als het queryplan dat wordt gespecificeerd met plan_handle uit de plancache is verwijderd, is de kolom query_plan van de teruggestuurde tabel nul. Deze aandoening kan bijvoorbeeld optreden als er een tijdsvertraging is tussen het moment waarop het planhandvat werd vastgelegd en het moment waarop het met sys.dm_exec_text_query_plan werd gebruikt.

• Sommige Transact-SQL-statements worden niet gecacht, zoals bulk operation statements of statements met stringliterals groter dan 8 KB. Showplans voor dergelijke statements kunnen niet worden opgehaald met sys.dm_exec_text_query_plan 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 door sys.dm_exec_text_query_plan wordt geretourneerd voor de batch of opgeslagen procedure. In plaats daarvan moet je een aparte aanroep doen naar sys.dm_exec_text_query_plan voor de plan_handle die overeenkomt met de door de gebruiker gedefinieerde functie.

Wanneer een ad hoc-query eenvoudige of geforceerde parametrisatie gebruikt, bevat de kolom query_plan alleen de statementtekst en niet het daadwerkelijke queryplan. Om het queryplan terug te geven, roep sys.dm_exec_text_query_plan aan voor de planhandle van de voorbereide geparametriseerde query. 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.

Permissions

Om sys.dm_exec_text_query_plan uit te voeren, moet een gebruiker lid zijn van de sysadmin vaste serverrol of de VIEW SERVER STATE perspectie op de server hebben.

Machtigingen voor SQL Server 2022 en hoger

Vereist de machtiging PRESTATIESTATUS VAN DE WEERGAVESERVER op de server.

Voorbeelden

Eén. Het ophalen van het gecachte queryplan voor een langzaam draaiende Transact-SQL query of batch

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 Showplan kunt ophalen voor een langzaam lopende query of batch.

Opmerking

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:

USE master;  
GO  
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:

USE master;  
GO  
SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

De tabel die door sys.dm_exec_requests wordt teruggegeven , geeft aan dat de planhandle voor de langzaam lopende query of batch is 0x06000100A27E7C1FA821B10600. Het volgende voorbeeld geeft het queryplan terug voor de opgegeven planhandle en gebruikt de standaardwaarden 0 en -1 om alle statements in de query of batch terug te geven.

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO  

B. Elk queryplan ophalen 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_text_query_plan te geven. De Showplan-uitvoer voor elk plan dat momenteel in de plancache staat, bevindt zich in de query_plan kolom van de tabel die wordt teruggegeven.

USE master;  
GO  
SELECT *   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);  
GO  

C. Het ophalen van elk queryplan 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_text_query_plan te geven. De Showplan-output voor elk plan staat in de query_plan kolom van de tabel die wordt teruggegeven.

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO  

D. Informatie ophalen over de top vijf zoekopdrachten op basis van gemiddelde CPU-tijd

Het volgende voorbeeld geeft de queryplannen en de gemiddelde CPU-tijd voor de top vijf queries terug. De sys.dm_exec_text_query_plan-functie specificeert de standaardwaarden 0 en -1 om alle statements in de batch in het queryplan terug te geven.

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_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO  

Zie ook

sys.dm_exec_query_plan (Transact-SQL)