Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Seeks, Scans, and Lookups Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 5:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 02, 2010 6:20 AM
Points: 16, Visits: 46
Comments posted to this topic are about the item Index Seeks, Scans, and Lookups

Peter

http://seattleworks.com including my blog The SQL Janitor
Post #863743
Posted Monday, March 01, 2010 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 6:18 PM
Points: 60, Visits: 872
I think you may need u.database_id = db_id() in the Where Clause
Post #874375
Posted Monday, March 01, 2010 11:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:09 AM
Points: 69, Visits: 344
Running MSSQL 2000, I get

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'.
Post #874614
Posted Tuesday, November 02, 2010 12:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:26 AM
Points: 1,310, Visits: 958
I think that sp_indexinfo is much better than this query.


Post #1014300
Posted Tuesday, November 02, 2010 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:20 PM
Points: 37, Visits: 174
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



Post #1014774
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse