Though well-done index maintenance is usually an iterative process, trial and assessment, I have two default modes for how I set up index maintenance tasks for a given database. I refer to them as “Aggressive” and “Relaxed”.
When I set up index maintenance tasks, I set them up to run as often as I can. I’m using Ola Hallengren’s IndexOptimize script, and I usually set it to run nightly, with a time limit dependent on what else goes on within that particular system. For example, on one server I have some backups that kick off at 8PM, and an ETL process that runs around midnight, so I set IndexOptimize to run at 6 PM, with a 90 minute time limit. That gives SQL a half hour to repopulate it’s buffer cache with data needed for queries or complete any running index tasks that may spill over the time limit. This seems to be a good setting for this system. It’s slightly different elsewhere.
The difference between what I consider aggressive, and more relaxed, is how I set the thresholds for fragmentation and index size. For a more aggressive plan, I would set those thresholds lower. For a more relaxed plan, I would set them higher. Much depends on both the usage and the performance of the system, but as a baseline, this is a decent place to start.
Lower thresholds mean that the indexes are going to be maintained more often, but the average fragmentation levels will remain lower. Maintenance jobs will tend to average run times that are a bit longer and use more available RAM. Higher thresholds mean that indexes would be maintained less often, and fragmentation levels would average a bit higher. However, you’ll have shorter running maintenance tasks on average, and more available RAM left for caching query-related data.
Where to set these thresholds depends on the individual system, of course, but here’s what I use as a rough guideline. Keep in mind for the following thresholds that “page count” refers to the number of 8k pages contained in the index, so a page count of 1500 is around 12 MB of data.
Aggressive: Page Count = 1500, Defrag at 5%, Rebuild at 15%.
Relaxed: Page Count = 4000, Defrag at 15%, Rebuild at 30%
So on a system that has lots of available RAM for cache, or smaller indexes, I may be more relaxed with my index maintenance, since many of the indexes will reside in cache and reading them from disk will be more rare. On a system that would have less RAM or larger indexes, I’ll want to be more aggressive with my index maintenance, since I’ll be reading data more often from disk than from cache. It will take more time and resources, but will pay off in better query performance.
On the topic of frequency, index maintenance should ideally be done as often as you can allow. It hurts nothing to do some maintenance on your databases as long as nobody is using them. However, if you’re trying to schedule index maintenance in a tight window in order to keep the database available to users, or run maintenance along with other resource intensive jobs, then setting the thresholds as I describe may alleviate some contention between index maintenance and other process. You will also find it more efficient than the “rebuild everything every time” approach.
Thanks for reading,