When our database size was too large, we need to Compress or shrink data to reduce the disk/file/database size. I will provide some guidance to reduce the database/index size to the minimal which ensure the high availability of disk space.
I will discuss below points to Compress as much as possible ...
Related Articles
- Find the Tables need to Compress
- Estimate the Compression rate/size.
- Compress the Table Data in Page Level. (it can be in ROW level)
- Compress the Index size in Page Level. (it can be in ROW level)
- Compress/Shrink full database.
NOTE: If any Table has Sparse Column, we can't compress that table/index directly.
1. First of all, we have to find the affected/large in size Tables. Use below SQL to Find the large Tables.
----------------------------------------------------
-- Determine which tables to compress
SELECT
t.NAME AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
-- i.index_id
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
object_name(i.object_id)
----------------------------------------------------
2. After getting the table and index list; Now we have to estimate the size compression, how much size we can reduce.
Use below SQL to check the reduced size.
----------------------------------------------------
-- For Table Data Compression
EXEC sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'DATABASE_NAME',
@index_id = NULL, -- For Table Data Compression
@partition_number = NULL,
@data_compression = 'Page' ;
-- For Index Data Compression
EXEC sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'DATABASE_NAME',
@index_id = 1, -- Index ID -- For Index Data Compression
@partition_number = NULL,
@data_compression = 'Page' ;
----------------------------------------------------
3. After making the final list of Table, we can now compress the table using below SQL
----------------------------------------------------
ALTER TABLE dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
----------------------------------------------------
4. After Table Compression, we now can compress the Indexes (one by one) ..
----------------------------------------------------
ALTER INDEX INDEX_NAME ON dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
----------------------------------------------------
5. Finally we can Shrink/Compress the full database .. use below SQL ..
Bellow command have some other parameters, for details please check DBCC SHRINKDATABASE (Transact-SQL)
----------------------------------------------------
DBCC SHRINKDATABASE(N'DATABASE_NAME');
----------------------------------------------------
NOTE: You may use the procedure sp_spaceused to track the data size
all above is the example which I did for a database of 1 TB. You can choose your own compression strategy to compress the database or files.
Use below SQL to check the reduced size.
----------------------------------------------------
-- For Table Data Compression
EXEC sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'DATABASE_NAME',
@index_id = NULL, -- For Table Data Compression
@partition_number = NULL,
@data_compression = 'Page' ;
-- For Index Data Compression
EXEC sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'DATABASE_NAME',
@index_id = 1, -- Index ID -- For Index Data Compression
@partition_number = NULL,
@data_compression = 'Page' ;
----------------------------------------------------
3. After making the final list of Table, we can now compress the table using below SQL
----------------------------------------------------
ALTER TABLE dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
----------------------------------------------------
4. After Table Compression, we now can compress the Indexes (one by one) ..
----------------------------------------------------
ALTER INDEX INDEX_NAME ON dbo.TABLE_NAME REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
----------------------------------------------------
5. Finally we can Shrink/Compress the full database .. use below SQL ..
Bellow command have some other parameters, for details please check DBCC SHRINKDATABASE (Transact-SQL)
----------------------------------------------------
DBCC SHRINKDATABASE(N'DATABASE_NAME');
----------------------------------------------------
NOTE: You may use the procedure sp_spaceused to track the data size
all above is the example which I did for a database of 1 TB. You can choose your own compression strategy to compress the database or files.
