patternsqlMinor
Nonclustered Index still doing index scan
Viewed 0 times
scanstilldoingnonclusteredindex
Problem
I have this simple query.
This produces the following execution plan,
I want to get rid all
It looks like it's using the Primary Key index,
So I created one based on this article: Why use the INCLUDE clause when creating an index?
I am expecting that the sql statement will now be converted into
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.
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.DateSubmittedThis 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:
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
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.