March 9, 2018 at 2:50 pm
Greetings, all.
Analyzing one of my query plans, as compared to the SQL code calling it, I noticed that the query plan did more key lookups than I think it should have. The SQL code involved consisted of several CTE's, which build on each other. There is one table that I only directly call once, although I reference the results of that CTE in a following CTE or two. In the query plan, SQL Server performs key lookups on that table 3 times. It seems that SQL Server might be calling the table each time the CTE results are called, rather than storing them in-memory? Something like this happens with another table as well, it is directly called twice, but there are three key lookups in the query plan. Those 6 key lookups are the most expensive operations in this particular query plan.
I'm just wondering if there are ways to improve a query like this, so that it doesn't perform multiple key lookups. Or, is referencing the SQL on-disk tables directly, actually more efficient (rather than using the CTE results which I would've thought were stored in-memory.) Or, does SQL Server not store CTE results in-memory?
Thanks,
Randy
March 9, 2018 at 2:54 pm
Well, sort of hard to help when we can't see what you see. Could you post the query and execution plan (as an *.sqlplan file)? It may also help to get the DDL for the tables and indexes.
March 13, 2018 at 1:51 am
wittr - Friday, March 9, 2018 2:50 PMGreetings, all.
Analyzing one of my query plans, as compared to the SQL code calling it, I noticed that the query plan did more key lookups than I think it should have. The SQL code involved consisted of several CTE's, which build on each other. There is one table that I only directly call once, although I reference the results of that CTE in a following CTE or two. In the query plan, SQL Server performs key lookups on that table 3 times. It seems that SQL Server might be calling the table each time the CTE results are called, rather than storing them in-memory? Something like this happens with another table as well, it is directly called twice, but there are three key lookups in the query plan. Those 6 key lookups are the most expensive operations in this particular query plan.
I'm just wondering if there are ways to improve a query like this, so that it doesn't perform multiple key lookups. Or, is referencing the SQL on-disk tables directly, actually more efficient (rather than using the CTE results which I would've thought were stored in-memory.) Or, does SQL Server not store CTE results in-memory?
Thanks,
Randy
Key lookups might've occurred in query plan when the query optimizer tried to resolve the query thru a non clustered indexed column. Could you please post the query for us?
March 13, 2018 at 4:33 am
wittr - Friday, March 9, 2018 2:50 PMGreetings, all.
Analyzing one of my query plans, as compared to the SQL code calling it, I noticed that the query plan did more key lookups than I think it should have. The SQL code involved consisted of several CTE's, which build on each other. There is one table that I only directly call once, although I reference the results of that CTE in a following CTE or two. In the query plan, SQL Server performs key lookups on that table 3 times. It seems that SQL Server might be calling the table each time the CTE results are called, rather than storing them in-memory? Something like this happens with another table as well, it is directly called twice, but there are three key lookups in the query plan. Those 6 key lookups are the most expensive operations in this particular query plan.
I'm just wondering if there are ways to improve a query like this, so that it doesn't perform multiple key lookups. Or, is referencing the SQL on-disk tables directly, actually more efficient (rather than using the CTE results which I would've thought were stored in-memory.) Or, does SQL Server not store CTE results in-memory?
Thanks,
Randy
"Or, does SQL Server not store CTE results in-memory? " - this bit.
Except for recursive CTE's, they're simply a coding construct.
If your CTE is referenced more than once in the top-level query, then you might benefit from persisting the result as a #temp table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2018 at 6:36 am
wittr - Friday, March 9, 2018 2:50 PMGreetings, all.
Analyzing one of my query plans, as compared to the SQL code calling it, I noticed that the query plan did more key lookups than I think it should have. The SQL code involved consisted of several CTE's, which build on each other. There is one table that I only directly call once, although I reference the results of that CTE in a following CTE or two. In the query plan, SQL Server performs key lookups on that table 3 times. It seems that SQL Server might be calling the table each time the CTE results are called, rather than storing them in-memory? Something like this happens with another table as well, it is directly called twice, but there are three key lookups in the query plan. Those 6 key lookups are the most expensive operations in this particular query plan.
I'm just wondering if there are ways to improve a query like this, so that it doesn't perform multiple key lookups. Or, is referencing the SQL on-disk tables directly, actually more efficient (rather than using the CTE results which I would've thought were stored in-memory.) Or, does SQL Server not store CTE results in-memory?
Thanks,
Randy
Usually, the reason for a key lookup has little to do with the usage of a CTE, but with what the overall query has to return. If the columns that are being looked up are not part of any index save for the clustered one, then it's no surprise that a key lookup occurs when said columns are part of the final SELECT. I suspect you need to look at your indexing, but without seeing either your query or your execution plan, this is pure guesswork. Query structure can also contribute to the optimizer deciding that the key lookup is less expensive than the alternative, from it's point of view. However, so can out of date statistics. Please post your query and the query plan, because without those, it's all SWAG (scientific wild-a$$ guesswork).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply