October 7, 2009 at 9:18 am
Hi,
I have a SQL Server 2005 database, the index usage statics report shows the following:
Table User Seeks User ScansUser Updates
PRICE_KEYCLUSTERED 184566 1 0
SUBTIPOCLUSTERED 60149 14 1
ITEM CLUSTERED 49020 2902 16
TBL CLUSTERED 41933 4 2309
How do I solve this?
Should I work with developers to change the queries?? or re-build indexes to acomodate diferent columns?? to make the execution plan to use scan instead of seek??
And what about the oposite?, when you have high scans and look seeks like in this:
User Seeks User Scans
PK_EC_FA_REPARTIDORESCLUSTERED 82 11352
PK_EC_FA_SOURCE CLUSTERED 21119 9912
October 7, 2009 at 9:26 am
IIRC, Index Seeks are better than Index Scans for the most part. If I am wrong, I'm sure someone else will come along and correct me.
October 7, 2009 at 9:39 am
More seeks is better. Scans are slower.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2009 at 9:40 am
jflzc (10/7/2009)
Hi,I have a SQL Server 2005 database, the index usage statics report shows the following:
Table User Seeks User ScansUser Updates
PRICE_KEYCLUSTERED 184566 1 0
SUBTIPOCLUSTERED 60149 14 1
ITEM CLUSTERED 49020 2902 16
TBL CLUSTERED 41933 4 2309
How do I solve this?
Should I work with developers to change the queries?? or re-build indexes to acomodate diferent columns?? to make the execution plan to use scan instead of seek??
What you have is fine, better to have seek rather than scan
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 9:46 am
jflzc (10/7/2009)
Hi,I have a SQL Server 2005 database, the index usage statics report shows the following:
Table User Seeks User ScansUser Updates
PRICE_KEYCLUSTERED 184566 1 0
SUBTIPOCLUSTERED 60149 14 1
ITEM CLUSTERED 49020 2902 16
TBL CLUSTERED 41933 4 2309
How do I solve this?
Should I work with developers to change the queries?? or re-build indexes to acomodate diferent columns?? to make the execution plan to use scan instead of seek??
And what about the oposite, when you have high scans and look seeks like in this:
User Seeks User Scans
PK_EC_FA_REPARTIDORESCLUSTERED 82 11352
PK_EC_FA_SOURCE CLUSTERED 21119 9912
October 7, 2009 at 9:55 am
It depends. Are you getting reports of problems with queries being slow or sow response times in your application(s)? If so, instead of looking at the number of seeks vs scans, you really should be identifying the queries that may be the problem.
October 7, 2009 at 9:56 am
jflzc (10/7/2009)
jflzc (10/7/2009)
And what about the oposite, when you have high scans and look seeks like in this:
Then either the indexes aren't appropriate for the queries been run against the table or the queries are written in such a way that seek operations aren't possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply