Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2011-05-25 : 11:42:57
|
| I was windering if you could help me.I've got some heavily fragmented indexes on one of my tables.When I look at the execution plan for my query it shows that 86% of the cost is with a Clustered Index Update.Will rebuilding the index improve performance?I was under the impression that rebuilding an index will improve SELECT queries, not INSERTS. Is this correct? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-25 : 12:23:58
|
| It should leave space in the leaf level so that there is less likelyhood of page splits.Could it be that your problem is a wide index and a lot of non clustered indexes?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-25 : 14:18:12
|
| The optimiser's cost estimates do not take fragmentation into account.--Gail ShawSQL Server MVP |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-26 : 06:47:24
|
| Yes I understand the optimiser doesn't take fragmentation into account. However if the execution plan contains a clustered index insert and that index is fragmented, will rebuilding the index improve the performance of the clustered index insert? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-26 : 06:56:39
|
| Possibly.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-05-26 : 16:59:34
|
| When you rebuild the index, new pages/extents are allocated data compacted (if possible) into new contiguous pages. So as Nigel said, possible.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
mEmENT0m0RI
Starting Member
2 Posts |
Posted - 2011-07-26 : 16:08:07
|
| I suppose, if the table is clustered on the PK, each insert would need to be validated for uniqueness against the clustered index, and if it is fragmented, this look up would take longer. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-26 : 16:58:48
|
| Fragmentation affects large range scans from disk.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Inserting into a fragmented clustered index