Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Andraax
Aged Yak Warrior
790 Posts |
 Posted - 2005-02-16 : 08:12:28
|
Hello guys and gals!I'm running a simple stored procedure, which inserts values in a table. It looks like this (simplified):create proc test @value1 int, @value2 intASinsert tt (value1, value2)select @value1, @value2 Table tt also has an identity column which is the primary key.When I run this proc, it recompiles every time. I can see that it does using performance monitor (for example, I run 500 batches/second, and it recompiles all of them. Why is this? Shouldn't it use a precompiled version of the SP? The only thing that changes bewteen calls is one of the parameters. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-16 : 08:17:38
|
| If the sproc only inserts the variables you pass, try a version like this:create proc test @value1 int, @value2 int ASset nocount oninsert tt (value1, value2) values(@value1, @value2)You may also want to run DBCC FREEPROCCACHE and run the procedure the first time using WITH RECOMPILE at the end. Anytime I see weird recompiles that usually fixes it. |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2005-02-21 : 04:49:46
|
| Hi Rob, thanks for the reply.I have tried this to no avail, it is still recompiled every time, even with exactly the same parameters. Can it be something else, server settings or whatever?/Andraax |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-21 : 09:52:22
|
| Can you post the non-simplified version?Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Why recompilation?