A Tidy Database is a Fast Database: Why Index Management Matters

Question

A Tidy Database is a Fast Database: Why Index Management Matters

Phil Grayson

SSC Veteran

Points: 225
More actions
November 11, 2024 at 12:00 am

#4475056

Comments posted to this topic are about the item A Tidy Database is a Fast Database: Why Index Management Matters

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 1

This is a nicely written article, Phil. Thanks for taking the time to write it.

There are a few "gotcha's" because other people have published the same information for a very long time. A lot of people never took the time to write some demonstrations.

One of the big ones is the 5/30 fragmentation boundary recommendations that Microsoft carried literally for decades (it was 10/30 earlier). It was carried forward even after the guy who originally came up with the numbers said to "take them with a grain of salt" way back in 2009 and few listened.

Øhttps://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

A lot of people worked with MS an got those recommendations dropped from the MS documentation on the 20th of April, 2021. What they replaced it with is a whole lot more complex but at least they got rid of the original 5/30 thing.

Also, be real careful about using REORGANIZE on anything. It can use a whole lot more log file and other resources than what a REBUILD does even in the Full Recovery Model and. quite literally, can take days to REORGANIZE just one large index. It is NOT the quiet little kitten that so many advertise it to be. Further, when used on an evenly-distributed index such as ZipCodes, Telephone Numbers, CustomerIDs, EmployeeIDs, and Random GUIDs, REORGANIZE will actually perpetuate fragmentation and make page splits much worse because it does NOT create empty space above the Fill Factor. In fact, REORGANIZE does it's best to remove any free space from pages that have a page density < the Fill Factor. Think of all those nice "0/100" default Fill Factor indexes that you have that are fragmenting. 😀

--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)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 2

Thank you for the article and taking the time to put it together. I do have a couple of notes :

"Use a Staging Environment: Replicate your production environment to safely test changes." > This can be a challenge if you operate a 24/7 busy website as your production envirnoment. So sometimes you have to rely on experience and Query store to (to compare before and after).
Fragmentation maintenance is not needed with SSDs. > It theory this seems to make sense but in a busy server I have seen highly fragmentation tables be the ones that cause issues with plans regressing (on SSDs). Mainting non sequential leading key having indexes at a lower fragmentation seems to have, by empirical evidence, kept regression issues at bay. It can be argued that this is due to stats updating but for some time I did follow the 5/30 rule and re-org were being done. Do you have a resource to Microsoft that speaks to SSD's and fill factor ?

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 3

MMartin1 wrote:

Mainting non sequential leading key having indexes at a lower fragmentation seems to have, by empirical evidence, kept regression issues at bay.

Have you tried rebuilding statistics instead of indexes?

--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)