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 |
|
monty
Posting Yak Master
130 Posts |
 Posted - 2007-04-29 : 00:09:54
|
| Hi All,we do index rebuilding and updating statistics as an recurring job which works for our environment, now what iam trying to include is a job which does recompilation of all the objects which are refrenced by all SP's, does any one have a script which will do the recompilation of all the objects of all the databases.also if you have any other best practices please let me know.last to mention that iam aware of the fact that sql server would randomly recomplie the objects.its me monty |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-29 : 10:29:20
|
| sql server doesn't jus randomply recompiles objects.if the object's execution plan doesn't exist in the cache then the object is recompiled.recompilations are generaly a bad thing which you want to avoid._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-29 : 12:53:15
|
| "recompilations are generaly a bad thing which you want to avoid."Except after the index/table that they refer to has had its statistics updated, perhaps ?Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-29 : 13:03:54
|
ok true, then they are desired  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-30 : 04:18:44
|
| There's some stuff about SQL automatically detecting stats. updates, and recompiling, but I haven't seen it in practice (under SQL 2000 at least).We check the elapsed runtime of our SProcs and recompile them when they drop below a given threshold, which is about every 3 days (our indexes [and stats] rebuild every night)Kristen |
|
|
|
|
|
|
SP_Recompile