Restore database
-
Hi all.
After restoring database, my app runs very slowly for the first time. It seems the running store procedures need to be compiled. How can I make it pre-compile or whatever to make my app run faster? Thanks.
-
Using sp_recompile will mark the procedure for recompilation next time it is run...you may be better off creating a script to loop through all the procedures and executing them by specifying the "with recompile" option so that you can run this script after a restore...
**ASCII stupid question, get a stupid ANSI !!!** -
Maybe there is a misunderstood. Using sp_recompile will make all my store procedures recompiled each time they are excuted. That is not what I want because it will slow down my app. I want to make them PRE-COMPILE after restoring database so that they can run immediately when they are called. How can I do?
-
I'm not totally convinced that it is the recompilation of your stored procedures that is slowing the application down initially. The compilation of stored procedures happens extremely quickly, which is why there is no "pre-compilation" function for them.
Once your database is restored any previously cached data will have to re-read from disk. I would guess that this is probably what is taking the time until your frequently used data is held and retrieved from cache/memory.
Also, depending on what application you are using, that could also be a cause of the slow down e.g. ASP.NET applications recompile all aspx pages upon first request after the service or application pool is restarted.
-
I think there was definitely a misunderstanding - I was suggesting you run a script to loop through procedures using "with recompile" immediately after you do a restore...NOT sp_recompile which only marks them for recompilation..
However, "noggin" has some excellent suggestions which you're probably looking into right now ?! 🙂
**ASCII stupid question, get a stupid ANSI !!!** -
When you restore a database the sprocs are indeed recompiled the next time they are run. This is usually very fast, however very complex sprocs can take up a minute or so recompiling.
What you are probably seeing is your statistics being rebuilt and your indexes may also be getting updated. This happens after a restore operation if the autoupdate stats database option is enabled.
Richard L. Dawson
Microsoft Sql Server DBA/Data ArchitectI can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
-
Thanks for all replies.
The reason why I post this question is my app almost throws timeout exception for the first time running after restoring database. It also happens when I restart the server. My app runs on .NET framework, MS SQL Server 2000, using MS Enterprise Library released on Jan this year. My connect timeout property set very high (600) but the app raised error for about 1 or 2 min. Any advice for this?
-
I sometimes find a similar situation for my app just after the server has started - the DB is taking a long time to read a lot of data in to cache. Once you have your server running smoothly, try one or both of the following two commands and see how it affects your performance.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
The first effectively clears the disk cache, the second clears the procedure cache - should help you pinpoint if it is recompilation of procs, slow reading in to cache, or some application fault (such as the recompiling ASPX pages - nice thinking outside the square!)
Cheers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply