Columnstore dmv sys.column_store_segments some columns always NULL Azure Database

Arthur Olcot 5

Within an Azure SQL database, I have a clustered column store index (many millions of records), with a number of columns of which are mainly INT based.

When I interrogate the dmv sys.column_store_segments, the columns [has_nulls], [min_data_id] and [max_data_id] are always NULL. I'm mainly bothered about the min/max data id's so that I can try and tune our indexes/workload accordingly.

I have enough permissions, tried rebuilding the partitions etc, nothing seems to affect it. Querying it seems fine so trying to understand why the dmv's are like this and cannot find anything anywhere that could indicate why these are always NULL.

Current SKU is a serverless gen5 up to 6 vcores.

Any thoughts/answers/ideas welcome!

Sai Raghunadh M 1,760 Reputation points Microsoft Vendor

2024-12-02T19:01:25.72+00:00

Hi @Arthur Olcot

Thanks for the question and using MS Q&A platform.

The behavior you are observing in the sys.column_store_segments DMV, where the columns [has_nulls], [min_data_id], and [max_data_id] are always NULL in your Azure SQL Database, could be attributed to several factors. Specifically, this may relate to Azure SQL's handling of Columnstore indexes, the Serverless SKU, or the nature of the data in the column store itself. Here are some potential reasons and considerations for why these values might appear as NULL:

After upgrading to a version of SQL Server that supports string min/max segment elimination (SQL Server 2022 (16.x) and later), min_deep_data and max_deep_data will be NULL until after the columnstore index is rebuilt, using a REBUILD or DROP/CREATE. After a rebuild, the segments that contain data types that can benefit from string min/max segment elimination will contain data.

Please go through this document for more Information: sys.column_store_segments (Transact-SQL)

All columns require at least VIEW DEFINITION permission on the table. The following columns return NULL unless the user also has SELECT permission: has_nulls, base_id, magnitude, min_data_id, max_data_id, and null_value.

Please refer Permissions

Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Arthur Olcot 5 Reputation points

2024-12-02T21:24:05.6766667+00:00

I have gone through that document and as I said, this isn't a permissions issue as I have db_owner permissions.

I can see values for [min_deep_data] and [max_deep_data] hence saying that the issue lies with the three columns I mention in the problem description
Erland Sommarskog 115.3K Reputation points MVP

2024-12-02T22:51:54.0933333+00:00

Not sure that it is relevant, but what is the compatibility level of the database?
Arthur Olcot 5 Reputation points

2024-12-03T07:48:41.9666667+00:00

Fair question - It is latest - 160. Azure database was created sometime earlier this year and running mostly default options (it isn't a prod db hence why its on a serverless SKU)
Arthur Olcot 5 Reputation points

2024-12-03T08:14:33.1266667+00:00

Hi @Erland Sommarskog Fair question! Its running latest 160. The database was created earlier this year, all other options likely default at the time of creation as its not production (hence it being on a serverless sku)

Sai Raghunadh M 1,760 Microsoft Vendor

Hi @Arthur Olcot

could you please share the output from the following query, replacing the placeholders with actual names:

SELECT HAS_PERMS_BY_NAME(OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id), 'OBJECT', 'SELECT') AS has_permissionsFROM sys.tables AS tINNER JOIN sys.schemas AS sON t.schema_id = s.schema_idWHERE t.name = 'replace-with-columnstore-table-name'      AND   s.name = 'replace-with-columnstore-table-schema-name'

2 answers

Arthur Olcot 5 Reputation points

2024-12-03T11:37:03.4466667+00:00
Ok I've finally found the issue and it looks like a bug in the dmv's themselves by not correctly handling valid table names that need to have delimiters adding to make it a valid identifier.

See column definition code from within the sys.column_store_segments view:

(case when has_perms_by_name(OBJECT_SCHEMA_NAME(p.idmajor) + '.' + OBJECT_NAME(p.idmajor), 'OBJECT', 'SELECT') = 1 then s.min_data_id else NULL end) as min_data_id

Can clearly see that we need QUOTENAME() in there to correctly generate the object identifier as without it, and when using tablenames that need delimiting, it will always return NULL.

This isn't the only column/view with this issue/pattern. I can see the same issue in sys.column_store_dictionaries. May well be other dmv's not related to columnstore with a similar issue where the correctly delimiting of table names is not happening

@Erland Sommarskog i don't have a means to raise this as a bug with MS, any advice on how to get this raised within MS to address in a future release?

Thanks
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 115.3K Reputation points MVP

2024-12-03T21:22:17.47+00:00

Good catch! Although, I am not sure that it matters with all special characters. I tried this query in a table where there is a table called Order Details:

SELECT t.name, has_perms_by_name(OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id), 'OBJECT', 'SELECT') FROM sys.tables t

And I get back 1 for Order Details. But then I created this table:

CREATE TABLE "Dirty.Harry" (a int)

And of course, without QUOTENAME, the query above returns 0 for this table.

You can report bugs on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 Note that this of the kind "let Microsoft know". They may fix it quickly, it may end up in the end of their backlog. If you create an item, please share the link so that we can vote. And include all DMVs you have found in the bug report.

If this is a blocking issue, you need to open a support case. It absolutely helps to have a compelling business case.

I converted your Comment to an Answer, since it is an answer. You will not be able to accept it due to the forum rules, but this is as good as it gets.

Erland Sommarskog 115.3K Reputation points MVP

2025-01-12T18:03:52.19+00:00

As Arthur reported a bug, I did so myself:

https://feedback.azure.com/d365community/idea/2225267b-0fd1-ef11-95f6-6045bd77c83d

Erland Sommarskog 115.3K Reputation points MVP

2025-01-13T21:43:22.98+00:00

I have received this response on the feedback item:

Thanks Arthur for finding the bug and Erland for bringing this to our attention. We've added this to our backlog and will track for a future fix. We ask all customers who see this bug impacting their business to open a support case with Microsoft and share the details of the impact with the support engineer.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Columnstore dmv sys.column_store_segments some columns always NULL Azure Database

2 answers

Your answer