Why Defragmenting Your Indexes Isn’t Helping with Brent Ozar
Why Database Administrators Stack Exchange recommends it
Database Administrators Stack Exchange users link it when answering questions about index rebuilds, fragmentation and fill factor, to argue that fragmentation is often overblown as a performance problem.
Recommended by 2 people across 2 different months, 2017 to 2022.
What it is
A GroupBy conference session in which SQL Server expert Brent Ozar explains why routine index defragmentation and fill factor tuning often do little for performance on modern hardware. He walks through the reasoning and then covers what to do instead of daily index maintenance jobs.
What people said
Recommends Sabin B · 2017-09 · Database Administrators Stack Exchange · on “Index manteinance: does the order of processing make a difference?”
Are you sure that fragmentation is your problem ? See this : `Why Defragmenting Your Indexes Isn’t Helping with Brent Ozar` [this video]
Recommends Marcin · 2022-01 · Database Administrators Stack Exchange · on “Constant rebuilding indexes to fix performance - why?”
It very depends on how database is used. If it is a static database (like archive) and you only read from it, then index rebuild is not important because data are not changed. But if database has a lot of insert/update/delete then rebuild might play an important role together with updating statistics. So there is no one answare. Here is interesting video from Brent Ozar about Index maintenance: [this video]
Cites Paul White · 2022-02 · Database Administrators Stack Exchange · on “Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns”
Does it make sense to remove CustomerTotalID completely and have CustomerNumber + AccountNumber be a PK with a NON-clustered index? It does make sense to remove the surrogate key, since you won't be using it. A primary key on the natural key (CustomerNumber, AccountNumber) should probably be clustered rather than nonclustered. The query optimizer will introduce a sort for large inserts if it makes sense from a cost point of view. With a nonclustered PK, you'd be inserting to the heap and a separate nonclustered index. Worrying about fill factors, rebuilding, and fragmentation in general is often overblown. See Why Defragmenting Your Indexes Isn’t Helping [this video] . If new (CustomerNumber, Acc