A Celebrity among all Execution Plan Operators – KEYLOOKUP

I will start with this contour, this operator is crazy on changing its name, and it has changed its name three times in the last three release of SQL Server. From BookMark Lookup to Cluster Index Seek and now it is Key Lookup. I am personally comfortable with Key Lookup, which make more sense to me to the way it works.

Few facts I would like to point out here about KeyLook Up.

• It typically comes with Non Clustered Index.
• It fetches values via cluster index when the required values are not present in (Cover in) Non Clustered Index.
•  It perform one of the expensive IO operation, I will take on this further in sometime  :)

I want to take it further to explain more on this Celebrity operator, when rows are requested by the query with predicate, optimizer look into non-clustered index applied on column participate in where predicate to retrieve the requested data. If the all requested data is not found in Non Clustered Index then it go to data page where the data is stored and retrieve it.

Now in above situation, if clustered Index present on table, it goes to Clustered index to get the physical location of data and this operation is called as KEYLOOKUP and if there is no clustered index present on table then there is something called RID lookup comes into picture.

Now it’s time to get yourself relaxed and have popcorn with cup of tea, ah I m addicted to Tea, you can have CokeJ

Ya, now we can take up our point for more understanding “It perform one of the expensive IO operation”

It is one of the most expensive operations because for each value in non clustered index it goes to clustered index to retrieve the asked data.

Lets stimulate a condition where we have Keylookup operator comes into picture in execution plan.

I am using AdventureWorks2008R2 database and Person.Address Table to stimulate the condition, let’s see how many different indexes are present on Person.Address table

select * from sys.indexes where object_id = OBJECT_ID('Person.Address')

Table   name                         type_desc

------- ---------------------------  ---------------

Address PK_Address_AddressID         CLUSTERED

Address AK_Address_rowguid           NONCLUSTERED

Address IX_Address_AddressLine1_    

        AddressLine2_City_           

        StateProvinceID_PostalCode   NONCLUSTERED

Address IX_Address_StateProvinceID   NONCLUSTERED

So here we can see, we have 4 indexes, one cluster and 3 non clustered index. Lets disable all non clustered indexes first.

alter index AK_Address_rowguid

on Person.Address DISABLE

go

alter index IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode

on Person.Address DISABLE

go

alter index IX_Address_StateProvinceID

on Person.Address DISABLE

go

Command(s) completed successfully.

Now lets fire the query

select AddressID, City, StateProvinceID from Person.Address where City = 'Las Vegas'

Here we are creating a non clustered index on City column which is used in where predicate. Once it s done, again we will check the query Plan.

CREATE NONCLUSTERED INDEX ix_PersonAddress_City

ON [Person].[Address] ([City])

Now lets query the same select statement to see how this newly created index perform, is it  better then the above clustered index scan method

select AddressID, City, StateProvinceID from Person.Address where City = 'Las Vegas'

To be honest, seeing Keylookup in Execution plan excites SQL guy, because he has a good chance to improve further the performance of Query by simply creating Covering Index. I will take covering index in separate article.

Here Key Lookup operator is used to read the value of AddressID, StateProvinceID from by referencing to clustered Index, since this two column are not included in Non Clustered Index.

But at the same time Key Lookup Operator will worsen your cost, if the number of rows to return is more because for each rows it has to switch to Clustered Index to get the value. So that moments scan would be more efficient strategy then Key Lookup. But yes we have the option of Covering those column in Non Clustered Index which prompt Key Lookup by creating Covering Index.

         

If I can love someone, I need to love her as I love my Programming with SQL Server! :)

 

Post Reference: Vikram Aristocratic Elfin Share

Listing Next 5 Sunday

Again I got an opportunity to write an interesting piece of College Code in response to the question posted on one of my SQL Server forum. The problem statement was

“I want a list of next five Sunday, how I can achieve this through T-SQL”

Ya its very simple, just a couple of Date and String function, blend it with college logic, have your tea with Parle G and that’s all code will all set to fly high. Enjoy Sunday J

So here I am sharing a piece of code which will do the rest, the code is self explanatory, So I don’t think I should buy my time to catch up anything from code to explain.

declare @dt datetime

declare @howMany int

declare @cntr int

set @cntr = 0

set @howMany = 6

set @dt = getdate()

create table #temp

(

SundayCol datetime,

WeekDayName varchar(10)

)

while (datename(w,@dt) not like 'Sunday')

begin

       set @dt= dateadd(dd,1,@dt)

end

--select datename(w,@dt)

insert into #temp

select @dt, Datename(w,@dt)

while (@cntr < @howMany)

begin

       set @dt = dateadd(dd,7,@dt)

       insert into #temp

       select @dt, Datename(w,@dt)

       set @cntr = @cntr + 1

end

select convert(varchar(10),SundayCol,103), WeekDayName from #temp

drop table #temp

Execute it and output you will get is next 6 Sunday. Enjoy your Sunday J

Date       WeekDayName

---------- -----------

29/06/2014 Sunday

06/07/2014 Sunday

13/07/2014 Sunday

20/07/2014 Sunday

27/07/2014 Sunday

03/08/2014 Sunday

10/08/2014 Sunday

The popping up of Sunday every week makes me happy and my potential double up to buys more time for my articles on SQL! J

 

Post Reference: Vikram Aristocratic Elfin Share

Sometime Index SCAN Operation outperform Index SEEK

Here I will try to stimulate an example where we will see Index scan outperform Index seek. But before that let me define theses two term.

Index Scan: Scan touches every row in a table, whether or not it qualifies the where predicate. It is good if your table is small or most rows qualifies where predicate.

Index Seek: Whereas Seek only touches those rows and pages which qualifies where predicate. It is efficient when you have big table and only a small set of data qualifies your predicate.

Lets come back to our problem statement, to stimulate the situation where index scan outperform we are creating a table with five column.

create table Tab_ScanVsSeek

(id int identity(1,1) primary key,

col1 varchar(300) not null default newid(),

col2 varchar(300) not null default newid(),

col3 varchar(300) not null default newid(),

col4 datetime not null default getdate())

go

Command(s) completed successfully.

Table is ready, lets insert some good amount of records in it.

set nocount on

go

insert into Tab_ScanVsSeek default values

go 100000

Beginning execution loop

Batch execution completed 100000 times.

select * from Tab_ScanVsSeek

Now here we are creating non clustered index on col1,col2,col3 leaving col4 out of index.

create nonclustered index ix_ScanVsSeek_Col1Col2Col3 on Tab_ScanVsSeek(col1, col2, col3)

go

Command(s) completed successfully.

Now lets fire the query to get the records where col1 starts with ‘a’. and see the statistic of the query.

set statistics io on

select *

from Tab_ScanVsSeek

where col1 like 'a%'

set statistics io off

Table 'Tab_ScanVsSeek'. Scan count 1, logical reads 1702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So we can see Logical read is 1702 and in the execution plan below we can see even though we have non cluster index on col1, the index didn’t participate in query execution.

Now lets force the query to use the NON CLUSTERED index we created on the column col1, and see the result in term of logical read.

set statistics io on

select *

from Tab_ScanVsSeek with(index = ix_ScanVsSeek_Col1Col2Col3)

where col1 like 'a%'

set statistics io off

Table 'Tab_ScanVsSeek'. Scan count 1, logical reads 19352, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here we saw the Logical reads increased to 19352, which s extreme big number compare to previous approach. If you see execution plan Non cluster Index comes into play, and if dig more on Execution plan, the cost increased due to Key Lookup operator. So we found in this situation Index scan perform better.

Now lets execute both the query in single batch and find which index take less cost compare to other

set statistics io on

select *

from Tab_ScanVsSeek

where col1 like 'a%'

set statistics io off

set statistics io on

select *

from Tab_ScanVsSeek with(index = ix_ScanVsSeek_Col1Col2Col3)

where col1 like 'a%'

set statistics io off

So here we saw, from batch execution of both query at a time that query1 take cost of only 18% of total batch execution whereas Index seek takes 82% high cost. So the conclusion is in this example index seek outperform index scan.

Coder cannot afford to lose temperament; this is the heart and soul of every programming! J

 

Post Reference: Vikram Aristocratic Elfin Share