< Back
Get-DbaPlanCache
Post
NAME Get-DbaPlanCache
SYNOPSIS
Provides information about adhoc and prepared plan cache usage
SYNTAX
Get-DbaPlanCache [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Checks adhoc and prepared plan cache for each database, if over 100 MB you should consider using
Remove-DbaQueryPlan to clear the plan caches or turning on "optimize for adhoc workloads" configuration if running
2008 or later.
References:
https://www.sqlskills.com/blogs/kimberl ... che-bloat/
Note: This command returns results from all SQL server instances on the destination server but the process column
is specific to -SqlInstance passed.
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[]>]
The target SQL Server instance or 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: Memory
Author: Tracy Boggiano, databasesuperhero.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaPlanCache -SqlInstance sql2017
Returns the single use plan cache usage information for SQL Server instance 2017
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaPlanCache -SqlInstance sql2017 -SqlCredential sqladmin
Returns the single use plan cache usage information for SQL Server instance 2017 using login 'sqladmin'
RELATED LINKS
https://dbatools.io/Get-DbaPlanCache
SYNOPSIS
Provides information about adhoc and prepared plan cache usage
SYNTAX
Get-DbaPlanCache [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [[-SqlCredential]
<Pscredential>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
Checks adhoc and prepared plan cache for each database, if over 100 MB you should consider using
Remove-DbaQueryPlan to clear the plan caches or turning on "optimize for adhoc workloads" configuration if running
2008 or later.
References:
https://www.sqlskills.com/blogs/kimberl ... che-bloat/
Note: This command returns results from all SQL server instances on the destination server but the process column
is specific to -SqlInstance passed.
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[]>]
The target SQL Server instance or 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: Memory
Author: Tracy Boggiano, databasesuperhero.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaPlanCache -SqlInstance sql2017
Returns the single use plan cache usage information for SQL Server instance 2017
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaPlanCache -SqlInstance sql2017 -SqlCredential sqladmin
Returns the single use plan cache usage information for SQL Server instance 2017 using login 'sqladmin'
RELATED LINKS
https://dbatools.io/Get-DbaPlanCache