August 30, 2010 at 8:57 am
Hi all, I'm running a query that tells me which indexes are causing the most table scans and from there finding ways to improve performance. I've had good luck with this and really knocked out the worst offenders, but the top culprit of table scans for me right now is a Primary Key!
I'm not sure how this would happen in the first place, but maybe yall can shed a light on why this might happen, and any possible solutions.
thanks much!
Ralph
for reference, here is my query:
select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc,
ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
from sys.indexes
left outer join sys.dm_db_index_usage_stats ddius
on indexes.object_id = ddius.object_id
and indexes.index_id = ddius.index_id
and ddius.database_id = db_id()
where object_schema_name(indexes.object_id) != 'sys'
--order by ddius.user_seeks + ddius.user_scans + ddius.user_lookups --desc --uncomment this line to find unused indexes
order by ddius.user_scans desc
August 30, 2010 at 9:38 am
When the primary key is clustered, table scans are actually scans of the primary key leaf level. It is still a table scan.
August 30, 2010 at 9:51 am
ahah, so if it is only scanning the leaf, should i be worried about this as a performance issue? If so, is there a way to address this or at least determine why this table out of all tables has so many more scans than anyone else?
thanks!
Ralph
August 30, 2010 at 11:22 am
Scans don't automatically present performance issues, but I'm usually worried about them and more often than not, eliminating a scan improves performance. So, yes, I'd be concerned.
The next step is to identify which queries are running against that table and figure out which ones are causing the scan. Then determine if the scan is necessary or a performance problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply