SQL Server Configuration
You are reading this SQL Server configuration article for one of 2 reasons. Either you have just installed it and want to get it right from the start, or you have inherited an existing environment and want to check it is set up properly.In this article I have not described more advanced setups, such as clusters, replication, etc. This article is designed as an overview of a basic, one-server, OLTP environment.
I have categorized configuration options into server options, disk configuration and startup options.
I won't go into too much detail here as a lot of these deserve entire articles to themselves. The information in this article should be sufficient to give you an understanding of what each option is for, and whether you should be worrying about it at present.
Server Options
The first and most important point to make is that SQL Server runs very efficiently with the default configuration. If you have just installed it please leave the options alone unless you have good reason to believe a change is needed. If you are auditing an existing system and suspect that poor performance may be due to non-default configuration options, you should consider restoring the defaults. You will obviously want to test any changes on a staging environment first.Most SQL Server configuration options can be viewed and changed through Management Studio or Surface Area Configuration, but it is easier to use sp_configure.
You can view all options using one of the following
sp_configure 'show advanced options', 1 go reconfigure go sp_configure go
Or
select * from sys.configurations go
affinity mask
The default value is 0. Please do not change this unless you fully understand what you are doing and have tested the impact on performance first.The default value allows the operating system to allocate CPUs to threads. Changing this will prevent SQL Server from using some CPUs. On most systems with 4 or less CPUs and no applications other than SQl Server this will only make things worse.
affinity io mask
This is similar to affinity mask except that it allows CPUs to be excluded from processing IO. There are very few situations where this will benefit performance and plenty where it will make it worse. Please leave it at the default of 0.cost threshold for parallelism
Default value 5.Generating an execution plan using parallelism (running on multiple CPUs) often takes longer than one using a single CPU. For this reason SQL Server only considers a parallel plan if it thinks it will take more than 5 seconds.
This configuration option allows you to change this threshold.
fill factor
Default value 0.Setting this affects the fill factor of all indexes created on the server, unless fill factor is specified in the CREATE INDEX statement. I suggest leaving this at the default option to prevent others from creating indexes unaware of your change.
index create memory
This option controls the initial amount of memory allocated to an index creation operation. The default value is 0, which allows SQL Server to set this itself. As with most configuration options the default value gives good performance in most cases.If you get an error creating large indexes, or find that they take a long time to create, it may be worth experimenting with this option. Bear in mind that this should be greater than the min memory per query option or warnings are generated.
locks
Every lock in SQL Server uses 96 bytes of memory. Initially space is allocated for 2500 locks, but this is increased if required up to a maximum of 60% of the memory pool. Setting a value in this option imposes a maximum on the number of locks that will be acquired. There are very few situations in which you need to set this.max degree of parallelism
On a server with multiple CPUs, the query optimizer evaluates each query to see if it would benefit from using parallelism. If your server is used exclusively for OLTP applications, with little or no complex SELECTs, you will probably not benefit from parallelism. In that case setting this to 1 may be beneficial.max worker threads
By default SQL Server uses 255 worker threads for user processes. If there are more user processes than this it uses thread pooling, meaning that the same threads may handle more than one process. If the number of users never exceeds this value, leave it as it is. If the number of users regularly exceeds 255, and there is memory to spare, consider increasing it.min and max server memory
min server memory and max server memory are used to control how much memory the SQL Server instance uses. The default values allow SQL Server to dynamically manage the memory and it is recommended that you do not change these options in most cases.Ideally, the server should be dedicated to SQL Server. This means that memory is only used by SQL Server and the operating system. The best performance will be obtained with the default options. However, if there is another application running on the server, and its memory requirements are known, you may wish to set the maximum server memory option.
There are no circumstances, in my opinion, where the minimum server memory should be used. When the SQL Server service first starts, it only uses as much memory as it needs. Once memory usage has exceeded the minimum server memory option, it will not drop back below that value.
In SQL Server 2000 you could set both to the same value, and set set working set size to 1. This forced a fixed, unvarying amount of memory. This option is no longer available in SQL Server 2005.
min memory per query
SQL Server allocates a minimum amount of memory to a query when it runs. This defaults to 1024 KB. Only increase this if you have an excess of memory, but I have never found it makes much difference.priority boost
Do not use this option. The purpose of this option is to give SQL Server a boost when sharing a server with other applications. Aside from the fact that it makes barely any difference to performance, it has also been known to introduce instability. I recommend that you do not use it.recovery interval
This option determines the period of time between checkpoints. When a checkpoint occurs, all inserts, updates and deletes since the last checkpoint are flushed to disk. This results in extra write activity to the disks, and additional overhead on the server. Extending the period between checkpoints will always improve performance, but will increase the recovery time when the SQL Server service is restarted.The default value is 0, which allows SQL Server to determine the interval itself. By setting this you are specifying the maximum number of minutes for recovery. So a value of 2 equals a 2 minute recovery time.
As always, you will need to experiment to find the best compromise. The default value gives good performance in most cases.
Disk Configuration
The following is intended as a quick list of recommendations. For more detail there are numerous articles on SQL Server configuration, both on the Microsoft website and in various books.
Multiple smaller disks are preferable to fewer large ones.
Try to use as many IO channels as possible.
Correct choice of RAID level is important.
Use RAID 1 for operating system files and SQL Server binaries. These can coexist on one drive (normally the C drive).
Log files are written sequentially, with no random access so do not benefit from striping. They also need maximum write performance, so RAID 1 is the obvious choice.
The mdf files can use RAID 5, RAID 1 or RAID 10, depending upon how much money you have! I personally try to avoid RAID 5. It has fast read performance, but there is an overhead when writing (to calculate the parity bit). Some implementations can slow down considerably when a disk fails as they use the parity bits to derive the missing data.
Try to use a separate RAID controller for each RAID array.
Spread the load evenly across physical spindles by having 0.25 to 1 file per CPU core in each filegroup. As well as increasing performance through striping, it also makes full use of the CPUs during IO operations.
All data files in a filegroup should be the same size. SQL Server uses a proportional fill algorithm to decide which file to allocate pages to. Keeping all files the same size ensures the load is evenly distributed.
tempdb files should be on their own RAID array. The principle of spreading across physical spindles also applies here, except you should have exactly 1 file per CPU core.
The log file(s) should ideally be on their own RAID array as well, although on systems with lower throughput they can share an array with the data files.
Is this realistic? Probably not; from the above it quickly becomes apparent that a lot of disks are needed for optimum performance. For a server with 2 databases and 4 dual core CPUs you need a minimum of 2 databases * (0.25 * 8 CPU cores) + 8 (for tempdb) + 2 (for the logs) + 1 (OS etc). Assuming RAID 1 this is then doubled, giving a total of 30 physical spindles! Some compromises may be needed.
Startup Options
Startup options are specified using SQL Server Configuration Manager. Each option consists of - followed by the option and then a semicolon.I only list options here that could affect performance.
-g memory_to_reserve
Reserves non pool memory. The parameter specifies the number of MB to reserve. Non pool memory is used by the SQL Server process for extended stored procedures, distributed queries and COM objects. Only set this if one or more of these are used.
Incorrect usage of this could adversely affect performance, and even prevent SQL Server from starting up.
-T trace flag
Trace flags have a variety of purposes, ranging from enabling monitor of certain events, to performing configuration changes that may improve performance. For example -T1118 can improve performance when there is heavy use of the tempdb database. (Warning, just seen this http://support.microsoft.com/kb/936185 that casts doubt on this. As always test any configuration changes first.
-x
Disables the recording of statistics for cache hit ratio and CPU time, and does improve performance slightly.
This article has listed and described some of the SQL Server configuration options you should be aware of. Please remember that for most small or medium-size systems the default SQL Server configuration will probably be adequate, but you should by now be aware of ways in which things can be improved.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!