Performance Tuning
SQL Server Pop Quiz: A Key Lookup Without the Index
How do we get this to show up in an execution plan:
Without having an index seek or index scan operation on the same table?
Take any copy of the Stack Overflow database and write a query that will produce a key lookup on the Users, table, but it’s not allowed to have an index seek or index scan operation on the Users table. You’re allowed to change the database structure – for example, if you need to change the clustered index, drop it, add nonclustered indexes, whatever – and of course include those changes in your comment as well.
Post your query in the comments here by end of day Wednesday, and I’ll pick my favorites and put ’em in the blog on Friday. My favorite creative answers will get 1 year access to the Recorded Class Season Pass: Masters Classes. My own answer blog post is written & scheduled for Thursday.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Okay, this one took some out of the box thinking. Here’s what I tried:
Add a ton of newlines to the name of the PK, this adds a lot of vertical space to each “row” of the graphical query plan. When you can only see one operator on the screen at a time, is that mission accomplished? (Not quite, No seeks/scans allowed even offscreen).
Adding weird unicode to identifiers to take advantage of maybe some rendering quirk? Unicode is notoriously tricky. (But I couldn’t get this to work.)
Is there some permission that will selectively hide the operators you’re not allowed to see? (Nope showplan privileges aren’t that granular)
I recognized that key lookups are still just seeks into a table. They’re only shown as key lookups when SQL Server renders the query plan and needs to pick an object. I hacked the xml and added Lookup=”True” to an index seek. That’s enough for SQL Server to say, If you say so boss, I’ll use the “key lookup” operator.
This logic seems to work in paste-the-plan too:
https://www.brentozar.com/pastetheplan/?id=Syj3nxX25
HAHAHA, I love that, hahaha. That’s awesome. That’s definitely not a solution I was expecting, and I salute it!
In that same spirit, one can hack the XML so that the seek proceeding the key lookup doesn’t appear to be on the Users table.
https://www.brentozar.com/pastetheplan/?id=Hy25srXhq
Looking forward to learning the answer you have in mind.
you have a single row key lookup showing as 54% of the plan cost. Given the single row key lookup is 0.003281, that would make the remaining portion 46%, or 0.002797.
OK, great! Now all you have to do is… write the query. 😉
This will win no prizes for creativity, and requires Enterprise or equivalent:
[sql]
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING
AS
SELECT U.Id
FROM dbo.Users AS U
WHERE U.AccountId = -1;
GO
CREATE UNIQUE CLUSTERED INDEX c ON dbo.V (Id);
GO
SELECT U.DisplayName
FROM dbo.Users AS U
WHERE U.AccountId = -1
OPTION (KEEP PLAN);
[/sql]
Yesss! And this was similar to my blog post solution coming up this week. Nicely done!
I do have an interesting RID Lookup without a same-table seek or scan.
See the actual plan for the DELETE below:
Look at that witchcraft! That is indeed odd!
Did your follow-up get published, Brent? I can’t find it 🙁
This solution is similar to Michael’s (I was too late).
No Index Seek or Scan – Check
Only Key lookup – Check (bonus points for the same percentage)
Does it show up in the Execution plan? – Check. It’s the column name
tsqlSELECT CAST(N'
' AS xml) AS [Execution plan]
I put it into Pastebin. Even PasteThePlan had problems.
https://pastebin.com/bxDqhki1
Hahaha, yeah, that’s creative alright!
I have the same solution as the first post … after two days with various indexes and views playing … LOL