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 |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-12-06 : 11:08:49
|
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).
What qualifies as a 'change'?I am at the very early stages of debugging a proc that seems to periodically loose its mind. It runs very frequently and very quickly, but every now and then a 'switch is flipped' and now it take 2+ minutes to run. The only solution has been to drop and recreate the proc.I'm wondering what 'changes' qualify for causing the proc to be recompiled ... I'm thinking maybe one of the referenced tables goes through a load process where an index is dropped and a DML change is made to the table (causing the proc to be recompiled with an execution plan that doesn't see the index) and then the index is rebuilt.Jay White{0} |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-06 : 11:27:15
|
From The Guru's Guide to Transact-SQL page 306:quote:
SQL Server will automatically recreate a procedure's execution plan when:-The procedure's execution environment differs significantly from its creation environment-The sysobjects schema_ver column changes for any of the object the procedure references. The schema_ver and base_schema_ver columns are updated any time the schema information for a table changes. This includes column additions and deletions, data type changes, constraint additions and deletions, as well as rule and default bindings.-The statistics have changed for any of the objects the procedure references.-An index that was referenced by the procedure's execution plan is dropped.-A copy of the procedure's execution plan is not available in the cache. Execution plans are removed from the cache to make room for new plans using an LRU (least recently used) algorithm.
Ken also states you can force a recompile using WITH RECOMPILE at create or execute, or using sp_recompile to mark the objects you want flagged. You can also flush the procedure cache with DBCC FREEPROCCACHE, and recompile all sproc's with DBCC FLUSHPROCINDB.I have a feeling that there may be some data caching that's causing the sproc to run better under normal conditions, but when its data is flushed it has to go out to disk to get it again. Have you tested the sproc after DBCC DROPCLEANBUFFERS too? |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-12-06 : 13:48:57
|
| It's not a cache issue...Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-08 : 07:44:29
|
| What you are suggesting would certainly cause this affect.You shouldn't be playing around with indexes (permanent objects) on an active system.If you do this sort of thing you should expect problems (even corruption). Try running sp_recompile on the table after the index is re-created which should cause the SP to work again.Droppping the index will invalidate the plan causing the Sp to be recompiled on next run.I would have thought that you would prevent the SP from running while the load is going on so that it will be recompiled after the index is recreated and so should be OK.What could happen is that the data distribution has changed and the SP not recompiled (the recreation of the index should rebuild statistics). The sp_recompile on he table should fix that.>> and a DML change is made to the tableOops - all very hairy stuff.I would bounce the server after this but an sp_recompile may make it OK.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Recompiling a Stored Procedure