6

I tried index reorganization queries several times so far. They look like this:

ALTER INDEX ALL ON TableName REORGANIZE

Such query may take hours but it looks like it doesn't interfere with other database operations. For some table its effect is next to nothing but for some tables it actually saves like 15% of overall database space.

What if I just run this query for all indices every week or so? Is there any reason not to do so?

2 Answers 2

7

Reorganizing an Index should be done when you have elevated amounts of white space within your index (i.e. avg_page_space_used_in_percent column in the sys.dm_db_index_physical_stats DMV). Many people (and even solutions) in the SQL Server community say you need to look at Index Fragmentation levels (e.g. the avg_fragmentation_in_percent in that same DMV), but this is not as much a concern depending on the type of underlying storage (e.g. anything that's RAIDed fragments your data for redundancy purposes regardless). Others also have come to the same conclusion.

The downsides to Reorganizing your Indexes when not needed is that this operation will purge portions of your cache so it can have sufficient room to perform the reorg within memory. Reorgs aren't as destructive to your cache as Rebuild operations, but they will reduce your Page Life Expectancy (PLE) and potentially purge useful data that you will have to re-read from disk (e.g. increased I/O operations). Additionally, Reorg operations do NOT update Index Statistics. Updated Statistics do more to produce optimal executions plans than defragmented indexes, so if you're going to mindlessly run a nightly operation I would say Updating Stats is the better choice over Reorging instances.

The right answer though is to have a solution check your White Space thresholds and reorg/rebuild indexes when needed. Have another process check your statistics and update when needed. There are a few solutions out there, such as Ola's maintenance solution, Minionware's Reindex solution, etc. But I often find myself customizing solutions to fit my needs.

2

PS: John Eisbrener's answer is excellent advice to which, I would only add that, one reason you might not run this regularly, is that reorg/rebuild generates transaction log. Of course, the same is true of any major data operation.

If you are running a log-shipped environment, it might take significantly longer to ship that period's log-backup, which might have a (not always proportional) impact on your recovery-point objective.

I have certainly seen instances where a 5 minute index reorg has generated x gigabytes of log in the next backup, which has taken y minutes to transfer and restore, where y > RPO. Thankfully, so far, I've not had an actual DR issue during those y minutes, so we've always been OK, but it's not fun receiving all those LSAlert emails (default every 2 mins) because either the primary hasn't backed up and/or the secondary hasn't restored in a while.

Also, if you are running on a tight-for-storage environment or high cost-per-IO environment, the additional data might tip cost-tier thresholds, max your available storage or throw out your nightly offsite backup window.

Some people might argue that you shouldn't have to worry about such things, and that you should only care about how well the database is performing, but I see cost and recoverability as just additional dimensions in the n-dimensional set of 'good states' your DB needs to be in to be useful.

As always, it depends entirely on the logical or physical constraints in your environment and/or the needs and politics of your organisation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.