[Script of Feb. 5] Script to review non-default SQL configuration settings (sp_configure)

  • Article

Script Download: NonDefaultSpConfigure.zip
https://gallery.technet.microsoft.com/scriptcenter/Script-to-review-non-d5c1a6ae

This is a frequent troubleshooting requirement for individual sp_configure values during most SQL performance troubleshooting. We've hundreds of known cases which have this settings check as among first step of SQL performance troubleshooting.

Quickly checking sp_configure values is a frequently asked question by IT administrators in the TechNet forums.  IT professionals want to validate the non-default SQL Server configuration values so as to quickly identify these values using T-SQL scripts, for example,

  • Encountering a performance issue.
  • Validating SQL instance configuration settings.
  • Running same script across multiple versions (SQL Server 2005/2008/2008R2/2012)
  • When automating this collection across multiple server using scripts
  • Checking Sql instance configuration settings with respect to https://support.microsoft.com/kb/319942 (How to determine proper SQL Server configuration settings).
image image
image

You can find more All-In-One Script Framework script samples at https://aka.ms/onescriptingallery