functions/Test-DbaOptimizeForAdHoc.ps1
|
Function Test-DbaOptimizeForAdHoc { <# .SYNOPSIS Displays information relating to SQL Server Optimize for AdHoc Workloads setting. Works on SQL Server 2008-2016. .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/glennberry/2011/02/25/some-suggested-sql-server-2008-r2-instance-configuration-settings/ These are just general recommendations for SQL Server and are a good starting point for setting the "optimize for adhoc workloads" option. .PARAMETER SqlInstance A collection of one or more SQL Server instance names to query. .PARAMETER SqlCredential Allows you to login to servers using SQL Logins instead of Windows Authentication (AKA Integrated or Trusted). To use: $cred = Get-Credential, this pass this $cred to the param. Windows Authentication will be used if SqlCredential is not specified. To connect as a different Windows user, run PowerShell as that user. .NOTES Author: Brandon Abshire, netnerds.net Website: https://dbatools.io Copyright: (C) Chrissy LeMaire, clemaire@gmail.com License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0 .LINK https://dbatools.io/Test-DbaOptimizeForAdHoc .EXAMPLE Test-DbaOptimizeForAdHoc -SqlInstance sql2008, sqlserver2012 Get Optimize for AdHoc Workloads setting for servers sql2008 and sqlserver2012 and also the recommended one. #> [CmdletBinding()] Param ( [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)] [Alias("ServerInstance", "SqlServer", "SqlServers")] [DbaInstanceParameter[]]$SqlInstance, [PSCredential]$SqlCredential ) BEGIN { $notesAdHocZero = "Recommended configuration is 1 (enabled)." $notesAsRecommended = "Configuration is already set as recommended." } PROCESS { foreach ($servername in $SqlInstance) { Write-Verbose "Attempting to connect to $servername" try { $server = Connect-SqlInstance -SqlInstance $servername -SqlCredential $SqlCredential } catch { Write-Warning "Can't connect to $servername or access denied. Skipping." continue } if ($server.versionMajor -lt 10) { Write-Warning "This function does not support versions lower than SQL Server 2008 (v10). Skipping server $servername." Continue } #Get current configured value $optimizeAdHoc = $server.Configuration.OptimizeAdhocWorkloads.ConfigValue #Setting notes for optimize adhoc value if ($optimizeAdHoc -eq 1) { $notes = $notesAsRecommended } else { $notes = $notesAdHocZero } [pscustomobject]@{ Instance = $server.Name CurrentOptimizeAdHoc = $optimizeAdHoc RecommendedOptimizeAdHoc = 1 Notes = $notes } } } } |