By: Eli Leiba | Updated: 2016-10-24 | Comments (1) | Related: > 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.
About the author
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
Article Last Updated: 2016-10-24
