Microsoft® SQL Server 2008 R2 Unleashed

Using sys.dm_exec_query_plan

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 showplan 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 showplan 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 the same information ...