Why Defragmenting Your Indexes Isn’t Helping with Brent Ozar

[@nezbrun872]

Been doing SQL Server since 1991. I still have regular uphill battles with colleagues and clients regarding index rebuilds. I can't ever remember seeing any significant benefit of doing a rebuild that appropriate stats updates wouldn't fix on their own. Often these rebuilds are done on tables with highly transient data during a maintenance window, after data processing has completed, so the value is negligible, other than to exercise the data center aircon. The only good news is that thanks to modern storage hardware & multiple cores, and presumably better algorithms inside SQL Server itself such as parallelism, the speed of doing a rebuild is _much_ faster than it once was.

[@matthanson1325]

I have a slight quibble about the "Monitoring this stuff" slide about 6 minutes in. Brent says that finding both the internal and external fragmentation involves scanning all of the pages. If I'm not mistaken, that's true of internal fragmentation, but external fragmentation can be measured without going all the way down to the leaf level pages (i.e. LIMITED mode). It's a bit beside the point here because the overall thrust of the presentation is that we shouldn't be worrying so much about external fragmentation anyway, but if you were going to worry about it, you might as well do so a little more efficiently :)

[@Youhavebeenprogrammed]

B tree indexes are self balancing right? So this is also a reason not to rebuild the index... Am I correct?

[@JeremyMitts]

INTs don't go to a bajillion, Brent! they only go up to a little over 2.1 billion! (some of us learned that the hard way.)

[@christiaan6488]

How can you rebuild data pages please? I have exact copies of 2 tables, same database, same data types, same numbers, one is 1.2gig one is 1.6gig in size. I am trying to understand why, and how to get the storage to the minimum.

[@GroupbyOrg]

For questions, head to a Q&A site like dba.stackexchange.com.

[@christiaan6488]

I rebuilt the indexes and... perfect match. Thank you for your help.

[@mavenchows]

15:27

[@fakecorelevel]

this is a stolen video

[@jeffmoden4332]

Stolen from who?