November 25, 2010 at 8:24 am
Hi Experts
I've written a query I needed for getting stats on a given stored procedure (taken various parts from other peoples). It looks sound and the results look like what I need / expect, but my understanding of the query plan DMF's is a little flakey.
Is this correct?
DECLARE @ObjName AS VARCHAR(100)
SET @ObjName = 'usp_SomeSP'
SELECT
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
((qs.Total_Worker_Time / cp.usecounts) / 1000) Avg_Worker_Time,
(qs.Total_Worker_Time) / 1000 Total_Worker_Time,
qs.Total_Read_From_Memory,
qs.Total_Reads_From_Disk,
qs.Last_Execution_Time,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
INNER JOIN (
SELECT
plan_handle,
SUM(total_worker_time) Total_Worker_Time,
SUM(total_logical_reads) Total_Read_From_Memory,
SUM(total_physical_reads) Total_Reads_From_Disk,
MAX(last_execution_time) Last_Execution_Time
FROM sys.dm_exec_query_stats
GROUP BY plan_handle
) qs ON
cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE OBJECT_NAME(st.objectid,st.dbid) = @ObjName
Thanks
Nick
November 25, 2010 at 9:58 am
Looks good to me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2010 at 1:53 pm
Thanks Jason, i just wanted to be sure I wasnt missing something on the aggregations from the exec_query_stats. Excellent 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply