sys.dm_db_xtp_hash_index_stats (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

这些统计信息有助于了解和优化内存优化表中哈希索引的存储桶计数。 它还可用于检测索引键具有许多重复项的情况。

平均链长度较大指示将许多行哈希处理至同一个存储桶。 之所以出现这种情况，原因可能是：

  • 如果空存储桶的数目较少，或平均链长度和最大链长度接近，则存储桶总数可能会过少。 这将导致许多不同的索引键哈希处理至同一个存储桶中。

  • 如果空存储桶数较高，或者相对于平均链长度的最大链长度较高，则有两个可能的解释。 有许多行具有重复索引键值，或者键值存在偏差。 在任一情况下，具有相同索引键值哈希的所有行都指向同一存储桶，导致该存储桶中的长链长度。

长链长度可以显著影响单个行上所有 DML 操作的性能，包括 SELECT 和 INSERT。 链长度较短以及空存储桶计数较高指示 bucket_count 过高。 这将降低索引扫描的性能。

警告

此 DMV 扫描整个表。 因此，如果数据库中有大型表， sys.dm_db_xtp_hash_index_stats 可能需要很长时间才能运行。

有关详细信息，请参阅 内存优化表的哈希索引。

列名称 类型 描述
object_id int 父表的对象 ID。
xtp_object_id bigint 内存优化表的 ID。
index_id int 索引 ID。
total_bucket_count bigint 索引中哈希存储桶的总数。
empty_bucket_count bigint 索引中空哈希存储桶的数量。
avg_chain_length bigint 索引中所有哈希存储桶的平均行链长度。
max_chain_length bigint 哈希存储桶中的最大行链长度。
xtp_object_id bigint 对应于内存优化表的内存中 OLTP 对象 ID。

权限

需要对数据库拥有 VIEW DATABASE STATE 权限。

SQL Server 2022 及更高版本的权限

需要对数据库拥有 VIEW DATABASE PERFORMANCE STATE 权限。

示例

A. 排查哈希索引桶计数问题

以下查询可用于对现有表的哈希索引桶计数进行故障排除。 该查询返回有关用户表上所有哈希索引的空存储桶和链长度百分比的统计信息。

  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];

有关如何解释此查询结果的详细信息，请参阅 内存优化表的哈希索引故障排除。

B. 内部表的哈希索引统计信息

某些功能使用使用哈希索引的内部表，例如内存优化表上的列存储索引。 以下查询返回链接到用户表的内部表的哈希索引的统计信息。

  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];

无法更改内部表上的索引桶计数，因此，此查询的输出应仅被视为信息性。 无需执行任何操作。

除非使用对内部表使用哈希索引的功能，否则此查询不会返回任何行。 以下内存优化表包含列存储索引。 创建此表后，你将在内部表上看到哈希索引。

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

相关内容