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) @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.
