SQL Server Stored Procedure to Compress Non Partitioned Tables
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?
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
- Only the Enterprise (or Developer) Editions of SQL Server 2008 through SQL Server 2016 support this solution.
- 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.
- Execute this procedure only if the amount of storage space saved is considerably large.
- 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.
- 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
View all my tips
Article Last Updated: 2016-08-09