June 5, 2014 at 7:59 pm
I've been searching on DMV's for the past hour and have had no luck.
I'm trying to get stats for a stored procedure that has already executed. It calls many other procs and UDF's. I use a version of sp_who (sp_who3) that I found on this forum. It shows me great stats as the proc is running (elapsed, CPU, IO reads, IO writes). SP_WHO3 is getting it's stuff from sys.dm_exec_requests.
How can I get these same kind of stats for the entire run after the procedure has finished? I don't want the stats for each procedure that is called. Just the cummulative of everything executed.
We are trying to do some benchmarks and need to get total stats from each run.
June 5, 2014 at 8:53 pm
john.p.lantz (6/5/2014)
I've been searching on DMV's for the past hour and have had no luck.I'm trying to get stats for a stored procedure that has already executed. It calls many other procs and UDF's. I use a version of sp_who (sp_who3) that I found on this forum. It shows me great stats as the proc is running (elapsed, CPU, IO reads, IO writes). SP_WHO3 is getting it's stuff from sys.dm_exec_requests.
How can I get these same kind of stats for the entire run after the procedure has finished? I don't want the stats for each procedure that is called. Just the cummulative of everything executed.
We are trying to do some benchmarks and need to get total stats from each run.
On Management Studio, if you run it from there, you can use Client Stats and that will give you average execution time, network statistics, etc. But the best way to benchmark the store procedure if getting the actual execution plan.
If you want more exact information based on given parameters, you can setup a server side trace or Extended Events.
But using DMVs, something like this may help:
SELECT CASE WHEN dbid = 32767 THEN 'Resource' ELSE DB_NAME(dbid)END AS DBName
,OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME]
,OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME]
,MAX(qs.creation_time) AS 'cache_time'
,MAX(last_execution_time) AS 'last_execution_time'
,MAX(usecounts) AS [execution_count]
,SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU
,SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED
,SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS
,SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES
,SUM(total_physical_reads) / SUM(usecounts)AS AVG_PHYSICAL_READS
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc'
AND [text]
NOT LIKE '%CREATE FUNC%'
GROUP BY cp.plan_handle,DBID,objectid
You may have to filter based on database and object name.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply