Sql-server – Can’t reduce cost of query plan and get rid of Key Lookup because of cursor

bookmark-lookupcursorsexecution-plansql server

I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere .
I have read here that it has to do with the Cursor .

Is there any way I could keep the Cursor and get reduce the cost of the Query plan and get rid of the Key Lookup?

     DECLARE @ClaimUniqueNo INT
        DECLARE ClaimAudit CURSOR
                FOR
                    SELECT CA.Claim_Audit_ID, CA.Claim_Audit_Action, CA.Create_Date, CA.Created_By, C.Claim_Status
                    FROM dbo.Claim_Audit_Tbl CA LEFT JOIN Claim_Tbl C 
                    ON CA.Claim_Unique_No = C.Claim_Unique_No
                    WHERE CA.Claim_Unique_No = @ClaimUniqueNo
                    ORDER BY Create_Date ASC

Best Answer

Two solutions were mentioned in the comments to your question. Let's review both of them.

Use a READ_ONLY cursor

As the article implies, are you able to use the STATIC and READ-ONLY arguments? – Jacob H 23 hours

That comment refers to Paul Randal's blog post Adventures in query tuning: unexpected key lookups. This points out that a cursor type is dynamic optimistic by default. From the Microsoft Docs, you can see that the "optimistic" part is what's causing all your problematic key lookups:

OPTIMISTIC
...
It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor.

So it has to do the key lookups to check and see if the rows you've read in have been modified.

The solution proposed in the blog post, and by Jacob H, is to use a more restrictive cursor type (such as READ_ONLY) in order to avoid these extra key lookups entirely.

Optimize the index

You can improve part of the Key Lookup to support the predicate by adding Claim_Unique_No to the nonclustered index as a key column. – sp_BlitzErik 23 hours ago

The query is currently using the clustered index to do the key lookup.

This comment points out that you could add Claim_Unique_No as a key column in your existing nonclustered index (the one being used in the scan: IDX_Claim_Audit...) to support the predicate (it looks like it already supports the output list that you want).

Related Solutions

Sql-server – get SSMS to show me the Actual query costs in the Execution plan pane

I don't know of a way to do this in the plan from Management Studio, but this is one of the many things the free SentryOne Plan Explorer will do for you when you generate an actual plan from within the tool - it includes all the runtime metrics per statement.

Sql-server – Key Lookup and Full-text index

Key lookup operations can be avoided by the use of a covering index. Full-text-indexes cannot be "included" in a covering index, however, including the TitleTable in the covering index is still useful since SQL Server can find all the details it needs for the query, aside from the full-text-query results, by seeking the covering index.

I've created a simple test-bed to show this in action.

First, we'll create an empty database for our test, since we cannot create full-text catalogs in tempdb:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'FullTextTest') 
BEGIN
    ALTER DATABASE FullTextTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FullTextTest;
END
CREATE DATABASE FullTextTest;
ALTER DATABASE FullTextTest SET RECOVERY SIMPLE;
BACKUP DATABASE FullTextTest TO DISK = 'NUL:';
GO

Here, we'll create a mock-up of your table. You say in the question that all mentioned columns have an associated non-clustered index, so we'll define those too:

USE FullTextTest;
GO

CREATE FULLTEXT CATALOG ftc
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo;

CREATE TABLE dbo.TitleTable
(
    PK_ID int NOT NULL
        CONSTRAINT PK_TitleTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FKID1 int NOT NULL
    , FKID2 int NOT NULL
    , Title nvarchar(100) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID1
ON dbo.TitleTable (FKID1);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID2
ON dbo.TitleTable (FKID2);

CREATE NONCLUSTERED INDEX IX_TitleTable_Title
ON dbo.TitleTable (Title);

Here's the full-text index:

CREATE FULLTEXT INDEX ON dbo.TitleTable(Title) 
KEY INDEX PK_TitleTable 
ON ftc 
WITH (CHANGE_TRACKING = AUTO, STOPLIST SYSTEM);

I've got a database with around 47,000 words in it which I'll use to fill the dbo.TitleTable table:

INSERT INTO dbo.TitleTable (FKID1, FKID2, Title)
SELECT wl.WordRow, wl.WordRow, wl.Word
FROM WordsDB.dbo.WordList wl;

Here's the query from your question:

DECLARE @ID_PARAM1 int;
DECLARE @ID_PARAM2 int;
DECLARE @SINGLE_WORD_PARAM nvarchar(100);
SET @ID_PARAM1 = 46777;
SET @ID_PARAM2 = 46777
SET @SINGLE_WORD_PARAM = N'"' + 'it' + N'"';
SELECT T2.Title 
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1 
    AND T2.FKID2 = @ID_PARAM2 
    AND CONTAINS(T1.Title, @SINGLE_WORD_PARAM);

At this point, if we run the query, we see the following plan:

As expected, there is an index seek on the IX_TitleTable_FKID2 non-clustered index, with an associated key-lookup against the table itself for the Title column.

If we add a compound index on both FKID2 and FKID1, we'd expect a different plan, which is what we get:

CREATE INDEX IX_TitleTable_FTS_Cover_NoInclude
ON dbo.TitleTable (FKID2, FKID1);

However, the key lookup for the Title column is still there. What if we add an INCLUDE clause to our index above?

CREATE INDEX IX_TitleTable_FTS_Cover
ON dbo.TitleTable (FKID2, FKID1)
INCLUDE (Title);

Success! No key-lookup operation required. The cost of the query has also dropped from 0.016 to 0.013, so that's a win.