Remove Key Lookup, the performance Degrader from your Execution Plan

Read Previous Post: Remove your RID Lookup, the performance Degrader

In first series of this article,  we were focusing on finding TABLE SCAN operator in execution plan, and finding it we try to remove it by creating clustered index,

Then we saw, how can we eliminate index scan to index seek by creating nonclustered index, but by doing this we introduce one more operator in the execution plan that us Key Look up operator.

Now what is Keylook up operator??

Key Lookup Operator: When your nonclustered index doesn’t cover all the data required by the select query it jumps to cluster index to get the data.

This reflect that Key Lookup is a very expensive operation because it perform a IO to clustered index the from clustered to heap to get the data which is not covered by nonclutered index.

But to be frank, I am always happy to see key lookup because I know I get a chance to improve the performance of query by removing the key lookup.

  

Lets stimulate a situation where we get lookup operator

CREATE TABLE RIDLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

It’s time to create clustered index in column on id field.

create clustered index ix_RIDLookup_Demo_TB_id

on RIDLookup_Demo_TB(id)

Now since cluster index is in place, lets create non cluster index on column col2

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Let’s fire the Query, to see whether we are able to remove Key Lookup

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

The next focus will be removing KEYLOOK from the execution plan.

Now since we have stimulated the Key lookup its time to remove the key lookup from the execution plan. The work around solution is covering index.

To create covering index, first we will delete the nonclustered index we created on column col2.

drop index [ix_col2] on RIDLookup_Demo_TB

Command(s) completed successfully.

Now lets create covering nonclustered index on the same column

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc) include (col3)

Command(s) completed successfully.

Now lets fire the query to see whether we are able to remove the Key Lookup operator.

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

So here we saw with the help of Covering index we are able to remove the Key Lookup operator.

 I love my sleepless night when you are with me… me and my love SQL Server ;)

 

Post Reference: Vikram Aristocratic Elfin Share

Remove your RID Lookup, the performance Degrader

Either a rotten Apple or a bad fish spoil the whole pond, as a kid we all heard this quote, but did we understand the meaning behind it.

Spoiling our Execution plan by a bad fish, that what we would not want in our Plan cache. Here we are checking how RID lookup is ruining the whole plan and then we look out work around solution to the RID lookup problem.

RID Lookup: When you have Non Clustered index on a table without having Cluster index, then a RID corresponding to each key value of NONCLUSTERED  index is put on place in order to find the rest of the data from the table(HEAP)

A HEAP table is one without CLUSTERED index, and data are sorted in the table in the order in which insertion is made, but it is not guaranteed because to increase the efficiency optimizer can move the data around  in the heap. So it is always advisable to use order when you scan heap table.

Lets check with sample demo, how to remove RID Lookup,

CREATE TABLE RIDLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO RIDLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

Since we are concern about RID LOOKUP, which is associated with Non Clustered index on table which has no Clustered index define.

it’s time to create Non Clustered Index on col2 of table.

create nonclustered index [ix_col2]

on RIDLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now since NonClustered Index is in place, lets fire the same query to see the RID Lookup in the Execution Plan.

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

Work around solution

When I see RID Lookup, I always think, why the table has no clustered index on it. So the solution is creating Clustered Index on the table.

create clustered index ix_RIDLookup_Demo_TB_id

on RIDLookup_Demo_TB(id)

Let’s fire the Query, to see whether we are able to remove RID lookup

SELECT SUM(col3) FROM RIDLookup_Demo_TB WHERE col2='20140617'

So here we can see we are able to successfully remove RID lookup from Heap.

The next focus will be removing KEYLOOK from the execution plan.

Thinking to leave with you scared, Ah you are wonderful! mY cODE J

 

Post Reference: Vikram Aristocratic Elfin Share

Insert Multiple Null value in an Unique Field through NONCLUSTERED Index

Read Previous Post: Allowing only two null values for a column in SQL table

Problem Statement: We want a unique constraint in such a way that, it maintains uniqueness of the values in the field along with it; it allows entering multiple NULL value in the field.

There is an exception with unique constraint, which evaluate two null values as duplicate whereas, ANSI says any operation with NULL evaluate to UNKNOWN.  Here we are trying to find the alternative way to insert multiple null value in Unique filed through NONCLUTERED Index.

We can implement the same using trigger, as we demonstrate in previous post; here we will try to implement the same using UNIQUE NONCLUSTERED Index.

Here we are creating table with three column id, name and salary.

create table TestMutipleNull_TB

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

name varchar(10),

salary decimal)

Command(s) completed successfully.

Table creation is done; let’s create a UNIQUE NON CLUSTERTED Index on name field.

create unique nonclustered index ix_TestMultipleNull_TB_name

on TestMutipleNull_TB(name) where name is not null

Command(s) completed successfully.

Here the index has where condition which says name is not null, so the uniqueness will applied to only non-null value keeping null value aside, which is our objective to do in this article.

Lets insert some data in the table.

insert into TestMutipleNull_TB

select 'Babu',null union all

select 'Rinny',30000 union all

select 'Binny',null union all

select 'Lopa', 50000

Now here we are trying to insert a name which is present in the table.

insert into TestMutipleNull_TB values('Babu',40000)

Msg 2601, Level 14, State 1, Line 17

Cannot insert duplicate key row in object 'dbo.TestMutipleNull_TB' with unique index 'ix_TestMultipleNull_TB_salary'. The duplicate key value is (Babu).

The statement has been terminated.

Since the same name is present in the table, the insertion fails. This means uniqueness is maintained.

Now lets try to insert multiple null values in the name field.

insert into TestMutipleNull_TB values(null,40000)

(1 row(s) affected)

insert into TestMutipleNull_TB values(null,200)

(1 row(s) affected)

select * from TestMutipleNull_TB(nolock)

id          name       salary

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

1           Babu       NULL

2           Rinny      30000

3           Binny      NULL

4           Lopa       50000

6           NULL       40000

7           NULL       200

So here we saw multiple null values gets inserted into name field but it maintain the uniqueness of non null values.

Conclusion: You can use NONCLUSTERED Index to allow multiple null values keeping other values unique.

Software Engineer: Life became NULL if you go away my darling; me and my code :)

Post Reference: Vikram Aristocratic Elfin Share