SQL Server Performance

index depth

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by BikeBoy, Feb 18, 2006.

  1. BikeBoy New Member

    Not entirely sure if this is the right forum to post this, but I have never before considered using index depth in performance tuning. Since this is available in SQL 2005 Summary report, I feel that maybe I've been missing something.

    Why would I can about the depth, and how would I use it?

    Tnx for the thoughts.
  2. joechang New Member

    there is not much you can do with it
    in SQL 2000, use could use INDEXPROPERTY ( table_ID , index , property )
    to get index depth

    example
    SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'UPKCL_auidind', 'IndexDepth')

    or select a single row from an index (no bookmark lookup) and check the logical IO count,

    one of the few things useful about paying attention to index depth is, if you are building a compound index with fat columns on a large table, compare the index depth to the depth of a narrow index on that table, it might indicate you are trying to build too fat an index on too large of a table

  3. mmarovic Active Member

    Also you could take into consideration index depth if you design table partitions. You may try to have partitions with smaller index depth then what would be index depth of the table without partitions.
  4. sql_machine New Member

    Thanks guys, this is a little clearer now.

Share This Page