SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Compression estimates

By Evgeny Garaev,

The script executes the procedure 'sp_estimate_data_compression_savings' for each physical object in the database for which the page compression has not been implemented. It shows the result in the tabular form ordered by the size of the object in descending order.

Just execute the script against the user database on which you are planning to implement compression. Bare in mind that the compression is available only on certain editions of MS SQL Server - https://docs.microsoft.com/en-nz/sql/sql-server/editions-and-components-of-sql-server-2016.

I also found that on some databases the procedure 'sp_estimate_data_compression_savings' causes deadlocks and only way to fix that is set instance wide MAXDOP option to 1 in order to prevent parallel executions.

Total article views: 250 | Views in the last 30 days: 5
 
Related Articles
FORUM

Constraint Execution Order

Constraint Execution Order

SCRIPT

Compress All Objects

Creates compression scripts for all objects in all databases in the instance.

FORUM

order of SQL execution

order of SQL execution

FORUM

query execution order

query execution order

BLOG

Data compression

This post examines the compression of a database named Bankware (BW) at a client site. The database ...

 
Contribute