How to get scan count by object (table) in SQL Server 2005

Question

How to get scan count by object (table) in SQL Server 2005

Don Prentice

SSC Journeyman

Points: 86
More actions
February 5, 2007 at 9:52 am

#171324

Greetings. I am trying to figure out if I can get a scan count by table/index on SQL Server 2005 via a query or SP. Need to be able to identify table that have a high scan count and eventually work to find SQL driving the high scan count. Can the same process be used for SQL Server 2000?
Any help would be appreciated.
TIA
Don

Viewing 4 posts - 1 through 3 (of 3 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I'm not sure you can achieve this, the perfmon counters have no granularity so are out, your only method would be to use a profiler trace collecting the scan event and the object id.

there are some dmv's in 2005 that track index usage, nothing in 2000, but I'm not sure they store the info you're looking for.

However, scans are not always bad, the decision of the optimiser to scan or seek is based upon the %age of rows used in the query so fundamentally I'm not convinced this approach is the right one. I usually start with high io queries, as a scan will generate more io. Remember that many of the sql perfmon counters are server wide so include internal io which may appear to distort figures.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 2

This might be helpful?:

SELECT o.name as table_name,coalesce(i.name,'HEAP')

,s.user_seeks, s.user_scans, s.user_lookups, s.user_updates

,s.user_seeks+s.user_scans+s.user_lookups+s.user_updates as user_tot

,s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update

from sys.indexes i

LEFT OUTER JOIN sys.dm_db_index_usage_stats s

ON i.object_id=s.object_id and s.database_id=db_id()

AND i.index_id=s.index_id

LEFT OUTER JOIN sys.objects o ON i.object_id=o.object_id

where o.type='U'

and o.is_ms_shipped=0

and (i.name NOT LIKE 'Msmerge_index%' OR i.name IS NULL)

order by o.name, i.name

jg

dr.lijenjin Newbie Points: 9 More actions · Answer 3

I'm not sure I understood you right, but maybe this is what you want?

SET STATISTICS IO ON

YourQuery

then after query execution, in messages you will see listed detailed information on scan count, reads etc. for every table in the query