data-konzept

'Hash indexes, used for SQL Server in-memory OLTP, are not ordered by the index keys and scans on hash indexes can significantly hinder performance. Additionally, hash index functions require that all key columns be supplied in equality expressions. If any of the key columns are not referenced by the criteria of the query, the hash index may not be used to find buckets with matching columns. Likewise, non-equality expressions on the key columns will result in a scan because the function requires explicit values, not a range of values.

Identifying queries that yield scans on nonclustered index hashes is difficult to find unless you examine the active plan cache. Querying the cache on a production server can have a noticeable affect if the query is not well targeted to only return the data you really want to see and filtered to reduce the the amount of cache that has to be scanned. This means careful parsing of the XML.'...

https://www.mssqltips.com/sqlservertip/4104/identify-sql-server-query-plans-with-scans-on-nonclustered-hash-indexes/