SPConfigure

Copy-DbaSpConfigure

Chrissy LeMaire (@cl), netnerds.net

Copy-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Copies SQL Server configuration settings (sp_configure values) from source to destination instances. Description This function retrieves all sp_configure settings from the source SQL Server and applies them to one or more destination instances, ensuring consistent configuration across your environment. Only settings that differ between source and destination are updated, making it safe for standardizing existing servers. The function automatically handles settings that require a restart and provides detailed reporting of which configurations were changed, skipped, or failed.

Set-DbaSpConfigure

Nic Cain, sirsql.net

Set-DbaSpConfigure View Source Nic Cain, sirsql.net Windows, Linux, macOS Synopsis Modifies SQL Server instance-level configuration settings through sp_configure Description This function safely modifies SQL Server instance-level configuration values that are normally changed through sp_configure. Use this when you need to adjust settings like max memory, xp_cmdshell, cost threshold for parallelism, or any other server configuration option. For dynamic settings, changes take effect immediately. For static settings, you’ll receive a warning that SQL Server must be restarted before the new value becomes active.

Test-DbaOptimizeForAdHoc

Brandon Abshire, netnerds.net

Test-DbaOptimizeForAdHoc View Source Brandon Abshire, netnerds.net Windows, Linux, macOS Synopsis Tests whether the SQL Server “optimize for ad-hoc workloads” configuration setting is enabled. Description Checks the current value of the “optimize for ad-hoc workloads” server configuration option and compares it against the recommended setting of 1 (enabled). This setting helps prevent plan cache bloat by storing only compiled plan stubs for single-use ad hoc queries instead of full execution plans.