Synapse Fundamentals for Tuning – Indexes Part2

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/60^th 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:

1. 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.
2. Small rowgroups – If an index’s average rowgroup size is less than 400k records, rebuild the index for better compression.
3. 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. 
4. 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:

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.