Configuring SQL Server for Optimal Performance

From SQLServerPedia

Jump to: navigation, search

See Also: Performance Tuning

SQL Server has numerous configuration options that affect how it uses resources. These options are set at the server level - that is all connections and all databases on a particular instance of SQL Server will share this configuration.

SQL Server behavior can also be altered for a specific database or even a connection. Such options typically don't affect performance; rather they specify how SQL Server handles data, how data integrity is maintained in each database and whether some advanced features are used.

Configuring SQL Server

As before, you can configure an instance of SQL Server by executing sp_configure system stored procedure or through graphical tools: SQL Server Management Studio, SQL Server Configuration Manager and SQL Server Surface Area Configuration. Review all configuration options by querying sys.configurations view in master database. If the column "is_dynamic", has a value of 1, the option takes effect when you execute the RECONFIGURE statement. If the same column has a value of zero, restart SQL Server in order for the new value to take effect. Microsoft made an effort to make the large majority of configuration options dynamic - only 15 of 63 options require you to restart SQL Server service in order to take effect.

Configuration Option Description of the Option
user connections Total number of concurrent user connections allowed.
Locks Upper limit for number of locks that can be allocated for all sessions.
open objects Upper limit for concurrently open database objects.
fill factor (%) Default fill factor percentage for indexes.
remote access Boolean value showing whether remote access to the SQL Server instance is allowed.
max worker threads Upper limit for concurrent worker threads.
c2 audit mode Whether the instance is running in c2 audit mode compliant mode.
priority boost Shows whether the SQL Server process is running under elevated priority on Windows.
set working set size This option has no effect in SQL Server 2005, even though it appears in the list of options requiring restart.
media retention Tape retention period in days.
lightweight pooling User mode scheduler uses lightweight pooling (Windows fibers as opposed to threads) scan for startup procs Scan for startup stored procedures.
awe enabled Address Windowing Extensions (AWE) enabled in the server.
affinity I/O mask Affinity I/O mask limiting SQL Server tasks to a single processor or a subset of all processors on a multiprocessor server.
common criteria compliance enabled This new option introduced in Service Pack 2 of SQL Server 2005 assures SQL Server compliance with Common Criteria evaluation assurance level 4 for IT security.

You can also review available configuration options by executing sp_configure procedure without supplying any parameters. The output will show minimum and maximum values for each option, configured value and running value. If a configuration setting requires restarting the SQL Server service, then the configured value might differ from the running value. This means the service hasn't been restarted since the configuration setting value was changed. Those settings which do not require a restart can be applied by executing RECONFIGURE WITH OVERRIDE statement. For most settings running "RECONFIGURE" will suffice, but "RECONFIGURE WITH OVERRIDE" will work for all settings.

You will notice that by default sp_configure system procedure only returns 14 configuration options; this is because "is_advanced" column within sys.configurations view has a value of 0 for 14 configuration options. Before you can view or modify any other configuration option you must execute: 

sp_configure 'show advanced options', 1  
GO  
RECONFIGURE WITH OVERRIDE
Starting with SQL Server 2005 the preferred way of configuring database options is by using ALTER DATABASE statement. Although sp_dboption system procedure is still available it doesn't support any new options. This procedure is deprecated and will be removed in future releases of the software. You can also change database level configuration settings using SQL Server Management Studio from database properties' dialog.

Retrieved from "http://sqlserverpedia.com/wiki/Configuring_SQL_Server_for_Optimal_Performance"