Index Seeks, Scans, and Lookups

Question

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

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

Edward Boyle-478467 Ten Centuries Points: 1135 More actions · Answer 1

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

Rob Maurer SSC Veteran Points: 242 More actions · Answer 2

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: 6551 More actions · Answer 3

I think that sp_indexinfo is much better than this query.

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 4

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 joinsys.sysobjects as o on u.object_id = o.id and o.xtype = 'U'

inner joinsys.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 joinsys.sysobjects as o on u.object_id = o.id and o.xtype = 'U'

inner joinsys.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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

SSC Guru

Points: 76202

May 18, 2016 at 6:50 am

Thanks for the script. It was very helpful.