Home > Data Compression, Handy Scripts > Check all database tables sizes and their Data Compression estimates

Check all database tables sizes and their Data Compression estimates

This script comes very useful to run before making any decision and taking actions on Data Compression

DECLARE @tv_table_size TABLE (table_name SYSNAME
, num_rows INT
, reserved_size VARCHAR(20)
,
data_size VARCHAR(20)
,
index_size VARCHAR(20)
,
unused VARCHAR(20)
);
DECLARE @tv_compression_estimate_PAGE TABLE ([object_name] SYSNAME
, [schema_name] SYSNAME
, index_id tinyint
, partition_number tinyint
, size_with_current_compression_setting_KB INT
, size_with_requested_compression_setting_KB INT
, sample_size_with_current_compression_setting_KB INT
, sample_size_with_requested_compression_setting_KB INT
);
DECLARE @tv_compression_estimate_ROW TABLE ([object_name] SYSNAME
, [schema_name] SYSNAME
, index_id tinyint
, partition_number tinyint
, size_with_current_compression_setting_KB INT
, size_with_requested_compression_setting_KB INT
, sample_size_with_current_compression_setting_KB INT
, sample_size_with_requested_compression_setting_KB INT
);
DECLARE @v_sql NVARCHAR(500);
DECLARE cur CURSOR FOR
SELECT
s.name, t.name
FROM sys.schemas s INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name
DECLARE @cv_schema_name SYSNAME, @cv_table_name SYSNAME
OPEN
cur
FETCH cur INTO @cv_schema_name, @cv_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@v_sql = @cv_schema_name + '.' + @cv_table_name;
INSERT INTO @tv_table_size EXEC sp_spaceused @v_sql;

SET @v_sql = N’execute sp_estimate_data_compression_savings ”’ + @cv_schema_name + ”’, ”’ + @cv_table_name + ”’, NULL, NULL, ”PAGE”’;
INSERT INTO @tv_compression_estimate_PAGE EXEC sp_executesql @v_sql

SET @v_sql = N’execute sp_estimate_data_compression_savings ”’ + @cv_schema_name + ”’, ”’ + @cv_table_name + ”’, NULL, NULL, ”ROW”’;
INSERT INTO @tv_compression_estimate_ROW EXEC sp_executesql @v_sql
FETCH cur INTO @cv_schema_name, @cv_table_name
END
CLOSE
cur
DEALLOCATE cur
SELECT s.name AS [schema]
, t.name AS table_name
, CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024 AS table_size_Mb
—— PAGE ——–
, cep.size_with_requested_compression_setting_KB/1024 AS PAGE_compression_size_Mb
, (cep.size_with_requested_compression_setting_KB/1024) * 100
/
(
CASE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
WHEN 0
THEN 1
ELSE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
END) AS ratio_from_original_size_PAGE_pct
, 100 – (cep.size_with_requested_compression_setting_KB/1024) * 100
/
(
CASE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
WHEN 0
THEN 1
ELSE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
END) AS space_saving_PAGE_pct
——————-
—— ROW ——–
, cer.size_with_requested_compression_setting_KB/1024 AS ROW_compression_size_Mb
, (cer.size_with_requested_compression_setting_KB/1024) * 100
/
(
CASE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
WHEN 0
THEN 1
ELSE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
END) AS ratio_from_original_size_ROW_pct
, 100 – (cer.size_with_requested_compression_setting_KB/1024) * 100
/
(
CASE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
WHEN 0
THEN 1
ELSE CONVERT(INT, REPLACE(data_size, ‘ KB’, ))/1024
END) AS space_saving_ROW_pct
——————-
FROM @tv_table_size tv
INNER JOIN sys.tables t ON tv.table_name = t.name
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN @tv_compression_estimate_PAGE cep ON s.name = cep.schema_name AND t.name = cep.OBJECT_NAME
INNER JOIN @tv_compression_estimate_ROW cer ON s.name = cer.schema_name AND t.name = cer.OBJECT_NAME
ORDER BY CONVERT(INT, REPLACE(data_size, ‘ KB’, )) DESC, s.name, t.name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: