Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

Symptoms

Assume that you use Microsoft SQL Server 2014. You may encounter the following two issues that are included in SQL Server 2014 Release Notes:

  • Missing index details incorrectly report included columns for memory-optimized table

    Issue: If SQL Server 2014 detects a missing index for a query on a memory-optimized table, it will report a missing index in the SHOWPLAN_XML, as well as in the missing index DMVs such as sys.dm_db_missing_index_details. In some cases, the missing index details will contain included columns. As all columns are implicitly included with all indexes on memory-optimized tables, it is not allowed to explicitly specify included columns with memory-optimized indexes.

  • Missing index details omit missing indexes if a hash index exists but is not suitable for the query

    Issue: If you have a HASH index on columns of a memory-optimized table referenced in a query, but the index cannot be used for the query, SQL Server 2014 will not always report a missing index in SHOWPLAN_XML and in the DMV sys.dm_db_missing_index_details.

    In particular, if a query contains equality predicates that involve a subset of the index key columns or if it contains inequality predicates that involve the index key columns, the HASH index cannot be used as is, and a different index would be required to execute the query efficiently.

Cause

The issues occur because the missing index logic in the query optimizer does not differentiate between the traditional no-clustered indexes on disk-based tables and the new index types for memory-optimized tables.

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:


Workaround

To work around these issues, use the following workarounds:

  • Workaround for issue: Missing index details incorrectly report included columns for memory-optimized table

    Do not specify the INCLUDE clause with indexes on memory-optimized tables.

  • Workaround for issue: Missing index details omit missing indexes if a hash index exists but is not suitable for the query

    In case you are using hash indexes, inspect the queries and query plans to determine if the queries could benefit from Index Seek operations on a subset of the index key, or Index Seek operations on inequality predicates. If you need to seek on a subset of the index key, either use a NONCLUSTERED index, or use a HASH index on exactly the columns you need to seek on. If you need to seek on an inequality predicate, use a NONCLUSTERED index instead of HASH.

Note The workarounds are from SQL Server 2014 Release Notes

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Facebook LinkedIn Email
SUBSCRIBE RSS FEEDS

Need more help?

Want more options?

Discover Community

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×