In SQL Server 2008, there is the possibility to compress the data stored in the database.<\/p>\n
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.<\/p>\n
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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
set nocount on
\ndeclare @idx table(
\nobjnname sysname,
\nschname sysname,
\nindex_id int,
\npartition_number int,
\ncurrentSize bigint,
\ncompSize bigint,
\nScurrentSize bigint,
\nScompSize bigint)<\/p>\ndeclare
\n@sql varchar(max),
\n@tn varchar(128),
\n@sn varchar(128),
\n@oid int,
\n@iid int<\/p>\ndeclare c cursor local fast_forward for<\/p>\n
select object_schema_name(object_id), object_name(object_id), t.[object_id]\nfrom sys.tables t with(nolock)
\nwhere t.[type] = ‘U’
\norder by object_schema_name(object_id), object_name(object_id)
\nopen c
\nwhile 1=1
\nbegin
\nfetch next from c into @sn, @tn, @oid
\nif @@FETCH_STATUS <> 0 break
\nraiserror(‘%s.%s’, 10, 1, @sn, @tn) with nowait<\/p>\n– Get the evaluation of the effectiveness of compression index<\/strong><\/p>\n
insert into @idx
\nexec sp_estimate_data_compression_savings @sn, @tn, NULL, NULL, ‘PAGE’ ;<\/p>\ndeclare ic cursor local fast_forward for
\nselect si.name, si.index_id
\nfrom @idx i
\ninner join sys.indexes si
\non si.[object_id] = @oid
\nand si.index_id = i.index_id
\n— Do not compress that bad contracts<\/strong>
\nwhere i.ScurrentSize > i.ScompSize*1.3
\n— Do not compress small indexes and tables<\/strong>
\nand i.currentSize > 10000 — >10Mb
\nopen ic
\ndeclare @index varchar(128)
\nwhile 1=1
\nbegin
\nfetch next from ic into @index, @iid
\nif @@FETCH_STATUS <> 0 break
\nprint @iid
\n— If index_id> 0, this index
\nif @iid > 0
\nset @sql = ‘alter index [‘ @index ‘] on [‘ @sn ‘].[‘ @tn ‘] rebuild with(data_compression=page)’
\n— If index_id = 0, then it is “a lot”
\nelse
\nset @sql = ‘alter table [‘ @sn ‘].[‘ @tn ‘] rebuild with(data_compression=page)’
\nraiserror(‘%s’, 10, 1, @sql) with nowait
\nexec(@sql)
\nend
\nclose ic
\ndeallocate ic
\ndelete from @idx<\/p><\/blockquote>\nend<\/p>\n
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.<\/p>\n
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.<\/p>\n
Please Note:<\/strong> 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<\/a><\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"
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… <\/p>\n