5

I have inherited a number of SQL Server 2005 and 2008 systems (all running in Simple recovery mode) that each currently do the following things each night from an SQL Agent task:

  • Backup
  • Shrink
  • Reindex

I have stopped the shrink from happening as this is likely to be causing more problems than it is solving. (The usage of the server is to store a consistent amount of information on a rolling 5 day basis, so the space from deleted rows will be used again for fresh information without much growth).

However, I do not know whether I should also stop the nightly re-index job. It was necessary when the shrink was active because of the massive index fragmentation that shrinking causes. I cannot find much information on the internet suggesting when a nightly re-index might be sensible. Should I disable this too?

2 Answers 2

7

If the information is rolling that indicates that there is a large portion, roughly 20%, of the total DB being inserted every day. This indicates that your stats and your indexes are 20% wrong.

That sounds like exactly the conditions you should be re-indexing for.

1
  • +1, It also depends on how often your pulling info from the DB, and how it's structured, etc; but this is correct for the general case.
    – Chris S
    Sep 15, 2010 at 13:04
1

Basically, your shrink and reindex were simply undoing each other (the shrink fragments tables to recover space, then the reindex uses extra space to defragment tables).

So since you've removed the shrink, you probably can remove the nightly reindex as well. (Still probably advisable to have a weekly full reindex, in most cases.)

But given the rolling 5-days of data, you might actually be ok with NO REINDEXES at all on those rolling tables (depending on exactly how the clustered index is configured). Here's what I mean:

Assuming you do inserts only (no updates), and the clustered index is either on the date/time, or just a perpetually increasing numeric value, the internal table arrangement might look something like:

[  Day 1  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Empty  ]

On day 6, you delete the data from day 1, and insert the data from day 6:

[  Empty  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]

and the same pattern continues, re-using the recently-freed space:

[  Empty  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Empty  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Day 8  ][  Empty  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Day 8  ][  Day 9  ][  Empty  ][  Day 5  ][  Day 6  ]

To know for sure if this is what is happening, I would use SQL's table fragmentation queries (dm_db_index_physical_stats) after a final reindex,then turning off the reindexes for several days. If the fragmentation numbers are very low, then this is probably what is going on. If they are high, then this probably isn't an accurate picture, and you might need to reinstate your reindexes.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .