The Bit Bucket (Greg Low): IDisposable : Suggest Compression Strategies for Tables and Indexes

Suggest Compression Strategies for Tables and Indexes

I make no secret of the fact that I think that table compression was the best thing added to SQL Server 2008.

It seems like every week that I’m talking to clients about it. They have Enterprise Edition but they weren’t using it because they were familiar with O/S level disk compression and they think that compression will mean smaller and slower, yet this often couldn’t be further from the truth for customers whose databases are currently I/O bound or who don’t have enough memory in their buffer cache for it to work correctly.

It’s important though, to make sure the correct form of compression is used for each table and index. In fact, the decision should be taken for each partition of each table and index if the table is partitioned. We often recommend different compression strategies for different partitions of the same table.

To make it easier to decide which form of compression might suit, I’ve created another version of the script that I use as a first pass in determining compression strategies. The earlier version suggested ROW and/or PAGE compression. While this version can do the same, it also considers the clustered columnstore indexes that are part of the Enterprise Edition of SQL Server 2014. (I’ve included a switch to turn that function on or off in case you don’t have 2014 yet).

The cutoff points are configurable but my default values are as shown.

  • CCI (Clustered Columnstore Index) will be recommended where the partition is scanned more than 95% of the time, updated less than 10% of the time, seeks and lookups are less than 5% of the time, and where there are at least 800,000 rows. It will also only be recommended if it is supported.
  • PAGE will be recommended where the partition is scanned more than 75% of the time and updated less than 20% of the time.
  • ROW will be recommended in all other cases. We believe that ROW should be the default in SQL Server across the board, instead of NONE.

It is important that this script only be run after the system has been in use for long enough to have experienced typical usage patterns.

I hope this helps you to get into using compression where it makes sense for you.

— Suggest data compression changes for tables and indexes
— Dr Greg Low
— March 2015
DECLARE @ClusteredColumnstoreScansCutoff int = 95;
DECLARE @ClusteredColumnstoreUpdatesCutoff int = 10;
DECLARE @ClusteredColumnstoreSeeksLookupsCutoff int = 5;
DECLARE @ClusteredColumnstoreTotalRowsCutoff bigint = 800000;
DECLARE @PageCompressionScansCutoff int = 75;
DECLARE @PageCompressionUpdatesCutoff int = 20;
DECLARE @IsClusteredColumnstoreSupported bit = 1;
WITH IndexUsageStats
( SELECT object_id AS ObjectID,
         index_id AS IndexID,
         COALESCE(user_seeks, 0) + COALESCE(system_seeks, 0) AS Seeks,
         COALESCE(user_scans, 0) + COALESCE(system_scans, 0) AS Scans,
         COALESCE(user_lookups, 0) + COALESCE(system_lookups, 0) AS Lookups,
         COALESCE(user_updates, 0) + COALESCE(system_updates, 0) AS Updates,
         COALESCE(user_seeks, 0) + COALESCE(system_seeks, 0)
           + COALESCE(user_scans, 0) + COALESCE(system_scans, 0)
           + COALESCE(user_lookups, 0) + COALESCE(system_lookups, 0)
           + COALESCE(user_updates, 0) + COALESCE(system_updates, 0) AS Operations
  FROM sys.dm_db_index_usage_stats
  WHERE database_id = DB_ID()
( SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, AS TableName, AS IndexName,
         i.index_id AS IndexID,
         i.type_desc AS IndexType,
         CASE WHEN COALESCE(Operations, 0)  0
              THEN CAST((COALESCE(Seeks, 0) + COALESCE(Lookups, 0)) * 100.0
                        / COALESCE(Operations, 0) AS int)
              ELSE 0
         END AS SeekLookupPercentage,
         CASE WHEN COALESCE(Operations, 0)  0
              THEN CAST(COALESCE(Scans, 0) * 100.0 / COALESCE(Operations, 0) AS int)
              ELSE 0
         END AS ScanPercentage,
         CASE WHEN COALeSCE(Operations, 0)  0
              THEN CAST(COALESCE(Updates, 0) * 100.0 / COALESCE(Operations, 0) AS int)
              ELSE 0
         END AS UpdatePercentage,
         p.partition_number AS PartitionNumber,
         p.data_compression_desc AS CurrentCompression,
         p.rows AS TotalRows
   FROM sys.tables AS t
   INNER JOIN sys.indexes AS i
   ON t.object_id = i.object_id
   INNER JOIN sys.partitions AS p
   ON i.object_id = p.object_id
   AND i.index_id = p.index_id
   LEFT OUTER JOIN IndexUsageStats AS ius
   ON i.object_id = ius.ObjectID
   AND i.index_id = ius.IndexID
   WHERE i.index_id > 0
   AND t.is_ms_shipped = 0
   AND t.type = N’U’
( SELECT pud.*,
         CASE WHEN pud.ScanPercentage >= @ClusteredColumnstoreScansCutoff
              AND pud.UpdatePercentage <= @ClusteredColumnstoreUpdatesCutoff
              AND pud.SeekLookupPercentage <= @ClusteredColumnstoreSeeksLookupsCutoff
              AND pud.TotalRows >= @ClusteredColumnstoreTotalRowsCutoff
              AND @IsClusteredColumnstoreSupported  0
              THEN N’CCI’
              WHEN pud.ScanPercentage >= @PageCompressionScansCutoff
              AND pud.UpdatePercentage <= @PageCompressionUpdatesCutoff
              THEN N’PAGE’
              ELSE N’ROW’
         END AS SuggestedCompression
  FROM PartitionUsageDetails AS pud
SELECT spct.SchemaName,
FROM SuggestedPartitionCompressionTypes AS spct
WHERE spct.SuggestedCompression  spct.CurrentCompression
ORDER BY spct.SchemaName,
         CASE WHEN spct.IndexID = 1 THEN 0 ELSE 1 END,
Published Thursday, March 05, 2015 7:56 PM by Greg Low

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s