January 31, 2025 at 12:36 pm
I am a starter on the concept of indexing. I have been looking into a table which has clustered column store index and has very poor performance. I was able to get the following script online to find how the row-groups are partitioned, number of rows per group and fragmentation level etc.,
SELECT i.object_id,
object_name(i.object_id) AS TableName,
i.name AS IndexName,
i.index_id,
i.type_desc,
CSRowGroups.*,
100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0) AS 'Fragmentation'
FROM sys.indexes AS i
JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_id
WHERE object_name(i.object_id) = '<<table-name>>'
ORDER BY object_name(i.object_id), i.name, row_group_id;
This returned 14 rows split across a single partition indicating (0-11) row-groups have Total_rows = 1048576 with COMPRESSED state and NO-TRIM and the last row-group (12) was the residual row-group in COMPRESSED state with lesser number of rows (646264) compared to the rest.
What I do not understand is that there is an additional row with delta_store_hobt_id 72057606181945344 and OPEN state with NULL values for most of the other columns. When I checked, it says this could be a carried-forward record from the previous SQL version. Can someone help me understand what record means and if it will have any impact on the table performance.
I have attached the result.
February 1, 2025 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply