December 15, 2017 at 8:12 am
I was just wondering about the 'math' behind using the properties of the Key Lookup and Index seek operators to construct the missing index.
If I have the following Key Lookup, Index Seek pairOperator = KeyKey Lookup
Predicate = AssessmentStatusID, OverallRating
Seek Predicate = SubjectVisitAssessmentID
Output List = Empty
Operator = Index Seek
Predicate = IsDeleted
Seek Predicate = SubjectVisitID, AssessmentTypeID
Output List = SubjectVisitAssessmentID, IsContactNeeded
The column SubjectVisitAssessmentID is the primary key of the table.
Do you index the Seek Predicate Columns and have the Predicate and Output List columns as includes, or Index both the Seek Predicate and Predicate columns and just Include the Output List Columns?
This did nothingCREATE NONCLUSTERED INDEX [NonClusteredIndex-20171215-095255] ON [dbo].[SubjectVisitAssessment]
(
[SubjectVisitAssessmentID] ASC,
[SubjectVisitID] ASC,
[AssessmentTypeID] ASC
)
INCLUDE (
[AssessmentStatusID],
[IsContactNeeded],
[OverallRating],
[IsDeleted])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I'll let you know my results - but I'm trying to establish a process for resolving this for my team is a mathematical/scientific reproducible way.
Thanks,
Doug
December 15, 2017 at 10:21 am
It's a bit difficult to figure out without seeing the query behind the seek and lookup, but if the optimizer is choosing an index to seek on (SubjectVisitID, AssessmentTypeID), then adding a new index that starts with (SubjectVisitAssessmentID...) would not be used here.
Is the Primary Key index the clustered index? If so, then the column SubjectVisitAssessmentID is a hidden include column within the index that the optimizer choose to do the seek on:
https://www.brentozar.com/archive/2013/07/how-to-find-secret-columns-in-nonclustered-indexes/
Considering the lookup has an additional predicate of (AssessmentStatusID, OverallRating), I'm thinking to avoid the lookup you would want to make sure both of those columns are include columns in the index with the seek. I probably wouldn't make another index that has key columns of (SubjectVisitID, AssessmentTypeID)
Can you share the definition of the index that currently has the seek operation, and the query?
December 16, 2017 at 5:10 am
The index you created will do nothing, because the leading column is the clustered index key, and is hence already indexed.
Key lookups are single-row seeks against the clustered index, hence their seek predicate will *always* be against the clustered index key. Look rather at the predicate (what columns had to be fetched to do more filters on the date) and output column (what columns had to be fetched to add to the resultset)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2017 at 11:20 am
GilaMonster - Saturday, December 16, 2017 5:10 AMThe index you created will do nothing, because the leading column is the clustered index key, and is hence already indexed.Key lookups are single-row seeks against the clustered index, hence their seek predicate will *always* be against the clustered index key. Look rather at the predicate (what columns had to be fetched to do more filters on the date) and output column (what columns had to be fetched to add to the resultset)
So, oddly enough, this index fixed my five scenarios ( I only asked about 1 of them that I saw twice in the QA ). SubjectVisitAssessment is the clustered PK.
CREATE NONCLUSTERED INDEX IX_SVA_SVAID_SubjectVisitID_AssessmentTypeID#AssessmentStatusID_IsContactNeeded_OverallRating_IsDeleted ON RaterStation.dbo.SubjectVisitAssessment (
SubjectVisitAssessmentID ASC,
SubjectVisitID ASC,
AssessmentTypeID ASC
)
INCLUDE (
AssessmentStatusID,
IsContactNeeded,
OverallRating,
IsDeleted)
I thought I would not need SubjectVisitAssessmentID either Gail - so that seems odd. Everything else holds true. This is SQL 2008 if that makes a difference.
Thanks,
Doug
December 18, 2017 at 1:48 pm
Leading with the clustered index key means that the only advantage it has over the clustered index is that the new index is smaller. It'll give you gains for queries that just need the columns in the index, but small ones from being able to (probably) scan a smaller index than the clustered index.
It is NOT going to be the best possible index to replace the index seek/key lookup set that you posted, because SQL wants to be able to seek for SubjectVisitID and AssessmentTypeID, not SubjectVisitAssessmentID. It wants SubjectVisitAssessmentID as an output column only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply