Quickly identify non-default sp_configure values for a SQL Server instance

By:   |   Comments (1)   |   Related: > SQL Server Configurations


You need a simple tool to detect any SQL Server configuration values that have been changed from their default value. This task is common when investigating SQL Server performance problems. Checking sp_configure for non-default options is a first step in SQL Server performance tuning, because there is the need to validate the configuration settings to make sure the same SQL scripts are being executed with the same configuration settings across SQL instances. In this tip we show a way that this can be done.


I chose a solution that creates a stored procedure called usp_ReportNonDefaultConfigValues in order to report all non-default configuration values. This script will work with the following versions of SQL Server 2005, 2008, 2008R2, 2012, 2014 and 2016.

There are several ways this can be setup, but the approach I took was using a comma delimited file to store the default values. A table could also be created to store the default values.

Create sp_configure Default Value File

The first step is to create a simple CSV file (using notepad or similar tool), with all the default sp_configure values. The values and name were taken from a new SQL installation by querying sys.configurations. This file was stored in the following folder C:\SQL\DefCfg.txt. Save the below in this file.

access check cache bucket count, 0
access check cache quota, 0
ad hoc distributed queries, 0
affinity I/O mask, 0
affinity64 I/O mask, 0
affinity mask, 0
affinity64 mask, 0
allow updates, 0
backup compression default, 0
blocked process threshold, 0
c2 audit mode, 0
clr enabled, 0
common criteria compliance enabled, 0
contained database authentication, 0
cost threshold for parallelism, 5
cross db ownership chaining, 0
cursor threshold, -1
Database Mail XPs, 0
default full-text language, 1033
default language, 0
default trace enabled, 1
disallow results from triggers, 0
EKM provider enabled, 0
filestream_access_level, 0
fill factor, 0
ft crawl bandwidth (max), 100
ft crawl bandwidth (min), 0
ft notify bandwidth (max), 100
ft notify bandwidth (min), 0
index create memory, 0
in-doubt xact resolution, 0
lightweight pooling, 0
locks, 0
max degree of parallelism, 0
max full-text crawl range, 4
max server memory, 2147483647 
max text repl size, 65536
max worker threads, 0
media retention, 0
min memory per query, 1024
min server memory, 0
nested triggers, 1
network packet size, 4096
Ole Automation Procedures, 0
open objects, 0
optimize for ad hoc workloads, 0
PH_timeout, 60
precompute rank, 0
priority boost, 0
query governor cost limit, 0
query wait, -1
recovery interval, 0
remote access, 1
remote admin connections, 0
remote login timeout, 10
remote proc trans, 0
remote query timeout, 600
Replication XPs Option, 0
scan for startup procs, 0
server trigger recursion, 1
set working set size, 0
show advanced options, 0
SMO and DMO XPs, 1
transform noise words, 0
two digit year cutoff, 2049
user connections, 0
user options, 0
xp_cmdshell, 0

Create Stored Procedure To Check for Differences

Create a stored procedure called usp_ReportNonDefaultConfigValues in the master database. The stored procedure creates a temporary table (#DefaultConfig) and populates it with the data from DefCfg.txt file using a BULK INSERT statement. It then joins the temporary table with the sys.configurations system view that holds the current configuration values. It only returns rows where the value_in_use does not equal the default value.

USE master

CREATE PROCEDURE usp_ReportNonDefaultConfigValues

 IF OBJECT_ID('#DefaultConfig') IS NOT NULL
  DROP TABLE #DefaultConfig

 CREATE TABLE #DefaultConfig (
  opt_name VARCHAR(60)
  ,opt_val SQL_VARIANT

 BULK INSERT #DefaultConfig
 FROM 'C:\SQL\DefCfg.txt' WITH (

  ,DEF.opt_val AS DefaultValue
 FROM #DefaultConfig DEF
  ,sys.configurations CONF
 WHERE rtrim(ltrim(lower(CONF.NAME))) = rtrim(ltrim(lower(DEF.opt_name)))
  AND rtrim(ltrim(convert(VARCHAR(10), DEF.opt_val))) != rtrim(ltrim(convert(VARCHAR(10), CONF.value_in_use)))
  AND CONF.NAME != 'show advanced options'

 DROP TABLE #DefaultConfig


Example Run

Here is an example using the stored procedure where values have been changed.

USE master
EXEC usp_ReportNonDefaultConfigValues

Here are the results from my server.

name                       value_in_use   DefaultValue
Ole Automation Procedures  1              0
xp_cmdshell                1              0
Next Steps
  • The procedure was tested with SQL Server 2012 and 2014 Developer editions.
  • Note that the sp_configure default options may change in later SQL Server versions, so keep this in mind and update values accordingly.
  • One difference for versions prior to SQL Server 2012 is that the 'remote login timeout' was 20 and not 10.
  • The procedure runs from the master database and needs permissions to do a BULK INSERT and to read values from the system view sys.configurations.
  • You could also create a file share and put the file DefCfg.txt in the file share. If the share is accessible from all of your SQL Servers you would only have one place to update values.
  • As noted above, another approach would be to create a table in the master database with the default values instead of using an external file.  You could make this part of your installation process when a new instance is setup.  Before any changes are made you can pull in the default values into the table. Using a table instead would require a minimal tweak to the above code.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, October 24, 2016 - 12:21:24 PM - Richard Vantrease Back To Top (43624)

 Curious why you chose to use a CSV instead of a table within SQL.  Could you explain a little why you made that choice?


get free sql tips
agree to terms