April 28, 2014 at 2:49 am
Hi Experts,
When I ran the below script , I got result of around 1700 rows and when I removed the where clause WHERE qs.database_id = DB_ID() the result set increased to 1800 rows. Total number of procedures in that DB is around 7000,why I am not getting result of all procedure and why the difference?
Is there any way to get the execution time taken for all the procedures in that DB.
SELECT
p.name AS [SP Name] ,
qs.total_physical_reads AS [TotalPhysicalReads] ,
qs.total_logical_reads AS [TotallogicalReads] ,
qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads] ,
qs.total_worker_time AS TotalCPU ,
qs.total_worker_time / qs.execution_count AS AvgCPU ,
qs.execution_count AS ExecCount,
ISNULL(qs.execution_count / DATEDIFF(Second, qs.cached_time, GETDATE()),
0) AS [Calls/Sec_SinceCahcedTime] ,
qs.total_elapsed_time ,
qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
qs.cached_time ,
qs.last_execution_time ,
qs.last_worker_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY [avg_elapsed_time] DESC
TIA
April 28, 2014 at 3:09 am
When was the last restart? Have all procs executed since then?
😎
April 28, 2014 at 3:28 am
In your query add colum qs.database_id, next to 'SP Name' and you will be surprise to see more than one db_id and when you remove the db_id() clause it is increasing no. of rows.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 28, 2014 at 5:45 am
The DMV only contains stats for procedures whose plans are still in cache, so if the procedure hasn't run since SQL last started or the plan has since been aged out of cache, it won't appear in that DMV
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply