1018giorni trascorsi da
SQLBits 9

powered by

Progetti‎ > ‎

Compress Tables

Obiettivi

  • comprimere tutte le tabelle/indici di un database o una specifica tabella (con tutti i suoi indici non-clustered) o uno specifico indice di un database.

Per iniziare


Le mie tabelle e i miei indici sono compressi? E come?

 SELECT
    table_name = quotename(schema_name(tbl.schema_id)) + '.' + quotename (tbl.name),
    index_name = quotename(idx.name),
    object_type = CASE idx.index_id
     WHEN 0 THEN 'Heap'
     WHEN 1 THEN 'Clustered'
        else 'Non-Clustered'
    END,
    partition_number = p.partition_number,
    row_count = CAST(p.rows AS float),
    filegroup_name = fg.name,
    data_compression = case p.data_compression
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Row'
        WHEN 2 THEN 'Page'
    END   
FROM
    sys.tables AS tbl
INNER JOIN
    sys.indexes AS idx ON idx.object_id = tbl.object_id
INNER JOIN
    sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int)
 AND p.index_id=idx.index_id
INNER JOIN
    sys.indexes AS indx ON p.object_id = indx.object_id
 AND p.index_id = indx.index_id
LEFT OUTER JOIN
    sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id
 AND dds.destination_id = p.partition_number
LEFT OUTER JOIN
    sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN
    sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
 OR fg.data_space_id = indx.data_space_id

 


Stored procedure

create proc dbo.stp_compress_tables (
    @compression_type varchar(30) = 'PAGE', --(NONE, ROW, PAGE)
    @schema_name sysname = null,
    @table_name sysname = null,
    @index_name sysname = null
)
as
declare
    @sql_string nvarchar(4000),
    @edition varchar(30),
    @version varchar(10);
set @table_name = coalesce(quotename(@schema_name) + '.' + quotename(@table_name), null);
;with a as (
    select
        edition = convert(varchar, SERVERPROPERTY ('Edition')),
        version = convert(varchar, SERVERPROPERTY('ProductVersion'))
)
select @edition = edition, @version = left(version, charindex('.', version)-1) from a;

if @edition like 'Enterprise Edition%' or @edition like 'Developer Edition%'
 and @version >= 10
begin
    ;with cte as
    (
        select
   table_name = quotename(schema_name(tbl.schema_id)) + '.' + quotename (tbl.name),
            index_name = idx.name
        from
            sys.tables as tbl
        inner join
            sys.indexes as idx on idx.object_id = tbl.object_id
        inner join
            sys.partitions as p on p.object_id=cast(tbl.object_id as int) and p.index_id=idx.index_id
        where
            @compression_type <> (case p.data_compression
                                    when 0 then 'none'
                                    when 1 then 'row'
                                    when 2 then 'page'
                                end)
    )
    select table_name, index_name
        into #t1
    from cte
        where (@table_name is null or @table_name = table_name)
   and (@index_name is null or @index_name = index_name)
   
 set @table_name = '';
 set @index_name = '';
 
    declare c cursor static
    for
        select table_name, index_name from #t1;
           
    open c;
    fetch next from c into @table_name, @index_name;
    while @@FETCH_STATUS = 0
    begin
      
  if @index_name is null --it's a Heap  
   set @sql_string = 'ALTER TABLE @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)';   
  else --it's an Clustered or Non-Clustered index
   set @sql_string = 'ALTER INDEX @index_name ON @table_name REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = @compression_type)'; 
  
        set @sql_string = REPLACE(@sql_string, '@table_name', @table_name)
        set @sql_string = REPLACE(@sql_string, '@index_name', coalesce(@index_name,''))
        set @sql_string = REPLACE(@sql_string, '@compression_type', @compression_type)
      
        print @sql_string;
        exec sp_executesql @sql_string;
      
        fetch next from c into @table_name, @index_name;  
    end
    close c;
    deallocate c;
end
else
begin
     print 'La edition/versione del SQL Server non è compatibile con la data compression.'
     print 'Edition: ' + @edition;
     print 'Versione: ' + @version;   
end;
go


Č
ą
Francesco Quaratino,
31/dic/2010 02:20
ċ
FrancescoQuaratino_DataCompressionState.rgtool
(2k)
Francesco Quaratino,
31/dic/2010 02:17
ċ
FrancescoQuaratino_LetsCompressTables.rgtool
(5k)
Francesco Quaratino,
31/dic/2010 02:17
Comments