question

sbamaty avatar image
sbamaty asked

Rebuild Index based on empty index pages

If I want to reorganize indexes, I have an option to look at Index Fragmentation and decide whether to reorganize or not. I even use it to rebuild indexes based on Index Fragmentation but now I am in the situation where Index Fragmentation is below the threshold I specified. Because of that it is neither reorganizing nor rebuilding indexes. But the flaws with my logic is, reorganize kicks in before rebuild. When it reorganizes, it leaves breadcrumbs behind and does not delete the empty index pages. But when I rebuild indexes, it shrinks those index pages. So do we have a similar option for rebuilding indexes where I look at some stats and fire up rebuild?
indexes
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please share how you sort out whether you need a REORGANIZE or a REBUILD? Moreover, how you find out there are empty index pages?
0 Likes 0 ·
I look at Index Fragmentation using system view, sys.dm_db_physical_stats and checking fragment percent to decide whether the table needs REORG OR REBUILD. When I REORG heavily fragmented table(s), page_count column still shows same number of pages but when I do REBUILD, the page_count goes down and my query performs lot better than when I do REORG. I might not be looking at right thing....that is why I wanted some insights from experts. My dilemma is, if page_count column is the right column to look at, how do I decide how many pages each index should have because index pages varies based on amount of data you have in a table. I am trying to see if there is a something, some sort of function or option that tells me total number of pages index is using out of X number of pages in index. That way I can check if REBUILD is needed or not.
0 Likes 0 ·

1 Answer

·
Usman Butt avatar image
Usman Butt answered
I will try to address things bit by bit > checking fragment percent to decide whether the table needs REORG OR REBUILD This is fine. It is the recommended practice to decide it on basis of **avg_fragmentation_in_percent** > But the flaws with my logic is, reorganize kicks in before rebuild Can you please share the code for it? Generally, the threshold for index reorganize is, if avg_fragmentation_in_percent is >= 10% and <=30% and for rebuild it is >30%, but it may vary depending upon scenarios. See my last advice though. > When I REORG heavily fragmented table(s), page_count column still shows same number of pages but when I do REBUILD, the page_count goes down It is as expected. Reorganize works on the leaf level of the index, whereas rebuild is a recreation of the structure from the scratch, so the page compactness, page ordering, page fullness etc. would be catered in a much efficient manner. This is the trade off between keeping the index available in Reorganize and no access to the index in Rebuild. > and my query performs lot better than when I do REORG Is this the major point for asking this question? Anyhow, there could be many factors involved. Major factor could be the **statistics update**. Rebuild updates the statistics but Reorganize do not. Moreover, if you are facing Memory/IO pressure, then a more compact index and better read-ahead throughput could be more beneficial through index rebuild. But it all depends upon your data access patterns. Generally, the queries with scans see more benefits than singleton lookups in such scenario. > page_count column is the right column to look at The page_count can be used but not for this purpose. Rebuilding small indexes is generally of no use, so this column is generally used to filter smaller indexes. I would rather look for very low density pages i.e. a very very low value for avg_page_space_used_in_percent. But still only this column solely would not a give a clear indicator. I have tried to be very concise with my response as the internals of the index is a huge topic and even chapters of books are dedicated to the details. Last but not least, my recommendation would be not to come up with your customized solution. That is kind of re-inventing the wheel as the following two scripts are very comprehensive and authentic available with support available from 2005+. Not to mention these scripts are updated regularly. Explore any of them and I am quite sure you would find them very useful. [SQL Server Index and Statistics Maintenance (Ola Hallengren)][1] [AdaptiveIndexDefrag][2] (inspired by Michelle Ufford) [1]: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html [2]: https://github.com/Microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/usp_AdaptiveIndexDefrag.sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Usman for your detailed response. I have the code ready and doing testing on it to make sure it does what I expected it to do. I ended up looking at average fragment percent and page count to determine if I should do rebuild or reorg and it seems working but still need to do some more testing. Will reply to thread once I have it working.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.