Ola's code is great but it doesn't help you if you use the current "Best Practice" (it's not) of blindly rebuilding indexes after you've exceeded 30% logical fragmentation. Even "Books Online" warns against it.
Here's the link where the supposed "Best Practice" was born... almost no one Reads'n'Heeds the warning tip highlighted in light green just below that "recommendation".
Here's the warning tip from that link...
These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. The performance benefit is less noticeable for indexes that are used primarily for seek operations. Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.
What I've found the hard way is that a whole lot of people (including me at the time up until 18 Jan 2016) never realize that the day after index maintenance, they suffer a whole lot of slowdowns in the form of some very significant blocking. There are several reasons for this...
- REORGANIZE doesn't do what you think it does even though it's documented (but poorly explained when it comes to the impact) as to what it does and does not do to an index. It's also not the tame little kitty that a whole lot of people make it out to be (especially when it comes to log file use) and, because of the way it operates, removes free space at the most critical time when it is needed and does almost nothing to add free space in the critical area between the Fill Factor and 100% page fullness. The effect delivered is that REORGANIZE "conditions" a whole lot of indexes in such a fashion as to perpetuate page splits, which is both logical and physical (page density) fragmentation at its worst. This is why a lot of people mistakenly say that GUIDs cause a lot of fragmentation. They can actually be setup to run for weeks and months without any page splits at all... not even good ones. If, however, you use REORGANIZE on such indexes, you end up with perpetual pages splits, instead. It also affects other types of indexes the same way. I don't use it even when all I have is the "Standard Edition".
- The other thing is that most people don't actually analyze what's going on with their indexes. The either leave the default Fill Factor of "0" (which is the same as 100) or they assign Fill Factors that not only may not help but may actually harm the system both for performance and space used. They also don't realize that waiting until 30% fragmentation is actually waiting for the worst to happen insofar as page splits. For example, for GUIDs and other evenly distributed index, it's great to lower the Fill Fact to 91, 81, of even 71 because that will provide weeks or even months of fragmentation free inserts and some updates but as soon as you have 1% logical fragmentation, you need to do a REBUILD (never REORGANIZE for this) immediately because the only way for such an index to start fragmenting is for ALL the pages are close to 100% full and on the verge of fragmenting.
- A lot of people also don't realize that "ExpAnsive Updates" are what is actually causing the fragmentation nor do they realize that inserts will always try to fill pages to 100%. In only one very rare instance, INSERTs don't care about the Fill Factor and neither do UPDATES. If you're getting fragmentation on an ever-increasing index, lowering the Fill Factor might only be wasting space and doing nothing at all to prevent fragmentation. That normally occurs when people do INSERTs to such an index and then UPDATE the rows shortly after ("ExpAnsive" Modified_By columns are the normal but not the only reason). Since the INSERTs went into at 100% (regardless of Fill Factor), there's no room left for "ExpAnsive Updates" and you immediately get massive fragmentation during the UPDATEs.
There's a whole lot more to be concerned with but way too much for a forum post. The bottom line is that if you follow the current perception of the 5/30 "guidlines" actually being a "Best Practice", it's extremely likely that no matter what code you use to accomplish it, you're doing more harm than good.
I'm writing a Stairway on the "Black Arts" of Index Maintenance with proofs for all that I claim but it's going to take a while to write it and get it out there. I have given several presentations at various SQLSaturdays on the subject to try to begin to make people aware of the problems and that the current "Best Practices" actually aren't... they're guidelines to start with and, as it says in "Books Online", they're not actually very good and can cause more damage than doing no index maintenance other than occasional maintenance to "recover space" according to page density (page fullness).
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)