SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

This article is follow up of my previous article SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup. Please do read my previous article before continuing further.

I have described there two different methods to reduce query execution cost. Let us compare the performance of the SELECT statement of the previous query.

We have created two different indexes on the table.

Method 1: Creating covering non-clustered index.

In this method, we will create a non-clustered index that contains the columns used in the SELECT statement along with the column used in the WHERE clause.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover] ON [dbo].[OneIndex]
(
City, FirstName, ID
) ON [PRIMARY]
GO

Method 2: Creating included column non-lustered index.

In this method, we will create a nonclustered index that includes the columns used in the SELECT statement along with the column used in the WHERE clause. Here, we will use new syntax introduced in SQL Server 2005. An index with included nonkey columns can significantly improve query performance when all the columns in the query are included in the index.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName,ID) ON [PRIMARY]
GO

Let us compare the performance of our query by running the following query with two different indexes.

SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Cover))
WHERE City = 'Las Vegas'
GO
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Include))
WHERE City = 'Las Vegas'
GO

Let us examine the execution plan and compare the query costs.


In fact, in this example, the performance of both the queries is quite same. In this case, we can use either of the query and obtain the same performance.

I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.

1) Index can exceed the 900-byte limitation of the index key.

2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.

3) Size of the key index can be reduced, which improves the overall performance of the index operation.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Related Post:

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

18 thoughts on “SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

  1. hi every one,
    I need a query, and the requirement is–

    Count the number of customers that shopped 10 months ago, but have not returned since.
    (example: For October , count total number of customers that shopped in January. Then count the number of those customers that DID not return Feb-Sept. )

    My table name is customerInvoices, consists invoiceId, customerId and invoiceDate.

    I wrote the fallowing query for the october month( means the customer should have an invoice in january and he should not have any invoice in the next 8 months(feb-sep)).

    — To get lapsed customers of october
    select month(GETDATE())AS [Month],count(ci.customerId) AS [Number of Lapsed customers] from CustomerInvoices ci
    where YEAR(ci.invoiceDate)=2009
    and month(ci.invoiceDate)=MONTH(DATEADD(month,-09,getdate()))
    and customerID not in
    (
    select customerID from customerInvoices
    where (invoiceDate)>
    ( CAST(
    (CAST
    (month(DATEADD(MONTH,-08,GETDATE())) AS CHAR(2))
    +’-01-‘+
    CAST(YEAR(ci.invoiceDate) AS CHAR(4))) AS date)
    )
    and (invoiceDate)<
    (
    CAST(
    (CAST
    (month(DATEADD(MONTH,-01,GETDATE())) AS CHAR(2))
    +'-01-'+
    CAST(YEAR(ci.invoiceDate) AS CHAR(4))) AS date)
    )
    )

    It's working perfectly for the october month, but i want to display the result from january to october, how can i include other months also…

    If anyone knows this plz help,
    thank you.

    Like

  2. @Rajendra

    What I understand upto reading 1 time only is

    select distinct month from the year 2009 (january to this month)

    and then use CURSOR…

    I think this might help you..

    Like

  3. Hi Pinal,

    Great post, but how do i remove Bookmark lookup (table with clustered index) where it is not possible create index with include columns. Or is it possible?

    thanks!!

    Like

  4. Pingback: SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3 Journey to SQL Authority with Pinal Dave

  5. Pingback: SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup Journey to SQL Authority with Pinal Dave

  6. I need some help…. I am not a DBA, and am just getting into this SQL thing. I have a query established, but it is returning HTML characters in two columns. How can you eliminate that inside of the query. I have pasted the query below for your review.

    SELECT vWorkOrderReportRandle.WOOID, vWorkOrderReportRandle.WODateScheduled, vWorkOrderReportRandle.EquipDesc, vWorkOrderReportRandle.WOStatusDesc, vWorkOrderReportRandle.Type, vWorkOrderReportRandle.WOProblem, vWorkOrderReportRandle.WORepairsRequired, vWorkOrderReportRandle.WOLaborHoursEst, vWorkOrderReportRandle.WOCostAct, vWorkOrderReportRandle.WOCostEst, vWorkOrderReportRandle.WOKit, vWorkOrderReportRandle.Initiator, vWorkOrderReportRandle.Assignee, vWorkOrderReportRandle.WOStatus, vWorkOrderReportRandle.Requestor, vWorkOrderReportRandle.FullPath
    FROM HamptonMaintenance.dbo.vWorkOrderReportRandle vWorkOrderReportRandle

    Like

  7. Pingback: SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup Journey to SQL Authority with Pinal Dave

  8. I wanted to ask one question that is does the Include options to create covering index actually i.e. physically create non-clustered index on the column.

    Like

    • Hi,

      I’m not sure if I understood correctly. Did you meant…

      CREATE NONCLUSTERED INDEX IX_Foo ON FOO (bar) INCLUDE (baz)

      …that is there now a nonclustered index created on “baz” column?

      No, there isn’t. Index is created only on “bar” column. It’s just plain normal nonclustered index.

      But the data that’s in “baz” column is copied to TEMPDB and any read operation, that uses IX_Foo index, is done againts TEMPDB. If IX_Foo index is not used then the “baz” column is read from the actual data table.

      Like

  9. Hi,
    This is an awesome article. I came across to search method of non-clustered index on Google and I find this article on top. I obtained exact needs in this and now soon, I will be practical.
    Thanks for providing such useful information.

    Like

  10. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

  13. Why I cant remove Key Lookup? I am using SQL Sever 2008.
    I have a Document table and create a index as
    CREATE NONCLUSTERED INDEX [DOCUMENT_MPSACCOUNT_FK] ON [dbo].[DOCUMENT]
    (
    [MPSACCOUNTID] ASC
    )
    include (documentID,documenttypeid)
    GO

    The query plan shows it is still doing Key lookup for documentID and documenttypeid after Index Seek on INDEX [DOCUMENT_MPSACCOUNT_FK], why?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s