Erroneous results on last_execution_time, execution_count obtained from DMV sys.dm_exec_procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Erroneous results on last_execution_time,...

Erroneous results on last_execution_time, execution_count obtained from DMV sys.dm_exec_procedure_stats

Rate Topic

Display Mode

Topic Options

Author

Message

Marios Philippopoulos

Posted Tuesday, November 06, 2012 11:20 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #1381670

Marios Philippopoulos

Posted Thursday, November 08, 2012 6:35 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Any ideas on this anyone?

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #1382460

webwilliam

Posted Wednesday, November 14, 2012 2:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 4:28 AM
Points: 2, Visits: 64

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.

Post #1384862

Marios Philippopoulos

Posted Wednesday, November 14, 2012 2:30 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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...

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #1384867

webwilliam

Posted Wednesday, November 14, 2012 2:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 4:28 AM
Points: 2, Visits: 64

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.

Post #1384885

Marios Philippopoulos

Posted Wednesday, November 14, 2012 5:54 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

webwilliam (11/14/2012)

I see, thanks for letting me know.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #1384943

« Prev Topic | Next Topic »

Permissions