|
Home > Archive > Microsoft SQL Server forum > 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
| |
|
| 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.
| |
| Erland Sommarskog 2006-03-23, 8:34 pm |
| 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.
| |
| Tibor Karaszi 2006-03-24, 8:25 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:25 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:37 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:37 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:35 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 ********************
***
|
|
|
|
|