April 24, 2008 at 7:02 am
If I have a query indexed to the point that its estimated execution plan basically looks like 3% index seek and 97% bookmark lookup, is there much point in trying to tune it further with indexes, short of creating a covering index (which, in my case, is not practical) to eliminate the lookup altogether?
Thank you!
April 24, 2008 at 8:53 am
Make sure your clustered index is an efficient index for the lookup. If you have a clustered index on say a bit field, you end up having to scan a huge number of records within the clustered index. You also need to make sure you do not have a really fragmented table - this can really slow down a bookmark lookup.
April 24, 2008 at 8:57 am
I'd say no. The only thing you can do is eliminate the lookup by including or adding columns to the nonclustered index. Are you sure you can't do an include? After that, like the previous post said, make darned sure the cluster is as efficient as it can be.
"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
April 24, 2008 at 9:44 am
Yep--my clustered index is on the same 2 columns as the PK.
April 24, 2008 at 12:22 pm
Depending on how complex the query is, you might look at adding/modifying indexes or changing the query to generate a different plan. Without seeing the existing query or plan it's impossible to say.
April 25, 2008 at 12:02 am
Michael Earl (4/24/2008)
Make sure your clustered index is an efficient index for the lookup. If you have a clustered index on say a bit field, you end up having to scan a huge number of records within the clustered index.
All clustered indexes are, behind the scenes, unique. If the index isn't created UNIQUE, then SQL adds a 'uniquifier' to make the index key unique.
In a non-unique clustered index, both the clustering key and the uniquifier will be present in the NC index pages to allow the lookup.
Steve: Make sure your clustered index is as narrow as possible. Wide clustering keys means that your index is larger than it neeeds to be, meaning more page reads will be required to get the required rows
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
April 28, 2008 at 3:21 pm
Steve F. (4/24/2008)
If I have a query indexed to the point that its estimated execution plan basically looks like 3% index seek and 97% bookmark lookup, is there much point in trying to tune it further with indexes, short of creating a covering index (which, in my case, is not practical) to eliminate the lookup altogether?Thank you!
It will have to do a bookmark lookup (at the very least) for columns not contained in the index, so there really isn't a way to eliminate that, except to add them to a covering index. (That, of course, doesn't apply to columns that are part of the clustered index, since that's part of every index.)
If you're trying to get more performance out of the code, without adding columns to an index, I'd start with reviewing the code itself. There are a lot of things that can make SQL be slow, beyond mis-indexing. A LOT of things.
If you can post the code, we can take a look at to see if we have any specific suggestions.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply