auto recompile in sql server

[Harold]

Hi,
I have a question in SQL Server 2K, I use SQL Profile to trace, and
find Stored Procedure was auto recompiled, like this row in the
trace:
SP:Recompile 15 1680 76 2004-02-27 16:01:11.610

How can I stop the auto recompile.

Thanks
Harold

[Simon Hayes]

"Harold" <ch******@hotmail.com> wrote in message
news:6f**************************@posting.google.c om...

Hi,
I have a question in SQL Server 2K, I use SQL Profile to trace, and
find Stored Procedure was auto recompiled, like this row in the
trace:
SP:Recompile 15 1680 76 2004-02-27 16:01:11.610

How can I stop the auto recompile.

Thanks
Harold

http://support.microsoft.com/default...&Product=sql2k

Simon

[Erland Sommarskog]

Harold (ch******@hotmail.com) writes:

I have a question in SQL Server 2K, I use SQL Profile to trace, and
find Stored Procedure was auto recompiled, like this row in the
trace:
SP:Recompile 15 1680 76 2004-02-27 16:01:11.610

How can I stop the auto recompile.

Simon posted a very useful link. But permit me some short notes.

Recompiles are a mixed blessing. Sometimes they kill your performance,
sometimes they save the day. Not at all knowing your situation, it is
difficult to tell.

The most common reason for recompiles are temp tables that are filled
with data in the procedure. There are two remedies: use table variables
instead, or use OPTION (KEEPFIXED PLAN).

Table variables does not have statistics, which is why they cannot cause
recompile. But that also means that SQL Server has less information about
them when building a plan. Also, be aware of that insering data into a
table variable precludes parallellism. This can also be a performance
killer.

If you litter your code with OPTION (KEEPFIXED PLAN) you can prevent
recompiles, but forget it in one place, and you are in for it again.

A war story: some time back, I fought with a procedure that took 1½
minute to run. I figured I try to cut that down, and since there
were several recompiles, I tried to switching to table variables
plus a few more tricks. Sure, the recompiles went away - but I lost
on the roundabouts, so the execution time was still 1½ minute.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...

Harold (ch******@hotmail.com) writes:

I have a question in SQL Server 2K, I use SQL Profile to trace, and
find Stored Procedure was auto recompiled, like this row in the
trace:
SP:Recompile 15 1680 76 2004-02-27 16:01:11.610

How can I stop the auto recompile.
Simon posted a very useful link. But permit me some short notes.

I'm going to jump in with my own real life experience here.

We went through the steps MS recommended.... big first step was making sure
we called all of our stored procs fully qualified with names, etc.

i.e. dbo.stored_proc as opposed to just stored_proc.

This helped. We got about 10% improvement in throughput.

Just last week, we found another issue (which I think is partly related to
the ODBC drivers, but that's beyond the scope here.)

In any case, rather than doing: exec stored_proc @foo=123 @bar='xyz' etc.

we wrapped the stored_proc call in a sp_executesql.... exec
sp_executesql(stored_proc, etc etc.)

What used to take two sql boxes to handle the load for (and even then we
were getting a lot of problems) we now run on ONE box and have processing
power to spare.

One thing that helped us track this down was a recent article in SQL Server
Magazine that provided some stored procs on tracking wait states.

Whereas over a 2 minute period we'd get MILLIONS of LCK_MX_I (I think I got
that right off the top of my head) we now get hundreds to thousands.)

So, in this case, making the change made a major difference for us. (in our
case this stored proc gets called millions of times a day.)

So, in our case, the savings was HUGE.


Recompiles are a mixed blessing. Sometimes they kill your performance,
sometimes they save the day. Not at all knowing your situation, it is
difficult to tell.

The most common reason for recompiles are temp tables that are filled
with data in the procedure. There are two remedies: use table variables
instead, or use OPTION (KEEPFIXED PLAN).

Table variables does not have statistics, which is why they cannot cause
recompile. But that also means that SQL Server has less information about
them when building a plan. Also, be aware of that insering data into a
table variable precludes parallellism. This can also be a performance
killer.

If you litter your code with OPTION (KEEPFIXED PLAN) you can prevent
recompiles, but forget it in one place, and you are in for it again.

A war story: some time back, I fought with a procedure that took 1½
minute to run. I figured I try to cut that down, and since there
were several recompiles, I tried to switching to table variables
plus a few more tricks. Sure, the recompiles went away - but I lost
on the roundabouts, so the execution time was still 1½ minute.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Erland Sommarskog]

Greg D. Moore (Strider) (mo****************@greenms.com) writes:

Just last week, we found another issue (which I think is partly related to
the ODBC drivers, but that's beyond the scope here.)

In any case, rather than doing: exec stored_proc @foo=123 @bar='xyz' etc.

we wrapped the stored_proc call in a sp_executesql.... exec
sp_executesql(stored_proc, etc etc.)

What used to take two sql boxes to handle the load for (and even then we
were getting a lot of problems) we now run on ONE box and have processing
power to spare.

Interesting.

But I don't really understand. Did you change from using sp_executesql,
or to using it?

Assuming that changed to, how did you call the procedures before that?
Did you send EXEC statements over the wire, or did you call the procedures
through RPC? How do you call sp_executesql now? EXEC statement or RPC?
How are you passing the parameters to the procedures? Just part of the
EXEC string, or as parameters to sp_executesql?

Intuitively, I would think that wrapping the call in sp_executesql would
just be overhead, but I've been wrong before.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Harold]

Thank all of you!

Erland,
The OPTION (KEEPFIXED PLAN) only affect when Auto Update
Statistics(the properties of the database) is true, I turned of the
Auto Update Statistics.

This is from SQL 2K BOOK:
Forces the query optimizer not to recompile a query due to changes in
statistics or to the indexed column (update, delete, or insert).
Specifying KEEPFIXED PLAN ensures that a query will be recompiled only
if the schema of the underlying tables is changed or sp_recompile is
executed against those tables.

I think it may other cause the recompiler.

Thanks
Harold

[Erland Sommarskog]

Harold (ch******@hotmail.com) writes:

The OPTION (KEEPFIXED PLAN) only affect when Auto Update
Statistics(the properties of the database) is true, I turned of the
Auto Update Statistics.
Have you done that for tempdb as well? (Which I am not sure that I would
recommend.)
This is from SQL 2K BOOK:
Forces the query optimizer not to recompile a query due to changes in
statistics or to the indexed column (update, delete, or insert).
Specifying KEEPFIXED PLAN ensures that a query will be recompiled only
if the schema of the underlying tables is changed or sp_recompile is
executed against those tables.

I think it may other cause the recompiler.

Of course. If you have a procedure outer_sp that creates a temp
table and then calls inner_sp to operate on that table, then inner_sp
will be create at least once for each call to outer_sp, since it is a
new temp table each time.

But that's a common problem in these newsgroups. People post to little
information, so it often becomes a guessing game.

By the way, to identify why a procedure is being recompiled, this link
is useful.
http://support.microsoft.com/default...;EN-US;q308737.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Harold]

Erland,
I did not turn of the Auto Update Statistics config at tempdb, you
are right, so the temp table still cause the recompiler.

Thank you so much!
Harold