SQL Server Stored Procedure to Compress Non Partitioned Tables

By: Eli Leiba   |   Comments (3)   |   Related: > Compression

Problem

My SQL Server database has some very big tables that can be compressed. The tables are simple, non-partitioned tables and I want a quick tool to compress them. I do not want to shrink the whole database since it will apply a global lock on the whole database, including both the partitioned and non-partitioned objects. How can this task be achieved?

Solution

The solution shown here includes constructing a T-SQL stored procedure (I called it dbo.usp_CompressNonPartitionedTables). The stored procedure gets the schema name, the compression method and a minimal space threshold value. The procedure loops over all the non-partitioned tables in the database that belong to the given schema that are not already compressed by any method. Note - The partition number is always 1 for all non-partitioned tables and the data compression column is always 0 for the non-compressed tables. Therefore, the list of tables that meet the above conditions can be retrieved by executing the following query:

SELECT DISTINCT t.NAME AS table_name
FROM sys.partitions p
  ,sys.tables t
  ,sys.schemas s
WHERE p.object_id = t.object_id
  AND p.partition_number = 1
  AND t.schema_id = s.schema_id
  AND s.NAME = 'dbo'
  AND p.data_compression = 0
 

Executing this code on the Northwind sample database will produce the following results:

Employees
Categories
Customers
Shippers
Suppliers
Orders
Products
Order Details
CustomerCustomerDemo
CustomerDemographics
Region
Territories
EmployeeTerritories

The procedure will execute the sp_estimate_data_compression_savings system stored procedure. This procedure estimates the amount of disk savings (in KB) and predicts what happens if we enable row or page level compression, which are the two methods of SQL Server compression. These compression options are available for all kinds of objects: tables, indexes, and file groups. If the difference (in KB) between size_with_current_compression_setting and size_with_requested_compression_setting exceeds the given space parameter threshold value, then the given parameter compression method is applied using dynamic T-SQL.

SQL Server Stored Procedure to Compress Tables

CREATE PROCEDURE dbo.usp_CompressNonPartitionedTables (
  @compression_mode VARCHAR(10),
  @schema VARCHAR(30),
  @size_thresholdKB INT )

AS

BEGIN
 set nocount on
 
 DECLARE @tsql VARCHAR(200)
 DECLARE @tablename VARCHAR(60)
 DECLARE @expectedSavingKB BIGINT
 
 DECLARE cur CURSOR
 FOR
 (SELECT DISTINCT t.NAME AS table_name
 FROM sys.partitions p ,sys.tables t ,sys.schemas s
 WHERE p.object_id = t.object_id AND p.partition_number = 1 AND t.schema_id = s.schema_id
  AND s.NAME = @schema AND p.data_compression = 0);

 -- Create results table
 CREATE TABLE #estimated_savings (
  [object_name] SYSNAME
  ,[schema_name] SYSNAME
  ,[index_id] INT
  ,[partition_number] INT
  ,[size_with_current_compression_settingKB] BIGINT
  ,[size_with_requested_compression_settingKB] BIGINT
  ,[sample_size_with_current_compression_settingKB] BIGINT
  ,[sample_size_with_requested_compression_settingKB] BIGINT );

 OPEN cur 
 FETCH NEXT FROM cur INTO @tablename
 WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO #estimated_savings
  EXECUTE sp_estimate_data_compression_savings @schema_name = @schema
  ,@object_name = @tablename
  ,@index_id = NULL
  ,@partition_number = NULL
  ,@data_compression = @compression_mode;

  SELECT @expectedSavingKB =  ([size_with_current_compression_settingKB] - 
                               [size_with_requested_compression_settingKB])
  FROM #estimated_savings

  IF (@expectedSavingKB > @size_thresholdKB)
  BEGIN
   SET @tsql = 'ALTER TABLE ' + @schema + '.[' + @tableName + ']' +
    ' rebuild WITH( DATA_COMPRESSION = ' + @compression_mode + ' )'
   EXEC (@tsql)
  END
  TRUNCATE TABLE #estimated_savings
  FETCH NEXT FROM cur INTO @tablename
 END
 CLOSE cur
 DEALLOCATE cur
 DROP TABLE #estimated_savings
 set nocount off
END
GO

Stored Procedure Notes

1. Only the Enterprise (or Developer) Editions of SQL Server 2008 through SQL Server 2016 support this solution.
2. Rebuilding and compressing is an off-line operation. It must be done when the table is off-line and for the whole duration of the process.
3. Execute this procedure only if the amount of storage space saved is considerably large.
4. In the example shown here the threshold selected is small (15KB), usually it's not necessary to compress such small amount, it is done here only for demonstration purposes.

Sample Stored Procedure Execution

Let's take the northwind sample database and execute the stored procedure. Prior to the stored procedure execution, all the user tables that reside in the dbo schema are non-partitioned and also not compressed. (data_compression column = 0).

We execute the procedure like this:

USE Northwind
GO

EXEC dbo.usp_CompressNonPartitionedTables 
     @compression_mode = 'ROW',
     @schema = 'dbo',
     @size_thresholdKB = 15
GO

Now we check the results by running the following query in the northwind database:

SELECT DISTINCT t.NAME AS table_name
FROM sys.partitions p
  ,sys.tables t
  ,sys.schemas s
WHERE p.object_id = t.object_id
  AND p.partition_number = 1
  AND t.schema_id = s.schema_id
  AND s.NAME = 'dbo'
  AND p.data_compression <> 0

The result is: Orders. That means that the orders table has been compressed using ROW level compression and the storage saved is greater than 15KB.

Further notes: The procedure was tested on SQL Server 2012 Enterprise Edition.

Next Steps

• Consider using this procedure if you want to compress non-partitioned tables without a whole database shrink.
• Learn more about SQL Server Compression.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips