Query plans possess vital information which can help a DBA to make optimization decisions.

The T-SQL below will retrieve the query plan for database of your choice. By extending the where clause in the sub query you can be more precise about the query plans you return based on particular commands for example. The sub query could even be replaced with your own SPID.

select query_plan
from sys .dm_exec_requests as er
cross apply sys. dm_exec_query_plan(er .plan_handle)
where session_id in ( select spid from sys. sysprocesses where dbid = DB_ID('database_name' ))

Happy viewing :)

 

 

 

When examining the contents of sys.sysprocesses one of the very last columns in the table is labelled as the sql_handle. This is a very useful hex number which represents the T-SQL which was run during a process. We can use the inline table value expression dm_exec_sql_text() to translate this hex into text showing what T-SQL was run.

Very simply,

select *
from sys .dm_exec_sql_text( <<sql_handle goes here>> )

Its also possible to use dbcc inputbuffer with the spid from a process,

dbcc inputbuffer (<<spid goes here>>)

This will also return the T-SQL run but there is a limit to how many characters inputbuffer can display. If the command is long the end will be cut off.