In SQL Server 2008, there is the possibility to compress the data stored in the database.

Compression can give a good performance gain for those databases whose size is much larger than the server’s memory and as a consequence, the server is experiences a substantial load on the disk system. Efficient use of disk in such cases is increasing at an actual rate of data compression.

In order to use this compression, it is necessary for a particular table or index to use the rebuild command with the desired compression type (page or row).

The following script compresses the data in all user tables in the database, after assessing the extent appropriate compression. Will not be compressed tables and indexes, the compression ratio which is estimated server in less than 1.3 or size – less than 10 megabytes.

This script is used to compress the page level, but the script is easy to refine and to determine the most appropriate for a particular table or index compression, – page / row.

set nocount on
declare @idx table(
objnname sysname,
schname sysname,
index_id int,
partition_number int,
currentSize bigint,
compSize bigint,
ScurrentSize bigint,
ScompSize bigint)

declare
@sql varchar(max),
@tn varchar(128),
@sn varchar(128),
@oid int,
@iid int

declare c cursor local fast_forward for

select object_schema_name(object_id), object_name(object_id), t.[object_id] from sys.tables t with(nolock)
where t.[type] = ‘U’
order by object_schema_name(object_id), object_name(object_id)
open c
while 1=1
begin
fetch next from c into @sn, @tn, @oid
if @@FETCH_STATUS <> 0 break
raiserror(‘%s.%s’, 10, 1, @sn, @tn) with nowait

– Get the evaluation of the effectiveness of compression index

insert into @idx
exec sp_estimate_data_compression_savings @sn, @tn, NULL, NULL, ‘PAGE’ ;

declare ic cursor local fast_forward for
select si.name, si.index_id
from @idx i
inner join sys.indexes si
on si.[object_id] = @oid
and si.index_id = i.index_id
— Do not compress that bad contracts
where i.ScurrentSize > i.ScompSize*1.3
— Do not compress small indexes and tables
and i.currentSize > 10000 — >10Mb
open ic
declare @index varchar(128)
while 1=1
begin
fetch next from ic into @index, @iid
if @@FETCH_STATUS <> 0 break
print @iid
— If index_id> 0, this index
if @iid > 0
set @sql = ‘alter index [‘ @index ‘] on [‘ @sn ‘].[‘ @tn ‘] rebuild with(data_compression=page)’
— If index_id = 0, then it is “a lot”
else
set @sql = ‘alter table [‘ @sn ‘].[‘ @tn ‘] rebuild with(data_compression=page)’
raiserror(‘%s’, 10, 1, @sql) with nowait
exec(@sql)
end
close ic
deallocate ic
delete from @idx

end

Before you apply compression on the industrial base, it is better to assess the degree of compression in both modes to test and evaluate the growth or productivity. And only then, apply compression to the tables and indexes for which it is really important.

The compression and the extraction of compressed data processor should be the same, or depending on the database, you can lose in performance. For example, there’s virtually no point to compress data in the database, if the total amount of real-world data is less than the amount of available SQL Server memory. In this case, you can get a win when the dedicated server web hosting starts at the time when the buffer pool (cache server data) is filled with data from the database, but the rest of the time probably any advantage from the fact that the data is compressed, will be gone.

Please Note: If you are looking for Rich Data Center Web Hosting, Cloud Services, Dedicated Hosting, Colocation or Software Services in India at Affordable Costs, Visit our website at WWW.ESDS.CO.IN.