Frequently fragmented indexes

Question

Frequently fragmented indexes

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
March 30, 2010 at 9:27 am

#213219

I have noticed that a number of nonclustered indexes in my database are getting frequently (almost daily) fragmented (fragmentation > 10%).
Because of the size of the underlying tables, defragmenting these indexes puts a heavy load on my transaction log, causing more problems downstream.
The nightly defragmenting job also requires significant server resources as a result and takes a while to complete.
I want to find ways of reducing the amount of fragmentation of these indexes and, therefore, the frequency at which they need to be rebuilt/reorganized.
I can reduce their fill factors, but is there anything else I should look into?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are the indexes used for range scans, or for selecting single rows?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

GSquared (3/30/2010)
Are the indexes used for range scans, or for selecting single rows?

Good question; I guess it points to a 2nd strategy, finding out which of these "problem" indexes are actually being used for seeks.

I have monitoring set up for this, so I will need to analyze the data I have accumulated, but, supposing these indexes are being used optimally for data retrieval, is there anything else I should be looking into, other than reducing their fill factors?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

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

Marios Philippopoulos (3/30/2010)
I have noticed that a number of nonclustered indexes in my database are getting frequently (almost daily) fragmented (fragmentation > 10%).
Because of the size of the underlying tables, defragmenting these indexes puts a heavy load on my transaction log, causing more problems downstream.
The nightly defragmenting job also requires significant server resources as a result and takes a while to complete.
I want to find ways of reducing the amount of fragmentation of these indexes and, therefore, the frequency at which they need to be rebuilt/reorganized.
I can reduce their fill factors, but is there anything else I should look into?

Which edition of SQL Server do you have? Standard? Enterprise? or??

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

I'm on SQL 2008 SP1, Enterprise Ed.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

10% fragmentation seems like an awfully low threshold. I've been in environments where we didn't touch it unless it was 50% (1TB plus databases). Then we would defrag conditionally and only groups of tables on certain nights.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

CirquedeSQLeil (3/30/2010)
10% fragmentation seems like an awfully low threshold. I've been in environments where we didn't touch it unless it was 50% (1TB plus databases). Then we would defrag conditionally and only groups of tables on certain nights.

According to this link, the min threshold could be as low as 5%, although that is stated as a guideline and not as something written on stone:

http://technet.microsoft.com/en-us/library/ms189858.aspx

50% fragmentation seems too high a threshold to me.

I guess it all depends how indexes are being used; if is for scans, then fragmentation will affect performance. If it is for seeks, then fragmentation will not be a factor (I recall reading about this somewhere).

So I think this is another criterion for deciding whether to defragment (reindex/reorganize) a certain index:

if it is found that this index is being used a lot in scans, then defragment it once fragmentation reaches a certain threshold; if, on the other hand, it is being used mostly for seeks, then defragmenting it will not have much of an effect on performance.

I have index-usage data collected over several months, and I will cross-reference that data with my frequently fragmented indexes to decide which of these indexes will need to be defragmented on a regular basis and which will not.

I think this is what GSquared was alluding to earlier...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

That's exactly what I was refering to.

Have you considered partitioning the index? That can help isolate the fragmentation to only certain partitions. That will make range scans on the non-fragmented partitions more efficient.

Another thing to look into is, would the index be as effective for queries with a leading edge that won't fragment as heavily?

If none of those will work, then adjusting the fill factor is probably what you're left with.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Marios Philippopoulos (3/30/2010)
I'm on SQL 2008 SP1, Enterprise Ed.

Since you have the Enterprise Edition, I'd consider partitioning the table and the indexes so they can be more easily managed. In fact, if you base the partitions on relative "age" of a row (ie: not updated much or not recently inserted), the "older" partitions will simply not need any maintenance because they won't become fragmented and neither will the indexes that serve them.

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

Jeff Moden (3/31/2010)
Marios Philippopoulos (3/30/2010)
I'm on SQL 2008 SP1, Enterprise Ed.
Since you have the Enterprise Edition, I'd consider partitioning the table and the indexes so they can be more easily managed. In fact, if you base the partitions on relative "age" of a row (ie: not updated much or not recently inserted), the "older" partitions will simply not need any maintenance because they won't become fragmented and neither will the indexes that serve them.

Thanks, I'll give partitioning a try.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 10

if you have enough resources and enterprise edition you should be able to do the operation online and only rebuild if you need to. I would start with looking at what hits the table and see how often it is actually seeking on the index and how much time it is spending updating it. there may be a better index that could be created or that index may not be needed at all

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 11

JDixon-385826 (4/7/2010)
if you have enough resources and enterprise edition you should be able to do the operation online and only rebuild if you need to. I would start with looking at what hits the table and see how often it is actually seeking on the index and how much time it is spending updating it. there may be a better index that could be created or that index may not be needed at all

Thank you, these are indeed some of the things I will need to look into.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Nadrek SSC-Insane Points: 20039 More actions · Answer 12

What's the Fill Factor on the indexes with the fragmentation issues?

If you're doing updates or inserts throughout the values of the index, I would recommend trying a value lower than 100% fill factor. Calculate the row size of the index, calculate how many rows fit on a 8060 byte page, and then see how many rows you want to leave empty on each new page.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 13

Nadrek (4/8/2010)
What's the Fill Factor on the indexes with the fragmentation issues?
If you're doing updates or inserts throughout the values of the index, I would recommend trying a value lower than 100% fill factor. Calculate the row size of the index, calculate how many rows fit on a 8060 byte page, and then see how many rows you want to leave empty on each new page.

Thanks, most if the indexes are on fill factor 80.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 14

fill factor 80 seems a little low - I would review the fill factor you are most likely cauing more page splits than needed