By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

slow insert, logical fragmentation

P: n/a
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

Mar 23 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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.

Mar 23 '06 #2

P: n/a
Dave (da******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 23 '06 #3

P: n/a
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.

Mar 24 '06 #4

P: n/a
try making no clustered index on the table.

Mar 24 '06 #5

P: n/a
Are you absolutely certain that the pages were 100% full after the index rebuild? 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 also 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 execution 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" <da******@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.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.


Mar 24 '06 #6

P: n/a
Doug (dr*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 24 '06 #7

P: n/a
hmmm.
"always have a clustered index"?????

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

Mar 25 '06 #8

P: n/a
Doug (dr*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 25 '06 #9

P: n/a
Erland Sommarskog a écrit :
Doug (dr*********@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?

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 ***********************
Mar 27 '06 #10

P: n/a
You are certainly the minority with that opinion then. At the least most
tables should have a clustered index to control fragmentation and speed
inserts.

--
Andrew J. Kelly SQL MVP
"Doug" <dr*********@hotmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
hmmm.
"always have a clustered index"?????

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

Mar 27 '06 #11

P: n/a
>>> Why? IMO, clustered indexes are a bad idea for most tables.
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


That doesn't say that you shouldn't have clustered clustered indexes for the tables.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQLpro [MVP]" <br******@club-internet.fr> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Erland Sommarskog a écrit :
Doug (dr*********@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?

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 ***********************

Mar 27 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.