Dynamic management views (DMVs) can return server state information that can be used to monitor and diagnose database engine issues and help tune performance. The
sys.dm_exec_query_plan DMV returns the execution plan information for a T-SQL batch whose query execution plan resides in the plan cache. This can be any SQL batch, not just the batch executed by the current user session. The
sys.dm_exec_query_plan DMV also provides the capability to retrieve the execution plan for currently long-running processes to help diagnose why they may be running slowly.
The execution plan information provided by
sys.dm_exec_query_plan is returned in a column called
query_plan, which is of the
xml data type. This column provides ...