16 June 2012

How to turn on Row level compression for SQL databases

One of my favorite things to do to improve the performance of a DB is to turn on a simple option... Row level compression. This reduces the column storage space to exactly what is needed for the field. Check out what MS has to say about it.

Anyway this script will turn on Row level compression for all tables and all partitions on tables.
(Only works with MS SQL Server Enterprise)


DECLARE @SqlStatment AS NVARCHAR(512)
DECLARE SqlStatmentCursor CURSOR FOR
  SELECT
    'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' AS SqlStatement 
  FROM
    sysobjects
  WHERE
    TYPE = 'U' -- all user tables
UNION
  SELECT
    'ALTER INDEX ALL on dbo.[' + name + '] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW, FILLFACTOR = 100, PAD_INDEX  = OFF, MAXDOP = ' + @MAXDOP + ', STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON );' AS SqlStatement
  FROM
    sysobjects
  WHERE
    TYPE = 'U' -- all user tables

OPEN SqlStatmentCursor

FETCH NEXT FROM SqlStatmentCursor INTO @SqlStatment
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC (@SqlStatment)
  END TRY
  BEGIN CATCH
    Select 'Failure' AS ExecutionStatus, @SqlStatment AS [SqlStatment]
  END CATCH
 FETCH NEXT FROM SqlStatmentCursor INTO @SqlStatment
END

CLOSE SqlStatmentCursor
DEALLOCATE SqlStatmentCursor 

No comments:

Post a Comment

Blog Slideshow (all images from this blog)