- 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.
- 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.
Article ID: 2938462 - Last Review: 21 Apr 2014 - Revision: 1