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 |
|
rkc01
Starting Member
43 Posts |
 Posted - 2002-04-26 : 14:56:15
|
| I was performing maintenance on a clients 7.0 database. There were about 60 tables thatwere badly fragmented. I did a dbcc dbreindex on all of them. I got no errors during the reindex. When I ran another showcontig, approx. half were unchanged. I did the reindex again and the scan density came back to 100%. I don't understand why the first reindex had no affect on half of the tables the first time around.I also had 3 tables that would not defrag no matter how many times I ran reindex. The only thing those 3 tables seem to have in common is their size, (relatively small, 6 pages or less). I had many tables of comparable size that did come back to 100% scan density. If anyone is interested, I have the results of the showcontigs for comparison. For tables that had no clustered index, I created one then dropped it. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-26 : 15:33:21
|
| My guess is that the 3 tables in question belong to mixed extents, possibly they all share their extents with each other. Tables that are that small wouldn't benefit as much from reindexing; if their extents are contiguous they'll most likely be read into memory in the same read operation anyway.You can try running DBREINDEX several times with different fill factors, say a 10% fill factor, then a 100% fill factor. You could also create a clustered index with a low fill factor, then DBREINDEX with a higher one. Don't go crazy trying to get them to 100% scan density, as long as extent switches are less than 3 for each table you'll be fine. |
 |
|
|
rkc01
Starting Member
43 Posts |
Posted - 2002-04-26 : 23:39:49
|
| What's your take on half the tables not responding to the DBREINDEX on the first try? That's really driving me nuts. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-27 : 08:48:26
|
| Other than mixed extents, fillfactors, and large row sizes (2000 bytes or more) I'm not aware of anything that would make DBREINDEX act funny. I wouldn't worry too much about half of the tables not defragmenting on the first try, as long as it works on the second try what's the big deal? I've seen some weirdness like this in SQL 6.5, and I think it might be a fluke that was carried over into 7.0, and may be fixed in a service pack. If you don't have SP3 installed yet, try that and see if it fixes it.Are these tables highly transactional...lots of deletes and inserts? You might want to use a lower fillfactor, maybe even 50%, and continue to use that fillfactor when you reindex. You may also want to experiment on what to use for your clustered index, especially if you're using an identity column as a clustered primary key.UPDATE: You might want to apply Service Pack 4, it just became available:http://www.microsoft.com/sql/downloads/sp4.aspEdited by - robvolk on 04/27/2002 09:38:05 |
|
|
|
rkc01
Starting Member
43 Posts |
Posted - 2002-04-27 : 10:11:45
|
| Appreciate the advice, thanks. I'll play with the fill factors and try applying the service pack asyou suggested. |
|
|
|
|
|
|
|
|
Clustered Index Defrag