March 31, 2005 at 1:56 pm
Question:
If you detach and attach a DB, do you essentially clear out the procedure cache? If so, is there any way to avoid this? I have a situation where there is a DB that is being copied to a reporting server (production server has 8 processors, reporting server has 2 processors) and attached. It seems after that the stored procedures all run slow the first time through.
Any ideas, links that explain, or workarounds you can suggest?
Mindy
March 31, 2005 at 2:36 pm
The procedure cache is part of the memory pool for each specific SQL Server instance. So if you move a database from one server to another then naturally the procedures will need to be compiled when they are first executed.
March 31, 2005 at 3:35 pm
When you execute sp_detach_db, the procedure cache is flushed for that database. This is probably due to DBCC DETACHDB() [which sp_detach_db wraps], though this command is undocumented.
Edit: You can see this by watching syscacheobjects. I did so with the pubs db on a development sql server. Run a stored procedure, verify it's in the procedure cache, detach, and check the cache. It'll be gone (along with everything else for that dbid).
K. Brian Kelley
@kbriankelley
April 1, 2005 at 8:13 am
Thanks for both your replies. After I posted the message I got to thinking that procedure cache is in the 2-3GB user mode memory space (not even available to use AWE). Guess there is no way around this then, huh? Anyone come up with something creative to get the stored procedures to all compile upon reattach?
April 1, 2005 at 9:16 am
Not a very pretty way but you could always capture normal usage in profiler for say 30 mins and then replay it once you'd re-attached.
You'd have to be careful that you didn't do any inserts in the SP's though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply