473,324 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Mar 23 '06 #1
11 6786
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
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
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
try making no clustered index on the table.

Mar 24 '06 #5
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
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
hmmm.
"always have a clustered index"?????

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

Mar 25 '06 #8
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
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
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
>>> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around...
5
by: Paul Shaw | last post by:
Can anyone explain why an insert might cause multiple logical bufferpool data reads? Here's a situation that has me scratching my head. Table A's data resides in tablespace B Table A's...
20
by: John Bailo | last post by:
I have a c# program that loops through a table on a DB2 database. On each iteration it assigns data to values in the SqlParameter collection. The command text is an INSERT statement to a Sql...
46
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array...
6
by: Raj | last post by:
We have a batch process that deletes about 7-8 million records every day & takes about 30-35 mins to complete. Is there a way to optimize deletes? Below is the statement delete from fact where...
2
by: bie2 | last post by:
Hi, I ran test data on my development machine and it took 1 minute to insert the data. Ran the same set of data on the server and took 5 minutes. Check both database and everything is the...
12
by: Somebody | last post by:
Hi, I'm trying to write the function below and have it working, but I benchmarked it against strcmp() and its much slower. My test is to compare 2 strings that are identitical 100,000,000 times....
5
by: Lennart | last post by:
Here is a problem I would like some opinions on. I have a stored procedure (I suspect that the problem exists for other procedures as well, but I haven't verified it). First time it runs (each day)...
12
by: Marc Baker | last post by:
Bear with me here folks, I don't know much MS SQL performance. Wondering if someone can point me in the right direction. I have 1 particular database where queries are slow. To test this theory,...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.