Some tips for using SQL Server 2014 configuration options
SQL Server 2014 configuration options may be separated to two categories:
– Options that take effect immediately after setting the option and issuing the RECONFIGURE.
– Options that take effect only after restarting the instance of SQL Server.
In this article, you can find the description of some useful SQL Server 2014 configuration
options with the examples how to make it by using the sp_configure system stored procedure.
Keep in mind, because setting up some SQL Server 2014 configuration options can degrade
performance you should change these options very carefully and continue monitoring after
changing.
Consider changing the ‘fill factor’ option to the appropriate value.
The ‘fill factor’ option specifies how full SQL Server 2014 will make each index page. When there
is no free space to insert new row on the index page, SQL Server will create new index page and
transfer some rows from the previous page to the new one. This operation is called page splits.
You can reduce the number of page splits by setting the appropriate fill factor option to reserve
free space on each index page. The fill factor is a value from 1 through 100 that specifies the
percentage of the index page to be left empty. The default value for fill factor is 0. It is
treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some
space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage
is used only at the time the index is created. If the table contains read-only data (or data
that very rarely changed), you can set the ‘fill factor’ option to 100. When the table’s data
modified very often, you can decrease the ‘fill factor’ option to 70 percent, for example.
Note. You must restart the SQL Server 2014 to apply changes to the ‘fill factor’ option.
This is the example to set the value of the ‘fill factor’ option to 70 percent:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘fill factor’, 70
GO
RECONFIGURE
GO
You can set the value of the ‘Ad Hoc Distributed Queries’ to 1 to allow ad hoc distributed
queries using OPENROWSET and OPENDATASOURCE.
By default, SQL Server 2014 does not allow ad hoc access. By the way, consider using a
linked server for any data sources that will be accessed more than several times instead
of using ad hoc distributed queries. Because the ‘Ad Hoc Distributed Queries’ configuration
option is an advanced option, you should set the ‘show advanced option’ option to 1 to make
the ‘Ad Hoc Distributed Queries’ available.
This is the example to enable ad hoc distributed queries:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO
Consider setting the ‘index create memory’ configuration option.
This option controls the maximum amount of memory initially allocated for creating indexes.
However, SQL Server 2014 may exceed the value of this option if more memory is later needed
for index creation and the memory is available.
Because the ‘index create memory’ configuration option is an advanced option, you should set
the ‘show advanced option’ option to 1 to make the ‘index create memory’ available.
This is the example to set the value of the ‘index create memory’ option to 4096 Kb:
USE master
GO
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘index create memory’, 4096
GO
RECONFIGURE
GO
You can set the value of the ‘remote admin connections’ to 1 to allow remote clients
to use the dedicated administrator connection (DAC).
Even when SQL Server 2014 is locked or running in an abnormal state, the DAC lets an
administrator access a running server in order to execute diagnostic functions or
troubleshoot problems on the server. By default, the DAC is only available from a local
client on the server.
This is the example to allow client applications on remote computers to use the DAC:
USE master
GO
EXEC sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE
GO
Consider setting the ‘min server memory’ and ‘max server memory’ options.
These options can be used to specify the fixed amount of memory to allocate to SQL Server 2014.
In this case, you should set the ‘min server memory’ and ‘max server memory’ to the same value
(equal to the maximum amount of physical memory that SQL Server 2014 will use). This can improve
performance because SQL Server will not dynamically allocate memory. You can also change these
options when SQL Server works on the same computer with other applications. In this case, the
‘min server memory’ options is used to allow SQL Server works when other applications pretend
to use all available memory, and the ‘max server memory’ options is used to allow other
applications work when SQL Server tried to use all available resources.
Because the ‘min server memory’ and ‘max server memory’ options is an advanced option, you should
set the ‘show advanced option’ option to 1 to make these configuration options available.
This is the example to set the value of the ‘min server memory’ and ‘max server memory’
options to 2 GB:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘min server memory’, 2048
GO
EXEC sp_configure ‘max server memory’, 2048
GO
RECONFIGURE
GO
Set the ‘max worker threads’ options to the maximum number of the user
connections to your SQL Server box.
The default setting for the ‘max worker threads’ option is 0 that allows SQL Server 2014 to
automatically configure the number of worker threads at startup. This setting is best for
most systems; however, setting ‘max worker threads’ to a specific value sometimes improves
performance. For example, if the maximum number of the user connections to your SQL Server
box is less than 128, you can set the ‘max worker threads’ options to 128, this frees up
resources for SQL Server to use elsewhere. If the maximum number of the user connections to
your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500,
this can improve SQL Server performance because thread pooling will not be used.
Because the ‘max worker threads’ configuration option is an advanced option, you should set
the ‘show advanced option’ option to 1 to make the ‘max worker threads’ available.
Note. You must restart the SQL Server 2014 to apply changes to the ‘max worker threads’ option.
This is the example to set the value of the ‘priority boost’ option to 128:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘max worker threads’, 128
GO
RECONFIGURE
GO
Consider increasing the ‘min memory per query’ option to improve the performance
of queries that use hashing or sorting operations.
You can increase this option, if your SQL Server 2014 has a lot of memory available and there
are many queries running concurrently on the server. The default ‘min memory per query’ option
is equal to 1024 Kb. The SQL Server 2014 automatically allocates, at a minimum, the amount of
memory set in this configuration setting. Because the ‘min memory per query’ configuration
option is an advanced option, you should set the ‘show advanced option’ option to 1 to make
the ‘min memory per query’ available.
This is the example to set the value of the ‘min memory per query’ option to 4096 Kb:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘min memory per query’, ‘4096’
GO
RECONFIGURE
GO
You can change the ‘network packet size’ option to the appropriate value.
This option can improve performance on networks whose base topology supports larger packets
than TCP/IP default of 4096 bytes. For example, if client sends or receives large amounts
of data, a larger packet size can improve performance, because it results in fewer network
reads and writes. The default value for the ‘network packet size’ option is 4096 bytes.
Because the ‘network packet size’ configuration option is an advanced option, you should set
the ‘show advanced option’ option to 1 to make the ‘network packet size’ available.
This is the example to set the value of the ‘network packet size’ option to 8192 Kb:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘network packet size’, ‘8192’
GO
RECONFIGURE
GO
You can set the ‘clr enabled’ option to specify whether user assemblies can be run by
SQL Server 2014.
By default, the value of the ‘clr enabled’ option is 0 and assembly execution not allowed
on SQL Server. To enable assembly execution, set the ‘clr enabled’ option to 1.
Note. You must restart the WOW64 servers before the changes to this setting will take effect.
This is the example to enable assembly execution:
USE master
GO
EXEC sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
You can increase the value of the ‘recovery interval’ configuration option.
The ‘recovery interval’ option specifies the maximum number of minutes per database that
SQL Server 2014 needs to complete its recovery procedures. The default value of this option
is 0. It means that SQL Server will automatically configure this option. SQL Server issues
a checkpoint using the ‘recovery interval’ option. Microsoft does not recommend changing
this option in general case, but sometimes you can improve performance by changing this
option. You can monitor disk-write activity on the data files, and if you see periodic spikes
that send disk utilization to 100 percent, you can increase the recovery interval. In this
case, you can set the ‘recovery interval’ option to 5 and continuing monitoring.
This is the example to set the value of the ‘recovery interval’ option to 5 minutes:
USE master
GO
EXEC sp_configure ‘show advanced option’, ‘1’
GO
RECONFIGURE
GO
EXEC sp_configure ‘recovery interval’, 5
GO
RECONFIGURE
GO