Restore database

Question

Restore database

Le Huy

SSC Rookie

Points: 33
More actions
May 23, 2005 at 3:32 am

#164498

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

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 !!!**

Le Huy SSC Rookie Points: 33 More actions · Answer 2

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?

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

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.

Dan
www.firstcs.co.uk

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

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 !!!**

Richard L. Dawson Ten Centuries Points: 1062 More actions · Answer 5

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 Architect

I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

Le Huy SSC Rookie Points: 33 More actions · Answer 6

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?

Ian Yates SSCoach Points: 19738 More actions · Answer 7

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