SQL Performance

Question

SQL Performance

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

JacekO (3/13/2009)
If the index is on the identify field and you use 100% fill factor (with identity, you can go only one way so 100% should not hurt ?) -

Providing you never update in such a way that the row size increases. If you do, the new row, being bigger than the old won't fit and the page has to split.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JacekO SSCertifiable Points: 6610 More actions · Answer 2

Gail,

Does your answer assume the index is clustered? Nonclustered index on identify should not be affected by the row's growth - or I am missing something here?

Josehp,

Nice link.

Thanks

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

JacekO (3/16/2009)
Gail,
Does your answer assume the index is clustered?

Clustered or nonclustered with variable-width key or include columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JacekO SSCertifiable Points: 6610 More actions · Answer 4

Ok, good.

So going back to my question about Identity (INT - fixed lenght datatype) with Fill Factor of 100% - that should work fine and avoid the page splits.

(Actually when I think about this now any Fill Factor should work in this case, but why would you need anything but 100%?)

Now if you fetching the data and the split happens on the data page (not on the index pages) would you still get the duplicate records you mentioned before? Or this applies to index page splits only?

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

JacekO (3/16/2009)
Ok, good.
So going back to my question about Identity (INT - fixed lenght datatype) with Fill Factor of 100% - that should work fine and avoid the page splits.

Providing there are no variable-length or nullable columns anywhere within the index (key or include)

Now if you fetching the data and the split happens on the data page (not on the index pages) would you still get the duplicate records you mentioned before?

Data page splits when the data pages are scanned.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass