February 10, 2009 at 2:01 pm
I have an application that passes the following query:
SELECT TOP 50 imageid, hires, thumb1, thumb2, head
FROM MainTable WITH (NOLOCK)
where (sync is null or sync=0) and datatype<>'TEXT'
It should be hitting a nonclustered index 'synccomp' on (imageid, sync, datatype) but instead it's hitting the PK index on imageid and it's causing the query to take over 60 seconds. When I give it an index hint for the correct one it drops down to 3 seconds. I've checked the fragmentation on 'synccomp' and it's looked OK, I've de-fragged it, and also dropped and recreated it, and tried it with just sync and datatype fields, but the query always hits the PK one.
I've seen this at times on other queries where it hits the PK instead of the desired one, but have never figured out how to fix it, apart from when an index was severely fragmented. Changing the code isn't an option since I don't have control of that, and it appears to be an issue with just one system of mine and not the others. Has anyone run into this kind of problem?
February 10, 2009 at 2:49 pm
If the index's leading column is imageid, then SQL will never pick it by choice, as the leading column of the index is not in the where clause at all. That means SQL has to scan that entire index, and then go and look up all the rows it finds from the cluster. Check the IO stats, you'll very likely see that the query with the forced index is much higher than the one that scans the cluster.
The cost of the bookmark lookup is probably why it's not picking the index with just sync and data type. SQL will only do that i the number of rows returned is under 1% of the table
Maybe this will help you - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
An optimal index for that query will be sync, datatype, imageid, hires, thumb1, thumb2, head
On 2005, I'd put everything but the first 2 into the INCLUDE columns and have just 2 key columns, but that's not possible on SQL 2000
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
February 11, 2009 at 6:25 am
I believe a (sync,datatype) index was tried, but I'm not sure more than that ( I have a colleague that's been trying to play with it). I'll give your suggestions a shot.
February 11, 2009 at 8:15 am
Well it worked out on my QA server which has the same customer db schema and was producing the same problem, but not on the actual customer one. I'll have my colleague update the statistics as well, maybe something's off in there.
February 24, 2009 at 9:25 am
We ended up not being able to resolve this one, and got the developer to add an index hint if it determines the requisite index exists.
February 24, 2009 at 9:40 am
Mindy Hreczuck (2/24/2009)
We ended up not being able to resolve this one, and got the developer to add an index hint if it determines the requisite index exists.
Post the exec plan here? (in SQL 2000, SET STATISTICS PROFILE ON, run the query to grid, copy the results of the exec plan to excel, zip and attach)
There certainly are edge cases where the optimiser will not pick the best index no matter what, but those are rare. If this is one, it'll be the second I've seen in 5 years.
If I had to guess, I'd say the index is not covering and the optimiser thinks the lookup is too expensive.
What indexes did you try?
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply