prevent SP compilation

[Bas]

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

[Simon Hayes]

"Bas" <nomailplease> wrote in message
news:3f*********************@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

[Simon Hayes]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"Bas" <nomailplease> wrote in message
news:3f*********************@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

[Erland Sommarskog]

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp