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.
Lets see what indexes are already created for table 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.
So we have achieved stable and consistent performance by creating an Covering Index without requiring recompilation.