Major Configuration Options In SQL Server

Major Configuration Options In SQL Server

Ad Hoc Distributed Queries

By default, SQL Server doesn't allow ad-hoc distributed queries using OPENROWSET or OPENDATASOURCE. When this option is set to 1, SQL Server allows ad-hoc access. When this option is not set or is set to 0, SQL Server does not allow ad-hoc access.

Affinity Mask

The affinity mask configuration allows you to specify which CPUs on a multiprocessor computer are to be used to run threads from an instance of SQL Server. You can use the affinity mask configuration option to exclude SQL Server threads from processors that reserved for operating system (OS) processes.

Agent XPs

The Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SSMS Object Explorer.

Allow Updates

The allow updates optiom is to direct updates can be made to system tables. By default, allow updates is disabled, so users cannot update system tables through ad-hoc updates. Users can update system tables by using system stored procedures only. 

Caution  If you create stored procedures while the allow updates option is enabled, those stored procedures always have the ability to update system tables even after you disable allow updates.

Awe Enabled

To enable AWE, set awe enabled to 1. MS SQL Server will reserve almost all available memory, leaving 128 MB to other Application and OS, unless a value has been specified for max server. 


It is strongly recommended that you set a value for the max server memory option each time you enable AWE. 


Otherwise other applications or instances of MS SQL Server 2000 will have less than 128 MB of physical memory in which to run.

Fill Factor

Use the fill factor option to specify how full MS SQL Server should make each page when it creates a new index using existing data. The fill factor percentage affects performance because SQL Server must take time to split pages when they fill up. The recommended is use fill factor is 80% so 20% is reserved for free space.

The setting takes effect after stopping and restarting the server.

Lightweight Pooling

Use the lightweight pooling option is to reducing the system overhead associated with the excessive context switching. When excessive context switching is present, lightweight pooling can provide better throughput by performing the context switching inline, thus helping to reduce user & kernel transitions.

Setting lightweight pooling to 1 causes Microsoft SQL Server to switch to fiber mode scheduling. The default value for this option is 0. 

The setting takes effect after stopping and restarting the server.

Max Degree Of Parallelism

Use the max degree of parallelism option to limt the number of processors to use in parallel plan execution. Set the value to a number greater than 1 to restrict the max number of processors used by a single query execution. If a value is greater than the number of availble CPUs is specified, the actual number of available CPUs is used.

Max Worker Threads

Use the max worker threads option to configure the number of worker threds available to SQL Server processes. SQL Server uses the native thread services of the MS Windws 2000 and Windows Server 2003 operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

Recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.

Min Memory Per Query

Use the min memory per query option to specify the minimum amount of memory(KB) that will be allocated for the execution of a query. For example, if min memory per query is set to 2048 kilobytes, the query is guaranteed to get at least that much total memory. 


You can set min memory per query to any value from 512 through 2147483647 KB (2 gigabytes). The default is 1024 KB.

Remote Access

Use the remote access option to control executing stored procedures from remote servers running instances of MS SQL Server. Set remote access to 1 (default) to allow permission to execute stored procedures from remote servers. Set the option to 0 to prevent execution of stored procedures from a remote server.

The setting takes effect after stopping and restarting the server.

Remote Login Timeout

Use the remote login timeout option to specify the number of seconds to wait before from failing remote login attempt. For example, if you are attempting to log in to a remote server and that server is down, remote login timeout ensures that you do not have to wait indefinitely before your computer ceases its attempts to log in.

The setting takes effect immediately (without a server stop and restart). 

Remote Query Timeout

Use the remote query timeout option to specify the number of seconds that must wait when processing a remote operation before SQL Server assumes the command failed or took too much time to perform (times out error). The default is 600, which allows a ten minute wait.

The setting takes effect immediately (without a server stop and restart).

User Connections

The user connections option to specify the maximum number of simultaneous user connections allowed on SQL Server. The actual number of user connections allowed is depends on the limits to your applications and hardware. 


SQL Server allows a maximum of 32,767 user connections.

XP_Cmdshell

the xp_cmdshell is Introduced in SQL Server 2005. This option that enables systm administrators to control whether the xp_cmdshell extended SP can be executed on a system. By default, the xp_cmdshell option is disabled on new installations.

SQL Mail XPs

Use the SQL Mail XPs option to enable SQL Mail on this server. The possible values are:

0 indicating SQL Mail is not available (default)

1 indicating SQL Mail is available

Remote Admin Connections Option

MS SQL Server 2005 provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server in order to execute diagnostic functions or T-SQL statements such as finding locks, blocks, cache, etc for troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a Database Engine connection. 

0 - indicates only local connections are allowed using the DAC

1 - indicates remote connections are allowed using the DAC

Default Trace Enabled

The default trace enabled option to enable or disable the default trace log files. The default trace is for troubleshooting minor issues. It doesn't take much resources, you can always disable through SP_Configure.