HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Nonclustered Index still doing index scan

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scanstilldoingnonclusteredindex

Problem

I have this simple query.

SELECT a.RecordID SubmissionID,
  a.[DateSubmitted],
  a.[FinalAction],
  a.[FinalActionReason],
  a.[FinalActionDate],
  b.GradeSubmissionListID,
  b.[Action],
  b.ActionReason,
  b.ActionDate
FROM [dbo].[GradeSubmissionList] a
  LEFT JOIN dbo.GradeSubmissionApproveRejectLog b
  ON a.RecordID = b.GradeSubmissionListID
WHERE a.EdpCode = '1314ACT1258'
ORDER BY a.DateSubmitted


This produces the following execution plan,

I want to get rid all Clustered Index Scan. When I hover the first clustered index scan, it generated this image,

It looks like it's using the Primary Key index,

ALTER TABLE [dbo].[GradeSubmissionList] 
ADD CONSTRAINT [GradeSubmissionList_pk_RecordID] PRIMARY KEY CLUSTERED 
([RecordID] ASC) ON [PRIMARY]


So I created one based on this article: Why use the INCLUDE clause when creating an index?

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(RecordID, FinalApprovedRejectedBy, EdpCode , DateSubmitted)
INCLUDE ( FinalAction, FinalActionDate, FinalActionReason );


I am expecting that the sql statement will now be converted into Index Seek, but shows out it's still scanning the entire row.

What are the thing the I need to do in order to make this work? If there are things that you want to know about my situation please do ask.

Solution

Try to create this index:

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );


Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, these fields must be selective which means that the condition must filter just a little percent or rows from a table.

Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.

Actually, this is very complex subject. To know more about using indexes in Sql Server you can read this article SQL Server Index Design Guide

Code Snippets

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(EdpCode, DateSubmitted)
INCLUDE (FinalApprovedRejectedBy, FinalAction, FinalActionDate, FinalActionReason );

Context

StackExchange Database Administrators Q#51724, answer score: 7

Revisions (0)

No revisions yet.