Can't reduce cost of query plan and get rid of Key Lookup because of cursor

Question

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

Answer 1

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).