August, 2009 | Michael J. Swart

August 31, 2009

Get Rid of RID Lookups

Takeaway: If you see an unexpected RID lookup in an execution plan, consider adding a clustered index as a db schema improvement.

What is a RID lookup?

You may come across the RID lookup operator as part of an execution plan. With Management Studio, RID lookups are displayed with the following icon (sort of … I fancied it up a little).

A RID Lookup is a lookup into a heap using a Row ID. The Row is included with entries in a non-clustered index in order to find the rest of a table’s data in a heap. (Remember, with a heap, the table data is stored unordered so a Row ID is needed for the correlation).

The RID Lookup’s official docs can be read here at Books Online. But it’s light on the details and it references a more thorough explanation of lookups in general which can be found at Craig Freedman’s blog here.

They’re bad… or at least not good.

When you find an RID Lookup in a query plan, it’s a symptom. It indicates a database schema that breaks several rules of thumb. Those rules-of-thumb are:

Each table should have a clustered index (of course there are exceptions but we’re dealing with rules-of-thumb here).
A non-clustered index has been created indicating that someone somewhere identified an ordering on one or more columns that made sense for that data.
There is at least one query (i.e. the one that generated the RID Lookup) that needs columns that are not covered by the non-clustered index.

These three points mean a wasted opportunity. A clustered index should have been created. A heap plus a non-clustered index take about the same space as a clustered index. At least logically: the non-clustered pages map to the clustered index’s index nodes and the heap’s pages map to the clustered index’s leaf nodes.

If possible, definitely consider modifying the schema to include a clustered index (After assessing risks of course).

Really? Always?

Well of course no respectable blogger writes in absolutes and Grant Fritchley has a video here that discusses RID lookups further. He says that when the RID lookup only deals with one row, that the impact is negligible. True, but had the table been designed with a clustered index, the performance would have been no worse.

-- Comments (26)

August 10, 2009

Another LEFT JOIN example

Filed under: Technical Articles,Tongue In Cheek — Tags: left join example — Michael J. Swart @ 11:22 am

A posting by Denis Gobo called Teaser: Left Join..the SQL ego crusher prompted me to write a query that illustrates what happens when a condition is placed in a LEFT OUTER JOIN’s ON clause versus when a condition is placed in a WHERE clause (SQL 2008 syntax):

DECLARE @strings TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX));
INSERT @strings(val) VALUES
   ('When using a left outer join,'),
   ('you'),
   ('put the condition'),
   ('in the outer join clause and not'),
   ('in the where clause. In effect'),
   ('all rows are returned and no'),
   ('filtering out '),
   ('is done.'),
   ('You can see that all rows are '),
   ('returned, both primes and'),
   ('non-primes');
 
DECLARE @primes TABLE (id INT)
INSERT @primes VALUES(2), (3), (5), (7), (11);
 
-- a query with a proper left join
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
ON s.id = p.id
ORDER BY s.id;
 
-- a query with the condition in the where clause
SELECT p.id, s.val
FROM @strings s
LEFT OUTER JOIN @primes p
   ON 1 =1
WHERE s.id = p.id
ORDER BY s.id;

Kinda reminds me of those Mad Magazine fold-ins.

-- Comments (13)

August 7, 2009

Intellectual Property Issues on SQL Blogs.

Filed under: Miscelleaneous SQL — Michael J. Swart @ 9:26 am

Late last week I got an email from a guy preparing to give a Sql Server seminar. He asked whether it would be okay if he used a drawing I did in an earlier blog post (with attribution of course). I’m not sure I replied in time for the seminar.

So since I’m glad to let anyone use my stuff for training (some rights reserved), I thought I’d license my blog under creative commons. You can find the details at the bottom of this page (unless you’re reading through RSS).

It was pretty easy, in case you’re wondering, you can generate nifty html to license your stuff on CC’s site starting here.

I thought this post fitting considering the fact that some bloggers I follow (notably Jonathan Kehayias, and Brent Ozar) have talked about blog plagiarism lately. So I wanted to make it explicitly clear what’s okay with the content of this blog.

-- Comments (0)