< Back
Set-DbaDbCompression
Post
NAME Set-DbaDbCompression
SYNOPSIS
Sets tables and indexes with preferred compression setting.
SYNTAX
Set-DbaDbCompression [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-CompressionType]
<System.Object>] [[-MaxRunTime] <Int>] [[-PercentCompression] <Int>] [[-InputObject] <System.Object>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
This function sets the appropriate compression recommendation, determined either by using the Tiger Team's query
or set to the CompressionType parameter.
Remember Uptime is critical for the Tiger Team query, the longer uptime, the more accurate the analysis is.
You would probably be best if you utilized Get-DbaUptime first, before running this command.
Set-DbaDbCompression script derived from GitHub and the tigertoolbox
(https://github.com/Microsoft/tigertoolb ... sion-Gains)
PARAMETERS
-CompressionType [<System.Object>]
Control the compression type applied. Default is 'Recommended' which uses the Tiger Team query to use the most
appropriate setting per object. Other option is to compress all objects to either Row or Page.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-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
-InputObject [<System.Object>]
Takes the output of Test-DbaDbCompression as an object and applied compression based on those recommendations.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxRunTime [<Int>]
Will continue to alter tables and indexes for the given amount of minutes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PercentCompression [<Int>]
Will only work on the tables/indexes that have the calculated savings at and higher for the given number
provided.
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
<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
OUTPUTS
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:\\>Set-DbaDbCompression -SqlInstance localhost -MaxRunTime 60 -PercentCompression 25
Set the compression run time to 60 minutes and will start the compression of tables/indexes that have a difference
of 25% or higher between current and recommended.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Set-DbaDbCompression -SqlInstance ServerA -Database DBName -CompressionType Page
Utilizes Page compression for all objects in DBName on ServerA with no time limit.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Set-DbaDbCompression -SqlInstance ServerA -Database DBName -PercentCompression 25 | Out-GridView
Will compress tables/indexes within the specified database that would show any % improvement with compression and
with no time limit. The results will be piped into a nicely formatted GridView.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$testCompression = Test-DbaDbCompression -SqlInstance ServerA -Database DBName
PS C:\\> Set-DbaDbCompression -SqlInstance ServerA -Database DBName -InputObject $testCompression
Gets the compression suggestions from Test-DbaDbCompression into a variable, this can then be reviewed and passed
into Set-DbaDbCompression.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>$cred = Get-Credential sqladmin
PS C:\\> Set-DbaDbCompression -SqlInstance ServerA -ExcludeDatabase Database -SqlCredential $cred -MaxRunTime 60
-PercentCompression 25
Set the compression run time to 60 minutes and will start the compression of tables/indexes for all databases
except the specified excluded database. Only objects that have a difference of 25% or higher between current and
recommended will be compressed.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>$servers = 'Server1','Server2'
PS C:\\> foreach ($svr in $servers) {
>> Set-DbaDbCompression -SqlInstance $svr -MaxRunTime 60 -PercentCompression 25 | Export-Csv -Path
C:\\temp\\CompressionAnalysisPAC.csv -Append
>> }
Set the compression run time to 60 minutes and will start the compression of tables/indexes across all listed
servers that have a difference of 25% or higher between current and recommended. Output of command is exported to
a csv.
RELATED LINKS
https://dbatools.io/Set-DbaDbCompression
SYNOPSIS
Sets tables and indexes with preferred compression setting.
SYNTAX
Set-DbaDbCompression [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-CompressionType]
<System.Object>] [[-MaxRunTime] <Int>] [[-PercentCompression] <Int>] [[-InputObject] <System.Object>]
[-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
This function sets the appropriate compression recommendation, determined either by using the Tiger Team's query
or set to the CompressionType parameter.
Remember Uptime is critical for the Tiger Team query, the longer uptime, the more accurate the analysis is.
You would probably be best if you utilized Get-DbaUptime first, before running this command.
Set-DbaDbCompression script derived from GitHub and the tigertoolbox
(https://github.com/Microsoft/tigertoolb ... sion-Gains)
PARAMETERS
-CompressionType [<System.Object>]
Control the compression type applied. Default is 'Recommended' which uses the Tiger Team query to use the most
appropriate setting per object. Other option is to compress all objects to either Row or Page.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-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
-InputObject [<System.Object>]
Takes the output of Test-DbaDbCompression as an object and applied compression based on those recommendations.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxRunTime [<Int>]
Will continue to alter tables and indexes for the given amount of minutes.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PercentCompression [<Int>]
Will only work on the tables/indexes that have the calculated savings at and higher for the given number
provided.
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
<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
OUTPUTS
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:\\>Set-DbaDbCompression -SqlInstance localhost -MaxRunTime 60 -PercentCompression 25
Set the compression run time to 60 minutes and will start the compression of tables/indexes that have a difference
of 25% or higher between current and recommended.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Set-DbaDbCompression -SqlInstance ServerA -Database DBName -CompressionType Page
Utilizes Page compression for all objects in DBName on ServerA with no time limit.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Set-DbaDbCompression -SqlInstance ServerA -Database DBName -PercentCompression 25 | Out-GridView
Will compress tables/indexes within the specified database that would show any % improvement with compression and
with no time limit. The results will be piped into a nicely formatted GridView.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$testCompression = Test-DbaDbCompression -SqlInstance ServerA -Database DBName
PS C:\\> Set-DbaDbCompression -SqlInstance ServerA -Database DBName -InputObject $testCompression
Gets the compression suggestions from Test-DbaDbCompression into a variable, this can then be reviewed and passed
into Set-DbaDbCompression.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>$cred = Get-Credential sqladmin
PS C:\\> Set-DbaDbCompression -SqlInstance ServerA -ExcludeDatabase Database -SqlCredential $cred -MaxRunTime 60
-PercentCompression 25
Set the compression run time to 60 minutes and will start the compression of tables/indexes for all databases
except the specified excluded database. Only objects that have a difference of 25% or higher between current and
recommended will be compressed.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>$servers = 'Server1','Server2'
PS C:\\> foreach ($svr in $servers) {
>> Set-DbaDbCompression -SqlInstance $svr -MaxRunTime 60 -PercentCompression 25 | Export-Csv -Path
C:\\temp\\CompressionAnalysisPAC.csv -Append
>> }
Set the compression run time to 60 minutes and will start the compression of tables/indexes across all listed
servers that have a difference of 25% or higher between current and recommended. Output of command is exported to
a csv.
RELATED LINKS
https://dbatools.io/Set-DbaDbCompression