Geek Answers Handbook

Does row compression help before placing in the database?

I use C # to populate a database table (SQL Server 2005 or 2008). I want the database table size to be kept as small as possible because Godaddy only allows 200 MB databases, and I want to get the most out of it. Should I compress my string data when I insert it, or is there a better way to reduce the size of the database with some form of compression?

+5
source share
3 answers

Option D - do nothing. Because he did for you .

+15
source

, , . ? , /.

+2

. :

3. T-SQL script ( )

/*

  Description: This stored procedure can compress a database or list out estimations for the savings from possible compression. 
  Create the stored procedure on database level.

  Call:        
    USE AdventureWorksDW2014;
    GO

    EXEC dbo.sp_compress_database 
    @dbname = 'AdventureWorksDW2014',            -- Database to compress
    @compression_type = 'PAGE',                    -- Compression type: PAGE, ROW or NONE
    @mode = 1;                                    -- Mode can be: 1 -> compression and 0 -> estimation for compression

  Author: Igor Micev
  Date: 2012-10-25 

*/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[sp_compress_database]
(
 @dbname NVARCHAR(100),@compression_type VARCHAR(10),@mode BIT
)
AS 
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#tables_for_compression') IS NOT NULL DROP TABLE #tables_for_compression;

    CREATE TABLE #tables_for_compression
    (
     [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,[object_name] VARCHAR(100),[schema_name] VARCHAR(20),[index_id] INT,[partition_number] INT,[size_before_compression_KB] INT,[size_after_compression_KB] INT,[sample_size_before_compression_KB] INT,[sample_size_after_compression_KB] INT
    );

    IF @compression_type NOT IN('PAGE','ROW','NONE')
    BEGIN
        RAISERROR('Compression type is not valid.',16,1);

        RETURN;
    END;

    DECLARE @dynamic_cmd NVARCHAR(1000);
    DECLARE @tbl NVARCHAR(100);
    DECLARE @schema NVARCHAR(20);
    DECLARE tbl_cursor CURSOR FOR SELECT isc.TABLE_NAME,isc.TABLE_SCHEMA
                                  FROM INFORMATION_SCHEMA.TABLES AS isc
                                  WHERE isc.TABLE_CATALOG=@dbname AND isc.TABLE_TYPE='BASE TABLE';

    OPEN tbl_cursor;

    FETCH NEXT FROM tbl_cursor INTO @tbl,@schema;

--Examine the clustered indexes

    WHILE @@fetch_status=0
    BEGIN
        IF @mode=1
        BEGIN
            SET @dynamic_cmd='USE ['+@dbname+'] ALTER TABLE ['+@schema+'].['+@tbl+'] 
            REBUILD PARTITION = ALL WITH (ONLINE = ON, DATA_COMPRESSION = '+@compression_type+')';
        END;

        BEGIN TRY
            IF @mode=0
            BEGIN
                SET @dynamic_cmd='EXEC sp_estimate_data_compression_savings '''+@schema+''', '''+@tbl+''', NULL, NULL,'''+@compression_type+'''';

                INSERT INTO #tables_for_compression([object_name],[schema_name],index_id,partition_number,size_before_compression_KB,size_after_compression_KB,sample_size_before_compression_KB,sample_size_after_compression_KB)
                EXEC sp_executesql @dynamic_cmd;
            END;

            IF @mode=1
            BEGIN
                EXEC sp_executesql @dynamic_cmd;

                PRINT @schema+'.'+@tbl+' was compressed.';
            END;
        END TRY
                BEGIN CATCH
            PRINT 'Failed command: '+@dynamic_cmd;
        END CATCH;

        FETCH NEXT FROM tbl_cursor INTO @tbl,@schema;
    END;

    CLOSE tbl_cursor;

    DEALLOCATE tbl_cursor;

--Examine the nonclustered indexes. Exclude XML type indexes.

    IF @mode=1
    BEGIN
        DECLARE @ind_name NVARCHAR(100);
        DECLARE ncix CURSOR FOR SELECT ss.name AS [schema],OBJECT_NAME(ddips.object_id) AS table_name,si.name AS index_name
                                FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'SAMPLED') AS ddips
                                JOIN sys.indexes AS si ON ddips.index_id=si.index_id AND ddips.object_id=si.object_id
                                JOIN sys.tables AS st ON ddips.object_id=st.object_id
                                JOIN sys.schemas AS ss ON st.schema_id=ss.schema_id
                                WHERE si.index_id>1 AND si.[type]=2 AND ddips.page_count>64;

--Nonclustered indexes with more than 64 pages

        OPEN ncix;

        FETCH NEXT FROM ncix INTO @schema,@tbl,@ind_name;

        WHILE(@@fetch_status=0)
        BEGIN
            SET @dynamic_cmd='ALTER INDEX '+@ind_name+' ON '+@schema+'.'+@tbl+' 
            REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = '+@compression_type+')';

            BEGIN TRY
                EXEC sp_executesql @dynamic_cmd;

                PRINT 'Index '+@ind_name+' was compressed.';
            END TRY
                    BEGIN CATCH
                PRINT 'Index '+@ind_name+' cannot be compressed. Err.Msg: '+@@error;
            END CATCH

            FETCH NEXT FROM ncix INTO @schema,@tbl,@ind_name;
        END;

        CLOSE ncix;

        DEALLOCATE ncix;
    END

    IF @mode=0
    SELECT *
    FROM #tables_for_compression;

    IF OBJECT_ID('tempdb..#tables_for_compression') IS NOT NULL DROP TABLE #tables_for_compression;
END
0

More articles:


All Articles