SQL Statement in cache, it’s execution time

— Display SQL statement (SQLServer 2005 and SQLServer 2008), it displays creation time, last exeuction time, max time it ran for in microseconds, average execution time, # of times sql statement executed

SELECT j.plan_handle
, sqltext
, deqs.creation_time
, deqs.last_execution_time
, deqs.
, deqs.total_worker_time/deqs.execution_count avg_exec_time
, deqs.execution_count
FROM (SELECT decp.plan_handle, st.text sqltext
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st) j,
sys.dm_exec_query_stats deqs
WHERE deqs.plan_handle = j.plan_handle

Output:
Plan handle sqltext creation_time last_execution_time max_worker_time avg_exec_time execution_count
—————— ———— ——————— ————————– ———————— ——————— ———————-
0x060001006F639935B8C1E480000000000000000000000000 select * from sys.dm_exec_cached_plans 2010-11-15 21:30:28.183 2010-11-15 21:35:16.700 35 32 4
…………

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s