May 9, 2011 at 12:53 pm
I'm playing with code from the 70-441 Database Design exam book (MS Press). Chapter 4, lesson 2. When I added a non-clustered index to a table, I came up with a bizarre Execution plan.
Here's the code:
CREATE TABLE dbo.MyContacts
(ContactId int NOT NULL,
Title nvarchar(16) NULL,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
Suffix nvarchar(200) NULL,
EmailAddress nvarchar(100) NULL,
Phone nvarchar(50) NULL);
GO
INSERT INTO dbo.MyContacts
SELECT '1','Mr.','Gustavo','Achong','','gustavo0@adventure-works.com','398-555-0132' UNION ALL
SELECT '2','Ms.','Catherine','Abel','','catherine0@adventure-works.com','747-555-0171' UNION ALL
SELECT '3','Ms.','Kim','Abercrombie','','kim2@adventure-works.com','334-555-0137' UNION ALL
SELECT '4','Sr.','Humberto','Acevedo','','humberto0@adventure-works.com','599-555-0127' UNION ALL
SELECT '5','Sra.','Pilar','Ackerman','','pilar1@adventure-works.com','1 (11) 500 555-0132' UNION ALL
SELECT '6','Ms.','Frances','Adams','','frances0@adventure-works.com','991-555-0183' UNION ALL
SELECT '7','Ms.','Margaret','Smith','','margaret0@adventure-works.com','959-555-0151' UNION ALL
SELECT '8','Ms.','Carla','Adams','','carla0@adventure-works.com','107-555-0138' UNION ALL
SELECT '9','Mr.','Jay','Adams','','jay1@adventure-works.com','158-555-0142' UNION ALL
SELECT '10','Mr.','Ronald','Adina','','ronald0@adventure-works.com','453-555-0165' UNION ALL
SELECT '11','Mr.','Samuel','Agcaoili','','samuel0@adventure-works.com','554-555-0110' UNION ALL
SELECT '12','Mr.','James','Aguilar','Jr.','james2@adventure-works.com','1 (11) 500 555-0198' UNION ALL
SELECT '13','Mr.','Robert','Ahlering','','robert1@adventure-works.com','678-555-0175' UNION ALL
SELECT '14','Mr.','François','Ferrier','','françois1@adventure-works.com','571-555-0128' UNION ALL
SELECT '15','Ms.','Kim','Akers','','kim3@adventure-works.com','440-555-0166' UNION ALL
SELECT '16','Ms.','Lili','Alameda','','lili0@adventure-works.com','1 (11) 500 555-0150' UNION ALL
SELECT '17','Ms.','Amy','Alberts','','amy1@adventure-works.com','727-555-0115' UNION ALL
SELECT '18','Ms.','Anna','Albright','','anna0@adventure-works.com','197-555-0143' UNION ALL
SELECT '19','Mr.','Milton','Albury','','milton0@adventure-works.com','492-555-0189' UNION ALL
SELECT '20','Mr.','Paul','Alcorn','','paul2@adventure-works.com','331-555-0162' UNION ALL
SELECT '1350','','Greg','Guzik','','greg6@adventure-works.com','138-555-0100'
--I didn't include every last bit of data. Basically, the entire table is a copy of
-- AdventureWorks.Person.Contact
-- Turn on the actual execution plan
-- Search for the contact 1350
-- You should get a table scan
SELECT ContactId,
FirstName, LastName
FROM dbo.MyContacts
WHERE ContactId = 1350;
GO
-- Create a unique clustered index on the ContactId column
CREATE UNIQUE CLUSTERED INDEX CL_Mycontacts_ContactId
ON dbo.MyContacts(ContactId);
GO
-- Repeat the contact 1350 query
SELECT ContactId,
FirstName, LastName
FROM dbo.MyContacts
WHERE ContactId = 1350;
GO
-- Exercise 2: Creating a Non-Clustered Index
-- Search for the contact with LastName Guzik
SELECT ContactId,
FirstName, LastName
FROM dbo.MyContacts
WHERE LastName = 'Guzik';
GO
-- Create a non-clustered index on the LastName column
CREATE NONCLUSTERED INDEX NCL_Mycontacts_LastName
ON dbo.MyContacts(LastName);
GO
-- Repeat the contact Guzik query
SELECT ContactId,
FirstName, LastName
FROM dbo.MyContacts
WHERE LastName = 'Guzik';
GO
It's at the last SELECT that things go weird. The book says I should see an Index Seek for the nonclustered index seek and a Key Lookup operator for the lookup in the clustered index for the actual data pages. Instead, I get a Nested Loops Inner Join (cost 0) that leads to the Index Seek for the nonclustered index and an Index Seek for the clustered index (no lookup operator).
You may or may not see it if you just use the data I've provided. If you want to replace my SELECT list with all the data from AW, just see the attachment provided and copy it over my data.
Anyway, does anyone have any idea where the Nested Loops Inner Join operator came from? That's the part that's bugging me. There's no join in the code.
EDIT: Fixed string quote in code and attachment.
May 9, 2011 at 1:31 pm
Brandie Tarvin (5/9/2011)
It's at the last SELECT that things go weird. The book says I should see an Index Seek for the nonclustered index seek and a Key Lookup operator for the lookup in the clustered index for the actual data pages. Instead, I get a Nested Loops Inner Join (cost 0) that leads to the Index Seek for the nonclustered index and an Index Seek for the clustered index (no lookup operator).
You're using Management Studio from SQL 2005 and you have not patched the client tools.
http://sqlinthewild.co.za/index.php/2009/01/27/a-bookmark-lookup-by-any-other-name/
Install at least SP2 on the client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2011 at 4:29 am
GilaMonster (5/9/2011)
Brandie Tarvin (5/9/2011)
It's at the last SELECT that things go weird. The book says I should see an Index Seek for the nonclustered index seek and a Key Lookup operator for the lookup in the clustered index for the actual data pages. Instead, I get a Nested Loops Inner Join (cost 0) that leads to the Index Seek for the nonclustered index and an Index Seek for the clustered index (no lookup operator).You're using Management Studio from SQL 2005 and you have not patched the client tools.
http://sqlinthewild.co.za/index.php/2009/01/27/a-bookmark-lookup-by-any-other-name/
Install at least SP2 on the client.
Ahha. Thank you, Gail.
EDIT: Wait a sec. Was the Bookmark Lookup doing a Nested Inner Join behind the scenes in SQL 2000? Did we just not see it?
May 10, 2011 at 6:07 am
Brandie Tarvin (5/10/2011)
EDIT: Wait a sec. Was the Bookmark Lookup doing a Nested Inner Join behind the scenes in SQL 2000? Did we just not see it?
Yeah, kinda.
The (high level) explanation of a key lookup (bookmark lookup) is as follows:
For each row in the resultset
Seek against the clustered index to find the additional columns
Join the row returned from the cluster to the row in the resultset.
That 'for each row... join with another row' is a nested loop.
SQL 2000 showed the entire thing as a bookmark lookup. SQL 2005 RTM and SP1 showed 1 and 3 as a nested loop and 2 as a clustered index seek. SQL 2005 SP2 and beyond show 1 and 3 as a nested loop and 2 as a key lookup (RID if it's a heap not a cluster)
Despite the change in how it's shown, it's the same operator, does the same thing - single row clustered index seek for each row that needs the lookup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2011 at 6:09 am
Thank you for the explanation. That's exactly what I needed to know.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply