sys.dm_db_xtp_hash_index_stats (Transact-SQL)sys.dm_db_xtp_hash_index_stats (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

这些统计信息可用来了解和优化存储桶计数。These statistics are useful for understanding and tuning the bucket counts. 它还可用于检测索引键具有许多重复项的情况。It can also be used to detect cases where the index key has many duplicates.

平均链长度较大指示将许多行哈希处理至同一个存储桶。A large average chain length indicates that many rows are hashed to the same bucket. 之所以出现这种情况,原因可能是:This could happen because:

  • 如果空存储桶的数目较少,或平均链长度和最大链长度接近,则存储桶总数可能会过少。If the number of empty buckets is low or the average and maximum chain lengths are similar, it is likely that the total bucket count is too low. 这将导致许多不同的索引键哈希处理至同一个存储桶中。This causes many different index keys to hash to the same bucket.

  • 如果空存储桶的数目较高,或最大链长度比平均链长度高,则可能许多行具有重复的索引键值,或者键值存在偏斜。If the number of empty buckets is high or the maximum chain length is high relative to the average chain length, it is likely that there are many rows with duplicate index key values or there is a skew in the key values. 索引键相同的所有行都将哈希处理至同一个存储桶,因而,该存储桶有较长的链长度。All rows with the same index key value hash to the same bucket, hence there is a long chain length in that bucket.

链长度较长可能会显著影响针对各单独行的所有 DML 操作的性能,包括 SELECT 和 INSERT。Long chain lengths can significantly impact the performance of all DML operations on individual rows, including SELECT and INSERT. 链长度较短以及空存储桶计数较高指示 bucket_count 过高。Short chain lengths along with a high empty bucket count are in indication of a bucket_count that is too high. 这将降低索引扫描的性能。This decreases the performance of index scans.

警告

sys.dm_db_xtp_hash_index_stats扫描整个表。sys.dm_db_xtp_hash_index_stats scans the entire table. 因此,如果在数据库中,有大型表sys.dm_db_xtp_hash_index_stats可能需要很长时间运行。So, if there are large tables in your database, sys.dm_db_xtp_hash_index_stats may take a long time run.

有关详细信息,请参阅内存优化表的哈希索引For more information, see Hash Indexes for Memory-Optimized Tables.

列名Column name typeType 描述Description
object_idobject_id intint 父表的对象 ID。The object ID of parent table.
xtp_object_idxtp_object_id bigintbigint 内存优化表的 ID。ID of the memory-optimized table.
index_idindex_id intint 索引 ID。The index ID.
total_bucket_counttotal_bucket_count bigintbigint 索引中哈希存储桶的总数。The total number of hash buckets in the index.
empty_bucket_countempty_bucket_count bigintbigint 索引中空哈希存储桶的数量。The number of empty hash buckets in the index.
avg_chain_lengthavg_chain_length bigintbigint 索引中所有哈希存储桶的平均行链长度。The average length of the row chains over all the hash buckets in the index.
max_chain_lengthmax_chain_length bigintbigint 哈希存储桶中的最大行链长度。The maximum length of the row chains in the hash buckets.
xtp_object_idxtp_object_id bigintbigint 对应于内存优化表的内存中 OLTP 对象 ID。The in-memory OLTP object ID that corresponds to the memory-optimized table.

权限Permissions

要求对服务器具有 VIEW DATABASE STATE 权限。Requires VIEW DATABASE STATE permission on the server.

示例Examples

A.A. 对哈希索引桶计数进行故障排除Troubleshooting hash index bucket count

以下查询可以用于对现有表的哈希索引桶计数进行故障排除。The following query can be used to troubleshoot the hash index bucket count of an existing table. 查询将返回用户表有关的空 bucket 和所有哈希索引的链长度的百分比的统计信息。The query returns statistics about percentage of empty buckets and chain length for all hash indexes on user tables.

  SELECT  
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
    i.name                   as [index],   
    h.total_bucket_count,  
    h.empty_bucket_count,  
    FLOOR((  
      CAST(h.empty_bucket_count as float) /  
        h.total_bucket_count) * 100)  
                             as [empty_bucket_percent],  
    h.avg_chain_length,   
    h.max_chain_length  
  FROM sys.dm_db_xtp_hash_index_stats as h   
  INNER JOIN sys.indexes as i  
            ON h.object_id = i.object_id  
           AND h.index_id  = i.index_id  
    INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
    INNER JOIN sys.tables t on h.object_id=t.object_id
  WHERE ia.type=1
  ORDER BY [table], [index];  

有关如何解释此查询的结果的详细信息,请参阅故障排除的内存优化表的哈希索引For details on how to interpret the results of this query, see Troubleshooting Hash Indexes for Memory-Optimized Tables .

B.B. 内部表的哈希索引统计信息Hash index statistics for internal tables

某些功能使用利用哈希索引,例如内存优化表上的列存储索引的内部表。Certain features use internal tables that leverage hash indexes, for example columnstore indexes on memory-optimized tables. 下面的查询返回对链接到用户表的内部表的哈希索引的统计信息。The following query returns stats for hash indexes on internal tables that are linked to user tables.

  SELECT  
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [user_table],
    ia.type_desc as [internal_table_type],
    i.name                   as [index],   
    h.total_bucket_count,  
    h.empty_bucket_count,  
    h.avg_chain_length,   
    h.max_chain_length  
  FROM sys.dm_db_xtp_hash_index_stats as h   
  INNER JOIN sys.indexes as i  
            ON h.object_id = i.object_id  
           AND h.index_id  = i.index_id  
    INNER JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
    INNER JOIN sys.tables t on h.object_id=t.object_id
  WHERE ia.type!=1
  ORDER BY [user_table], [internal_table_type], [index]; 

请注意,不能更改内部表上索引的 BUCKET_COUNT,因此此查询的输出应视为信息性仅。Note that the BUCKET_COUNT of index on internal tables cannot be changed, thus the output of this query should be considered informative only. 不需要执行任何操作。No action is required.

此查询不应返回任何行,除非您使用的一项功能,利用内部表的哈希索引。This query is not expected to return any rows unless you are using a feature that leverages hash indexes on internal tables. 以下内存优化表包含列存储索引。The following memory-optimized table contains a columnstore index. 创建此表后,你将看到哈希索引有关的内部表。After creating this table, you will see hash indexes on internal tables.

  CREATE TABLE dbo.table_columnstore
  (
    c1 INT NOT NULL PRIMARY KEY NONCLUSTERED,
    INDEX ix_columnstore CLUSTERED COLUMNSTORE
  ) WITH (MEMORY_OPTIMIZED=ON)

请参阅See Also

内存优化表动态管理视图(Transact SQL)Memory-Optimized Table Dynamic Management Views (Transact-SQL)