Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

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?
0
Camillia
Asked:
Camillia
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now