Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2008-09-23 : 12:47:35
|
| I am rewritting a stored procedure and want to see if i gained anything from the rewrite in the way of i/o costs etc.So in test i ranDBCC FREEPROCCACHEDBCC FLUSHPROCINDB(DB_ID)Ran the SP - i cannot see the recompile coming out.I have SP Starting, SP Stmt Starting, SP Compile, SP CompletedStmt Recompile.But i cannot see the RECOMPILE coming out ...i was trying to see if the SP is getting recompiled each time or just the once and remains in the cacheWhat am i missing. |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-23 : 23:23:00
|
| Are you wanting to recompile the SP each time it is run? If not, would comparing the run-time of the proc with the before version with the run-time of the after version may be your best bet?Unless you create the SP using the "WITH RECOMPILE" clause (& you don't do any other things that would flush the cache generally) the proc should be in cache after the first compile/ CREATE PROCEDURE. |
 |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-23 : 23:31:44
|
| Sorry, to clarify, not first time CREATE PROCEDURE is run, but the first time it is run eg "exec yourProc". So if you time the second execution of the SP before your changes & compare this with the second execution of the SP after your changes, you should have the run-time difference. |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-24 : 00:11:55
|
| I just did some testing of this. I CREATEd the proc & ran it & the implicit compile didn't show in the trace. I then CREATEd the proc WITH RECOMPILE & SP:Recompile appeared every time it was run. I then CREATEd the proc again without recompile. Then the recompile appeared the first time it was run. If I ran the CREATE again the SP:Recompile appeared again. Even opening a completely new trace now it shows. In this case it seemed the trace needed a WITH RECOMPILE to kick-start the appearance of it in the trace. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-09-25 : 17:00:08
|
| Yes basically the SP is in the cache so i want to remove it.Rewrite it completely then execute it - which should do a recompile.Then check all the benefits of this with the statistics client i/o etc.So to remove the old one just do a exec sp_abc WITH RECOMPILE statement this will put the new one in here. |
|
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-09-25 : 20:34:34
|
| If I didn't miss something, what I tested seemed to show a minor bug with the trace. Under normal circumstances, you shouldn't need to worry about the "WITH RECOMPILE" option.If you do the following:1. CREATE myProc2. exec myProc -> After this, your new version will be in the cache, even if you don't use the "WITH RECOMPILE" option, even if it doesn't show in the trace(minor bug?). See this if you would like more background: http://msdn.microsoft.com/en-us/library/ms181055.aspx |
|
|
|
|
|
|
|
|
SQL 2005 Profiler not recording value