February 5, 2007 at 9:52 am
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
February 6, 2007 at 1:21 am
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/
February 6, 2007 at 9:59 am
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
November 26, 2008 at 1:19 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply