The server configuration options can be managed using sp_configure system stored procedure and SQL Server Management Studio. Some options needs to be configured with SQL Server Surface Area Configuration Tool. The most frequent options can be configured through SQL Server Management Studio. The entire configuration options can be managed and modified using sp_configure.
Is essential for DBA to know the meaning and understand each server configuration options. We know that is hard to accomplish this task, but we can do it, if we find out one by one each of this options. Hence, we are going to try to understand one by one (day by day) these options on this blog.
First we need to know what are the all the server configuration options. To know what are the entire server configurations, we can look into sys.configuration system view or use sp_configure system stored procedure.
select * from sys.configurations;
or
sp_configure 'show advanced options',1
GO
reconfigure
Go
sp_configure
go
In the following table we can the see all the server configuration options, the minimum / maximum value for each option, default value. Each value has a letter A ( advanced option, to change this options the setting show advanced options to 1 is required), RR ( this options require restart the db engine) and SC ( automatic configuration, restart is not required).
| Configuration option | Minimum value | Maximum value | Default |
| access check cache bucket count (A) | 0 | 16384 | 0 |
| access check cache quota (A) | 0 | 2147483647 | 0 |
| ad hoc distributed queries (A) | 0 | 1 | 0 |
| affinity I/O mask (A, RR) | -2147483648 | 2147483647 | 0 |
| affinity64 I/O mask (A, only available on 64-bit version of SQL Server) | -2147483648 | 2147483647 | 0 |
| affinity mask (A) | -2147483648 | 2147483647 | 0 |
| affinity64 mask (A, RR), only available on 64-bit version of SQL Server | -2147483648 | 2147483647 | 0 |
| Agent XPs (A) | 0 | 1 | 0 (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.) |
| allow updates (Obsolete. Do not use. Will cause an error during reconfigure.) | 0 | 1 | 0 |
| awe enabled (A, RR) | 0 | 1 | 0 |
| backup compression default | 0 | 1 | 0 |
| blocked process threshold (A) | 0 | 86400 | 0 |
| c2 audit mode (A, RR) | 0 | 1 | 0 |
| clr enabled | 0 | 1 | 0 |
| common criteria compliance enabled (A, RR) | 0 | 1 | 0 |
| cost threshold for parallelism (A) | 0 | 32767 | 5 |
| cross db ownership chaining | 0 | 1 | 0 |
| cursor threshold (A) | -1 | 2147483647 | -1 |
| Database Mail XPs (A) | 0 | 1 | 0 |
| default full-text language (A) | 0 | 2147483647 | 1033 |
| default language | 0 | 9999 | 0 |
| default trace enabled (A) | 0 | 1 | 1 |
| disallow results from triggers (A) | 0 | 1 | 0 |
| EKM provider enabled | 0 | 1 | 0 |
| filestream_access_level | 0 | 2 | 0 |
| fill factor (A, RR) | 0 | 100 | 0 |
| ft crawl bandwidth (max) , see ft crawl bandwidth(A) | 0 | 32767 | 100 |
| ft crawl bandwidth (min) , see ft crawl bandwidth(A) | 0 | 32767 | 0 |
| ft notify bandwidth (max) , see ft notify bandwidth(A) | 0 | 32767 | 100 |
| ft notify bandwidth (min) , see ft notify bandwidth(A) | 0 | 32767 | 0 |
| index create memory (A, SC) | 704 | 2147483647 | 0 |
| in-doubt xact resolution (A) | 0 | 2 | 0 |
| lightweight pooling (A, RR) | 0 | 1 | 0 |
| locks (A, RR, SC) | 5000 | 2147483647 | 0 |
| max degree of parallelism (A) | 0 | 64 | 0 |
| max full-text crawl range (A) | 0 | 256 | 4 |
| max server memory (A, SC) | 16 | 2147483647 | 2147483647 |
| max text repl size | 0 | 2147483647 | 65536 |
| max worker threads (A, RR) | 128 | 32767 (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.) | 0 Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+( |
| media retention (A, RR) | 0 | 365 | 0 |
| min memory per query (A) | 512 | 2147483647 | 1024 |
| min server memory (A, SC) | 0 | 2147483647 | 0 |
| nested triggers | 0 | 1 | 1 |
| network packet size (A) | 512 | 32767 | 4096 |
| Ole Automation Procedures (A) | 0 | 1 | 0 |
| open objects (A, RR, obsolete) | 0 | 2147483647 | 0 |
| optimize for ad hoc workloads (A) | 0 | 1 | 0 |
| PH_timeout (A) | 1 | 3600 | 60 |
| precompute rank (A) | 0 | 1 | 0 |
| priority boost (A, RR) | 0 | 1 | 0 |
| query governor cost limit (A) | 0 | 2147483647 | 0 |
| query wait (A) | -1 | 2147483647 | -1 |
| recovery interval (A, SC) | 0 | 32767 | 0 |
| remote access (RR) | 0 | 1 | 1 |
| remote admin connections | 0 | 1 | 0 |
| remote login timeout | 0 | 2147483647 | 20 |
| remote proc trans | 0 | 1 | 0 |
| remote query timeout | 0 | 2147483647 | 600 |
| Replication XPs Option (A) | 0 | 1 | 0 |
| scan for startup procs (A, RR) | 0 | 1 | 0 |
| server trigger recursion | 0 | 1 | 1 |
| set working set size (A, RR, obsolete) | 0 | 1 | 0 |
| show advanced options | 0 | 1 | 0 |
| SMO and DMO XPs (A) | 0 | 1 | 1 |
| SQL Mail XPs (A) | 0 | 1 | 0 |
| transform noise words (A) | 0 | 1 | 0 |
| two digit year cutoff (A) | 1753 | 9999 | 2049 |
| user connections (A, RR, SC) | 0 | 32767 | 0 |
| User Instance Timeout (A, only appears in SQL Server 2008 Express.) | 5 | 65535 | 60 |
| user instances enabled (A, only appears in SQL Server 2008 Express.) | 0 | 1 | 0 |
| user options | 0 | 32767 | 0 |
| xp_cmdshell (A) | 0 | 1 | 0 |
so, we are going to try day by day see some details for each of the server configuration options.
References:
SQL Server BOL - Server configuration options.
EmoticonEmoticon