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 |
|
ts383
Starting Member
1 Post |
 Posted - 2006-07-25 : 10:29:46
|
| Ok, I've been debating with a few folks around the office about the use of sp_recompile and when it should be run. Since a change to a table schema will essentially invalidate the cached plan for a stored procedure, should we not call sp_recompile 'TableName' any time we alter TableName by adding/deleting columns? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-25 : 10:36:26
|
when indexes on the table get modified or affected by the table changebut...i don't know what is happening but for long running queries, especially those used for heavy reporting,running the sproc apparently increases performance, these tables are subjected to thousands to millions of data archival everyday, it was run as a job every week and no schema changes made  --------------------keeping it simple... |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-25 : 10:41:37
|
checking bol, yep, that's whyquote:
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™ 2000 is restarted. It also occurs 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).
--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-25 : 10:43:59
|
| We monitor the run-time of SProcs and then run sp_recompile when their average elapsed run-time has slipped appreciably. This is usually because large amounts of data have been added, or purged, from the tables - or maybe because the STATs have been updated etc.Stop/Start the SQL Service will do the equivalent of an sp_recompile on the lot, of course!, but we try to do them in stages as if the whole lot get done in one go the server is under huge load whilst every single query-plan gets rebuilt ...Kristen |
|
|
|
|
|
|
|
|
sp_recompile : when to use?