Post reply

Checking fragmentation taking forever

  • April 7, 2010 at 10:44 am

    #156848

    I am running following command to check the fragmentation of a INVOICE table and it's running for almost 45 minutes.. Any idea..?

    SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc,

    avg_fragmentation_in_percent, fragment_count, page_count

    FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL , 'LIMITED')

    WHERE object_name(object_id) is not null and avg_fragmentation_in_percent > 10

    and page_count > 1000

    and object_name(object_id) ='invoice'

  • April 7, 2010 at 1:36 pm

    #1147715

    The way you are running it, it will still do it on all the objects in that database and then only return the results for the Invoice table.

    You might want to do it ONLY for that table, as in:

    SELECT TableName = object_name(object_id), database_id, index_id, index_type_desc,

    avg_fragmentation_in_percent, fragment_count, page_count

    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'invoice'), NULL, NULL , 'LIMITED')

    WHERE object_name(object_id) is not null and avg_fragmentation_in_percent > 10

    and page_count > 1000

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • April 7, 2010 at 2:15 pm

    #1147754

    Richard.. You are correct.

    Thanks a lot.

Viewing 3 posts - 1 through 2 (of 2 total)

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