3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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 answerconcrete 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.

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.

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.

2 linked to referenced answer
source | link

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 answerconcrete 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.

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.

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
source | link

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.