Rowstore Index Health
For SQL Server, rowstore index fragmentation is a critical indicator for performance. In Synapse Dedicated SQL Pools, the same holds true but high fragmentation is generally less of a performance impact since tables are distributed. This however doesn’t mean that clustered/non-clustered indexes and heaps don’t have to be rebuilt. Due to the way logical table indexes are distributed and are physically separate indexes on each of the distributions, there is no good way to aggregate and show fragmentation of rowstore indexes. Therefore, a maintenance schedule should be implemented to rebuild rowstore indexes on a frequency that reflects the ratio of change in tables. Although we can’t see rowstore index fragmentation, it is not all bad news because maintenance is much faster in Synapse. In fact, in an ideal situation index rebuilds will only be 1/60th of the time it would take in SQL Server due to the data being evenly distributed across 60 distributions.
Clustered Columnstore Index Health
For columnstore indexes, the rules around when to rebuild are completely different and much more complicated. Because these indexes are almost always on our largest tables, CCI health is critical to overall Synapse Dedicated SQL Pool performance. To get straight to the point, there are 4 main reasons to rebuild your CCI indexes or recreate a table with a rowstore index instead:
- Small CCI’s that don’t have enough data to fill rowgroups – These should probably be converted to a clustered index instead. Typically, these are tables with less than 60 million records but this is not a hard rule. You can still get “good-enough” performance benefit from a CCI with even 30 million records; however, the CCI will have diminishing marginal returns of performance as the table is smaller in size.
- Small rowgroups – If an index’s average rowgroup size is less than 400k records, rebuild the index for better compression.
- Large Delete Bitmaps – This is where as much as 20% of your data has been logically deleted and needs to be removed via a rebuild process. If you do many updates or deletes, data is logically deleted but not physically removed until the index is rebuilt.
- Many open/uncompressed rowgroups – In some cases, rowgroups do not compress on their own and you may want to force them to compress; issue a rebuild statement for these indexes. At most you should have 60 rowgroups in an open state (this is the delta store). If you see more than 60 rowgroups in open or closed state (not compressed), you will want to rebuild the index.
It needs to be mentioned that the resources used to compress CCI’s is very important. CCI compression relies on a memory buffer that is allocated through the workload group or resource class assigned to the rebuild statement. Typically, it is recommended to run rebuild statements in a medium resource class or equivalent WLM group so that more records can fit into the buffer and provide better compression. (Note that resource recommendations here are relative to the DWU’s assigned to your Dedicated Pool and the width of the CCI table and likely will need to be adjusted.) If a rowgroup does not have optimal compression (having less than 1million records), you can check the reason why the rowgroup was trimmed in [sys].[dm_pdw_nodes_db_column_store_row_group_physical_stats]. A list of possible trim reasons are below and more details can be found here:
Description of trim_reason.
0 – UNKNOWN_UPGRADED_FROM_PREVIOUS_VERSION: Occurred when upgrading from the previous version of SQL Server.
1 – NO_TRIM: The row group was not trimmed. The row group was compressed with the maximum of 1,048,576 rows. The number of rows could be less if a subset of rows was deleted after delta rowgroup was closed
2 – BULKLOAD: The bulk-load batch size limited the number of rows.
3 – REORG: Forced compression as part of REORG command.
4 – DICTIONARY_SIZE: Dictionary size grew too large to compress all of the rows together.
5 – MEMORY_LIMITATION: Not enough available memory to compress all the rows together.
6 – RESIDUAL_ROW_GROUP: Closed as part of last row group with rows < 1 million during index build operation. Note: A partition build with multiple cores can result in more than one trim of this type.
7 – STATS_MISMATCH: Only for columnstore on in-memory table. If stats incorrectly indicated >= 1 million qualified rows in the tail but we found fewer, the compressed rowgroup will have < 1 million rows
8 – SPILLOVER: Only for columnstore on in-memory table. If tail has > 1 million qualified rows, the last batch remaining rows are compressed if the count is between 100k and 1 million
9 – AUTO_MERGE: A Tuple Mover merge operation running in the background consolidated one or more rowgroups into this rowgroup.
To check CCI health and determine if you need to rebuild an index the following query can be used as a sample. Use the above 4 rules/guidelines to target the greatest offenders first.