|
Home > Archive > MS SQL Server > March 2006 > slow insert, logical fragmentation
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
|
| 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.
| |
| Tibor Karaszi 2006-03-24, 8:23 pm |
| 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" <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.
>
| |
| Erland Sommarskog 2006-03-24, 8:23 pm |
| 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.
| |
|
|
| SQLpro [MVP] 2006-03-27, 7:25 am |
| 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 ********************
***
| |
| Andrew J. Kelly 2006-03-27, 7:25 am |
| 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" <drmiller100@hotmail.com> wrote in message
news:1143307052.523687.212610@u72g2000cwu.googlegroups.com...
> hmmm.
> "always have a clustered index"?????
>
> Why? IMO, clustered indexes are a bad idea for most tables.
>
| |
| Tibor Karaszi 2006-03-27, 9:23 am |
| >>> Why? IMO, clustered indexes are a bad idea for most tables.
> 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]" <brouardf@club-internet.fr> wrote in message
news:%23C0JDpZUGHA.1636@TK2MSFTNGP10.phx.gbl...
> Erland Sommarskog a écrit :
> 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 ********************
***
|
|
|
|
|