Solved

Command to check last time stored proc was recompiled

Posted on 2007-11-14
9
463 Views
Last Modified: 2008-02-01
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
Comment
Question by:Camillia
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 300 total points
ID: 20285593
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
 
LVL 7

Author Comment

by:Camillia
ID: 20285633
just ran dbcc proccache. Doesnt say much.

I guess our client needs to clear the cache.
0
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
ID: 20286926
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 20287147
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
 
LVL 12

Expert Comment

by:kselvia
ID: 20287161
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20287166
:)
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20287217
cool bananas... sql 2005 is so much better
0
 
LVL 7

Author Comment

by:Camillia
ID: 20288349
it's sql 2000 but they use sql2005 management to look at sql2000. They dont use enterprise manager.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20288436
i guessed that from your other questions
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer Table Triggers 3 28
Syntax issue with my Where Clause SQL 2012 20 38
Need help with another query 10 34
Trigger C# code inside the SQL Server 6 25
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question