April 25, 2008 at 6:38 am
I was wondering whether there is a way to avoid page splits. Even if we put a fill factor of 1 or 100, there should be page splits occuring if I insert a few million records. We know that the space reserved for a table gets deallocated when you truncate or delete the records from the table. Is there a way we can allocate space for a table beforehand with zero records in the table?
April 25, 2008 at 7:08 am
Rajan John (4/25/2008)
Is there a way we can allocate space for a table beforehand with zero records in the table?
Don't think so.
And even if there's an option of doing that you could still get page splits when inserting data or updating varchar columns.
The only situation where you would never have pagesplits is when you have no variable length records and no index. But since that's not a very realistic scenario I would advise to use a Fillfactor which is low enough to keep page splits at an acceptable level.
To be honest I have never seen a situation where pagesplits created a problem.
[font="Verdana"]Markus Bohse[/font]
April 25, 2008 at 7:19 am
Mark, if we can preallocate space, cant we avoid page splits as there is no need of new page allocations? I am saying about a coniguous insertion on an index that has identity, getdate() etc.
April 25, 2008 at 8:15 am
The issue is not the allocation of space, but the allocation of rows onto the pages. When you create a new database with a file size of 10 Mb, that space will be allocated and empty pages will be created.
Page splits occur when either a row is inserted on a already full page or when a varchar column is updated and contains more characters.
Tony Rogerson has a very good blog bout them here
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply