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 tableIssue: 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 queryIssue: 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.
Resolution
The issues were first fixed in the following cumulative update of SQL Server.
Cumulative Update 1 for SQL Server 2014 /en-us/help/2931693
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 tableDo 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 queryIn 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.
