James
October 13, 2016 8:34 am
That was an awesome video. If i see no performance issues, is it a best practice to rebuild indexes daily? I am currently using Ola Halengren’s script.
Loading...
Reply
Nic Neufeld
October 13, 2016 8:50 am
Great post! A topic dear to my heart as I recall some previously-frequent on-call wakeups due to drives filling up during index maintenance…
One thing to consider with rebuilds on large indexes is data file space. While the reorg will just do its work on a sliding window of the original object, if you’re rebuilding a 300GB index you’ll need the whole original and the new structure coexisting at least temporarily while it is built, so if you’re tight on drive space, it can be difficult to do a non-partitioned rebuild.
Another thing I’ve noticed, if you use the old recommendation from Microsoft that Ola Hallengren implements by default…the >5%, reorg, >30%, rebuild…you may find that many of your large indexes never get rebuilt anyway. Most really huge tables tend to be less volatile than the smaller ones and if you’re reorging every night as it trips over 5% you’re not likely to see it jump from under 5% to 30% very often. Still, I have a 100GB table in a vendor database that fragments to 99%, and 70% page free space, almost instantly…thank you, GUID clustering key. We just let it fragment at this point, not worth it.
Also, clustered columnstores use the same syntax (ALTER INDEX … REBUILD / REORGANIZE) but they behave in completely different ways. REORGANIZE on a clustered columnstore will never deal with what we kind of understand to be the equivalent of fragmentation there…that is, the compressed rowgroups are essentially read-only and changes to the data are reflected by marking a row deleted in the deleted bitmap, and then if an update happens, the row is marked deleted and then a new row inserted into the delta store. So if you have a lot of updates or deletions on a columnstore, the only way to get your space back is to REBUILD, which is going to discard all the deleted rows at that point. REORGANIZE just launches the tuple mover which is how the delta store rows get compressed and put into a new rowgroup…but that happens every 5 minutes as I recall, so its not particularly useful to me.
Fun stuff…as long as it doesn’t wake you up at 2am!
Loading...
Reply
adrshen
October 13, 2016 1:53 pm
My all-time favor SQL mentor. 🙂 Thanks for the wonderful video and summing all relevant details.
Loading...
Reply
John G Hohengarten
October 13, 2016 3:47 pm
Another option to consider is instead of rebuilding or reorganizing indexes… can you get away with just updating statistics, either with a full scan or sample size? You might still see performance gains from simply updating statistics instead of doing the painful full REBUILD or less-painful-but-still-has-some-pain REORGANIZE on the large table.
Also, was this a typo or intentional pun (I put [brackets] around the word in question)?
“Index rebuilds have a [log] going for them, but they also have some drawbacks”
I’m assuming you meant “lot” going for them. Otherwise, great pun!
Loading...
Reply
John G Hohengarten
October 13, 2016 3:49 pm
I forgot to mention that doing statistics updates (in lieu of rebuild/reorg) would likely cost you less I/O and log writes than a rebuild / reorg would cost.
Loading...
Reply
Nic Neufeld
October 14, 2016 6:12 am
I would say the benefits of this approach depend upon your objectives…just updating stats will maintain your statistics, but it won’t deal with index fragmentation at all. If that’s something you’re willing to accept it might be fine, and it will indeed use a lot less I/O, particularly lean on writes. However, you don’t defragment indexes in order to update statistics, that just happens to be an additional benefit of a rebuild. If your stats are stale but your index is not heavily fragmented, yes, a stats update is much better than a rebuild…
In many cases you can accept leaving index fragmentation in place as the “least bad” of the options, but bear in mind the downsides…if you have a heavily fragmented index due to page splits, you could have (as I have on one table) 70% of every page being blank white space. So this table is three times as large as it would have been if defragmented…it requires three times as much I/O to scan, three times as much space to store in the buffer cache, etc. So you can pay a steep price in storage, memory, and I/O if you let fragmentation run completely wild.
Just my 2c!
Loading...
Reply
Mike F
October 14, 2016 1:56 pm
“Index rebuilds have a log going for them” …
Noticed a typo… It seems to work though and is kind of humorous.
Loading...
Reply
Kendra Little
October 14, 2016 3:21 pm
Yeah, I kind of like it 🙂
Loading...
Reply
John McCormack
October 24, 2016 2:55 am
Great video Kendra.
I have a very similar situation and your advice was really helpful.
Loading...
Reply
Gorden Trutt
November 17, 2016 7:43 am
I dont see the video of your posts? only the text – any suggestions? for instance, right under: If you’re short on time, scroll down: an article with all the content is below the 23 minute video.
is blank space
Loading...
Reply
Kendra Little
November 17, 2016 9:08 am
Oh, interesting! Do you have javascript disabled, or is perhaps access to YouTube blocked for you?
Loading...
Reply
Gorden
November 21, 2016 8:49 am
my bad. all i had to do was click an icon in the URL! I swear I never saw that b/4 but I’m on high alert now! :^)
Thank you again for all your help!
Loading...
Reply
Jimmy M
October 24, 2017 12:55 am
Thanks Kendra. This concisely sums up and reaffirms so many of the woes which have led us to exclusively REORG our large indexes (weekends only)! I have a burning question, however: Thinking about an index reorganize that doesn’t complete, and that defragments only about 50% of the fragmented pages before we kill it at the end of our maintenance window ….. would this leave one end of the table at, say, 1-5% fragmented and the other end of the table still heavily fragmented? Would it be better to fully defragment one large index, picking up from last weekend’s unfinished reorg, before moving onto this weekend’s newly identified, heavily fragmented indexes again? Or is the fragmentation in a partially reorganized, 35% fragmented index just as random as an index that has become 35% fragmented through page splits over time?! Many thanks in advance.
Loading...
Reply
Kendra Little
October 27, 2017 9:07 am
Hi Jimmy,
I am doing a free webcast on Nov 16 on defraying indexes (https://sqlworkbooks.com/webcasts/). One of the things I wanted to cover was why I’m excited about the new resumable index rebuild operation in 2017, and compare restarting it with restarting an alter index reorganize— which sounds like it matches up with your question! I’m going to put together some demos which hopefully will shed some light on your question. So stay tuned 🙂
Kendra
Loading...
Reply
Index Fragementation Still Matters | Data FLowe Solutions – Rick Lowe
February 5, 2018 8:28 am
[…] Do we rebuild or reorganize? That’s not something I’ll dig into in this post. In a nutshell, reorganizing an index simply redistributes data among the pages already allocated to the index while rebulding is a more drastic operation that actually rebuilds the index. Which makes more sense depends on many factors including SQL Server edition, whether or not index can be offline, whether or not we need to be able to interrupt the command, etc etc. A great post by Kendra Little on some of these issues can be found here. […]
Loading...
Reply
Poornima
September 8, 2018 10:15 am
Kendra,
Thank you for this post. Good information. Clarified some doubts I had before starting the rebuild and reorg of Terabits tables. I am wondering if there is enough time in the maintenance window, instead of rebuilding the index and causing the log growth, would it be better to drop the existing one and build the index?
Loading...
Reply
Artyom
September 13, 2019 10:34 pm
Kendra thank you for your article.
What do you thing about locking STATS by index reorganize. In my memory index reorganize lock exclusive metadata – statistics and queries who’s plan started to compile in this lock is waiting to grant shared lock to object Stats. whoh do you troubleshoot this moment?
Loading...
Reply
Kendra Little
September 18, 2019 6:38 am
I’ve never personally seen this cause blocking. Do you have a script or a way to reproduce the issue outside of your production environment?
Comments migrated from WordpressJames October 13, 2016 8:34 am That was an awesome video. If i see no performance issues, is it a best practice to rebuild indexes daily? I am currently using Ola Halengren’s script.
Loading...
Reply Nic Neufeld October 13, 2016 8:50 am Great post! A topic dear to my heart as I recall some previously-frequent on-call wakeups due to drives filling up during index maintenance…
One thing to consider with rebuilds on large indexes is data file space. While the reorg will just do its work on a sliding window of the original object, if you’re rebuilding a 300GB index you’ll need the whole original and the new structure coexisting at least temporarily while it is built, so if you’re tight on drive space, it can be difficult to do a non-partitioned rebuild.
Another thing I’ve noticed, if you use the old recommendation from Microsoft that Ola Hallengren implements by default…the >5%, reorg, >30%, rebuild…you may find that many of your large indexes never get rebuilt anyway. Most really huge tables tend to be less volatile than the smaller ones and if you’re reorging every night as it trips over 5% you’re not likely to see it jump from under 5% to 30% very often. Still, I have a 100GB table in a vendor database that fragments to 99%, and 70% page free space, almost instantly…thank you, GUID clustering key. We just let it fragment at this point, not worth it.
Also, clustered columnstores use the same syntax (ALTER INDEX … REBUILD / REORGANIZE) but they behave in completely different ways. REORGANIZE on a clustered columnstore will never deal with what we kind of understand to be the equivalent of fragmentation there…that is, the compressed rowgroups are essentially read-only and changes to the data are reflected by marking a row deleted in the deleted bitmap, and then if an update happens, the row is marked deleted and then a new row inserted into the delta store. So if you have a lot of updates or deletions on a columnstore, the only way to get your space back is to REBUILD, which is going to discard all the deleted rows at that point. REORGANIZE just launches the tuple mover which is how the delta store rows get compressed and put into a new rowgroup…but that happens every 5 minutes as I recall, so its not particularly useful to me.
Fun stuff…as long as it doesn’t wake you up at 2am!
Loading...
Reply adrshen October 13, 2016 1:53 pm My all-time favor SQL mentor. 🙂 Thanks for the wonderful video and summing all relevant details.
Loading... Reply John G Hohengarten October 13, 2016 3:47 pm Another option to consider is instead of rebuilding or reorganizing indexes… can you get away with just updating statistics, either with a full scan or sample size? You might still see performance gains from simply updating statistics instead of doing the painful full REBUILD or less-painful-but-still-has-some-pain REORGANIZE on the large table.
Also, was this a typo or intentional pun (I put [brackets] around the word in question)? “Index rebuilds have a [log] going for them, but they also have some drawbacks” I’m assuming you meant “lot” going for them. Otherwise, great pun!
Loading... Reply John G Hohengarten October 13, 2016 3:49 pm I forgot to mention that doing statistics updates (in lieu of rebuild/reorg) would likely cost you less I/O and log writes than a rebuild / reorg would cost.
Loading... Reply Nic Neufeld October 14, 2016 6:12 am I would say the benefits of this approach depend upon your objectives…just updating stats will maintain your statistics, but it won’t deal with index fragmentation at all. If that’s something you’re willing to accept it might be fine, and it will indeed use a lot less I/O, particularly lean on writes. However, you don’t defragment indexes in order to update statistics, that just happens to be an additional benefit of a rebuild. If your stats are stale but your index is not heavily fragmented, yes, a stats update is much better than a rebuild…
In many cases you can accept leaving index fragmentation in place as the “least bad” of the options, but bear in mind the downsides…if you have a heavily fragmented index due to page splits, you could have (as I have on one table) 70% of every page being blank white space. So this table is three times as large as it would have been if defragmented…it requires three times as much I/O to scan, three times as much space to store in the buffer cache, etc. So you can pay a steep price in storage, memory, and I/O if you let fragmentation run completely wild.
Just my 2c!
Loading... Reply Mike F October 14, 2016 1:56 pm “Index rebuilds have a log going for them” …
Noticed a typo… It seems to work though and is kind of humorous.
Loading... Reply Kendra Little October 14, 2016 3:21 pm Yeah, I kind of like it 🙂
Loading... Reply John McCormack October 24, 2016 2:55 am Great video Kendra.
I have a very similar situation and your advice was really helpful.
Loading... Reply Gorden Trutt November 17, 2016 7:43 am I dont see the video of your posts? only the text – any suggestions? for instance, right under: If you’re short on time, scroll down: an article with all the content is below the 23 minute video.
is blank space
Loading... Reply Kendra Little November 17, 2016 9:08 am Oh, interesting! Do you have javascript disabled, or is perhaps access to YouTube blocked for you?
Here is a direct link to the video: https://www.youtube.com/watch?v=fu6DGsDYnKI
Loading... Reply Gorden November 21, 2016 8:49 am my bad. all i had to do was click an icon in the URL! I swear I never saw that b/4 but I’m on high alert now! :^)
Thank you again for all your help!
Loading... Reply Jimmy M October 24, 2017 12:55 am Thanks Kendra. This concisely sums up and reaffirms so many of the woes which have led us to exclusively REORG our large indexes (weekends only)! I have a burning question, however: Thinking about an index reorganize that doesn’t complete, and that defragments only about 50% of the fragmented pages before we kill it at the end of our maintenance window ….. would this leave one end of the table at, say, 1-5% fragmented and the other end of the table still heavily fragmented? Would it be better to fully defragment one large index, picking up from last weekend’s unfinished reorg, before moving onto this weekend’s newly identified, heavily fragmented indexes again? Or is the fragmentation in a partially reorganized, 35% fragmented index just as random as an index that has become 35% fragmented through page splits over time?! Many thanks in advance.
Loading... Reply Kendra Little October 27, 2017 9:07 am Hi Jimmy,
I am doing a free webcast on Nov 16 on defraying indexes (https://sqlworkbooks.com/webcasts/). One of the things I wanted to cover was why I’m excited about the new resumable index rebuild operation in 2017, and compare restarting it with restarting an alter index reorganize— which sounds like it matches up with your question! I’m going to put together some demos which hopefully will shed some light on your question. So stay tuned 🙂
Kendra
Loading... Reply Index Fragementation Still Matters | Data FLowe Solutions – Rick Lowe February 5, 2018 8:28 am […] Do we rebuild or reorganize? That’s not something I’ll dig into in this post. In a nutshell, reorganizing an index simply redistributes data among the pages already allocated to the index while rebulding is a more drastic operation that actually rebuilds the index. Which makes more sense depends on many factors including SQL Server edition, whether or not index can be offline, whether or not we need to be able to interrupt the command, etc etc. A great post by Kendra Little on some of these issues can be found here. […]
Loading... Reply Poornima September 8, 2018 10:15 am Kendra,
Thank you for this post. Good information. Clarified some doubts I had before starting the rebuild and reorg of Terabits tables. I am wondering if there is enough time in the maintenance window, instead of rebuilding the index and causing the log growth, would it be better to drop the existing one and build the index?
Loading... Reply Artyom September 13, 2019 10:34 pm Kendra thank you for your article. What do you thing about locking STATS by index reorganize. In my memory index reorganize lock exclusive metadata – statistics and queries who’s plan started to compile in this lock is waiting to grant shared lock to object Stats. whoh do you troubleshoot this moment?
Loading... Reply Kendra Little September 18, 2019 6:38 am I’ve never personally seen this cause blocking. Do you have a script or a way to reproduce the issue outside of your production environment?
Loading...