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.

Check INDEX Status and REBUILD Index, SQL Server

When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.

In my example, I will show how to get the fragmented indexes which have above 20% fragmentation level.

INDEX STATUS check

------------------------------------------

SELECT dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count      

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

------------------------------------------

Now using below SQL, I will create the REBUILD command to Defragment the infected indexes...

SQL to generate Rebuild Command

------------------------------------------

SELECT 

'ALTER INDEX '+dbindexes.[name]+' on '+dbschemas.[name]+'.'+ dbtables.[name]+' REBUILD;',

dbschemas.[name]  AS 'SchemaName',

       dbtables.[name]          AS 'TableName',

       dbindexes.[name]         AS 'IndexName',

       ROUND(indexstats.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent,

       indexstats.page_count

FROM   sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

       INNER JOIN sys.tables dbtables

            ON  dbtables.[object_id] = indexstats.[object_id]

       INNER JOIN sys.schemas dbschemas

            ON  dbtables.[schema_id] = dbschemas.[schema_id]

       INNER JOIN sys.indexes   AS dbindexes

            ON  dbindexes.[object_id] = indexstats.[object_id]

            AND indexstats.index_id = dbindexes.index_id

WHERE  indexstats.database_id = DB_ID()

       AND dbindexes.type_desc = 'NONCLUSTERED'

       AND dbindexes.[name] IS NOT NULL

       AND indexstats.avg_fragmentation_in_percent > 20

ORDER BY

       ROUND(indexstats.avg_fragmentation_in_percent,2) DESC

------------------------------------------