Snippet Name: SQL_Server_Compression_Estimates
Tags:
Created Date: 2025-06-16 13:12:50
Last Modified Date: 2025-05-31 10:32:01
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Server Compression Estimates
Author: user
for sql server, how can i get compression estimates for every table, into a single table for review?
Author: ChatGPT
To get compression estimates for every table in a SQL Server database, you can use the `sp_estimate_data_compression_savings` stored procedure. This procedure estimates the amount of space that can be saved by compressing a table or index using ROW or PAGE compression.
Here's a script to automate the process for all tables in a database and store the results in a review table:
### Step 1: Create a table to store the results
```sql
CREATE TABLE CompressionEstimates (
SchemaName NVARCHAR(128),
TableName NVARCHAR(128),
PartitionNumber INT,
CompressionType NVARCHAR(50),
SizeWithCurrentCompression DECIMAL(18, 2),
SizeWithRequestedCompression DECIMAL(18, 2),
SizeSavings DECIMAL(18, 2),
SavingsPercentage DECIMAL(18, 2),
CreateDate DATETIME DEFAULT GETDATE()
);
```
### Step 2: Use a script to gather the estimates
The script loops through all tables in the database, runs `sp_estimate_data_compression_savings`, and inserts the results into the `CompressionEstimates` table.
```sql
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @PartitionNumber INT;
DECLARE @SQL NVARCHAR(MAX);
-- Cursor to iterate over all tables
DECLARE TableCursor CURSOR FOR
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Generate SQL for compression estimation
SET @SQL =
'DECLARE @Result TABLE (
PartitionNumber INT,
SizeWithCurrentCompression DECIMAL(18, 2),
SizeWithRequestedCompression DECIMAL(18, 2)
);
INSERT INTO @Result
EXEC sp_estimate_data_compression_savings ''' + @SchemaName + ''', ''' + @TableName + ''', NULL, NULL, ''PAGE'';
INSERT INTO CompressionEstimates (SchemaName, TableName, PartitionNumber, CompressionType,
SizeWithCurrentCompression, SizeWithRequestedCompression,
SizeSavings, SavingsPercentage)
SELECT
''' + @SchemaName + ''', ''' + @TableName + ''',
PartitionNumber, ''PAGE'',
SizeWithCurrentCompression,
SizeWithRequestedCompression,
SizeWithCurrentCompression - SizeWithRequestedCompression,
CASE WHEN SizeWithCurrentCompression > 0 THEN
((SizeWithCurrentCompression - SizeWithRequestedCompression) * 100.0) / SizeWithCurrentCompression
ELSE
0
END
FROM
@Result;';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;
-- Move to the next table
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
```
### Explanation:
1. **Cursor**: Iterates through all user tables in the database.
2. **Dynamic SQL**: Executes `sp_estimate_data_compression_savings` for each table.
3. **Results Table**: Stores the compression estimate results along with calculated size savings and percentage savings.
4. **Requested Compression**: The script uses `PAGE` compression; you can change this to `ROW` or another type if desired.
### Step 3: Review the results
Once the script completes, query the `CompressionEstimates` table to review the compression savings.
```sql
SELECT *
FROM CompressionEstimates
ORDER BY SavingsPercentage DESC;
```
This approach provides a comprehensive view of compression savings for all tables in a database.
Author: user
i got teh error Msg 213, Level 16, State 7, Procedure sp_estimate_data_compression_savings, Line 570 [Batch Start Line 8]
Column name or number of supplied values does not match table definition.
Author: ChatGPT
This error typically occurs when the structure of the temporary table used to store the output of `sp_estimate_data_compression_savings` does not match the expected output columns. The stored procedure `sp_estimate_data_compression_savings` returns a fixed set of columns, so ensuring alignment between the procedure's output and the `@Result` table structure is critical.
Here???s an updated script that resolves this issue:
### Fix the Output Table Structure
The correct output structure of `sp_estimate_data_compression_savings` includes these columns:
- `object_id` (INT)
- `index_id` (INT)
- `partition_number` (INT)
- `size_with_current_compression_setting` (BIGINT)
- `size_with_requested_compression_setting` (BIGINT)
- `sample_size_with_current_compression_setting` (BIGINT)
- `sample_size_with_requested_compression_setting` (BIGINT)
Modify the script to correctly define the temporary table for capturing results.
### Updated Script
```sql
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
-- Cursor to iterate over all tables
DECLARE TableCursor CURSOR FOR
SELECT
s.name AS SchemaName,
t.name AS TableName
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.is_ms_shipped = 0;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Dynamic SQL for compression estimation
SET @SQL =
'DECLARE @Result TABLE (
object_id INT,
index_id INT,
partition_number INT,
size_with_current_compression_setting BIGINT,
size_with_requested_compression_setting BIGINT,
sample_size_with_current_compression_setting BIGINT,
sample_size_with_requested_compression_setting BIGINT
);
INSERT INTO @Result
EXEC sp_estimate_data_compression_savings ''' + @SchemaName + ''', ''' + @TableName + ''', NULL, NULL, ''PAGE'';
INSERT INTO CompressionEstimates (SchemaName, TableName, PartitionNumber, CompressionType,
SizeWithCurrentCompression, SizeWithRequestedCompression,
SizeSavings, SavingsPercentage)
SELECT
''' + @SchemaName + ''', ''' + @TableName + ''',
partition_number, ''PAGE'',
size_with_current_compression_setting / 1024.0 AS SizeWithCurrentCompression,
size_with_requested_compression_setting / 1024.0 AS SizeWithRequestedCompression,
(size_with_current_compression_setting - size_with_requested_compression_setting) / 1024.0 AS SizeSavings,
CASE
WHEN size_with_current_compression_setting > 0 THEN
((size_with_current_compression_setting - size_with_requested_compression_setting) * 100.0) / size_with_current_compression_setting
ELSE
0
END AS SavingsPercentage
FROM
@Result;';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL;
-- Move to the next table
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
```
### Changes Made:
1. **Correct Output Table**:
- Updated the temporary table `@Result` to match the exact structure returned by `sp_estimate_data_compression_savings`.
2. **Compression Size in KB**:
- Converted sizes from bytes to KB (`/ 1024.0`) for readability.
3. **Dynamic SQL Execution**:
- Incorporated proper alignment between the stored procedure output and the `CompressionEstimates` table.
### Step 3: Review the Results
Query the `CompressionEstimates` table to review the data.
```sql
SELECT *
FROM CompressionEstimates
ORDER BY SavingsPercentage DESC;
```
This script should now execute without errors and populate the `CompressionEstimates` table with the estimated compression savings for all tables.