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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-08 : 10:17:24
|
| Ravinder writes "We have had an upgrade to SQL server 2000 and we are finding that everytime we query the database from our inhouse application the stored procedures are being compiled each and everytime, apparently in earleier versions the recompilation process can be stopped by setting a parameter to indicate a recompilation is not required how can we do the same in sql server 2000. Your help would be much appreciated." |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-08 : 10:26:18
|
| Does your Stored Procedure uses Dynamic Sql. if it does then the Cached plan isnt used and it is compiled everytime you call that stored procedure----------------------------Anything that Doesn't Kills you Makes you Stronger |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-08 : 12:54:54
|
Were the procedures created using the WITH RECOMPILE option?Here's some info from the SQL 7 BOL that might help...quote:
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 Server™ is restarted. It also happens if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).
--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
Stored Procedures