< Back
Test-DbaDbCompression
Post
NAME Test-DbaDbCompression
SYNOPSIS
Returns tables and indexes with preferred compression setting.
SYNTAX
Test-DbaDbCompression [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>]
[[-Schema] <System.String[]>] [[-Table] <System.String[]>] [[-ResultSize] <Int>] [[-Rank] <String>] [[-FilterBy]
<String>] [-EnableException <Switch>] [<CommonParameters>]
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/tigertoolb ... sion-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
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilterBy [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Rank [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ResultSize [<Int>]
Allows you to limit the number of results returned, as some systems can have very large number of tables.
Default value is no restriction.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Schema [<System.String[]>]
Filter to only get specific schemas If unspecified, all schemas will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Table [<System.String[]>]
Filter to only get specific tables If unspecified, all User tables will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
Accepts a DbaInstanceParameter. Any collection of SQL Server Instance names or SMO objects can be piped to command.
OUTPUTS
Returns a PsCustomObject with following fields: ComputerName, InstanceName, SqlInstance, Database, IndexName,
Partition, IndexID, PercentScan, PercentUpdate, RowEstimatePercentOriginal, PageEstimatePercentOriginal,
CompressionTypeRecommendation, SizeCurrent, SizeRequested, PercentCompression
NOTES
Tags: Compression, Table, Database
Author: Jason Squires (@js_0505), jstexasdba@gmail.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Test-DbaDbCompression
SYNOPSIS
Returns tables and indexes with preferred compression setting.
SYNTAX
Test-DbaDbCompression [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>]
[[-Schema] <System.String[]>] [[-Table] <System.String[]>] [[-ResultSize] <Int>] [[-Rank] <String>] [[-FilterBy]
<String>] [-EnableException <Switch>] [<CommonParameters>]
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/tigertoolb ... sion-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
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FilterBy [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Rank [<String>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ResultSize [<Int>]
Allows you to limit the number of results returned, as some systems can have very large number of tables.
Default value is no restriction.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Schema [<System.String[]>]
Filter to only get specific schemas If unspecified, all schemas will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Table [<System.String[]>]
Filter to only get specific tables If unspecified, all User tables will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
Accepts a DbaInstanceParameter. Any collection of SQL Server Instance names or SMO objects can be piped to command.
OUTPUTS
Returns a PsCustomObject with following fields: ComputerName, InstanceName, SqlInstance, Database, IndexName,
Partition, IndexID, PercentScan, PercentUpdate, RowEstimatePercentOriginal, PageEstimatePercentOriginal,
CompressionTypeRecommendation, SizeCurrent, SizeRequested, PercentCompression
NOTES
Tags: Compression, Table, Database
Author: Jason Squires (@js_0505), jstexasdba@gmail.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Test-DbaDbCompression