SQL Server Performance

b-tree depth script

Discussion in 'Contribute Your SQL Server Scripts' started by mmarovic, Oct 3, 2005.

  1. mmarovic Active Member

    select o.nameas [table],
    i.nameas [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth')
    /*+ indexProperty(object_id(o.name), i.name, 'isClustered')*/as depth, --clustered index depth reported doesn't count leaf level
    i.[rows]as [rows],
    i.origFillFactoras [fillFactor],
    case (indexProperty(object_id(o.name), i.name, 'isClustered'))
    when 1 then 'clustered'
    when 0 then 'nonclustered'
    else 'statistic'
    endas type
    from sysIndexes i
    join sysObjects o on o.id = i.id
    where o.type = 'u'
    and indexProperty(object_id(o.name), i.name, 'IsHypothetical') = 0--filter hipothetical indexes out
    and indexProperty(object_id(o.name), i.name, 'IsStatistics') = 0--filter out statistics
    order by o.name
    Ordering by [rows] or depth or selecting just indexes with significant number of rows you can quickly see the most relevant existing indexes for tunning or checking fragmentation.
  2. FrankKalis Moderator

    Nice one. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />We probably need a more appropriate tool for those scripts. I mean, maybe some HTML textarea box where I can simply copy and paste into QA and not going to transfer to Word and then into QA. <br /><br />...just another thought...<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  3. mmarovic Active Member

    Script edited to filter out statistics and hipothetical indexes.

    IndexProperty(object_id(o.name), i.name, 'indexDepth') doesn't count leaf level of clustered index as part of the index. In theory index leaf level contains index column values + pointer to data, so clustered index leaf may be considered just data and level above can be considered leaf. If you want to have the 'real' depth of clustered index just uncomment commented code above.

Share This Page