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 |
|
OMB
Yak Posting Veteran
88 Posts |
 Posted - 2004-03-30 : 08:44:03
|
Hi All I ran a DBCC showcontig on a table and recieved the following results DBCC SHOWCONTIG scanning 'PriceChanges' table...Table: 'PriceChanges' (1371151930); index ID: 0, database ID: 9TABLE level scan performed.- Pages Scanned................................: 9- Extents Scanned..............................: 5- Extent Switches..............................: 4- Avg. Pages per Extent........................: 1.8- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]- Extent Scan Fragmentation ...................: 20.00%- Avg. Bytes Free per Page.....................: 1924.4- Avg. Page Density (full).....................: 76.22%DBCC execution completed. If DBCC printed error messages, contact your system administrator. I then tried runnig the dbcc dbreindex command but it seems as if nothing is being done. When i run the showcontig statement I get exactly the same results again, as if nothing is happening. I have full rights to do the task, any ideas?p.s the table only has 500 rows in it , could this be a factor?OMB |
|
|
debug2k
Starting Member
18 Posts |
Posted - 2004-03-30 : 09:19:52
|
| Hi,that's very rare ...can u please try runnign dbcc indexfrag on the indexes...and then check...the Showcontig.Regards,Deepak Uniayl |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-30 : 09:48:25
|
| Drop and recreate the primary key. That should fix it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2004-03-30 : 17:39:38
|
| We used to worry about this in our system as well. With such a small number of rows, you may not reach 100% by running reindex. Based on the width of your rows, it is very possible this is going to be the best you can do. In a case like this you'd want to worry more that your statistics were up to date, rather than trying to hit that 100% scan density number.Mike"oh, that monkey is going to pay" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-30 : 17:59:39
|
| What is the FILLFACTOR set to for the index?Tara |
|
|
|
OMB
Yak Posting Veteran
88 Posts |
Posted - 2004-03-31 : 03:11:57
|
| Hi AllDerrik tried that but no joy, whilst the table may only have 500 rows a the moment it will definately have many more in the future 000's. Tara the fill factor is set to 0 as the table will only ever have inserts. we did change the index from non clustered to clusterd and this seems to have fixed the problem, but cannot understand why this happend in the first palce.OMB |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-31 : 09:39:41
|
| If you didn't have a clustered index on the table, the DBCC DBREINDEX wouldn't take care of your problem. It's the reindexing of the primary key that reorders and tightens up your table. You shouldn't have the problem going forward.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
DBCC DBREINDEX not providing desired results