SQL server 2012 reports "Missing Index" on column which is covered by "Clustered Index"

Question

My table creation script is as follows

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[quotation_section](
    [quotation_id] [varchar](24) NOT NULL,
    [section_id] [varchar](24) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [description] [nvarchar](1000) NULL,
    [sort_order] [tinyint] NULL,
    [copied_from_library_flag] [bit] NULL,
    [max_sequence] [tinyint] NULL,
    [collaborator_id] [varchar](24) NULL,
    [status] [tinyint] NULL,
    [mandatory_flag] [bit] NULL,
    [read_only_flag] [bit] NULL,
    [draft_flag] [bit] NULL,
    [has_comment_flag] [bit] NULL,
    [revision_time] [datetime] NULL,
    [report_revision_time] [datetime] NULL,
    [last_submitted_max_sequence] [tinyint] NOT NULL,
    [buyer_code] [varchar](4) NULL,
 CONSTRAINT [PK_quotation_SECTION_01] PRIMARY KEY CLUSTERED 
(
    [quotation_id] ASC,
    [section_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

In one of my database queries the execution planner is reporting the following:

                <MissingIndexGroup Impact="25.8056">
                  <MissingIndex Database="[myschema]" Schema="[dbo]" Table="[quotation_section]">
                    <ColumnGroup Usage="EQUALITY">
                      <Column Name="[section_id]" ColumnId="2" />
                    </ColumnGroup>
                  </MissingIndex>
                </MissingIndexGroup>
              </MissingIndexes>

I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed (unless I have messed up something here, sorry in that case). I do admit that other tables I am using for queries do have the columns present in both clustered and non-clustered indexes. So what should the approach be here? Does adding the missing index (as reported by SQL server execution planner) is a good idea?

Answer 1

It is trying to filter[1] by section_id but not quotation_id - it can't do this by seeking on an index on quotation_id,section_id so it will have to scan that index (which as it is your clustered index, is the whole table). It would be able to use an index on section_id,quotation_id for this because the target columns are first in the index's key.

^{[1] or possibly sort, as we can't see the query or the full plan we can't know which or why. In the case of sorting it will be wanting the index for slightly different reasons, perhaps to replace a clustered index scan and spool to tempdb for sorting by scanning the non-clustered index and pulling the other data out in order more directly.}

I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed

That is correct. It will use seeks on the non-clustered index to find the clustering key value, then use that to find other data needed from the row. Unless it only needs data from columns in the index - in that case there is no need to refer to the clustered index as all the information is already available without.

Note that this is essentially the same if you don't have a clustered index. Instead of containing the clustering key each non-clustered index contains the rowid of each record in the heap instead.

Does adding the missing index (as reported by SQL server execution planner) is a good idea?

For that query as it stands, quite probably (though with the caveat that we don't know what the query is, or the query plan that was used, so it could be that refactoring that would be more useful). It would likely allow the query to be processed by seeking on that index then performing lookups on the clustered index as needed instead of scanning the whole clustered index, which the planner expects to be a significantly more efficient way around.

Answer 2

Section_id is the second key column which means the clustered index can't really be used for seeks or scans on it. A nonclustered index on section_id will also be considerably more compact than the clustered index and therefore quicker to read when needed.