0

I have already created the missing index on the table and the plan keeps telling me there is a missing index?

<MissingIndexes>
  <MissingIndexGroup Impact="10.6268">
    <MissingIndex Database="[OPTIONS]" Schema="[dbo]" Table="[T_markets_quotes]">
      <ColumnGroup Usage="EQUALITY">
        <Column Name="[symbol]" ColumnId="5" />
      </ColumnGroup>
      <ColumnGroup Usage="INEQUALITY">
        <Column Name="[mid]" ColumnId="17" />
      </ColumnGroup>
      <ColumnGroup Usage="INCLUDE">
        <Column Name="[actual_runtime]" ColumnId="2" />
        <Column Name="[bid]" ColumnId="16" />
        <Column Name="[ask]" ColumnId="18" />
      </ColumnGroup>
    </MissingIndex>
  </MissingIndexGroup>
</MissingIndexes>

Here is the index that is created already:

CREATE NONCLUSTERED INDEX [IDX1] ON [dbo].[T_markets_quotes]
(   [symbol] ASC,
    [mid] ASC
)
INCLUDE([actual_runtime],[bid],[ask]) 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 [PS_Monthly_Partition]([actual_runtime])
GO

Is something wrong with the execution plans?

I re-created the table and recreated all of the indexes but no luck getting the error to go away from the execution plan.

Improve this question
6
  • 1
    The index you have and the index it wants aren't exactly the same w.r.t. partition alignment. Please provide details of number of partitions in the table and the query that produces this warning (as requested when this was in staging ground) Commented Oct 31, 2024 at 11:52
  • 1
    Though TBH an impact of "10.6268" is pretty low and likely not worth the negative impact to maintenance of creating a non partition aligned index for a prospective 10% improvement on one specific query Commented Oct 31, 2024 at 11:53
  • 1
    +1 This estimated improvement isn't much, and the suggestion is to create a global index, and you created a partitioned index, which is physically one BTree index per partition. Also consider that having a global index will block partition switching. Commented Oct 31, 2024 at 12:32
  • 3
    Rule of thumb: count the Missing Indexes as merely gentle suggestions to look at the query plan. Do NOT take them at face value and blindly add the index. Commented Oct 31, 2024 at 12:39
  • 1
    yeah If partition_scheme_name or filegroup isn't specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table. - so I suppose the missing index suggestion could be clearer here that it doesn't want that default Commented Oct 31, 2024 at 13:35
Related questions
4484
How can I find the index for a given item in a list?
0
SQL Server DELETE performance issues
568
Why use the INCLUDE clause when creating an index?
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.