Installing and Configuring SQL Server 2008: sp_configure

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

What is sp_configure?

Sp_configure is a system stored procedure used to display and modify information about the configuration of SQL Server. If you simply type sp_configure into a query window and run it you will get output that looks like the following table, which is a sample of the output of running this command on my developmental server.

name	minimum	maximum	config_value	run_value
access check cache bucket count	0	16384	0	0
access check cache quota	0	2147483647	0	0
Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
….
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	2147483647	2147483647
max text repl size (B)	-1	2147483647	65536	65536
max worker threads	128	32767	0	0
…

The full list can be found at the end of this post. The above should give you a good idea of the settings and values that can be seen and changed through sp_configure. Many DBAs who have become dependent on using GUI based tools like SSMS and Configuration Manager might be reluctant to use this tool even to view the settings. But the fact is it will always be faster to use a query than to do any of this through the GUI. While I can agree that GUI based tools add a feeling of security, we should not be afraid to challenge ourselves to grow as professionals and true mastery only comes from being able to perform tasks accurately and in the most efficient manner possible. That being said, do not attempt to make changes to production servers using this or any other tool without a complete understanding of the settings you are changing. Be sure to consult BoL 2008 for a thorough description of what the settings do.

How to Use sp_configure

Sp_configure works like any other stored procedure. You use EXEC the procedure name and then the name of the option you wish to change followed by the value. The full syntax can be found here. Here is an example.


/***
 * This code will run sp_configure and change the 'Show Advanced Options' vslue to 1;
 * which turns it on. The only question is, does this take effect immediately?
 *
***/

EXEC sp_configure 'Show Advanced Options', 1

MINI LAB: Is running sp_configure enough to actually change the setting in real time? What is the difference between the config_value column and the run_value column? Which settings can be changed on the fly and which require a reboot?

Before you continue reading run a few examples of sp_configure on a SQL Server instance. Please be sure this is on a test server as changing these setting on a production machine could negatively impact performance and availability. It is not enough just to run sp_configure to change the run_value of a setting. You need to use the following code to modify the actual value that the instance is running with. EXEC sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO The RECONFIGURE command actually changes the running configuration of any property that is dynamic, meaning it can be changed without a reboot. If you want to find out if a property is dynamic or not, you need to query the instance metadata using the sys.Configurations DMV [LINK]. SELECT * FROM sys.Configurations WHERE name LIKE '%advanced%' This will return several columns, one of which is called is_dynamic. If this value is set to 1 then the value can modified by using RECONFIGURE and a reboot or restart of services is not required.

Column Meanings

The meaning of the column headers should be pretty clear, but I will cover them here briefly.

name: The name of the property to be changed or viewed.
minimum: The minimum value setting that is allowed
maximum: The maximum value that is allowed
config_value: The value that the instance is currently configured with.
run_value: The value the instance is currently running with.

Real World: You do not have to type all of the text of the name of the value you wish to change. You only need to specify a non-ambiguous amount. So if you would like to know the value for the max server memory (MB) just type sp_config 'max s'.

Example output of sp_configure from a developmental box on which I am running SQL Server 2008.

name	minimum	maximum	config_value	run_value
access check cache bucket count	0	16384	0	0
access check cache quota	0	2147483647	0	0
Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
Agent XPs	0	1	1	1
allow updates	0	1	0	0
awe enabled	0	1	0	0
backup compression default	0	1	0	0
blocked process threshold (s)	0	86400	0	0
c2 audit mode	0	1	0	0
clr enabled	0	1	0	0
common criteria compliance enabled	0	1	0	0
cost threshold for parallelism	0	32767	5	5
cross db ownership chaining	0	1	0	0
cursor threshold	-1	2147483647	-1	-1
Database Mail XPs	0	1	0	0
default full-text language	0	2147483647	1033	1033
default language	0	9999	0	0
default trace enabled	0	1	1	1
disallow results from triggers	0	1	0	0
EKM provider enabled	0	1	0	0
filestream access level	0	2	2	0
fill factor (%)	0	100	0	0
ft crawl bandwidth (max)	0	32767	100	100
ft crawl bandwidth (min)	0	32767	0	0
ft notify bandwidth (max)	0	32767	100	100
ft notify bandwidth (min)	0	32767	0	0
index create memory (KB)	704	2147483647	0	0
in-doubt xact resolution	0	2	0	0
lightweight pooling	0	1	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	2147483647	2147483647
max text repl size (B)	-1	2147483647	65536	65536
max worker threads	128	32767	0	0
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	0	8
nested triggers	0	1	1	1
network packet size (B)	512	32767	4096	4096
Ole Automation Procedures	0	1	0	0
open objects	0	2147483647	0	0
optimize for ad hoc workloads	0	1	0	0
PH timeout (s)	1	3600	60	60
precompute rank	0	1	0	0
priority boost	0	1	0	0
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
remote access	0	1	1	1
remote admin connections	0	1	0	0
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
remote query timeout (s)	0	2147483647	600	600
Replication XPs	0	1	0	0
scan for startup procs	0	1	0	0
server trigger recursion	0	1	1	1
set working set size	0	1	0	0
show advanced options	0	1	1	1
SMO and DMO XPs	0	1	1	1
SQL Mail XPs	0	1	0	0
transform noise words	0	1	0	0
two digit year cutoff	1753	9999	2049	2049
user connections	0	32767	0	0
user options	0	32767	0	0
xp_cmdshell	0	1	1	1