DBCC DBREINDEX, scan density not improved

Question

DBCC DBREINDEX, scan density not improved

Barkingdog

SSCoach

Points: 18670
More actions
May 5, 2007 at 11:42 pm

#173252

I looked at sql 2005 database AdventureWorks
               a. ran dbcc showcontig
               b. It shows that table 'Sales.StoreContact" has a scan density of 50%. Not good.
               c. I ran dbcc dbreindex ('sales.storecontact')
               d. I re-ran showcontig but it still shows a scan density of 50%.

    There was no improvement whatsoever. Did did I do something wrong or can some tables simply not be improved?
Thanks,
Bill

TIA,

barkingdog

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Yep you missed something Showcontig is obsolete, try the new version you are not alone most people don't know all the physical of SQL Server 2005 is new. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Kind regards,
Gift Peddie

MohammedU SSCoach Points: 19644 More actions · Answer 2

If I am not mistaken DBCC SHOWCONTIG is deprecated but not obsolete.

MohammedU
Microsoft SQL Server MVP

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

deprecated meaning per Microsoft Encarta Dictionary.

(comput declare obsolescence of something: to state that a computational method or computer feature is superseded )

Kind regards,
Gift Peddie

MohammedU SSCoach Points: 19644 More actions · Answer 4

Based on your answers I miss understood Obsolete word...

I though you trying to say some thing wrong with DBCC SHOWCONTIG....

Thanks, for the explanation...

MohammedU
Microsoft SQL Server MVP

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I get the same problem on any number of production databases when using sys.dm_db_index_physical_stats as the source of density/frag information too. Part of it is due to having databases that don't have enough free contiguous space inside them, especially for clustered index maintenance. However, I have been unable to find a suitable answer for the remainder of the failures to successfully defrag.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

we upgraded our publisher almost a month ago and set up sys.dm_db_index_physical_stats to dump the data into a database. we then run alter index rebuild on anything over 5% fragmented.

i looked back on some indexes that were highly fragmented last night and it looks like on most of them they had single digit fragmentation for days and then a huge jump to 30% or higher. my guess is someone is doing a big batch job that causes this, but it's impossible to tell without a trace.