SQL Server Performance Forum – Threads Archive

index depth

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.

---

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

---

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.

---

Thanks guys, this is a little clearer now.

---

]]>