Important SQL Server Configuration Options to be enabled on SQL Server Instance

As a DBA, we would need to make sure that the SQL Server is configured optimally for better performance, stability and security. There are various configuration settings in SQL Server which allows us to customize our SQL Server environment to the requirements of the specific Applications. One of the common SQL Server configuration settings can be found and can enabled using any of the below commands.

SP_Configure

Or

select * from sys.configurations

The results returned by sp_configure contains name of the configuration item, minimum and maximum values that can be configured for a particular setting and the config_value and run_value of this setting. Additionally sys.configurations system table contains more details about the configuration settings. Config_value means, it has been enabled or not, but still having this set for a setting will not be in effect until the run_value also shows the same value as config_value.

Example: Below shows config_value of “backup compression default” is disabled and “blocked process threshold (s)” is set to 5

name

minimum

maximum

config_value

run_value

backup compression default

0

1

0

0

blocked process threshold (s)

0

86400

5

5

At the bottom, you can find the table with list of all the SQL Server configuration options available in SQL Server 2008 R2.

By default, we will not be able to see all the configuration values using sp_configure, most of them will be hidden. Inorder for us to be able to see all the configuration settings, we need to run below command as a sysadmin. Now we will be able to see all the configuration settings.

sp_configure 'show advanced options',1
Go
Reconfigure with Override
Go
Sp_Configure
Go

Now let’s see how we can enable a particular configuration setting. Below is the command used to enabled “backup compression default” setting.

sp_configure 'backup compression default',1
Go
Reconfigure with Override
GO
sp_configure
GO

– Now, we can see that the backup compression default has been enabled and is used as well.

name

minimum

maximum

config_value

run_value

backup compression default

0

1

1

1

– There are many configuration options available, but in general most of these are better left untouched, but some are very important to be configured in most of the environments for better performance, stability and security. Below are some of those settings and some brief details about the same.

affinity mask – There are about 4 various of this setting listed in sp_configure which related to CPU and I/O affinity. This is better to be left at default, however on one server if there are multiple instances of SQL Server running, we can configure these values, so that we can limit a particular SQL Server instance to use only sub-set of available processors on the server, thus segregating the workload of the processors to each SQL Server instance. But proper testing needs to be done to make sure it does not lead to performance problems.

awe enabled – This setting is for x86 based systems, where we would like the SQL Server instance to use more than 4 GB of physical memory. X86 systems are limited to use up to maximum of 4 GB of physical memory, but using AWE option, we can enabled SQL Server to use more than 4 GB memory. This should be enabled on x86 based dedicated SQL Server systems with more than 4 GB of memory.

backup compression default – This option is available starting with SQL Server 2008. This should be enabled, so that the size of backup files will be reduced thus saving disk space and improves the performance of database backup as there is less amount of I/O operations to be performed.

Database Mail XPs – Enable this option, if you would like to use Database Mail feature, so that E-Mails can be sent from SQL Server.

default trace enabled – This is enabled by default and collects trace with details like Auto-growth or Shrink related events on databases, any changes to the schema of the objects, any server or database configuration changes, etc. There is not much overhead of running default trace, so better to leave this running and can be useful sometimes to know, if there were any activities like auto-growth or schema changes. Also, there will be only 5 trace files with 5 MB each will be retained, so on a OLTP system with high number of transactions, this can have information collected for less duration.

filestream access level – If you are using filestream feature, then enable this option, else you can leave this disabled.

max degree of parallelism (MAXDOP) – This is one of the important options which shows affect on SQL Server performance. It is advised to change this value which is appropriate to the Application. There are some recommendations on what value to set for this setting and depends on the type of Application(vendor application or custom home-built), type of workload(OLTP or DataWarehouse), type of transactions(Many small and few large queries or many large and few small transactions). Below articles should provide insights on setting this value.
http://support2.microsoft.com/kb/2806535
http://blogs.msdn.com/b/psssql/archive/2013/09/27/how-it-works-maximizing-max-degree-of-parallelism-maxdop.aspx

max server memory (MB) – This is another important setting which has to be configured. This basically limits the bufferpool memory region of SQL Server Memory. Generally, start with setting this value to 85% of total RAM on a dedicated SQL Server machine with one SQL Server instance and monitor the memory usage. Basically, we need to ensure that SQL Server does not use up entire memory on the server and causing memory pressure for other applications or to Operating System itself as it may cause server to hang or crash. Monitor the memory usage after setting this value, if still the Available memory on the server is falling to very low, then monitor and see which other application is consuming memory and if that application can be moved to other server. If other Application has to run on same server as SQL Server, then need to lower the Max Server Memory value, so that other application and OS has enough memory for their functioning. Note that this value is set interms of MB, so just be mindful.

optimize for ad hoc workloads – This setting is disabled by default and is better to be left this way, but in some environments where there are many Adhoc(queries not part of Stored Procedures) commands being run from application, then enabling this this setting will help with performance.

priority boost – This is disabled by default and ensure this is always in disabled state. Enabling this can cause serious problems to the system.

Rest all options, you may choose to leave as default or carefully consider making changes if it really has any benefits for your environment.

Below are all the SQL Server configuration settings available in SQL Server 2008 R2.

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

1

1

blocked process threshold (s)

0

86400

5

5

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

1

1

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

119808

119808

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

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *