I am running the below query and the below index is present to satisfy the query.

SELECT top 100 RCLNT, RBUKRS, RYEAR, RACCT, DRCRK, SUM(ISNULL([000],0.00)) AS CSLVT,SUM(ISNULL([001],0.00)) AS CSL01,SUM(ISNULL([002],0.00)) AS CSL02,SUM(ISNULL([003],0.00)) AS CSL03,SUM(ISNULL([004],0.00)) AS CSL04,SUM(ISNULL([005],0.00)) AS CSL05,SUM(ISNULL([006],0.00)) AS CSL06,SUM(ISNULL([007],0.00)) AS CSL07,SUM(ISNULL([008],0.00)) AS CSL08,SUM(ISNULL([009],0.00)) AS CSL09,SUM(ISNULL([010],0.00)) AS CSL10,SUM(ISNULL([011],0.00)) AS CSL11,SUM(ISNULL([012],0.00)) AS CSL12,SUM(ISNULL([013],0.00)) AS CSL13,SUM(ISNULL([014],0.00)) AS CSL14,SUM(ISNULL([015],0.00)) AS CSL15,SUM(ISNULL([016],0.00)) AS CSL16
FROM dbo.ACDOCA WITH (NOLOCK)

PIVOT
(
MAX(ACDOCA.CSL) FOR ACDOCA.POPER IN ([000],[001],[002],[003],[004],[005],[006],[007],[008],[009],[010],[011],[012],[013],[014],[015],[016])
) AS ACDOC_PIV
GROUP BY RCLNT, RBUKRS, RYEAR, RACCT, DRCRK

Index:

CREATE NONCLUSTERED INDEX [IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER] ON [dbo].[ACDOCA]
(
    [RCLNT] ASC,
    [RBUKRS] ASC,
    [RYEAR] ASC,
    [RACCT] ASC,
    [DRCRK] ASC
)
INCLUDE([CSL],[POPER]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

But, for some reason SQL Server is doing a Clustered Key lookup to fetch all the other columns in the table. Is there any reason why it’s doing that and can it be avoided to improve performance?

Execution plan screenshot: