With
SQL Server 2008, many options may affect the performance of individual
SQL statements or the overall performance of a SQL Server instance.
There are instance-wide configuration options (set using the sp_configure system stored procedure or with the server properties dialog within SSMS), database-level options (set with ALTER DATABASE), database compatibility levels (using the sp_dbcmptlevel system stored procedure), batch-level options (using the SET command for things such as SET ANSI_NULLS), and statement-level options (such as table hints, query hints, and join hints). Always remember that a hint overrides a SET option, a SET option overrides a database option, and a database option overrides an instance-wide option.
For SQL Server 2008, this article discusses the instance-wide configurable options in two
distinct categories: basic options and advanced options. The advanced
options are a superset of the basic options. As each option is
discussed, this chapter notes whether it is self-configuring. A self-configuring option
is an option that adjusts itself dynamically, according to the needs of
the system. In most cases, this eliminates the need for setting the
values manually. Sometimes you don’t want to rely on certain
self-configuring values, depending on how SQL Server is being used.
This chapter identifies self-configuring values.
As you can see in Figure 1,
SQL Server provides configuration parameters that the system
administrator can set to maximize the performance of a system from the
properties option within SSMS. You can set these and other SQL Server
configuration parameters by using the sp_configure system stored procedure as well.
Now, the only questions that
need to be answered are “What configuration options do I need to set
that aren’t already fine?” and “How do I set them?”
To
answer the “what” question, you first need to determine for what
purpose the applications are using SQL Server. The answer must include
understanding variables such as the number of potential connections to
support, resources available on the box, size of the database, type of
data accesses occurring, and workload being put on SQL Server. When you
know all this, it is easy to determine the configuration option setting
to adjust.
The following shows
a generalization of the types of applications you might find in the
real world that would be implemented using SQL Server 2008 and the
general behavior they elicit:
Online transaction processing (OLTP)— Mix of reads, writes, updates, and deletes. Large number of concurrent users.
Data warehouse— Incremental loads (deltas), aggregation/transformation processing, then primarily read-only. Medium number of users.
Online analytical processing (OLAP)— Big loads, then primarily read-only. Medium to large number of simultaneous users.
Mixed server— Mix of reads, writes, updates, deletes, big loads, and big extracts. Large number of users.
This is not a complete
list, just a generalized list. Because these configuration options are
set at the SQL Server level, it is important to know the combined
behavior of all application processing. For each SQL Server
configuration option discussed in this chapter, we recommend an
appropriate setting, based on these generalized application processing
behavior types.
Now let’s answer the “How do I
set them?” question. The next few sections describe all the types of
configuration options available on SQL Server 2008. These sections show
how to set these configuration options using both SSMS and the sp_configure system stored procedure. The rule is that you can certainly set a configuration option using SSMS, but you should keep an sp_configure
version of that setting change as a backup in case you need to rebuild
the entire server configuration from scratch. In addition, keeping an sp_configure version around in a file provides a great audit trail of what you did and why. If you simply execute sp_configure
without parameters, you are given a list of options that can be
addressed. When you have just installed a SQL Server instance, you can
see only the basic configuration options.
SQL Server 2008 has 16 basic configuration options available:
exec sp_configure
go
name minimum maximum config_value run_value
---------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 0 0
backup compression default 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
filestream access level 0 2 0 0
max text repl size (B) -1 2147483647 65536 65536
nested triggers 0 1 1 1
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
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 0 32767 0 0
By default, all SQL Server users have permission to run the sp_configure system stored procedure, but only users who have sysadmin and serveradmin fixed server roles (such as sa) can actually set the value of a parameter.
The proper syntax of the sp_configure command is as follows:
exec sp_configure [parameter_name [, parameter_value ]]
In this syntax, parameter_name is the name of the configuration parameter you want to set, and parameter_value is the value for the parameter. Both of these parameters are optional. Parameters set by sp_configure take effect at the server level.
Following is a brief explanation of the output of the sp_configure command. As you can see, the output consists of five columns:
Name— This is the name of the configurable option.
Minimum— This is the minimum legal value allowed for this parameter. Passing an illegal value causes SQL Server to return an error.
Maximum— This is the maximum legal value allowed for this parameter. Passing an illegal value causes SQL Server to return an error.
Config_value—
This column reflects the values that will take effect the next time SQL
Server is started. If you change static parameters, the new values are
listed under this column.
Run_value—
This column reflects the values that SQL Server is currently using. If
you change any dynamic parameters, the new values are listed in this
column. At the time of SQL Server startup, config_value for all the parameters is copied into run_value. Immediately after restart, both columns (run_value and config_value) should display the same values, corresponding to each parameter.
If you specify a parameter
name, SQL Server returns the current configuration value for that
particular parameter, as in this example:
exec sp_configure 'clr enabled'
go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ ---------
clr enabled 0 1 0 0
There are many
more than 16 basic configuration options. In fact, there are 70
configuration options in total. They consist of the original 16 basic
options plus 54 advanced options (including two for 64-bit processing).
To see a complete list of all options, you turn on the show advanced option configuration option with the value 1.
In addition, when using sp_configure to change a setting, you use the RECONFIGURE WITH OVERRIDE statement to make the change take effect immediately. You can also choose to use just the RECONFIGURE
statement. Depending on the configuration option, it may take effect
immediately, or it may not take effect until the server has been
restarted.
The following commands set the show advanced options configuration option and then retrieve the complete list of all configuration options:
exec sp_configure 'Show Advanced Options', 1 /* Advanced config options */
go
RECONFIGURE WITH OVERRIDE /* to have it take effect immediately */
go
sp_configure
go
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ ---------
access check cache bucket count 0 65536 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
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 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 0 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 1024 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 0
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 0 0
Microsoft suggests
that only very experienced SQL Server administrators change these
advanced configuration options. In general, this is good advice because
most of these options are set to where you might want them as you start
out. As you learn more about your application and the other things
happening in your SQL Server instance, you will change them more and
more. You have been warned!
Earlier SQL Server 2008 R2 documentation also listed a configuration option, enable_force_flush,
that wasn’t present in the November Community Technology Preview (CTP)
for R2. We’re not sure whether we will see it very soon.
With SQL Server 2008, you can see all the configuration options and their current settings via system views. The sys.configurations
view shows the option, a description, and the current value in use for
each configuration option. The following example shows what you might
expect to see if you query the sys.configurations view:
SELECT convert(varchar(10),value_in_use) AS 'Value in Use',
substring (name,1,30) AS 'Configuration Option',
substring (description,1,30) AS 'Description'
FROM sys.configurations
ORDER BY 2
Go
Value in Use Configuration Option Description
------------ ------------------------------ ------------------------------
0 access check cache bucket coun Default hash bucket count for
0 access check cache quota Default quota for the access c
0 Ad Hoc Distributed Queries Enable or disable Ad Hoc Distr
0 affinity I/O mask affinity I/O mask
0 affinity mask affinity mask
0 affinity64 I/O mask affinity64 I/O mask
0 affinity64 mask affinity64 mask
1 Agent XPs Enable or disable Agent XPs
0 allow updates Allow updates to system tables
0 awe enabled AWE enabled in the server
0 backup compression default Enable compression of backups
0 blocked process threshold (s) Blocked process reporting thre
0 c2 audit mode c2 audit mode
0 clr enabled CLR user code execution enable
0 common criteria compliance ena Common Criteria compliance mod
5 cost threshold for parallelism cost threshold for parallelism
0 cross db ownership chaining Allow cross db ownership chain
-1 cursor threshold cursor threshold
0 Database Mail XPs Enable or disable Database Mai
1033 default full-text language default full-text language
0 default language default language
1 default trace enabled Enable or disable the default
0 disallow results from triggers Disallow returning results fro
0 EKM provider enabled Enable or disable EKM provider
0 filestream access level Sets the FILESTREAM access lev
0 fill factor (%) Default fill factor percentage
100 ft crawl bandwidth (max) Max number of full-text crawl
0 ft crawl bandwidth (min) Number of reserved full-text c
100 ft notify bandwidth (max) Max number of full-text notifi
0 ft notify bandwidth (min) Number of reserved full-text n
0 index create memory (KB) Memory for index create sorts
0 in-doubt xact resolution Recovery policy for DTC transa
0 lightweight pooling User mode scheduler uses light
0 locks Number of locks for all users
0 max degree of parallelism maximum degree of parallelism
4 max full-text crawl range Maximum crawl ranges allowed
2147483647 max server memory (MB) Maximum size of server memory
65536 max text repl size (B) Maximum size of a text field i
0 max worker threads Maximum worker threads
0 media retention Tape retention period in days
1024 min memory per query (KB) minimum memory per query (kByt
0 min server memory (MB) Minimum size of server memory
1 nested triggers Allow triggers to be invoked w
4096 network packet size (B) Network packet size
0 Ole Automation Procedures Enable or disable Ole Automati
0 open objects Number of open database object
0 optimize for ad hoc workloads When this option is set, plan
60 PH timeout (s) DB connection timeout for full
0 precompute rank Use precomputed rank for full-
0 priority boost Priority boost
0 query governor cost limit Maximum estimated cost allowed
-1 query wait (s) maximum time to wait for query
0 recovery interval (min) Maximum recovery interval in m
1 remote access Allow remote access
0 remote admin connections Dedicated Admin Connections ar
20 remote login timeout (s) remote login timeout
0 remote proc trans Create DTC transaction for rem
600 remote query timeout (s) remote query timeout
0 Replication XPs Enable or disable Replication
0 scan for startup procs scan for startup stored proced
1 server trigger recursion Allow recursion for server lev
0 set working set size set working set size
1 show advanced options show advanced options
1 SMO and DMO XPs Enable or disable SMO and DMO
0 SQL Mail XPs Enable or disable SQL Mail XPs
0 transform noise words Transform noise words for full
2049 two digit year cutoff two digit year cutoff
0 user connections Number of user connections all
0 user options user options
0 xp_cmdshell Enable or disable command shel
Dynamically changing configuration options are also updated so that their values are visible through the system views.
