A Brute Force Way to Compress a Database

There are a handful of scripts out there to compress all of of the objects in your SQL 2008 database using Row, Page or a smart combination of both compression types. This is not one of those scripts.

This script is written to compress all tables in a database without wasting any time on analysis. I have implemented the script as a stored procedure that takes 2 paramaters. The first is whether to use row or page compression and the second is how long to run for in minutes. The intent would be to call the stored procedure from a job on a regular basis to run for x minutes compressing everything it can then shut down and wait for the next run time and do the same. Eventually all objects in the database will be compressed.

I would recommend against running this without any analysis against any database. That said, it can be a handy tool for compressing an ODS or even an EDW. It would also be a great way to pick up “the rest of the tables” in a database where proper analysis has been done and a particular compression type has been applied to a small set of tables.

Here is the script:

CREATE PROCEDURE dbo.sp_dba_compress_user_objects @compression_type nvarchar(20) = 'PAGE', @minutes_to_run int = 1440

AS

DECLARE @schema_name        sysname,
       @object_name        sysname,
       @sql_string         nvarchar(max),
       @row_count          int,
       @start_time         datetime,
       @index_type_desc    nvarchar(60)
       
SELECT  @row_count          = 1,
       @start_time         = GETDATE(),
       @compression_type   = UPPER(@compression_type)

IF @compression_type NOT IN ('ROW', 'PAGE')
   RAISERROR('@compression_type must be ''ROW'' or ''PAGE''', 16, 1)

WHILE @row_count > 0 AND DATEADD(mi, @minutes_to_run, @start_time) > GETDATE()
BEGIN
   SELECT      TOP 1
               @schema_name = OBJECT_SCHEMA_NAME(p.object_id, DB_ID()),
               @object_name = OBJECT_NAME(p.object_id, DB_ID()),
               @index_type_desc = ips.index_type_desc
   FROM        sys.partitions p
               INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
                   ON p.object_id = ips.object_id
                       AND p.index_id = ips.index_id
               LEFT OUTER JOIN sys.columns c
                   ON p.object_id = c.object_id
                       AND c.is_sparse = 1
   WHERE       p.data_compression_desc = 'NONE'
                   AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
                       AND OBJECTPROPERTY(c.object_id, 'IsMSShipped') = 0
                           AND c.object_id IS NULL
   ORDER BY    ips.page_count

   SELECT  @row_count = @@ROWCOUNT

Read more: Adventures in SQL

Posted via email from .NET Info