Subscribe to this blog
Briefcase
Print
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.
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