SelectEtoile : Base de connaissance SGBD

Posts Tagged 'compression'

Exemple de script pour estimer la compression

SET NOCOUNT ON;

DECLARE @tableName VARCHAR(256)

, @schemaName VARCHAR(100)

, @sqlStatementRow NVARCHAR(1000)

, @sqlStatementPage NVARCHAR(1000)

, @tableCount INT

, @statusMsg VARCHAR(1000);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#tables%')

DROP TABLE #tables;

CREATE TABLE #tables

(

database_name sysname

, schemaName sysname NULL

, tableName sysname NULL

, processed bit

);

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#compressionPage%')

BEGIN

DROP TABLE #compressionPage;

DROP TABLE #compressionRow;

END

IF NOT EXISTS(SELECT * FROM tempdb.sys.tables WHERE name LIKE '#compressionPage%')

BEGIN

CREATE TABLE #compressionPage

(

objectName varchar(100)

, schemaName varchar(50)

, index_id int

, partition_number int

, size_current_compression bigint

, size_requested_compression bigint

, sample_current_compression bigint

, sample_requested_compression bigint

);

CREATE TABLE #compressionRow

(

objectName varchar(100)

, schemaName varchar(50)

, index_id int

, partition_number int

, size_current_compression bigint

, size_requested_compression bigint

, sample_current_compression bigint

, sample_requested_compression bigint

);

END;

INSERT INTO #tables

SELECT DB_NAME()

, SCHEMA_NAME([schema_id])

, name

, 0 -- unprocessed

FROM sys.tables;;

SELECT @tableCount = COUNT(*) FROM #tables;

WHILE EXISTS(SELECT * FROM #tables WHERE processed = 0)

BEGIN

SELECT TOP 1 @tableName = tableName

, @schemaName = schemaName

FROM #tables WHERE processed = 0;

SELECT @statusMsg = 'Working on ' + CAST(((@tableCount - COUNT(*)) + 1) AS VARCHAR(10))

+ ' of ' + CAST(@tableCount AS VARCHAR(10))

FROM #tables

WHERE processed = 0;

RAISERROR(@statusMsg, 0, 42) WITH NOWAIT;

SET @sqlStatementRow = 'EXECUTE sp_estimate_data_compression_savings '''

+ @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''ROW'';'

SET @sqlStatementPage = 'EXECUTE sp_estimate_data_compression_savings '''

+ @schemaName + ''', ''' + @tableName + ''', NULL, NULL, ''PAGE'';'

INSERT INTO #compressionRow

EXECUTE sp_executesql @sqlStatementRow;

INSERT INTO #compressionPage

EXECUTE sp_executesql @sqlStatementPage;

UPDATE #tables

SET processed = 1

WHERE tableName = @tableName

AND schemaName = @schemaName;

END;

SELECT objectName

--, index_id

--, partition_number

, sum(size_current_compression/1024/1024) as CurentSize_GB

, sum( size_requested_compression/1024/1024) as EstimageSize_GB

, 100-100* sum( size_requested_compression/1024/1024)/sum(size_current_compression/1024/1024) as '%'

, 'ROW'

FROM #compressionRow

GROUP BY objectName

UNION

SELECT objectName

--, index_id

--, partition_number

, sum(size_current_compression/1024/1024) as CurentSize_GB

, sum( size_requested_compression/1024/1024) as EstimageCompression_GB

, 100-100* sum( size_requested_compression/1024/1024)/sum(size_current_compression/1024/1024) as '%'

, 'PAGE'

FROM #compressionPage

GROUP BY objectName

ORDER BY objectName