1

I am using this query to look deeper onto my columnstore indexes while t-shooting my execution plans (i.e. segment elimination etc.) :

    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;

(table name redacted).

The result of the analysis is as follows: Postimg.

My question is simple: How come in the outcome of the analysis does not correspond to the columns name in the indexes? As you can see on the right part of the image, the column names on the result grid are different to what is shown in columnstore index properties.

Environment: SQL 2019 CU10 Standard ; Windows Server 2019.

Thank you in advance for a feedback, take care S.

1 Answer 1

4

The column_id field that is part of the columnstore system catalog view: sys.column_store_segments

column_id int ID of the columnstore column.

Source

Is different from the column_id field that is part of the system catalog view: sys.columns

column_id int ID of the column. Is unique within the object.

Source

If you are ever wondered about what SSMS is running in the background you can always set up an extended event to capture the queries when you select the properties of the index. The query that SSMS uses that shows the properties of the index and the columns in correct order:

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'

Here we see that an intermediate system catalog view, sys.index_columns is used.

Knowing that, your final adapted query could be something like this:

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;
2
  • Hello Randi, Thank you for your elaborate answer. This answers my question fully and has helped me a lot to understand the relevant DMVs. I therefore consider my question as solved. Thank you, have a great day!
    – Sranda
    May 13, 2021 at 6:25
  • Hi @Sranda Thanks a lot for coming back to me on this with the great news. Have a great day as well! May 13, 2021 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.