Saturday, February 25, 2012

Page SPlits and finding out Record size and Page Info

I am trying to determine if pages in a table are close to full and likely to
page split.
Using DBCC SHOWCONTIG ( table) WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS
I get MinimumRecordSize, MaximumRecordSize only for the indexes How can I
get this information on the data itself? If an update is going to change a
the average record size by increasing it 15 bytes, and the avg is 500 bytes,
I would assume that there are on average 8 records per page and the increase
would push one record out, or 1 in 8, so a 25 million record table would
encounter 3,125,000 page splits. With a clustered index, I believe we have
been experiencing some bad performance issues and turning off clustering on
the index and adjusting the free space per page, hopefully will help with
the updates.
Is there anyway to reorganize the data so that it is only using say 60% of
the file pages so that updates are less likely to page split?I may have fired too soon.
My response refers to Index Organization. Not data itself.
Cheers,
GAJ

No comments:

Post a Comment