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.

Rate

Share

Share

Rate