< Back
Test-DbaOptimizeForAdHoc
Post
NAME Test-DbaOptimizeForAdHoc
SYNOPSIS
Displays information relating to SQL Server Optimize for AdHoc Workloads setting. Works on SQL Server 2008-2016.
SYNTAX
Test-DbaOptimizeForAdHoc [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
When this option is set, plan cache size is further reduced for single-use ad hoc OLTP workload.
More info: https://msdn.microsoft.com/en-us/library/cc645587.aspx
http://www.sqlservercentral.com/blogs/g ... nfiguratio
n-settings/
These are just general recommendations for SQL Server and are a good starting point for setting the "optimize for
ad-hoc workloads" option.
PARAMETERS
-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
-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[]>]
A collection of one or more SQL Server instance names to query.
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: Configure, SPConfigure
Author: Brandon Abshire, netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Test-DbaOptimizeForAdHoc -SqlInstance sql2008, sqlserver2012
Validates whether Optimize for AdHoc Workloads setting is enabled for servers sql2008 and sqlserver2012.
RELATED LINKS
https://dbatools.io/Test-DbaOptimizeForAdHoc
SYNOPSIS
Displays information relating to SQL Server Optimize for AdHoc Workloads setting. Works on SQL Server 2008-2016.
SYNTAX
Test-DbaOptimizeForAdHoc [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
When this option is set, plan cache size is further reduced for single-use ad hoc OLTP workload.
More info: https://msdn.microsoft.com/en-us/library/cc645587.aspx
http://www.sqlservercentral.com/blogs/g ... nfiguratio
n-settings/
These are just general recommendations for SQL Server and are a good starting point for setting the "optimize for
ad-hoc workloads" option.
PARAMETERS
-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
-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[]>]
A collection of one or more SQL Server instance names to query.
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: Configure, SPConfigure
Author: Brandon Abshire, netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Test-DbaOptimizeForAdHoc -SqlInstance sql2008, sqlserver2012
Validates whether Optimize for AdHoc Workloads setting is enabled for servers sql2008 and sqlserver2012.
RELATED LINKS
https://dbatools.io/Test-DbaOptimizeForAdHoc