Matt Miller (3/12/2008)
I know I'm showing late to the party - but is there any chance to make that a covering index? Assuming those extra fields aren't ludicrously long - you'd be able to get rid of the bookmark lookups....
Matt,
From now on I will be referring to you as the LATE Matt Miller.
Ronnie,
Here is a list of questions that have popped into my head as I have thought about the issue (questions on the same line are related):
Is the query always run from the same place in the application or are there different places it is run from? If you run the "same" query with schema.table and table you will get 2 plans and reduce the space in cache by having "duplicate" queries cached. You really should use teh schema.table syntax.
Have you checked CacheHits, CacheMisses?
What is the order of the columns in the composite index? Do they match the order of the Where clause?
Have you tried reversing the order of the SARG to see if that causes the optimizer to always choose the composite index?
How often are CRUD operations run on this table? It could be a longer running transaction causing contention.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question