Today Jonathan Lewis put up a blog post that got me thinking.
http://jonathanlewis.wordpress.com/2013/07/05/wrong-index/
The databases I spend my time thinking about are getting bigger and bigger all the time. There's just not enough time in a day to do maintenance that doesn't give a return.
So don't rebuild/reorg an index if updating the stats will do, and don't do any of it unless the profile of the table and related queries indicates it will matter.
In SQL Server 2014 it looks like I'll be able to make the decision and take action partition by partition. Excellent! But until then... when time starts getting tight on an individual system... what is the smallest unit on which to take action?
What are the conditions under which it would make sense to rebuild nonClustered index A from table B on Monday (along with some other stuff), and reorg NC index C from table B on Wednesday (along with some other stuff)... instead of planning a time to act on the entire table at once?
It seems to me to be a better plan, for now, to keep acting on whole tables at a time... and plan for the future with per-partition actions. I don't think I want the variability that could come with one defragged/updated object in a table while other objects are more fragmented/stale.
No comments:
Post a Comment