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 |
|
schmidtg
Starting Member
36 Posts |
 Posted - 2007-05-09 : 16:34:45
|
| I'm trying to performance tune a procedure and am sort of being thwarted by caching.When I first run the procedure, it takes a few seconds which is too long in this case. Subsequent executions in Management Studio are nearly instantaneous, though, which I imagine is due to caching and does not reflect the behavior of the procedure in production.Is there a way to disable caching so that each execution of the procedure in Management Studio will be consistent and reflect the "first run" performance? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-09 : 16:37:29
|
| Run these prior to each query that you are performance tuning:DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSTara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-09 : 16:43:24
|
the DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS might affect other users too if there are other people logged in. you can also DROP/recreate the proc each time (include it as part of your scrip) so any queryplans cached are removed explicitly.If Exists (SELECT * from Sysobjects Where type = 'p' and Name = 'YourProc') Drop Proc YourProcGoCreate proc YourProc ( )...... Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
schmidtg
Starting Member
36 Posts |
Posted - 2007-05-09 : 16:59:14
|
| Excellent, those calls will do the trick since I'm just working on an isolated dev machine. Thanks for the quick help. |
|
|
|
|
|
|
Avoiding caching