Issue
We recently received customer complaints on an Azure SQL DB (Hyperscale) where a Large table (in TBs) having a clustered columnstore Index (& a non-clustered Index as well) was not performing optimally and many delete queries being executed were running slower than before.
Error/Root Cause:
After analyzing the issue internally, we were able to identify the Object_ID and asked the customer to execute the SQL:
Select * from sys.internal_partitions where internal_object_type_desc = ‘COLUMN_STORE_MAPPING_INDEX’ and rows>1000
Select * from sys.objects where object_id in (‘123456’,’3456789’)
'SELECT OBJECT_SCHEMA_NAME(123456), OBJECT_NAME(3456789)
OBJECT_SCHEMA_NAME(123456), OBJECT_NAME(3456789)'
HistoryDataTable PRODUCT
Workaround/Fix
After several rounds of both Internal & testing with the customer, we figured that the root cause in this case was a poorly managed Mapping Index.
Mapping Index- Clustered Columnstore Indexes (CCIs) with Non-clustered Indexes (NCIs) maintain a mapping index to facilitate updates & deletes. Mapping Index is essentially a B-Tree mapping structure, that connects Non-clustered B-tree indexes to a clustered columnstore index. Its main purpose is to track movement of rows inside the clustered columnstore index. It contains a key tagged to the original location (where data was originally inserted) and the current location (Row Group ID and position) of the row.
The mapping index can grow over time and may need to be scanned multiple times when executing updates & deletes. If the mapping Index gets too large, updates & deletes may become expensive. Also, another negative associated with mapping indexes is performance of Range Lookups & point Lookups getting impacted, since we have to scan & collate an additional structure. However, reading the queries against the main Columnstore Index usually doesn’t get impacted as the processing is still happening from compressed segments.
To mitigate the situation, we had to seek assistance from the Product group to first enable a feature switch from the backend.
After this, the customer was asked to rebuild the impacted Non-clustered Indexes (NCIs) and that fixed issue underneath.
References
Clustered Columnstore Index in Azure SQL Database | Azure Blog | Microsoft Azure
Columnstore indexes - Design guidance - SQL Server | Microsoft Learn
Columnstore indexes: Overview - SQL Server | Microsoft Learn