Wednesday, January 13, 2010

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.