主页 / dba / 问题 / 291294
Accepted
Sranda
Asked: 2021-05-10 23:37:23 +0800 CST

列存储索引分析没有指出我要更正列

  • 772

我正在使用此查询来更深入地查看我的列存储索引，同时对我的执行计划进行 t-shooting（即段消除等）：

    SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    columns.name AS column_name,
    partitions.partition_number,
    column_store_segments.segment_id,
    column_store_segments.min_data_id,
    column_store_segments.max_data_id,
    column_store_segments.row_count
FROM sys.column_store_segments
INNER JOIN sys.partitions
ON column_store_segments.hobt_id = partitions.hobt_id
INNER JOIN sys.indexes
ON indexes.index_id = partitions.index_id
AND indexes.object_id = partitions.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
AND column_store_segments.column_id = 
     columns.column_id
WHERE tables.name = 'R***'

ORDER BY tables.name, columns.name, 
column_store_segments.segment_id;

（表名已编辑）。

分析结果如下： Postimg。

我的问题很简单：为什么分析的结果与索引中的列名不对应？正如您在图像右侧看到的那样，结果网格上的列名与列存储索引属性中显示的不同。

环境：SQL 2019 CU10 标准；视窗服务器 2019。

提前感谢您的反馈，请注意 S。

sql-server

1 个回答

  1. Best Answer

    作为列存储系统目录视图一部分的column_id字段：sys.column_store_segments

    column_id int 列存储列的 ID。

    资源

    不同于column_id系统目录视图中的字段：sys.columns

    column_id int 列的 ID。在对象内是唯一的。

    资源

    如果您想知道 SSMS 在后台运行什么，您可以随时设置扩展事件来在选择索引属性时捕获查询。SSMS 使用的查询以正确的顺序显示索引和列的属性：

    exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed],
CAST(ISNULL(ic.column_store_order_ordinal,0) AS int) AS [ColumnStoreOrderOrdinal]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tbl.name=@_msparam_3 and SCHEMA_NAME(tbl.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'IX_Column_bla',@_msparam_3=N'ColumnstoreTest',@_msparam_4=N'dbo'

    在这里，我们看到使用了中间系统目录视图sys.index_columns。

    知道了这一点，您最终调整后的查询可能是这样的：

    SELECT
    tbl.name AS table_name,
    i.name AS index_name,
    clmns.name AS column_name,
    p.partition_number,
    css.segment_id,
    css.min_data_id,
    css.max_data_id,
    css.row_count
FROM sys.column_store_segments AS css
INNER JOIN sys.partitions AS p
ON css.hobt_id = p.hobt_id
INNER JOIN
sys.tables as tbl ON p.object_id = tbl.object_id
INNER JOIN sys.indexes AS i ON (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON  ic.object_id=i.object_id
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE tbl.name = 'TableName'
AND i.name= 'IndexName'
ORDER BY tbl.name, clmns.name, 
css.segment_id;
    • 4

相关问题