Index Seeks, Scans, and Lookups

  • Peter Samson

    SSC Veteran

    Points: 216

    Comments posted to this topic are about the item Index Seeks, Scans, and Lookups

    Peter

    http://seattleworks.com including my blog The SQL Janitor

  • Edward Boyle-478467

    Ten Centuries

    Points: 1133

    I think you may need u.database_id = db_id() in the Where Clause

  • Rob Maurer

    SSC Veteran

    Points: 242

    Running MSSQL 2000, I get

    [font="Courier New"]Server: Msg 208, Level 16, State 1, Line 2

    Invalid object name 'sys.dm_db_index_usage_stats'.

    Server: Msg 208, Level 16, State 1, Line 2

    Invalid object name 'sys.sysobjects'.

    Server: Msg 208, Level 16, State 1, Line 2

    Invalid object name 'sys.sysindexes'.[/font]

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6359

    I think that sp_indexinfo is much better than this query.

  • JRoughgarden

    Ten Centuries

    Points: 1119

    I like this better:

    declare @bntTotalRowsScanned bigint

    select @bntTotalRowsScanned = sum(i.rowcnt * u.user_scans)

    from sys.dm_db_index_usage_stats as u

    inner join sys.sysobjects as o on u.object_id = o.id and o.xtype = 'U'

    inner join sys.sysindexes as i on u.index_id = i.indid and o.id = i.id

    where u.database_id = db_id()

    select top 10 o.name as 'Table'

    , isnull(i.name, '(Heap, No Indexes)') as 'Index'

    , i.rowcnt as 'Est Rows'

    , u.user_seeks as 'Seeks'

    , u.user_scans as 'Scans'

    , u.user_lookups as 'Lookups'

    , i.rowcnt * u.user_scans as 'Rows Scanned'

    , cast(cast(i.rowcnt * u.user_scans * 100 as float) /cast(@bntTotalRowsScanned as float) as decimal(5,2)) as PctTotal

    from sys.dm_db_index_usage_stats as u

    inner join sys.sysobjects as o on u.object_id = o.id and o.xtype = 'U'

    inner join sys.sysindexes as i on u.index_id = i.indid and o.id = i.id

    where u.database_id = db_id()

    order by 7 desc

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Edward Boyle-478467 (3/1/2010)


    I think you may need u.database_id = db_id() in the Where Clause

    I tried it both ways and the results were the same, for me anyway.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    henrik staun poulsen (11/2/2010)


    I think that sp_indexinfo is much better than this query.

    I thought the query was good but I'll try your suggestion.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script. It was very helpful.

Viewing 8 posts - 1 through 8 (of 8 total)

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