index fragmentation basics
-
I see a lot of focus here on not reindexing at all - mostly because no one can prove that reindexing improves performance. I am not sure how that myth ever got started, because you don't perform any type of index maintenance to improve performance.
The goal for rebuilding or reorganizing your indexes is to manage limited resources. Whether that is storage - or memory - or IO. The one case where rebuilding an index *might* help an individual queries performance is when that query performs an index scan across a lot of pages. The read-ahead algorithm, contiguous pages and minimal wasted space per page will have some effect on the query performance - but will that impact be enough to counter the rebuild process itself?
I see all kinds of different numbers thrown out - and they are just guestimates (at best). If you have a clustered index - that has more than 90% logical fragmentation and an average page density of 58% taking up 1.6TB of space, then you really should rebuild that index to recover the 1.3TB of wasted space.
Even when you have 70% average page fullness - you need to review that table and how it is used and what type of index. If you have a clustered index that uses an identity column (for example) - with minimal updates to new rows - and you previously rebuilt the index at some point, than a 70% average page fullness could indicate a very low page density for the latest rows.
It might not...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:
I see a lot of focus here on not reindexing at all - mostly because no one can prove that reindexing improves performance. I am not sure how that myth ever got started, because you don't perform any type of index maintenance to improve performance.
Here's what that seriously mistaken concept originated along with some mumbo-jumbo under a title about "optimizing indexes". Some thing similar has existed for the last 23 years or so and the really poor wording convinced people (who didn't read or truly understand the fine print in note "1") that it's a "Best Practice".
They changed the information on the page that used to be on. Unfortunately, they just made things worse and they mistakenly say to use REORGANIZE.
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
You can see by the title of "Optimize index maintenance to improve query performance and reduce resource consumption" why people have made the mistake of thinking that index maintenance is a must. I think that everyone on this thread that's now saying (me included) had a similar plan that they ran in the past before they saw the proverbial light. Unfortunately, 98% of the world still believes in those conjectures and even more have no idea of how REORGANIZE actually works or doesn't work.
Also, they really didn't improve the wording on that thread. They only gave into the fact that the 5/30 method wasn't a best practice and now want you to evaluate every index using their "A/B" method. If every index of your database has thousands of indexes, people aren't going to do that. They'll fall back to the 5/30 plan that people on the burning band wagon have been pushing quite literally for decades.
Worse yet, while they correctly and technically describe what REORGANIZE does, it's written in such a fashion that leaves people thinking that it works like REBUILD does and it patently doesn't even come close to clearing out the area above the Fill Factor, which is needed on a lot of the indexes we deal with every day. It also does talk about "Expansive Updates" in the hot spot and how you need to fix that or ignore it until average page density starts to waste a lot of memory and disk space.
Shifting gears a bit, have YOU ever watched the presentation that I previously provided the link for on this thread? If not, you should. Among the rampant destruction of many index maintenance myths, it also demonstrates how REORGANIZE does and does not work and also shows how it perpetuates fragmentation and actually makes page-splits worse especially for "evenly distributed" indexes (like but certainly NOT limited to Random GUIDs).
I've also developed and use some prototype code at work that auto-magically decides what type of fragmentation is occurring on every rowstore index in a given database and assigns a Fill Factor that identifies the type. And, no where does is use REORGANIZE. I have found just one fragmentation type where it can help with page density without becoming the cause of fragmentation but I've got a lot more testing to do on that and an awesome but major tweak to keep people from assigning a Fill Factor on fragmenting indexes where it won't actually help.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply