Posted by Erland Sommarskog on 03/24/06 00:41

Dave (daveg.01@gmail.com) writes:
> We had a table that had logical fragmentation of 50%. After rebuilding
> (with default fillfactor 0) I noticed that inserts are much faster. If
> my page density is 100% wouldn't I get more page splits? I know I am
> missing something fundamental here. Could someone get me back on
> track?

It depends on what your clustered index is on. If it is on a column that
is monotonically increasing, for instance an IDENTITY column or a
datetime column with the default of getdate(), then will be few splits,
because all new data goes into new pages.

But if the data inserted appears more random over your clustered index,
you will get more page splits, and your table will start to fragment
again.

In this case, if may be better to reindex to a lower fill factor than
100%, to create space for new rows in advance.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]