March 26, 2016 at 7:50 am
Dear Experts
is any one has a script that makes a page compression for all tables in a database
I have tried similar to this script but the db still in the same size
SET NOCOUNT ON
SELECT
'ALTER TABLE ['
+ s.[name]
+ '].['
+ o.[name]
+ '] REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas AS s WITH (NOLOCK)
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.[type] = 'U'
--AND s.Name LIKE '%%' -- filter by table name
--AND o.Name LIKE '%%' -- filter by schema name
ORDER BY ps.[reserved_page_count]
SET NOCOUNT OFF
------------------------------------------------------------------
And when I create page compression manually for a certain table the db size is downgraded
Thank you very much
March 27, 2016 at 3:09 am
Don't think that compression should be applied to everything in a database as sometimes the space gain is marginal which hardly justifies the compression.
😎
Below are two scripts, the first compresses tables and indices in two separate steps then runs DBCC CLEANTABLE on all the tables, the second one decompresses all tables and indices in one step.
Compression
/********************************************************************
COMPRESS ALL USER TABLES AND INDICES IN A DATABASE THEN
DBCC CLEANTABLE ON ALL USER TABLES
http://qa.sqlservercentral.com/Forums/Topic1772625-3411-1.aspx
********************************************************************/
-- SET @EXEC = 1 TO EXECUTE OTHERWISE SELECT.
DECLARE @EXEC INT = 0;
DECLARE @COMPRESS_TBL_STR NVARCHAR(MAX) = N'
USE {{@DB_NAME}}
RAISERROR(''COMPRESSING {{@SCHEMA_NAME}}.{{@TABLE_NAME}}'',0,0) WITH NOWAIT;
ALTER TABLE {{@SCHEMA_NAME}}.{{@TABLE_NAME}} REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE , MAXDOP = 1);
';
DECLARE @COMPRESS_IDX_STR NVARCHAR(MAX) = N'
USE {{@DB_NAME}}
RAISERROR(''COMPRESSING {{@INDEX_NAME}} ON {{@SCHEMA_NAME}}.{{@TABLE_NAME}}'',0,0) WITH NOWAIT;
ALTER INDEX {{@INDEX_NAME}} ON {{@SCHEMA_NAME}}.{{@TABLE_NAME}} REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE , MAXDOP = 1);
';
DECLARE @DBCC_CLEANTABLE_STR NVARCHAR(MAX) = N'
RAISERROR(''DBCC CLEANTABLE {{@SCHEMA_NAME}}.{{@TABLE_NAME}}'',0,0) WITH NOWAIT;
DBCC CLEANTABLE ( {{@DB_NAME}}, N''{{@SCHEMA_NAME}}.{{@TABLE_NAME}}'',1000000) WITH NO_INFOMSGS;
';
-- TABLE COMPRESSION
DECLARE @SQL_STR NVARCHAR(MAX) = N''
SELECT @SQL_STR =
(
SELECT
REPLACE(
REPLACE(
REPLACE(@COMPRESS_TBL_STR,N'{{@TABLE_NAME}}' ,QUOTENAME(ST.name))
,N'{{@SCHEMA_NAME}}',QUOTENAME(OBJECT_SCHEMA_NAME(ST.object_id)))
,N'{{@DB_NAME}}' ,QUOTENAME(DB_NAME(DB_ID()))
)
FROM sys.tables ST
WHERE ST.type = 'U'
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
IF (@EXEC <> 1)
BEGIN
SELECT @SQL_STR;
END
ELSE
BEGIN
EXEC ( @SQL_STR );
END
-- INDEX COMPRESSION
SELECT @SQL_STR =
(
SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE(@COMPRESS_IDX_STR,N'{{@TABLE_NAME}}' ,QUOTENAME(ST.name))
,N'{{@SCHEMA_NAME}}',QUOTENAME(OBJECT_SCHEMA_NAME(ST.object_id)))
,N'{{@INDEX_NAME}}' ,QUOTENAME(SI.name))
,N'{{@DB_NAME}}' ,QUOTENAME(DB_NAME(DB_ID()))
)
FROM sys.tables ST
INNER JOIN sys.indexes SI
ON ST.object_id = SI.object_id
WHERE ST.type = 'U'
AND SI.index_id > 1
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
IF (@EXEC <> 1)
BEGIN
SELECT @SQL_STR;
END
ELSE
BEGIN
EXEC ( @SQL_STR );
END
-- DBCC CLEANTABLE
SELECT @SQL_STR =
(
SELECT
REPLACE(
REPLACE(
REPLACE(@DBCC_CLEANTABLE_STR,N'{{@TABLE_NAME}}' ,QUOTENAME(ST.name))
,N'{{@SCHEMA_NAME}}',QUOTENAME(OBJECT_SCHEMA_NAME(ST.object_id)))
,N'{{@DB_NAME}}' ,QUOTENAME(DB_NAME(DB_ID()))
)
FROM sys.tables ST
WHERE ST.type = 'U'
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
IF (@EXEC <> 1)
BEGIN
SELECT @SQL_STR;
END
ELSE
BEGIN
EXEC ( @SQL_STR );
END
Decompression
/********************************************************************
DECOMPRESS ALL USER TABLES AND INDICES IN A DATABASE
http://qa.sqlservercentral.com/Forums/Topic1772625-3411-1.aspx
********************************************************************/
-- SET @EXEC = 1 TO EXECUTE OTHERWISE SELECT.
DECLARE @EXEC INT = 0;
DECLARE @SQL_STR NVARCHAR(MAX) = N''
DECLARE @DECOMPRESS_STR NVARCHAR(MAX) = N'
USE {{@DB_NAME}}
RAISERROR(''DECOMPRESSING {{@INDEX_NAME}} ON {{@SCHEMA_NAME}}.{{@TABLE_NAME}}'',0,0) WITH NOWAIT;
ALTER INDEX {{@INDEX_NAME}} ON {{@SCHEMA_NAME}}.{{@TABLE_NAME}} REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = NONE , MAXDOP = 1);
';
SELECT @SQL_STR =
(
SELECT
REPLACE(
REPLACE(
REPLACE(
REPLACE( @DECOMPRESS_STR,N'{{@TABLE_NAME}}' ,QUOTENAME(ST.name))
,N'{{@SCHEMA_NAME}}',QUOTENAME(OBJECT_SCHEMA_NAME(ST.object_id)))
,N'{{@INDEX_NAME}}' ,QUOTENAME(SI.name))
,N'{{@DB_NAME}}' ,QUOTENAME(DB_NAME(DB_ID()))
)
FROM sys.tables ST
INNER JOIN sys.indexes SI
ON ST.object_id = SI.object_id
WHERE ST.type = 'U'
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
IF (@EXEC <> 1)
BEGIN
SELECT @SQL_STR;
END
ELSE
BEGIN
EXEC ( @SQL_STR );
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply