Command to check last time stored proc was recompiled

I was asked this question today...what's the command to check the date of the last time a stored proc was recompiled. I thought it was sp_recompile. It's not..

Is there a command?
LVL 8
CamilliaAsked:
Who is Participating?
 
imitchieCommented:
dbcc freeproccache
will clear the cache so that everything is recompiled again. unless you are using many complicated queries, the performance hit is not noticeable.

sp's are recompiled if they are not in the proc cache, so if you like, the last compile time is when it entered the cache

the proc cache is clean on a sql server restart. other than   dbcc proccache   telling you some stats about it, I don't think there are other ways of getting exactly what's in there, not to mention whether a specific sp is in there and when.
0
 
CamilliaAuthor Commented:
just ran dbcc proccache. Doesnt say much.

I guess our client needs to clear the cache.
0
 
Ken SelviaRetiredCommented:
There may be better ways, but on SQL 2005 you can run

select top 10 *
from sys.dm_exec_query_stats

creation_time will contain when the query plan was created (i.e. first executed since restart/cache clear)

That won't show you what the query was though.  You can adapt this for that. If you are all SQL 2005 I'll script it out if you like. (I don't have it written up but I'll do it if you need it)

create table #t (id int identity(1,1), sqlcmd text)

-- Top 10 highest logical IO. Remove TOP to get all queries on the system
select top 10 sql_handle
into #x
from sys.dm_exec_query_stats
order by total_logical_reads , total_logical_writes

select 'insert #t select text from sys.dm_exec_sql_text(', t.sql_handle, ')'
FROM #x t
order by id

Execute output from command above to see highest 10 IO queries.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Aneesh RetnakaranDatabase AdministratorCommented:
In sql server 2005,  this will do
SELECT  DB_NAME(dbid) AS DatabaseName
       ,OBJECT_NAME(ObjectID) AS ObjectName
       ,der.creation_time
       ,der.Last_execution_time
       ,Text
FROM    sys.dm_exec_query_stats as der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) as dest

Open in new window

0
 
Ken SelviaRetiredCommented:
Cool. I had tried CROSS APPLY and was getting some error. Forget what it was now. Thought it was a limit of sys.dm_exec_sql_text()
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
:)
0
 
imitchieCommented:
cool bananas... sql 2005 is so much better
0
 
CamilliaAuthor Commented:
it's sql 2000 but they use sql2005 management to look at sql2000. They dont use enterprise manager.
0
 
imitchieCommented:
i guessed that from your other questions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.