Wednesday, August 29, 2018

Compress/Shrink SQL Server Database/Table/Index, tSql

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 ...
  1. Find the Tables need to Compress
  2. Estimate the Compression rate/size.
  3. Compress the Table Data in Page Level. (it can be in ROW level)
  4. Compress the Index size in Page Level. (it can be in ROW level)
  5. 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 <= 1 
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.

No comments:

Post a Comment