Test-DbaDbCompression

Want to see the source code for this command? Check out Test-DbaDbCompression on GitHub.
Want to see the Bill Of Health for this command? Check out Test-DbaDbCompression.

Synopsis

Returns tables and indexes with preferred compression setting.

Description

This function returns the results of a full table/index compression analysis and the estimated, best option to date for either NONE, Page, or Row Compression.

Remember Uptime is critical, the longer uptime, the more accurate the analysis is, and it would be best if you utilized Get-DbaUptime first, before running this command.

Test-DbaDbCompression script derived from GitHub and the Tiger Team's repository: (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.

This script will execute on the context of the current database.
Also be aware that this may take a while to execute on large objects, because if the IS locks taken by the
sp_estimate_data_compression_savings cannot be honored, the SP will be blocked.
It only considers Row or Page Compression (not column compression)
It only evaluates User Tables

Syntax

Test-DbaDbCompression
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [[-Schema] <String[]>]
    [[-Table] <String[]>]
    [[-ResultSize] <Int32>]
    [[-Rank] <String>]
    [[-FilterBy] <String>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Test-DbaDbCompression -SqlInstance localhost

Returns results of all potential compression options for all databases for the default instance on the local host. Returns a recommendation of either Page, Row or NO_GAIN

Example: 2

PS C:\> Test-DbaDbCompression -SqlInstance ServerA

Returns results of all potential compression options for all databases on the instance ServerA

Example: 3

PS C:\> Test-DbaDbCompression -SqlInstance ServerA -Database DBName | Out-GridView

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

Example: 4

PS C:\> $cred = Get-Credential sqladmin
PS C:\> Test-DbaDbCompression -SqlInstance ServerA -ExcludeDatabase MyDatabase -SqlCredential $cred

Returns results of all potential compression options for all databases except MyDatabase on instance ServerA using SQL credentials to authentication to ServerA.
Returns the recommendation of either Page, Row or NO_GAIN

Example: 5

PS C:\> Test-DbaDbCompression -SqlInstance ServerA -Schema Test -Table MyTable

Returns results of all potential compression options for the Table Test.MyTable in instance ServerA on ServerA and ServerB.
Returns the recommendation of either Page, Row or NO_GAIN.
Returns a result for each partition of any Heap, Clustered or NonClustered index.

Example: 6

PS C:\> Test-DbaDbCompression -SqlInstance ServerA, ServerB -ResultSize 10

Returns results of all potential compression options for all databases on ServerA and ServerB.
Returns the recommendation of either Page, Row or NO_GAIN.
Returns results for the top 10 partitions by TotalPages used per database.

Example: 7

PS C:\> ServerA | Test-DbaDbCompression -Schema Test -ResultSize 10 -Rank UsedPages -FilterBy Table

Returns results of all potential compression options for all databases on ServerA containing a schema Test
Returns results for the top 10 Tables by Used Pages per database.
Results are split by Table, Index and Partition so more than 10 results may be returned.

Example: 8

PS C:\> $servers = 'Server1','Server2'
PS C:\> $servers | Test-DbaDbCompression -Database DBName | Out-GridView

Returns results of all potential compression options for a single database DBName on Server1 or Server2
Returns the recommendation of either Page, Row or NO_GAIN in a nicely formatted GridView

Example: 9

PS C:\> $cred = Get-Credential sqladmin
PS C:\> Test-DbaDbCompression -SqlInstance ServerA -Database MyDB -SqlCredential $cred -Schema Test -Table Test1, Test2

Returns results of all potential compression options for objects in Database MyDb on instance ServerA using SQL credentials to authentication to ServerA.
Returns the recommendation of either Page, Row or NO_GAIN for tables with Schema Test and name in Test1 or Test2

Example: 10

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

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

Required Parameters

-SqlInstance

The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will be processed.

-ExcludeDatabase

The database(s) to exclude - this list is auto-populated from the server

-Schema

Filter to only get specific schemas If unspecified, all schemas will be processed.

-Table

Filter to only get specific tables If unspecified, all User tables will be processed.

-ResultSize

Allows you to limit the number of results returned, as some systems can have very large number of tables. Default value is no restriction.

-Rank

Allows you to specify the field used for ranking when determining the ResultSize
Can be either TotalPages, UsedPages or TotalRows with default of TotalPages. Only applies when ResultSize is used.

-FilterBy

Allows you to specify level of filtering when determining the ResultSize
Can be at either Table, Index or Partition level with default of Partition. Only applies when ResultSize is used.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.