Data Compression

Corey Beck on data compression:

Before we jump right into enabling either row or page compression, we can actually estimate the savings of each to determine which will provide us with the most savings on storage.  Since page compression includes row compression, we will start with row compression and the estimated savings.

EXEC sp_estimate_data_compression_savings
‘Person’,’Person’,null,null,’row’

In practice, data compression is extremely valuable and in most circumstances, the benefits outweigh the costs.  In certain workloads, you might even see CPU usage go down.

Related Posts

Backup Compression And Encryption

Arun Sirpal shows the combined effects of backup encryption and backup compression in SQL Server 2017: Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t?  You will get the dreaded thumbprint error. […]

Read More

Finding Compressable Indexes

Tracy Boggiano has a script to help you figure out which indexes make sense to compress: We can write procedure check periodically rather a table will benefit from compression or not.  There are a few tricks though: If you have sparse columns you cannot compress the table, we check for that in lines 70-74. Next […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031