Post reply

Question on Indexing

  • March 13, 2012 at 3:46 pm

    #254553

    I just used sys.dm_db_index_physcal_stats tables to view indexs in a DB. I used AdventureWorks.

    to observe the clusterd indexes. On some object mulitiple rows are generated for a clustered index. If I am not clear please look at the below example.

    For example I ran the below query on AdventureWorks. On one particular object with objectID '309576141'.

    select object_id, index_id, index_type_desc from sys.dm_db_index_physical_stats(

    DB_ID('AdventureWorks'),

    309576141,

    DEFAULT,

    DEFAULT,

    Default

    )X where x.index_type_desc = 'CLUSTERED INDEX'

    OUTPUT:

    3095761411CLUSTERED INDEX

    3095761411CLUSTERED INDEX

    3095761411CLUSTERED INDEX

    So I checked and ran the below query to to see the name of the object.

    select object_name(309576141)

    OUTPUT:

    Contact

    Why does it return multiple rows for the same index while on many other objects only 1 row is generated for clustered. Can any one throw some light on this?

  • March 13, 2012 at 4:05 pm

    #1458901

    Generally this happens because the index has multiple levels and when index physical stats is run with the detailed option it returns one row for each level of the index.

    Include the column index_level and see if that's it.

    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 2 posts - 1 through 1 (of 1 total)

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