SELECT qs.plan_handle, pvt.sql_handle,pvt.dbid,

(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

( (CASE WHEN statement_end_offset = -1

THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,

qs.execution_count AS [Execution Count],

qs.total_elapsed_time/(1000*1000) as [Total Time In Secs],

qs.total_elapsed_time/1000 as [Total Time In Millisecs],

qs.total_elapsed_time / (1000 * qs.execution_count) as [Avg Resp Time in Millisecs],

qs.max_elapsed_time / (1000) as [Max Resp Time in Millisecs],

qs.min_elapsed_time / (1000) as [Min Resp Time in Millisecs],

qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,

qs.total_elapsed_time / 1000000.0 AS total_seconds,

qs.last_execution_time [Last Executed At]

FROM (

SELECT plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = 'Compiled Plan') AS ecpa

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("dbid", "sql_handle")) AS pvt

inner join sys.dm_exec_query_stats AS qs on qs.sql_handle =pvt.sql_handle

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS s2

where pvt.dbid =DB_ID()

GO