slow insert, logical fragmentation

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?

Table Size 1.5 million
Insert Size 70K

Before: 15 minutes
After: 3 minutes

Index:
Compound clustered across varchar columns
There are also a couple non-clustered indexes

A page split will occur when you insert contigeous data between already
existing data, if your pages are already full. However if you are
inserting new records (under a new date) then I dont see any page split
happening.

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

The clustered is a compound index on email varchar(100) and a guid.  I
can tune the indexes.  I was just wondering why the inserts were faster
after I defragmented the index?  It doesn't make sense to me.

try making no clustered index on the table.

Are you absolutely certain that the pages were 100% full after the index reb
uild? Remember that 0%
in DBCC DBREINDEXD mean that you reapply the fillfactor value you specified 
when creating the index
(sysindexes.origfillfactor). Or perhaps the rebuild of the clustered index a
lso rebuild a bunch of
non-clustered index leading to this effect. But I agree that it does sound a
 bit strange. Or perhaps
you had such low page density so you got a bunch of physical I/O before the 
rebuild (the data didn't
fit in cache), but after rebuild, the data *did* fit in cache so the executi
on resulted in
significantly less I/O?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Dave" <daveg.01@gmail.com> wrote in message
news:1143214113.493719.50500@v46g2000cwv.googlegroups.com...
> The clustered is a compound index on email varchar(100) and a guid.  I
> can tune the indexes.  I was just wondering why the inserts were faster
> after I defragmented the index?  It doesn't make sense to me.
>

Doug  (drmiller100@hotmail
.com)  writes:
> try making no clustered index on the table.

Bad idea. Unless there is proof of the opposite, always have a clustered
index on a table. At least it makes defragmentation easier. Heaps are also
more prone to fragmentation.



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

hmmm.
"always have a clustered index"?????

Why?  IMO, clustered indexes are a bad idea for most tables.

Doug  (drmiller100@hotmail
.com)  writes:
> hmmm.
> "always have a clustered index"?????
>
> Why?  IMO, clustered indexes are a bad idea for most tables.

Arguments? Or is it just an opinion?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland Sommarskog a écrit :
> Doug  (drmiller100@hotmail
.com) writes: 
>
> Arguments? Or is it just an opinion?
>
take a look over Kimberly's paper about that.
You will see that CLUSTERED index is good for :
- monotonically growing index value
- monocolumn index
- no update to vartype data

A +


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
 ********************
* http://www.datasapiens.com  ********************
***