dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0



This function returns the results of a full table/index compression analysis.

This function returns the best option to date for either NONE, Page, or Row Compression.
Remember Uptime is critical, the longer uptime, the more accurate the analysis is. You would probably be best if you utilized Get-DbaUptime first, before running this command. Test-DbaCompression script derived from GitHub and the tiger toolbox (https://github.com/Microsoft/tigertoolbox/tree/master/Evaluate-Compression-Gains)

In the output, you will find the following information:
Column Percent_Update shows the percentage of update operations on a specific table, index, or partition, relative to total operations on that object. The lower the percentage of Updates (that is, the table, index, or partition is infrequently updated), the better candidate it is for page compression.
Column Percent_Scan shows the percentage of scan operations on a table, index, or partition, relative to total operations on that object. The higher the value of Scan (that is, the table, index, or partition is mostly scanned), the better candidate it is for page compression.
Column Compression_Type_Recommendation can have four possible outputs indicating where there is most gain, if any: ‘PAGE’, ‘ROW’, ‘NO_GAIN’ or ‘?’. When the output is ‘?’ this approach could not give a recommendation, so as a rule of thumb I would lean to ROW if the object suffers mainly UPDATES, or PAGE if mainly INSERTS, but this is where knowing your workload is essential. When the output is ‘NO_GAIN’ well, that means that according to sp_estimate_data_compression_savings no space gains will be attained when compressing, as in the above output example, where compressing would grow the affected object.

Note: Note that this script will execute in the context of the current database. Also be aware that this may take awhile to execute on large objects because if the IS locks taken by the sp_estimate_data_compression_savings cannot be honoured, the SP will be blocked.

Screenshots

dbatools - Test-DbaDbCompression

Examples

Returns all user database files and free space information for the local host

Test-DbaDbCompression -SqlInstance localhost

Returns results of all potential compression options for a single database with the recommendation of either Page or Row into and nicely formatted GridView

Test-DbaDbCompression -SqlInstance ServerA -Database DBName | Out-GridView

Returns results of all potential compression options for all databases with the recommendation of either Page or Row

Test-DbaDbCompression -SqlInstance ServerA

Returns results of all potential compression options for all databases with the recommendation of either Page or Row

$cred = Get-Credential sqladmin
Test-DbaDbCompression -SqlInstance ServerA -ExcludeDatabase Database -SqlCredential $cred

This produces a full analysis of all your servers listed and is pushed to a CSV for you to analyze.

$servers = ‘Server1′,’Server2’
foreach ($svr in $servers)
{
Test-DbaDbCompression -SqlInstance $svr | Export-Csv -Path C:\temp\CompressionAnalysisPAC.csv -Append
}

Author

This command was created by Jason Squires. You can find Jason on Twitter.

Get-Help

From PowerShell, execute Get-Help Test-DbaDbCompression -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaDbCompression.ps1 on GitHub
 

Related commands