November 6, 2012 at 11:20 am
When I run the following query to get the last execution time and execution count of a stored procedure in my prod environment, I seem to be getting erroneous results:
SELECT getdate()
,s.last_execution_time
,s.execution_count
FROM
sys.dm_exec_procedure_stats s
WHERE
OBJECT_NAME (s.object_id) = 'mySproc1';
The issue is that this stored procedure is executed from a job every 30 minutes (duration of each job run is only a couple of minutes).
However, I keep getting ever increasing values of last_execution_time and execution_count when I run above query, even when job is not running.
To make sure I was not missing anything, I even ran a SQL Profiler trace and was able to confirm that indeed this sproc is not executing except for 1-2 minutes every half hour.
Yet according to the sys.dm_exec_procedure_stats query, the sproc is executing almost continuously.
Any ideas what I am missing here?
PS. Here are some sample data from DMV query above (3 runs):
2012-11-06 13:22:40.663,2012-11-06 13:22:28.953,32419
2012-11-06 13:23:14.777,2012-11-06 13:23:10.460,32430
2012-11-06 13:23:26.160,2012-11-06 13:23:25.787,32446
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 8, 2012 at 6:35 am
Any ideas on this anyone?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 14, 2012 at 2:27 pm
I too am see the same thing. I have a job called every 15 minutes, but the execution_count is extremely higher. There is no way the execution_count is correct.
November 14, 2012 at 2:30 pm
webwilliam (11/14/2012)
I too am see the same thing. I have a job called every 15 minutes, but the execution_count is extremely higher. There is no way the execution_count is correct.
Thank you, I wonder if this is something we need to bring to Microsoft's attention...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 14, 2012 at 2:52 pm
Disregard my post. I ran a sp_recompile on my stored procedure to reset everything. Then when my job ran I saw the execution_count jump to 41.
I then realized that in fact the job did call this procedure 41 times.
All good on my end, seems to be correct.
November 14, 2012 at 5:54 pm
webwilliam (11/14/2012)
Disregard my post. I ran a sp_recompile on my stored procedure to reset everything. Then when my job ran I saw the execution_count jump to 41.I then realized that in fact the job did call this procedure 41 times.
All good on my end, seems to be correct.
I see, thanks for letting me know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 31, 2014 at 4:28 am
Did you find anything regarding this issue ?
I face the same problem.
last_execution_time is updated every hour but i cannot find any trace of procedure call through :
- sql profiler
- extended events
- changing sp definition to write log data in a table
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply