70

When should I rebuild the indexes in my relational database (SQL Server)?

Is there a case for rebuilding indexes on a regular basis?

1

7 Answers 7

49

At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly. However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.

This presents the question: when does an index require to be rebuilt or reorganized? Rolando touched on this nicely. Again, I risk being extremely broad. An index requires maintenance when the fragmentation level adversely affects performance. This level of fragmentation could vary based on the size and composition of the index.

Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. If an index contains less than 100 pages, I will perform no maintenance.

If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS, as this is taken care of by the REBUILD. Remember though that a rebuild only updates the statistics object directly associated with the index. Other column statistics will need to be maintained separately.

This answer is really just a long way to say: Yes, you should do routine index maintenance, but only on the indexes that need it.

2
  • Ah... Be really careful. Most of what you say is absolutely true. However, it's "Death by Defragmentation" to use REORGANIZE. It doesn't work the way people think it works. It removes freespace when it's most needed and does nothing to add freespace above the Fill Factor. It's all documented in the Microsoft documentation but no one reads it correctly because they're following the f or 10/30 thing you speak of, which was never meant to be a "Best Practice". Even Paul Randal says to, and I quote, "...take those numbers with a grain of salt and don't treat them as absolute". Link follows:
    – Jeff Moden
    Jul 25, 2021 at 13:44
  • sqlskills.com/blogs/paul/… The unspoken words are not to use REORGANIZE except to compress LOBs (and on other strange case) and then to a REBUILD right after that.
    – Jeff Moden
    Jul 25, 2021 at 13:52
21

When should I rebuild the indexes in my relational database (e.g. SQL Server)?

You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table.

Is there a case for rebuilding indexes on a regular basis?

So what if your indexes are becoming fragmented on a regular basis due to regular activity? Should you schedule regular rebuilds? How often should they run?

Tom Kyte, in this classic Ask Tom thread, recommends:

The time lag between index rebuilds should be approximately FOREVER.

...

Don't know how to say it better -- the index wants to be big and fat with extra space. It is on a column you update -- moving the index entry from place to place in the index. One day the row has a code of "A", the next day the code is "G", then "Z" then "H" and so on. So the index entry for the row moves from place to place in the index. As it does so, it needs space -- will, if the space isn't there, we split the block into two -- and make space. Now the index is getting fat. Over time the index is 2-3x the size it was when you started and is "half or more empty" But that is OK since you move rows around. Now when we move the rows around, we no longer have to split blocks to make room -- the room is already available.

Then you come along and rebuild or drop and recreate the index (which have the same effects -- just the rebuild is "safer" -- doesn't stand a chance of losing the index and can be faster as the index can be rebuilt by scanning the existing index instead of scanning the table and sorting and building a fresh index). Now, all of that nice space is gone. We start the process of splitting the blocks all over again -- getting us right back to where we started.

You saved no space.

The index is right back the way it was.

You would just be wasting your time to rebuild it again causing this vicious cycle to repeat itself.

The logic here is sound, but it is biased against a read-heavy load profile.

A "fat" index (i.e. one with lots of gaps) does indeed keep a good amount of room for new and moved rows, thus reducing page splits and keeping your writes speedy. However, when you read from that fat index you'll have to read more pages to get the same data because you're now sifting through more empty space. This slows your reads down.

So, in read-heavy databases you want to regularly rebuild or reorganize your indexes. (How often and under what conditions? Matt M already has a concrete answer to this question.) In databases that experience roughly equivalent read and write activity, or in databases that are write-heavy, you are likely harming your database's performance by rebuilding indexes regularly.

1
  • Yeah, the recommendation from the quote is a bit too radical. :) There are many cases where indexes are heavily read-biased, where it's a waste of space and CPU to keep it fragmented. SQL Server also does caching on page level, so an index that is 2x in size will take up 2x much RAM (or alternatively, half of the pages won't fit the cache).
    – vgru
    Oct 3, 2022 at 11:05
14

As noted in the accepted answer from Matt M, a common rule of thumb is that indexes that are over 30% fragmented should be rebuilt.

This query will help you find how many indexes you have that are over 30% fragmented (when you have some, you should rebuild them):

SELECT DB_NAME() AS DBName,
       OBJECT_NAME(ind.object_id) AS TableName,
       ind.name AS IndexName,
       indexstats.index_type_desc AS IndexType,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages,
       SUM(p.rows) AS Rows 
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
         INNER JOIN sys.indexes AS ind ON (    ind.object_id = indexstats.object_id
                                           AND ind.index_id = indexstats.index_id)
         INNER JOIN sys.partitions AS p ON (    ind.object_id = p.object_id
                                            AND ind.index_id = p.index_id)
 WHERE indexstats.avg_fragmentation_in_percent > 30
 GROUP BY
       OBJECT_NAME(ind.object_id),
       ind.name,
       indexstats.index_type_desc,
       indexstats.avg_fragmentation_in_percent,
       indexstats.fragment_count,
       indexstats.avg_fragment_size_in_pages 
 ORDER BY indexstats.avg_fragmentation_in_percent DESC
0
11

Most people rebuild them on a regular basis so that they never get to fragmented. When you need to rebuild them is based on how quickly they get fragmented. Some indexes will need to be rebuilt often, others basically never. Check out the script the SQLFool put together that handles a lot of figuring this stuff out for you.

2
  • Just an FYI to the dear readers that SQLFool's script hasn't been updated in > 5 years so it might not incorporate the newest bells and whistles when it does its thing. Dec 14, 2017 at 21:22
  • 1
    In fact, I believe the last time I checked the site (can't reach it now (may not be a good sign)), Michelle was no longer actively working in SQL Server, and had no active intent to work on the script further. If it's working for you, great! For new installations, consider Ola Hallengren's scripts: I've used both, and it's not a hard transition.
    – RDFozz
    Dec 14, 2017 at 22:21
5

When should I rebuild indexes?

When the index fragmentation percentage is more than 30%.

Is there a case for rebuilding indexes on a regular basis?

There is no such case, but in general, doing Index Maintenance once in a week, over the weekend is the best practice to keep the environment stable.

I would recommend using maintenance scripts from Ola Hallengren (best maintenance scripts), customize the scripts based on your environment and schedule them to run over the weekend.

https://ola.hallengren.com/

Note: Please don't forget to update stats after rebuilding indexes, because rebuilding indexes doesn't update all the statistics.

1
  • 4
    I am pretty sure your note is incorrect. An index rebuild does update the statistics. An index reorganize does not. Although it only updates the statsistics for the objects related to the index, not all statistics. That being said, I recommend updating statistics frequently as well to reduce the chance of slowdown due to parameter sniffing and poor query plans due to outdated statistics.
    – bmg002
    Oct 17, 2016 at 17:22
2

As with most things in IT, it depends. What problem are you trying to fix by doing rebuilding indexes? Can you show that it actually fixes the problem? If so, then tweak the numbers until you find the least amount of maintenance you need to do to fix the problem.

If it doesn't fix the problem, or the reason you are doing it is just to appease some metric that you monitor because it might make things better, then all you are doing is burning CPU and IO and possibly making your problem worse.

There's an argument that fixing the fragmentation will not make any difference to your server, so is it worth doing regularly at all?

https://www.brentozar.com/archive/2017/12/index-maintenance-madness/

http://brentozar.com/go/defrag

0

An effect that seems to happen with indexes on tables that are frequently updated or replaced, for example tables containing "logs from last 30 days", is that it can waste huge amounts of space.

The many updates/replacements leads to heavy index fragmentation, and that locks up space within the database. I.e. the database table and indexes may contain 2GB of actual data but an additional 8GB of effectively unused spaced is allocated by the indexes, for a total file size of 10GB. These 8GB are NOT considered "unused" so can therefore not be used by anything else in the database and can also not be freed by a database shrink. (If you are on SQL Server Express, this means your database hits the 10GB max size limit even though you have only 2GB (for example) of actual data in tables and indexes, and it will therefore not be possible to insert additional records.)

Reorganizing or rebuilding indexes will cause the index to release this space so it can be used for newly inserted rows, or returned to the operating system using shrink (if that otherwise makes sense).

2
  • Every other answer on this page talks about fragmentation; why, in your view, fragmentation is "another" issue, in addition to fragmentation?
    – mustaccio
    Jan 25, 2023 at 21:05
  • @mustaccio, I could have phrased that better, but did you also read the second half of that sentence, plus the next sentence, where I talk about the massive effect it can have on database (file) size? That was the point I was making, as all the other answers seem to focus on the performance aspect. Jan 27, 2023 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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