Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2016-10-24   |   Comments (1)   |   Related Tips: More > SQL Server Configurations

Problem

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.

Solution

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
GO

CREATE PROCEDURE usp_ReportNonDefaultConfigValues
AS
BEGIN
 SET NOCOUNT ON

 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 (
   FIELDTERMINATOR = ','
   ,ROWTERMINATOR = '\n'
   )

 SELECT CONF.NAME
  ,CONF.value_in_use
  ,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'
 ORDER BY CONF.NAME

 DROP TABLE #DefaultConfig

 SET NOCOUNT OFF
END
GO

Example Run

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

USE master
GO
EXEC usp_ReportNonDefaultConfigValues
GO

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.


Last Updated: 2016-10-24


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 


Learn more about SQL Server tools