Stored Proc Recompile

Question

Stored Proc Recompile

dkschill

Hall of Fame

Points: 3375
More actions
June 19, 2012 at 10:02 am

#276580

Is there a way to tell when a stored procedure was last recompiled/compiled?
I am trying to work with sys.dm_exec_procedure_stats, and want to give more perspective on the execution_count.
Which is defined as:
Number of times that the stored procedure has been executed since it was last compiled.
Link
Thanks for any help with this!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The cached_time column in that DMV tells you when the procedure was cached, the execution count is the count since that time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dkschill Hall of Fame Points: 3375 More actions · Answer 2

I was hoping that wasn't the case 🙂

I can't explain why I am seeing the following:

cacheTime: 2012-06-18 15:00:10.4870000

executionCount: 7,673,060

I know that it hasn't run that many times. I know other things can cause it to increment, but that seems like a lot.

dkschill Hall of Fame Points: 3375 More actions · Answer 3

I am pretty confused...I ran a trace on the server and waited for the execution count to increment, and after it incremented about 20 times I stopped the trace and didn't find any scripts that executed the stored proc...

Can someone help me understand what may be happening here?

I am not sure if I can supply anything else here...I started an unfiltered trace on the server (after my filtered trace yielded nothing) and dumped the results into a table and searched the text data for a mention of the stored proc and I only found my own query that was monitoring the stats...

Thanks for any help!

Script used to pull stats:

select

@@SERVERNAME as serverName,

db_NAME(database_id) as databaseName,

OBJECT_SCHEMA_NAME(object_id, database_id) as schemaName,

OBJECT_NAME(object_id, database_id) as storedProcedureName,

cached_time as statStartTime,

last_execution_time lastExecutetime,

execution_count executionCount,

total_worker_time totalCpuTime,

total_physical_reads + total_logical_reads + total_logical_writes as totalIo,

total_physical_reads totalPhysicalReads,

total_logical_reads totalLogicalReads,

total_logical_writes totalLogicalWrites,

total_elapsed_time totalElapsedTime

from sys.dm_exec_procedure_stats

where OBJECT_NAME(object_id, database_id) = 'NAME_HERE'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What did you trace for?

Procedure or function?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dkschill Hall of Fame Points: 3375 More actions · Answer 5

I ran it for RPC:Completed and SQL:BatchCompleted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Neither of which will show all procedures executed. If your app calls a proc which calls another proc, the RPC will only show the outer.

Trace for SP:Started or SP:completed (or whatever the events are called)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dkschill Hall of Fame Points: 3375 More actions · Answer 7

I extended the trace, and am still not seeing it.

I am doing all columns for the following Events:

SP:Completed

SP:Starting

SP:StmtCompleted

SP:StmtStarting

I searched the object name and the text data columns for the procedure, but didn't get anything other than my own monitoring query.

I really appreciate your help with this! Posting this was a last resort I assure you...I have read numerous blogs/BOL articles and anything else I can my hands on.

dkschill Hall of Fame Points: 3375 More actions · Answer 8

Could this be a plausible reason?

Hi Jarret,

Is the proc you are trying to watching is exec in a very high frequency? Is the sever a very busy server? I mean, besides the sp events, there are lots of other events generated at the same time, right? If this is the situation, I think the scenario you describe might be cause by profiler dropping some of the events.

Profiler will start to dropping event when it can no "catch up" the event generating frequency on server side, it doing this for limit the performance impact to the server. While DMV do not have this problem. I am not sure, but I think you can try server side trace instead of profiler to see if the number of sp events are closer to that in DMV.

Hope this will help.

Regards.

--------------------------------------------------------------------------------

panchao

Found on: http://social.msdn.microsoft.com/Forums/is/sqltools/thread/077cda3b-a126-456a-a899-30c07878cf97

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

It's certainly possible, but I doubt it could cause no procedure calls to show, if it was dropping at random, you should still see some calls and miss others.

Without a lot more investigation, it's hard to see what's going on. Just check that you are looking at the right databases and servers (you'll be surprised how often that happens)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dkschill Hall of Fame Points: 3375 More actions · Answer 10

I have checked at least five times 🙂 I will keep looking...

Thanks again, and if I find something I will post it here!

dkschill Hall of Fame Points: 3375 More actions · Answer 11

How does transactional replication play into DMVs? The DMVs should only reflect the activity on the server, correct? Not activity from the publisher.

Thanks again!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Correct, though check the replication settings, whether it replicates the changes or the procedure calls (can do the latter, sometimes more optimal). But should still have shown up in profiler.

No, DMVs aren;t replicated, are views into internal server state.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dkschill Hall of Fame Points: 3375 More actions · Answer 13

Not sure where to check for the replication setting (I checked the publication properties and googled it and didn't get anything overly useful 🙂 )...either way I am not seeing the stored procedure name in any of the text in the trace files.

I am curious as to how to check that setting though.**update below**

I assumed that DMVs would not replicate, but was curious if the changes coming across would update the DMVs on the subscriber...it would seem that they would.

*need to spell check prior to posting...sorry about that*

**update**

The stored procedures are being called at the subscriber (still don't see the one I am monitoring). Couldn't find the setting, but I looked at the trace events coming across and they are stored procedure calls.

Thanks again.