Friday, January 11, 2013

How to remove Key Lookup on your query plan

Key Lookup was Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column(Predicate) contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. Query performance can be improved by adding a covering index on nonclustered index.

When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.

use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]

--Find list of index currently on table  [Sales.SalesOrderDetail]

index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID

-- Run the this two queries with display estimated execution plan
Select ProductID 
From Sales.SalesOrderDetail 
Where ProductID = 776
go
select ProductID, OrderQty 
from Sales.SalesOrderDetail 
where ProductID =776
go

As you can see Key Lookup operator for second query. How would you simply remove from the query plan.?  Add an QrderQty column into INCLUDE on non clustered Index idx_SalesOrderDetail.



As you can see the Key Lookup has been removed from display execution plan

Source : Craig Freedman thoughts from his blog
Bookmark lookup is not a cheap operation.  Assuming (as is commonly the case) that there is no correlation between the non-clustered and clustered index keys, each bookmark lookup performs a random I/O into the clustered index.  Random I/Os are very expensive.  When comparing various plan alternatives including scans, seeks, and seeks with bookmark lookups, the optimizer must decide whether it is cheaper to perform more sequential I/Os and touch more rows using an index scan or a seek with a less selective predicate that covers all required columns or to perform fewer random I/Os and touch fewer rows using a seek with a more selective predicate and a bookmark lookup.

Wednesday, January 09, 2013

How do you find SQL Server version info

There are five place you can find a SQL Server version information.

--1 select @@version
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright
(c) Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

--2 exec sp
exec sys.sp_MSgetversion

10.50.1617.0 1 3

 -- 3 SQL Server Logs
Date 09/01/2013 07:12:16Log SQL Server (Current - 09/01/2013 17:18:00)Source
ServerMessage
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11
:57:00 Copyright
(c) Microsoft CorporationEnterprise Evaluation Edition
on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- 4 SERVERPROPERTY
SELECT SERVERPROPERTY('edition'),SERVERPROPERTY('ProductVersion'),SERVERPROPERTY('ProductLevel')Enterprise Evaluation Edition 10.50.1617.0 RTM


--5 exec sys.xp_msver



Still you can find sql server version information from Windows Registry but i do not want to expose more on it here.