Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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
   
    IF @row_count > 0
     BEGIN
        IF @index_type_desc = 'HEAP'
         BEGIN
            SELECT  @sql_string = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) + ' REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=' + @compression_type + ', ONLINE=ON)'
            RAISERROR(@sql_string, 0, 0) WITH NOWAIT
            EXEC    sp_executesql @sql_string
         END
        SELECT  @sql_string = 'ALTER INDEX ALL ON ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) + ' REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=' + @compression_type + ', ONLINE=ON)'
        RAISERROR(@sql_string, 0, 0) WITH NOWAIT
        EXEC    sp_executesql @sql_string
     END
    ELSE
     BEGIN
        PRINT 'No work to do.'
     END
 END
 
 IF GETDATE() > DATEADD(mi, @minutes_to_run, @start_time)
  BEGIN
    PRINT 'Shut down to due long run time.'
  END
GO

I hope you find this stored procedure useful, but I realize it is not for everyone. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Comments

Posted by Anonymous on 5 November 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, A Brute Force Way to Compress a Database - Adventures in SQL         [sqlservercentral.com]        on Topsy.com

Posted by Dukagjin Maloku on 5 November 2010

Interesting thing here, doing something without third party tools, thanks for sharing!

Posted by Nakul Vachhrajani on 8 November 2010

Hello!

I agree wtih Dukagjin. Doing compression without 3rd party tools is definitely interesting!

Thank-you so very much for spending the time researching and coming up with this. Thanks for sharing this script with all.

Posted by henrik staun poulsen on 9 November 2010

The Online=On part is "Enterprise only" version of SQL Server. Not that it works particularly well, IMNSHO.

Posted by Iulian on 9 November 2010

I tried on a test db but I get: No work to do. How to damage the db so that I can test?

Thanks,

Iulian

Posted by ralphonse on 9 November 2010

Ohoooo...yahoooo.....this is what i was expecting without paying extra money, without any 3PT ...great man...Rock..Rock

Posted by David Levy on 12 November 2010

@henrik - Ooh good catch. I completely forgot to point that out.

@Iulian - The simplest way to create a test data on a non-production machine set is to just do a select into from a reasonably sized table.

Posted by Perry Whittle on 15 November 2010

@henrik - Data compression is an Enterprise only feature anyway so online=on will not be an issue!

Rather than just enabling this for every table\index, remember this can really hit the CPU especially page compression (not good on your production server). The system stored procedure sp_estimate_data_compression_savings should really be used first to check whether savings will actually be made as types such as ntext, image, etc will not compress!

Posted by cmgui on 20 July 2012

Is there a mistake in line 35?

AND c.object_id IS NULL  

Should it be

AND c.object IS NOT NULL

Leave a Comment

Please register or log in to leave a comment.