May 8, 2012 at 8:02 am
Hi all
I have been searching forums all over the show and cannot find out how to find the date that a stored procedure was compiled the last time.
Seems like such a simple thing, but all articles only talk about "DateCreated" and "DateLastExecuted".
Please can someone help me.
Thanks you,
Theresa
May 8, 2012 at 8:11 am
What do you defined as 'compiled'?
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
May 8, 2012 at 8:16 am
If you're interested in knowing when the plan was placed into cache, which would be the compile point of the procedure, you can get that from Dynamic Management Objects (DMO) like sys.dm_exec_procedure_stats or sys.dm_exec_query_stats. Either will have what you're looking for. The first is specific to procedures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2012 at 8:46 am
Great, thank you Grant. Found cached_time in sys.dm_exec_procedure_stats - exactly what I was looking for.
May 8, 2012 at 8:47 am
Bear in mind that if/when the plan is flushed from cache, there will be no remaining record of when the plan was last generated.
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
May 8, 2012 at 8:48 am
use YourDatabase
go
select
db_name(database_id) as database_name,
object_name(object_id) as sp_name,
cached_time,
last_execution_time
from sys.dm_exec_procedure_stats
where database_id = db_id('YourDatabase')
Change YourDatabase to the database name in question (don't forget the database name in the WHERE clause).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply