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 |
|
Christina Agyeman
Starting Member
15 Posts |
 Posted - 2002-06-20 : 10:30:46
|
| What causes tables/Indexes to fragment. We have a couple of tables on our live db that shows low scan density during peak time.What can we do to reduce fragmentation whilst the db is in use. |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-20 : 11:22:15
|
| As you modify data in your table, you create "holes" (unused space) in the index pages (these "holes" may appear because you delete some rows, or updated values in the indexed fields, or needed to split a page while inserting). The more "holes" appear in the table, the more fragmented it becomes. There are index maintenance procedures that you need to perform periodically to keep fragmentation in check. See BOL for descriptions of DBCC DBREINDEX and DBCC INDEXDEFRAG (SQL2k only). |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 11:59:56
|
Additionally, there are things you can do on the physical design level to reduce fragmentation. WARNING: On an purely academic level, I should be taken out back and shot for what I am about to say. If you find your fragmentation is being cause by a high level of page split due to the fact that you are using a natural key for your clustered index (like say Social Security Number), then you may consider using a surrogate (Identity property) instead as all inserts will go to the end of your index. You will have to balance this against the impact it will have on query against your natural key as now you will either do an index scan or (if you create a non-clustered index on that SSN) a bookmark lookup following a seek. EDIT: additionally, this may result in a 'hot-spot' on the end extent.A less 'unpure' idea is to consider decreasing the fill factor % on you clustered index so that new indexes records are less likely to result in page splits. This, however, will come at the expense of disk space . . . That's the beauty of this stuff, there is a trade-off for just about everything! :)<O>Edited by - Page47 on 06/20/2002 12:02:01 |
|
|
|
|
|
|
Table Fragmentation