Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.

We have been running Ola Hallengren's index maintenance scripts weekly, and it runs for several hours each time. Based on the fragmentation threshold, the script will either reorganize or reindex an index. We have observed that during reindexing, the log files get huge which leads to an excessive consumption of bandwidth during the log shipping.

Then comes an article from Brent Ozar in which he says to stop worrying about SQL indexes:

Your hard drives are shared with other servers that are also making drive requests at the same time, so the drives will always be jumping all over the place to get data. Defragging your indexes is just meaningless busy work.

Googling this question leads to varying opinions, most supported with arguments that seem too brief or weak. Our tentative plan is to adjust the fragmentation threshold in our maintenance script so that it reorganizes much more often than it reindexes.

What is the final verdict? Is it worthwhile to defrag SQL indexes on a SAN considering the burdens associated with running weekly maintenance jobs?

share|improve this question
add comment

3 Answers 3

up vote 9 down vote accepted

Defragmentation strategies help improve scan speed to/from disk.

The wide variety of opinions is because an environment's ideal defragmentation strategy should depends on many different factors. There are also multiple potential layers of fragmentation in play.

Saying that your databases are stored on a SAN isn't enough information. For example:

  • Are database files stored on separate physical RAID groups or the same RAID group? What other processes are active on that same device? Are your backup files ending up there, too? You may have to ask your SAN admin for this information, because it's not always transparent.

  • What are the access patterns for the databases? OLTP is generally random access, but sometimes an application is table-scan-happy and you can't change its behaviour (ISV app). Are the applications read-mostly, write-mostly, or somewhere in between?

  • Are there performance SLAs in play during a recovery/failover period?

Brent's post assumes there is one giant pool of storage and everything shares it. This means the physical disks are rarely idle, and hence most access is random. If that is your situation, then the advice applies, and I agree with it for the most part. While this type of strategy is much easier to manage, it isn't necessarily (a) what you have in your environment, or (b) what is the best solution for your environment.

If index maintenance is burdensome, consider doing it less aggressively, and/or amortize the cost over the week (i.e., run light maintenance once/day, instead of heavy maintenance once/week).

You can also turn on the SortInTempdb option to potentially reduce the amount of logging that takes place in the user databases.

share|improve this answer
    
Wow, thorough answer. It will probably take some time for me to do all of the research, but I don't doubt that you are leading me on the right track. Our current strategy is in fact to run maintenance less aggressively from both a rebuild and a reorg standpoint, I think I misstated that in the question. From there I'll do more research into the rest of the factors you mentioned. –  dev_etter Sep 6 '12 at 20:29
1  
@dev_etter: I only listed a few factors; there are many more. The main point is the very first sentence. If you keep that in mind when thinking through your environment, it will correctly direct your decision. Everything stems from that. (Also, all this assumes no SSDs are involved.) –  Jon Seigel Sep 6 '12 at 20:43
    
FWIW, I had overlooked something completely - the actual script in the job step (rather than the source) was configured to address every index that had a minimum fragmentation percentage of 1. I bumped that up to 15 and also bumped the rebuild threshold from 30 to 35. The job now runs in a little over 3 hours rather than 8. Your suggestion to be less aggressive was correct. My fault lied in the fact that I thought the job had already been implemented to be less aggressive. This approach is probably best for us, still touch and go but it already alleviated some pain. –  dev_etter Sep 14 '12 at 13:37
    
@JonSeigel I totally agree with this answer. In my travels I see most DBAs sharing a single pool, or at least an array at the same RAID level. I have had DBAs up at 3AM 24x7 just to defrag individual filegroups of 100+TB databases...and for what exactly? We had totally random IO at the disks and latency was 15ms. At that point I should just point to 15ms and tell the developers to leave me alone. –  ooutwire Jun 17 '13 at 15:42
add comment

Ideally, you should be reorganizing/reindexing for ONLY those indexes that need attention, otherwise you are wasting resources and potentially causing other problems.

You need to establish a performance baseline and whenever you make changes compare the performance change against the baseline to determine if your change is worth implementing.

share|improve this answer
    
Our immediate strategy is to do just that - we are going to tweak the settings for the minFragmentation and rebuildThreshold variables in this script: sqlfool.com/2011/06/index-defrag-script-v4-1 –  dev_etter Sep 6 '12 at 20:31
add comment

Okay, the question is about Database Indexes, which are a construct of a file or set of files. Reading the answers above would lead a person to believe that we are talking about fragmentation at the disk level and not the indexes inside of a file. These totally separate subjects.

The myopic approach here is will performance when retrieving data within and OLTP Database improve if Indexes are De-fragmented or Rebuilt. The answer is YES! However, it is important to note that disk fragmentation is also a factor.

Lowest "cost" overall? Do your Database Maintenance. Second lowest cost, detach the database, move it somewhere else, re-format your disks and follow best practices for Disk Partition Alignment http://msdn.microsoft.com/en-us/library/dd758814.aspx. Last but not least, use a 3rd party advanced defragmentor like Diskkeeper.

Keep in mind, this is ONLY recommended for NTFS type storage (e.g. Windows OS) and this is not an endorsement for any product nor am I affiliated with Condusiv Technologies or its subsidiaries.

share|improve this answer
2  
You probably want to avoid categorically saying "The answer is YES!" to problem spaces that have been discussed at length by other posters. While it may be true that sometimes the answer is "Yes", as Brent Ozar has shown in his blog post, that is not always the case. –  Max Vernon Mar 1 '13 at 18:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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