Frequently fragmented indexes

Question

Frequently fragmented indexes

Viewing 5 posts - 16 through 19 (of 19 total)

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

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

JDixon-385826 (4/8/2010)
fill factor 80 seems a little low - I would review the fill factor you are most likely cauing more page splits than needed

A low fill factor will prevent page splits more than a high fill factor.

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

Jeff Moden (4/8/2010)
JDixon-385826 (4/8/2010)
fill factor 80 seems a little low - I would review the fill factor you are most likely cauing more page splits than needed
A low fill factor will prevent page splits more than a high fill factor.

Agreed, I need to lower the fill factor to a value less than 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]

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

Another consideration:

Lowering the fillfactors will reduce READ performance on index scans.

I guess nothing is for free... 🙂

__________________________________________________________________________________
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 4

The amount of page splits would depend on the fill factor and the type of data being written.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jeff Moden (4/8/2010)
JDixon-385826 (4/8/2010)
fill factor 80 seems a little low - I would review the fill factor you are most likely cauing more page splits than needed
A low fill factor will prevent page splits more than a high fill factor.

Quite right. Also, 80 is not at all unusual for a non-clustered index (which is specifically what we are discussing here)...though only Marios has access to the information that would determine an appropriate value.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi