SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 05:55 PM
| ||||
| ||||
 prevent SP compilation Hi, I'm using SQL Server 2000 MSDE on a laptop running Windows XP. I have a couple of SP's that that quite some time to compile. So I was wondering: is there any way to have the database *not* recompile them every time after a reboot? BOL says: "As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft® SQL ServerT 2000 is restarted." Now the SQL Server is restarted a lot, because laptops don't have endless batteries <g> Cheers, Bas      |
|
02-28-2008, 05:56 PM
| |||
| |||
| Re: prevent SP compilation "Bas" <nomailplease> wrote in message news:3f6720ff$0$120$e4fe514c@dreader4.news.xs4all. nl... > Hi, > > I'm using SQL Server 2000 MSDE on a laptop running Windows XP. > > I have a couple of SP's that that quite some time to compile. So I was > wondering: is there any way to have the database *not* recompile them every > time after a reboot? > > BOL says: "As a database is changed by such actions as adding indexes or > changing data in indexed columns, the original query plans used to access > its tables should be optimized again by recompiling them. This optimization > happens automatically the first time a stored procedure is run after > Microsoft® SQL ServerT 2000 is restarted." > > Now the SQL Server is restarted a lot, because laptops don't have endless > batteries <g> > > Cheers, > > Bas > > I don't believe there's a way around this - in MSSQL 2000, query plans are only held in memory, not on disk, so there's no way to store the plan. You could hibernate the laptop, not shut it down - that shouldn't keep the contents of the memory (although I haven't tried to verify this with MSSQL). Simon |
|
02-28-2008, 05:56 PM
| |||
| |||
| Re: prevent SP compilation "Simon Hayes" <sql@hayes.ch> wrote in message news:3f675d54$1_4@news.bluewin.ch... > > "Bas" <nomailplease> wrote in message > news:3f6720ff$0$120$e4fe514c@dreader4.news.xs4all. nl... > > Hi, > > > > I'm using SQL Server 2000 MSDE on a laptop running Windows XP. > > > > I have a couple of SP's that that quite some time to compile. So I was > > wondering: is there any way to have the database *not* recompile them > every > > time after a reboot? > > > > BOL says: "As a database is changed by such actions as adding indexes or > > changing data in indexed columns, the original query plans used to access > > its tables should be optimized again by recompiling them. This > optimization > > happens automatically the first time a stored procedure is run after > > Microsoft® SQL ServerT 2000 is restarted." > > > > Now the SQL Server is restarted a lot, because laptops don't have endless > > batteries <g> > > > > Cheers, > > > > Bas > > > > > > I don't believe there's a way around this - in MSSQL 2000, query plans are > only held in memory, not on disk, so there's no way to store the plan. You > could hibernate the laptop, not shut it down - that shouldn't keep the > contents of the memory (although I haven't tried to verify this with MSSQL). > > Simon > > Sorry, I meant to say that "should keep" the contents of the memory... Simon |
|
02-28-2008, 05:56 PM
| ||||
| ||||
| Re: prevent SP compilation Bas (nomailplease) writes: > I have a couple of SP's that that quite some time to compile. So I was > wondering: is there any way to have the database *not* recompile them > every time after a reboot? Yes, don't insist on running it after each reboot! The query plan for a stored procedure is held in memory, as Simon explain, so the baby goes out with the bathtub. What you possibly could do, is to declare the procedure as a startup procedure, so SQL Server executes the procedure when it starts up, so once you need it, the plan is already there in the cache for you. To this end, you use sp_procooption. If the procedure takes parameters, you can write a wrapper procedure which you declare a startup procedure. However, if the procedure returns a result set, I am little uncertain what happens with it. Ah, wait, you wrapper could look like this: CREATE PROCEDURE wrapper_sp AS CREATE TABLE #tmp (...) INSERT #tmp (...) EXEC your_big_sp -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
