Columnstore index record with huge delta_store_hobt_id

• Sandy67

  Valued Member

  Points: 63

  More actions

  January 31, 2025 at 12:36 pm

  #4526825

  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.

  Attachments:

  You must be logged in to view attached files.

• Site Owners

  SSC Guru

  Points: 80332

  More actions

  February 1, 2025 at 1:10 pm

  #4527475

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.