Post reply

DBCC DBREINDEX, scan density not improved

  • 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

  • May 6, 2007 at 6:25 am

    #704531

    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

  • May 6, 2007 at 5:10 pm

    #704548

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

     

    MohammedU
    Microsoft SQL Server MVP

  • May 6, 2007 at 6:11 pm

    #704550

     

     deprecated meaning per Microsoft Encarta Dictionary.

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

    Encarta ® World English Dictionary © & (P) 1998-2005 Microsoft Corporation. All rights reserved.

     

     

    Kind regards,
    Gift Peddie

  • May 6, 2007 at 9:39 pm

    #704557

    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

  • May 8, 2007 at 5:09 am

    #704862

    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

  • May 8, 2007 at 2:36 pm

    #705071

    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.

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

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