Confused by article in sqlservercentra.com ?

Question

Confused by article in sqlservercentra.com ?

Amit Choudhary-216780

Old Hand

Points: 334
More actions
July 26, 2007 at 7:49 am

#179912

Hi,
I m little bit of confused in an article in sqlservercentra.com at this link:
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
Which says :
If you find out that the number of page splits is high, consider increasing the fill factor of your indexes. An increased fill factor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.
Is it OK ?
Thanks in Advance
Amit Choudhary

Viewing 9 posts - 1 through 8 (of 8 total)

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

And I'm a bit confused by your question. The reason why this recommendation is being made, is that a large number of page splits imply a high volume of inserts into the 'middle' of the table, rather than at the end of the table. Allowing more insertion room within a page improves performance and reduces the volume of I/O that each insert requires.

Did I understand your question correctly? If NOT, then post the question again here, please.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

I think the wording in the article may be wrong.

It should say that the fill factor should be LOWERED, for example from 95% to 70%.

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

I'm not sure that's right... lowering the Fill Factor allows more room between items in an index. Having more "room" might also mean that a page may have to split to allow that extra room (free space, unfilled space, space left open for other inserts in the index).

--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)
Intro to Tally Tables and Functions

Amit Choudhary-216780 Old Hand Points: 334 More actions · Answer 4

Hi Michael and Jeff,

I think Michael is saying right , language is not proper in article ..

According to me :

if clustered index is created on any table with Fill factor 90 and a new row for Some x key will come there to insert in table between x-1 and x+1 then sql server will try to set that row in same page because 10 % still remains in page , if row size is greater than 10% of page size than a page split will happen...

M i right ?

Thanks in advance,

Amit Choudhary

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Ah... I see what you're getting at...

Yes, in that case, if the inserted row size is greater that than free size remaining on the target page due to a clustered index, the page will split. It will, in fact, split, no matter what the fill factor is if there isn't enough free space for the new insert of the index row.

The real problem is that I don't think that people actually spend enough time figuring out what a Fill Factor should be. The maximum size of an Index row is only 900 bytes. When I create a table that may suffer "middle of the table" inserts due to a clustered index, I'll carefully weigh out how many adjacent inserts there may be and multiply that times the number of bytes per row in the indexed columns (length of fixed columns + max length of variable length columns). Then, I use that number to calculate what I want for the fill factor based on a max index row length of 900...

Better than that, I usually try to avoid clustered indexes using natural keys on high volume insert tables... that way, I don't have to worry about fragging the indexes with page splits.

--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)
Intro to Tally Tables and Functions

Ed Hammar Ten Centuries Points: 1084 More actions · Answer 6

Jeff, what do you mean when you say "natural keys"? Are those values which are random in terms of the order they are inserted into the table/index?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

A natural key would be a SSN instead of an identity column. The SSN is a natural key because it is a unique identitfier for the person (by country... not sure it is unique for the whole planet).

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

Correct... SSN would be one example of such a key.

--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)
Intro to Tally Tables and Functions