Introduction:
This article brings you the allowable configuration options in SQL Server which includes options that are new to SQL Server 2008 also. The options can be used to tune, optimize and maximize the performance of the system in variety of ways. But careful planning and examination should be done before you implement the change. By default Microsoft has optimized it with default values. Some of those options does not require a service restart and some does. Those are called advanced options which you will come to know as we discuss.
Before going to see on what these options are let us know how to set these options using sp_configure procedure
| sp_configure [ [ @configname = ] ‘name’ [ , [ @configvalue = ] ‘value’ ] ] |
You can get the list of all available configurations using the code given below.
sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure
The table below gives the full list of the options available.
|
NAME
|
MINIMUM | MAXIMUN | REQUIRES RECONFIGURE | ADVANCED OPTION |
|
Ad Hoc Distributed Queries
|
0 | 1 | 1 | 1 |
|
Affinity I/O mask
|
-2147483648 | 2147483647 | 0 | 1 |
|
Affinity mask
|
-2147483648 | 2147483647 | 1 | 1 |
|
Agent XPs
|
0 | 1 | 1 | 1 |
|
allow updates
|
0 | 1 | 1 | 0 |
|
awe enabled
|
0 | 1 | 0 | 1 |
|
backup compression default
|
0 | 1 | 1 | 0 |
|
blocked process threshold (s)
|
0 | 86400 | 1 | 1 |
|
c2 audit mode
|
0 | 1 | 0 | 1 |
|
clr enabled
|
0 | 1 | 1 | 0 |
|
common criteria compliance enabled
|
0 | 1 | 0 | 1 |
|
cost threshold for parallelism
|
0 | 32767 | 1 | 1 |
|
cross db ownership chaining
|
0 | 1 | 1 | 0 |
|
Cursor threshold
|
-1 | 2147483647 | 1 | 1 |
|
Database Mail XPs
|
0 | 1 | 1 | 1 |
|
Default full-text language
|
0 | 2147483647 | 1 | 1 |
|
Default language
|
0 | 9999 | 1 | 0 |
|
Default trace enabled
|
0 | 1 | 1 | 1 |
|
disallow results from triggers
|
0 | 1 | 1 | 1 |
|
EKM provider enabled
|
0 | 1 | 0 | 1 |
|
fill factor (%)
|
0 | 100 | 0 | 1 |
|
ft crawl bandwidth (max)
|
0 | 32767 | 1 | 1 |
|
ft crawl bandwidth (min)
|
0 | 32767 | 1 | 1 |
|
ft notify bandwidth (max)
|
0 | 32767 | 1 | 1 |
|
ft notify bandwidth (min)
|
0 | 32767 | 1 | 1 |
|
index create memory (KB)
|
704 | 2147483647 | 1 | 1 |
|
in-doubt xact resolution
|
0 | 2 | 1 | 1 |
|
lightweight pooling
|
0 | 1 | 0 | 1 |
|
Locks
|
5000 | 2147483647 | 0 | 1 |
|
max degree of parallelism
|
0 | 64 | 1 | 1 |
|
max full-text crawl range
|
0 | 256 | 1 | 1 |
|
max server memory (MB)
|
16 | 2147483647 | 1 | 1 |
|
max text repl size (B)
|
-1 | 2147483647 | 1 | 0 |
|
max worker threads
|
128 | 32767 | 0 | 1 |
|
media retention
|
0 | 365 | 1 | 1 |
|
min memory per query (KB)
|
512 | 2147483647 | 1 | 1 |
|
min server memory (MB)
|
0 | 2147483647 | 1 | 1 |
|
Nested triggers
|
0 | 1 | 1 | 0 |
|
network packet size (B)
|
512 | 32767 | 1 | 1 |
|
Ole Automation Procedures
|
0 | 1 | 1 | 1 |
|
open objects
|
0 | 2147483647 | 0 | 1 |
|
PH timeout (s)
|
1 | 3600 | 1 | 1 |
|
Precompute rank
|
0 | 1 | 1 | 1 |
|
priority boost
|
0 | 1 | 0 | 1 |
|
query governor cost limit
|
0 | 2147483647 | 1 | 1 |
|
query wait (s)
|
-1 | 2147483647 | 1 | 1 |
|
recovery interval (min)
|
0 | 32767 | 1 | 1 |
|
remote access
|
0 | 1 | 0 | 0 |
|
remote admin connections
|
0 | 1 | 1 | 0 |
|
remote login timeout (s)
|
0 | 2147483647 | 1 | 0 |
|
remote proc trans
|
0 | 1 | 1 | 0 |
|
remote query timeout (s)
|
0 | 2147483647 | 1 | 0 |
|
Replication XPs
|
0 | 1 | 1 | 1 |
|
scan for startup procs
|
0 | 1 | 0 | 1 |
|
server trigger recursion
|
0 | 1 | 1 | 0 |
|
set working set size
|
0 | 1 | 0 | 1 |
|
show advanced options
|
0 | 1 | 1 | 0 |
|
SMO and DMO XPs
|
0 | 1 | 1 | 1 |
|
SQL Mail XPs
|
0 | 1 | 1 | 1 |
|
transform noise words
|
0 | 1 | 1 | 1 |
|
two digit year cutoff
|
1753 | 9999 | 1 | 1 |
|
user connections
|
0 | 32767 | 0 | 1 |
|
user options
|
0 | 32767 | 1 | 0 |
|
Web Assistant Procedures
|
0 | 1 | 1 | 1 |
|
xp_cmdshell
|
0 | 1 | 1 | 1 |
Ad Hoc Distributed Queries
By default SQL Server does not allow to run ad hoc distributed queries to be executed with OPENROWSET AND OPENDATASOURCE you need to set this option on to allow executing the feature.
Affinity I/O mask
Microsoft carries out multitasking moving threads between different processor in windows 2000 and 2003. This can affect when SQL Server runs with multiple processes affecting the performance of the server by frequent processor reloads whenever threads are moved between them in the cache. This can be changed by assigning threads to specific processor. This relation between thread and processor is called as processor affinity.
You need calculate the processor using bits and assign in to the SQL Server.
Note: This option should be used along with the affinity mask option such that they both are not disabled simultaneously. For 64-bit server you need to use the option that we will discuss next in line.
Affinity64 I/O mask
This option is same as the previous one except that this is to be used with 64-bit servers than the lower ones.
Affinity mask
Microsoft carries out multitasking moving threads between different processor in windows 2000 and 2003. This can affect when SQL Server runs with multiple processes affecting the performance of the server by frequent processor reloads whenever threads are moved between them in the cache. This can be changed by assigning threads to specific processor. This relation between thread and processor is called as processor affinity. This corresponds to the CPU affinity and is dynamic.
You need calculate the processor using bits and assign in to the SQL Server.
Note: This option should be used along with the affinity mask option such that they both are not disabled simultaneously
Affinity64 mask
This option is same as the previous one except that this is to be used with 64-bit servers than the lower ones. This corresponds to the CPU affinity and is dynamic.
Agent XPs
Use this option to enable SQL Server agent related extended stored procedures. When you use Surface Area configuration to start SQL Server Agent then these XPs are enabled by default.
Allow updates
This setting has no effects in SQL Server 2005 and 2008. This was supported with SQL 2000 in which when you enable this feature you will be able to update system tables.
Awe enabled
Address Windowing Extensions (AWE) API is used to provide access to the physical memory in the server more than the configured value. This feature depends on the operating system and hardware considerations. This option in not needed and cannot be configured in 64-bit systems. You need to carefully implement this option as this has implications in the server performance. This cannot be set when the virtual memory is more than the physical memory. When the virtual memory is less then this option can be set. With windows 2000, using this option maps SQL Server to memory which is not released until SQL Server is shutdown. With windows 2003, AWE memory is handled dynamically.
Backup compression default
This option is available in SQL 2008 only. This option specifies the default compression for the backup when a user does not specify in the backup TSQL.
Blocked process threshold
This option can be used to specify the threshold in seconds when blocked process reports are generated. By default the report is not enabled. Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. The event is generated once per reporting interval for each of the blocked tasks. This does not report system tasks and tasks that are waiting for resources.
C2 Audit Mode
This option can be used to track successful and failed attempts of SQL Server access to objects and statements. This can be used for profiling tracking information. You should use this option only when needed as this takes little overhead in the server. C2 log file can be found in MSSQL$instancenameData.
clr enabled
Enabling this option allows user assemblies to be run by SQL server. Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: “clr enabled” or “lightweight pooling. Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Declarative Management Framework.
Common criteria compliance enabled
This enables the common criteria elements as given below.
Residual Information Protection (RIP)
This option memory to be overwritten with a known pattern of bits before its allocated to a new resource. This contributes for enhanced security with some slow performance.
The ability to view login
Login audit is enabled when its on. This helps to track login information such as the last successful login time, the last unsuccessful login time, and the number of attempts between the last successful and current login times is made available.
That column GRANT should not override table DENY
When this is option is enabled a table level DENY takes precedence over a column level GRANT else it happens vice-versa.
This option is available in Enterprise, Evaluation and Developer editions of SQL 2005 and higher.
Cost threshold for parallelism
SQL server creates and runs a parallel plan for a query when it estimates that a serial plan will cost more than the parallel plan. This option specifies in seconds that SQL server should use a parallel plan instead of a serial plan. This is useful in symmetric multiprocessing systems.
This option is ignored by the database engine in the following conditions.
Your computer has only one processor.
b. Only a single CPU is available to SQL Server because of the affinity mask configuration option.
c. The max degree of parallelism option is set to 1.
Cross db ownership chaining
This option is used to configure cross-database ownership chaining for all databases.
When cross db ownership chaining is off (0) for the instance, cross-database ownership chaining is disabled for all databases.
When cross db ownership chaining is on (1) for the instance, cross-database ownership chaining is on for all databases.
It is always recommended that if your database uses chaining that it be enabled in the database level than the server level using the alter database TSQL.
Cursor threshold
This option specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.
If you set cursor threshold to -1, all keysets are generated synchronously, this benefits small cursor sets. If you set cursor threshold to 0, all cursor keysets are generated asynchronously. With other values, the query optimizer compares the number of expected rows in the cursor set and builds the keyset asynchronously if it exceeds the number set in cursor threshold. Do not set cursor threshold too low, because small result sets are better built synchronously.
Database Mail XPs
This option is used to enable Database Mail related extended stored procedures.
Default full-text language
This option is used to specify a default language value for full-text indexed columns
Default language
This option is used to specify the default language of newly created logins. To specify the language you want specify the language id available from sys.langauges table.
Default trace enabled
This option enables or disables default trace log for the SQL server instance. This log can be used for analysis of configuration options.
Disallow results from triggers
This option controls the behavior whether triggers return result sets. Triggers that return result sets can cause unexpected behavior in the application.
ft crawl bandwidth(max)
This option specifies the size to which the pool of large memory buffers can grow. Large memory buffers are 4 megabytes (MB) in size. This max option tells the maximum number of large buffer available for full-text memory manager.
ft crawl bandwidth(min)
This option specifies the size to which the pool of large memory buffers can grow. Large memory buffers are 4 megabytes (MB) in size. The min parameter specifies the minimum number of memory buffers that must be maintained in the pool of large memory buffers. Upon request from the Microsoft SQL Server memory manager, all extra buffer pools will be released but this minimum number of buffers will be maintained. If, however, the min value specified is zero, then all memory buffers are released.
ft notify bandwidth(max)
This option specifies the size to which the pool of small memory buffers can grow. Small memory buffers are 64 kilobytes (KB) in size. This max option tells the maximum number of small buffer available for full-text memory manager.
ft notify bandwidth(min)
This option specifies the size to which the pool of small memory buffers can grow. Small memory buffers are 4 megabytes (MB) in size. The min parameter specifies the minimum number of memory buffers that must be maintained in the pool of small memory buffers. Upon request from the Microsoft SQL Server memory manager, all extra buffer pools will be released but this minimum number of buffers will be maintained. If, however, the min value specified is zero, then all memory buffers are released.
Index create memory
This option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
In-doubt xact resolution
This option controls the default outcome of transactions that MSDTC is unable to resolve. The result is one among the following
|
Outcome value
|
Description
|
| 0 |
No presumption. Recovery fails if MS DTC cannot resolve any in-doubt transactions.
|
| 1 |
Presume commit. Any MS DTC in-doubt transactions are presumed to have committed.
|
| 2 |
Presume abort. Any MS DTC in-doubt transactions are presumed to have aborted.
|
Lightweight pooling
This option helps to reduce the system overhead associated with excessive context switches in symmetric multiprocessing. Using lightweight pooling can provide better throughput with excessive context switches by handling them inline. Lightweight pooling is not supported for Microsoft Windows 2000 and Microsoft Windows XP. Windows Server 2003 provides full support for lightweight pooling.
Locks
This option is given for backward compatibility. Lock space is automatically allocated instead of being set with this option.
max degree of parallelism
When SQL Server runs on server with more than one CPU, it best calculates the number of processor to be used for parallel execution of a query. This dynamic setting can be overridden by this system configuration parameter with setting a value greater than 1 and to a maximum of 64. This setting can also be overridden by the query using the MAXDOP query hint that is available.
max full-text crawl range
You can use this setting to specify the crawl to optimize the CPU in full-text operation. It accepts a minimum of 1 and maximum of 32 with default being 4.
max server memory
By default, SQL Server dynamically allocates memory. This setting can be overridden by specifying static memory configuration. This parameter sets the maximum memory limit that SQL Server can use in the server.
max text repl size
This parameter is used to specify the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default is 65536. A value of -1 indicates no limit, other than the limit imposed by the data type. This option applies to transactional replication and Change Data Capture. When a server is configured for both transactional replication and Change Data Capture, the specified value applies to both features. This option is ignored by snapshot replication and merge replication.
max worker threads
This option is used to configure the number of threads to be set to SQL Server processes. SQL Server uses thread services of windows for network support, database checkpoints and user connections. This setting should be only configured when there are large number of users connecting to the system and the default value is not helping with the performance.
Microsoft recommends a configuration of 1024 for 32 bit systems and 2048 for 64 bit systems.
media retention
This is the default system wide configuration on the backup retention period. You can specify a value between 0 and 365. This option can be overridden by using the RETAINDAYS clause of the BACKUP statement.
min server memory
By default, SQL Server dynamically allocates memory. This setting can be overridden by specifying static memory configuration. This parameter sets the minimum memory limit that SQL Server can use in the server.
nested triggers
This option is used to control whether can AFTER trigger can cascade which means that initiate another trigger and so on. By setting a value of 0, it cannot cascade and setting a value of 1, it can do so upto 32 levels.
network packet size
This option is used to specify the packet size used across the network. Packets are data chunks that are transferred between client and server. The default is 4096. If your application uses more data transfer then set a higher value than the default and if your application uses lower transfer then use a lower value. But it is recommended not to change this setting unless necessarily needed.
Ole Automation Procedures
This option is used to specify if OLE automation procedures can be called from the TSQL batches.
open objects
This option is used for backward compatibility and has no effect as SQL Server dynamically handles it.
ph timeout
This option is used to specify how long the full-text handler to wait to connect to the database before timing out. The default value is 60 seconds.
precompute rank
Use the precompute rank option to improve the performance of FREETEXTTABLE queries that specify the top_n_by_rank parameter. This option is an optimization strategy that can reduce the use of CPU resources when Microsoft SQL Server computes rank for FREETEXTTABLE at query time. When the option is set to 1, FREETEXTTABLE queries specified with top_n_by_rank use precomputed rank data stored in the full-text catalogs.
By default, this option is set to 0.
When using precomputed rank data, keep in mind that the result set returned by top_n_by_rank may not contain all the results that are returned when the precompute rank option is set to 0.
priority boost
This option specifies that whether SQL Server should run at a higher priority than the other processes in the server. Changing this option can causes other processes to drain for resources in the server.
query governor cost limit
This option refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration. Setting the default value of zero would mean, the query will run without any limitations. This configuration is a server wide setting and can be overridden per connection basis using the set QUERY_GOVERNOR_COST_LIMIT command.
query wait (s)
This option specifies the time in seconds that a query waits for resources before timing out. The default value of -1 refers to the timeout period of 25 times the query cost.
recovery interval (min)
This option specifies the maximum number of minutes that the SQL Server needs to recover a particular database. Also remember that this option has a effect on the number of checkpoints issued in the database.
remote access
This option is used to specify if stored procedures can run from the local or remote server machines. This option is only for those remote servers added using sp_addserver command.
remote admin connections
SQL Server 2005 has dedicated admin connections to diagonise and troubleshoot server level issues. You this option to specify if remote application or machine can use the DAC.
remote login timeout (s)
This option specifies the time in seconds to wait before returning a error after failing to connect from a remote machine. If the remote server is down and you are trying to connect this option ensures that you are immediately returned an error.
remote proc trans
This option specifies to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. This option is provided for compatibility with earlier versions of Microsoft SQL Server for applications that use remote stored procedures. Instead of issuing remote stored procedure calls, use distributed queries that reference linked servers, which are defined by using sp_addlinkedserver.
remote query timeout (s)
This option is used to specify time in seconds before before the remote operation can timeout from SQL Server.
Replication XPs
This is for internal use by SQL Server database engine and cannot be manually configured.
scan for startup procs
This option specifies for automatic execution of startup procedure when SQL Services are started. When set to 1, SQL Server scans for the startup procedures and executes them.
server trigger recursion
This option is used to specify whether server level triggers can fire recursively.
set working set size
You can use this option to specify to reserve the amount of physical memory space for SQL Server. This option is obsolete and is no more available with SQL Server higher versions.
show advanced options
This option is used to specify to display system stored procedure advanced options.
SMO and DMO XPs
This option is used to enable SQL Server Management Object (SMO) and SQL Distributed Management Object (SQL-DMO) extended stored procedures on this server.
SQL Mail XPs
This option is used to enable SQL Mail on this server.
transform noise words
This option is used to suppress an error message if noise words cause a Boolean operation on a full-text query to fail. This option is useful for full-text queries that use the CONTAINS predicate in which Boolean operations include noise words.
two digit year cutoff
This option is used to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.
A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
user connections
This option is used to specify the maximum number of simultaneous user connections allowed on Microsoft SQL Server.
user options
This option is used to specify global defaults for all users. This option can be overridden by the user level setting for each connection.
Web Assistant Procedures
This option is used to specify to enable web assistant procedures on the server.
The following procedures are enabled by this option:
sp_makewebtask
sp_dropwebtask
sp_runwebtask
sp_enumcodepages
xp_cmdshell
This option is used to enable system administrator to use xp_cmdshell to communicate to the OS using the SQL Server database engine.
Leave a Reply