Script to review non-default SQL configuration settings (sp_configure)

Introduction

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.

Scenarios

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 http://support.microsoft.com/kb/319942 (How to determine proper SQL Server configuration settings).

Script

Just open a new query window using SQL Server Management Studio. Then paste this script and hit the Execute button.

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL

Edit|Remove

SELECT N'Displaying non-default Sql configuration (sp_configure) values:';
SELECT N'Disclaimer: Non-default values are just for information and are for troubleshooting reference.';
SELECT sc.name, sc.value_in_use, DF.Value AS DefaultValue
FROM @tvDefaultValues DF JOIN sys.configurations sc
ON sc.name LIKE '%' + DF.ConfigurationOption + '%' AND DF.Value <> sc.value_in_use
WHERE sc.name <> 'show advanced options' ORDER BY sc.name

SELECT N'Displaying non-default Sql configuration (sp_configure) values:'; 
SELECT N'Disclaimer: Non-default values are just for information and are for troubleshooting reference.'; 
SELECT sc.name, sc.value_in_use, DF.Value AS DefaultValue 
FROM @tvDefaultValues DF JOIN sys.configurations sc 
ON sc.name LIKE '%' + DF.ConfigurationOption + '%' AND DF.Value <> sc.value_in_use 
WHERE sc.name <> 'show advanced options' ORDER BY sc.name

Script to review non-default SQL configuration settings (sp_configure)

Introduction

Scenarios

Script

Additional Resources