Post reply

Table name & Index name in finding Index fragmentation

  • February 13, 2010 at 8:05 pm

    #156675

    Hi,

    I'm using the below script to find Index fragmentation in a particular database:

    SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count

    FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, 'limited')

    where avg_fragmentation_in_percent>10 AND page_count>100

    order by page_count desc

    But I want to get Table name & Index name in results so that I can go to particular index in a particular table to rebuild or reorganize

    please advice

    thanks

  • February 13, 2010 at 8:13 pm

    #1118517

    Try using OBJECT_Name(object_id)

    or you could join back to sys.objects for the table name.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 14, 2010 at 1:32 am

    #1118546

    The index name is in the column called name in sys.indexes. Just add it to the select clause

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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