Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm confused both as to why it's doing this and what it's so slow. I'm not even referencing the PK in that table. I'm doing:

WHERE EXISTS
  (SELECT 1 FROM tblPriceTierQuantities [Extent8]
   INNER JOIN xxxx ON xxx=Extent8.AccountRateScheduleID
   WHERE Extent8.PriceTierID=tblPriceTiers.oid)

The details on the execution plan show as this:

Key Lookup Tooltip

That shows up as the only thing taking significant time to execute. Indeed, if I simply comment out that WHERE Extent8.PriceTierID=tblPriceTiers.oid clause, then the query is as speedy as I want it to be. The thing is, this lookup seems to be pointing to Extent8's oid and not tblPriceTier's oid field.

I'm confused why it's hitting the clustered index on that table because of this query. Nothing else in this query hits that table at all. Can you explain why this clustered lookup exists and also why it might be so slow? The ONLY columns ever referenced on this tblPriceTierQuantities table are AccountRateScheduleID and PriceTierID, NEVER oid which is the only column in this clustered index.

share|improve this question

migrated from stackoverflow.com Dec 11 '12 at 6:32

This question came from our site for professional and enthusiast programmers.

    
Could you publish the actual (post execution) XML execution plan? –  Sebastian Meine Dec 11 '12 at 1:25
3  
Have you updated statistics lately? Estimated Number Of Rows is 1 while Actual Number Of Rows is 10,259,536... Dated stats can lead to bad query plans (insufficient memory allocated for joins and sorts, bad join algorithm choices...). –  brian Dec 11 '12 at 1:38
    
@brian: That indeed was the problem. Please post it and I'll mark it as the answer. Also, for extra credit, can you tell this dev-who-is-forced-to-be-a-dba why this is necessary? It would seem to me that Auto Compute Statistics would take care of this for me. Nothing strange (like millions of inserts/edits in this table recently) have happened. Even without that help, I really appreciate your assistance!! :-) –  Jaxidian Dec 11 '12 at 14:58

2 Answers 2

up vote 3 down vote accepted

Have you updated statistics lately? Estimated Number Of Rows is 1 while Actual Number Of Rows is 10,259,536... Dated stats can lead to bad query plans (insufficient memory allocated for joins and sorts, bad join algorithm choices...).

When Auto Update Statistics is set to true on the database, SQL Server will automatically update statistics based on a percentage (around 20%) of changed rows in the statistic. This works well when tables are small but stats can get dated on bigger tables. A statistic in a table with 10000 records will automatically be updated when around 2000 of it's records get changed (and life is good). For a table with 10000000 records however, the auto update will still occur at 20% or 2000000 changed records (resulting in some very bad plans and long gaps between stat updates).

With the release of SQL Server 2008 R2 SP1 (and all future versions) the threshold can be set to a dynamic value by using trace flag 2371. This addresses the big table problem. You can read more about it here: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

share|improve this answer
    
This table has ~80M records in it so I think we're hitting that 20% issue and that makes much more sense now. We're using 2008 in just a couple environments but 2008 R2 in most. This sounds like a compelling-enough reason to push forwards with upgrading those last remaining environments. Thanks!! –  Jaxidian Dec 11 '12 at 21:09
1  
@brain For lookups (and nested loops) you should compare the Actual Number of rows with (Estimated Number of Rows * Number of Executions). In this case it is 10259536 = (10259536 * 1). So there is no statistics mismatch on this Index. However, the Estimated Number of Executions (derived from the Estimated Number of Rows from the Outer side of the key lookup) 1216 is significantly lower than the actual number of executions, which may indicate a problem with the statistics on the NC Index. –  Roji P Thomas Dec 12 '12 at 1:21
    
+1 Good catch @Roji I looked right over that. Still 1216 is well below 10 million so there is still a statistics mismatch. Also, OP stated in the comment that updating stats resolved the issue. –  brian Dec 12 '12 at 2:04

If you remove WHERE Extent8.PriceTierID=tblPriceTiers.oid, you're left with this:

WHERE EXISTS
  (SELECT 1 FROM tblPriceTierQuantities [Extent8]
   INNER JOIN xxxx ON xxx=Extent8.AccountRateScheduleID)

Which does not correlate to the rest of the query. SQL Server can very quickly determine a SINGLE match between [Extent8] and [xxxx] and resolve the EXISTS clause.

However, your full query is not as easy - it has a dependency on tblPriceTiers.oid, hence it is much slower. The full operation should include these steps, based on the statistics shown:

JOIN (xxx=Extent8.AccountRateScheduleID)
output: Extent8.oid (clustered key reference)

Then, using Extent8.oid, the part you showed:
Key Lookup: Extent8.oid using (above output) output: Extent8.PriceTierID

This output is then used to satisfy the correlation
Extent8.PriceTierID=tblPriceTiers.oid

If you want a better answer, you should have posted the full execution plan.

share|improve this answer
    
Thanks for this! My understanding is that this extra "hop" that you mention shouldn't be necessary if I have an index on AccountRateScheduleID, PriceTierID, and oid. That said, much of my understanding (I'm a Dev who unfortunately also DBA's) is wrong. –  Jaxidian Dec 11 '12 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.