SQL Centre of Excellence

Compress an entire database

by Bob Duffy 16. September 2011 04:30

I posted a script earlier to generate the script to compress an entire database. here is one to do the compress/rebuild partition by partition. one thing to bear in mind is that ONLINE rebuild is not supported  when rebuilding partition by partition. See http://technet.microsoft.com/en-us/library/ms188388.aspx 

My simpler script to just do compression at the table level is here

http://blogs.msdn.com/b/boduff/archive/2010/02/23/how-good-is-data-compression-in-sql-2008.aspx

So I think the best advice is “if” you can go offline, then do it a partition at a time. if you cannot, then do the entire table and accept the longer process.

/*
    Script to Produce Compression Statements  
    Notes: Can change MAXDOP and only select one type of index if needed

*/

select 'ALTER ' + case when si.type =1 then 'INDEX [' + si.name + '] ON ' else 'TABLE ' END +  ' [' + s.name + '].[' + o.name 
+ '] REBUILD PARTITION=' 
+ case when ds.type='PS' then convert(varchar,sp.partition_number) else 'ALL' end 
+ '  WITH (DATA_COMPRESSION=PAGE, MAXDOP=8, FILLFACTOR=100'
+ case when  ds.type='FG' then ' ,ONLINE = ON' else '' end -- Cannot REBUILD INDEX ONLINE for a partition
+'); ' 
from sys.indexes  si
inner join sys.objects o on o.object_id =si.object_id
inner join sys.schemas s on s.schema_id=o.schema_id 
inner join sys.partitions sp on si.index_id =sp.index_id  and sp.object_id =o.object_id 
inner join sys.data_spaces ds on ds.data_space_id =si.data_space_id 
where si.type>0 and o.type='U'
/* comment these out as appropriate if you only want to do one type of index or one table*/
--and si.type_desc ='CLUSTERED'
--and si.type_desc ='NONCLUSTERED'
--and o.object_id=object_id('DatabaseLog')
order by s.name, o.name, si.type, si.index_id
blog comments powered by Disqus

Page List

Page List