Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Stored Procedure to Compress Non Partitioned Tables


By:   |   Last Updated: 2016-08-09   |   Comments (2)   |   Related Tips: More > 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.


Last Updated: 2016-08-09


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, August 12, 2016 - 1:02:29 PM - SAinCA Back To Top

Could you briefly give your rationale for stating a compression_mode of ROW.  Would it not be more advantageous to run the estimate with ROW then PAGE and take the better option?  Having just run said SP on a 16M row table, small by this DB's standards, ROW reduced from 2.1GB to 1.55, but PAGE from 2.1 to 0.55.  Similaraly large savings of 1GB per index over the 5 non-CL ix's would appear, in this case, to recommend PAGE over ROW by a healthy margin.

Are there some unmentioned downsides to PAGE compression that you have found in practice?  I'm about to enlarge our use of compression on a critical customer's DB that was giving us huge disk Q numbers and thrashed SSDs until we applied PAGE compression to its, now, 914M+ rows in 3 indexes, and would hate to make a poor choice...

Thanks for the code - may well find it as the basis for our automation, with credit due you.


Friday, August 12, 2016 - 11:29:49 AM - Steve Smith Back To Top

Despite your note 2 in Stored Procedure Notes, when using Enterprise Edition the Compression can indeed be performed WITH (ONLINE=ON).  

There are a few exceptions (such as XML indexes, NVARCHAR(MAX) columns etc q.v. https://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx), but generally this is an easy option and essential on large 24/7 databases.

 


Learn more about SQL Server tools