CS #2 Using COVERING INDEX for improving performance

Problem Statement

Stored procedure CS2_COVERING_INDEX is running slow for some input parameters combinations and running fine for other input parameters.

CREATE PROC CS2_COVERING_INDEX 
     @start_prod_id INT,
        @end_prod_id   INT
AS
  BEGIN
    SELECT TOP 10 TransactionID,ProductID,TransactionDate
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;
 END;
GO

Step 1: Identifying Bottleneck

Tools used to identify bottleneck: SSMS Execution Plan

Lets execute this procedure with different parameters and observe the execution plan.

--This call takes less than second to complete
EXEC CS2_COVERING_INDEX @start_prod_id = 1346, @end_prod_id = 1355
--While this call takes 28 secs to complete
EXEC CS2_COVERING_INDEX @start_prod_id = 5451, @end_prod_id = 43387

This is same situation as discussed in previous Case Study, but instead of using OPTION RECOMPILE, we will use different approach here to handle this performance issue.

Lets first list all the columns used in this SQL Query.

  1. TransactionID
  2. ProductID
  3. TransactionDate
  4. ActualCost

Lets see what indexes are already created for table bigTransactionHistory

sp_helpindex bigTransactionHistory

Also verify using SSMS whether there are any included columns which are not shown using this builtin system stored procedure sp_helpindex.

Now as we are certain that non clustered index contains only ProductID column and since TransactionDate, TransactionID are created as Clustered Index key columns they are also present in the Non Clustered Index. So only ActualCost is missing and hence there is Key Lookup Operator in the execution plan to fetch it from Clustered Index.

So we have identified the bottleneck in this plan which is Key Lookup Operator

Step 2: Troubleshooting performance (Using Covering Index)

We can remove this Key Lookup Operator by modifying our existing NCI by adding ActualCost column in index making it Covering Index.

CREATE NONCLUSTERED INDEX [NCI_ProductID] ON [dbo].[bigTransactionHistory]
(
  [ProductID],[ActualCost]
)
WITH (DROP_EXISTING = ON) ON [BigAW_NCI]
GO

Lets execute queries again and observe the difference in the execution plan. Now we see Non Clustered Index Seek in every execution of query irrespective of parameters passed in the stored procedure.

Learnings

So we have achieved stable and consistent performance by creating an Covering Index without requiring recompilation.