A. Retrieve the cached query plan for a slow-running Transact-SQL query or batch
If a Transact-SQL query or batch runs a long time on a particular connection to SQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. The following example shows how to retrieve the Showplan for a slow-running query or batch.
.gif) Note: |
|---|
|
To run this example, replace the values for session_id and plan_handle with values specific to your server.
|
First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:
USE master;
GO
EXEC sp_who;
GO
The result set that is returned by sp_who indicates that the SPID is 54. You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:
USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO
The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600. The following example returns the query plan for the specified plan handle and uses the default values 0 and -1 to return all statements in the query or batch.
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
GO
B. Retrieve every query plan from the plan cache
To retrieve a snapshot of all query plans residing in the plan cache, retrieve the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view. The plan handles are stored in the plan_handle column of sys.dm_exec_cached_plans. Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. The Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.
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. Retrieve every query plan for which the server has gathered query statistics from the plan cache
To retrieve a snapshot of all query plans for which the server has gathered statistics that currently reside in the plan cache, retrieve the plan handles of these plans in the cache by querying the sys.dm_exec_query_stats dynamic management view. The plan handles are stored in the plan_handle column of sys.dm_exec_query_stats. Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. The Showplan output for each plan is in the query_plan column of the table that is returned.
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. Retrieve information about the top five queries by average CPU time
The following example returns the query plans and average CPU time for the top five queries. The sys.dm_exec_text_query_plan function specifies the default values 0 and -1 to return all statements in the batch in the query plan.
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