Sp_Configure Options

EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

Sp_Configure Options

In a previous post I discussed sp_configure and how to use it. In this post I will be covering some of the options and their meanings. Many of the meanings are pretty straight forward but a few of them are a bit obscure. The options I will be looking at now are listed below. These are either options I have come across in preparation for the SQL Server 2008 MCITP certification or that I felt might be of practical value to know.

• cost threshold for parallelism
• max degree of parallelism
• max server memory (MB)
• min server memory (MB)
• index create memory
• min memory per query
• AWE enabled
• ad hoc distributed queries
• filestream access level
• backup compression default
  

Max Degree of Parallelism and Cost Threshold of Parallelism
The first two are a little obscure and might cause some confusion as they are obviously related, though how they are related might not be aparent at first. To begin with we will need to define the term parallelism. Parallelism is the ability for SQL Server to use multiple processors simultaneously. The max degree of parallelism option is configurable from 0 to 64. Setting this option to 1 will turn off parallel querying, setting it to 2 will limit the parallel querying to 2 processors, and so on. Setting max degree of parallelism to 0 allows SQL Server to use any number of processors. You can use WITH MAXDOP(INT) in your queries to override the server wide configuration shown by sp_configure.
SQL Server first checks for the cheapest possible seriel execution plan before it will consider parallelism. Once the Query Optimizer has determined the best possible seriel plan it compares the cost of that plan to the configured value of cost threshold of parallelism. If the seriel plan's cost is less than this value it executes the plan. If the cost threshold of paralellism is less than the cost of the seriel plan it will produce a parallel plan based on some assumptions about available memory and processor utilization. The cheapest plan will win. The number of threads used to execute the query is determined at query plan execution and is the Degree of Parallelism or DOP[1].

Further Reading For more information regarding how Windows executes and schedules threads please see Windows Internals 5th Edition, Chapter 5 Processes, Threads, and Jobs.

Max Server Memory and Min Server Memory
The main, i.e. largest, component to SQL Server's memory management system is known as the buffer pool. The buffer pool contains several types of objects (data bages, index pages, plans, etc) and the buffer manager is in charge of managing disk I/O for brining these objects into and out of the data cache. A buffer is a memory block that's the same size as an index page or data page and when a component of SQL Server requires memory it requests a buffer from the pool [2]. The buffer pool is shared among the various caches, such as the plan cache and the data cache. It's the job of the Memory Broker to manage the various requests for memory and ensure that SQL Server is using memory efficiently.
In a 32 bit system with 4 GB RAM Windows will reserve the highest 2 GB for its own use allowing SQL Server no more than 2 GB of available memory. This can be increased to 3 GB by adding the /3GB switch to the boot.ini. By using the Max Server Memory (MB) and Max Server Memory (MB) options you can limit the memory used by SQL Server. It should also be noted that the max value of 2,147,483,647 has nothing to do with the actual amount of memory in a system, but is simply the maximum value allowed by the int data type. Min Server Memory limits the smallest amount of memory that can be allocated to SQL Server and Max Server Memory limits the total possible amount which can be allocated.

As with any setting in sp_configure you should properly test and evaluate the effects that making any change will have before deploying on a production server.

Real World It's important to know that SQL Server will not release memory if doing so would allow the amount of memory allocated to SQL Server to drop bellow the configured value for Min Server Memory. This could have serious implications for systems running additional applications. Remember that SQL Server was designed with the expectation that it will be the only application running on the server. This reason may be why you might want to configure both Min and Max Server Memory to ensure that SQL Server always has sufficient memory to run properly and to ensure that other applications are also able to perform properly.

AWE Enabled

Here is the definition given by Wikipedia for AWE:

Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space. The process of mapping an application's virtual address space to physical memory under AWE is known as "windowing," and is similar to the "overlay" concept of other environments. AWE is beneficial to certain data-intensive applications, such as database management systems and scientific and engineering software, that need to manipulate very large data sets.

On a 32 bit system having this option enabled means that 32 bit versions of SQL Server will be able to access however much memory the edition of SQL Server allows. For Standard, Developer, and Enterprise this is the maximum the OS allows.

It should be noted that this option only applies to 32 bit systems, although this option exists on 64 bit systems, it is ignored[3].
Index Create Memory

According to the BoL 2008 article:
In SQL Server, the index create memory
option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.

Its default value is set to 0.

Min Memory Per Query

This is basically just what it sounds like. This option ensures that each query begins its life with at least this option's configured value of memory (in KBs) and it may then request more memory from the buffer pool.
It should be noted that this option takes precedence over the Index Create Memory Option discussed above. In SQL Server 2000 these were distinct options but as of SQL Server 2005 they interact.

Further Reading BoL 2008: If you alter both options and the index create memory is less than min memory per query, you receive a warning message, but the value is set. During query execution you receive another similar warning.

Ad Hoc Distributed Queries

The Ad Hoc Distributed Queries option allows you to use the OPENROWSET and OPENDATASET functions and is disabled by default for security reasons. These functions allow you to query other data sources that use OLE DB such as Excel and CSV files. This option is deactivated by default because when enabled any authenticated user can access the providers.

Real World Configure any data sources that need to be queried regularly as linked servers.

Filestream Access Level

Filestream is a new feature of SQL Server 2008 that allows for Binary Large Objects (BLOBs) to be stored on the NTFS file system in a manner that allows them to be transactionally consistent with the database but in such a way that it does not reduce SQL Server performance by taking up space in the buffer pool.
There are three levels to this option.

Config Value	Description
0	Disables FILESTREAM support for this instance.
1	Enables FILESTREAM for Transact-SQL access.
2	Enables FILESTREAM for Transact-SQL and Win32 streaming access.

The Enables FILESTREAM for Transact-SQL and Win32 streaming access level allows developers to access the Filestream data with a logical UNC path within a transactionally consistent framework. Because of the requirement for data to remain transactionally consistent, Filestreamdata cannot be renamed or deleted when accessed through the file system.

Backup Compression Default

This option controls the default settings for backup compression at the instance level. If, for example, you have a business requirement that states all backups need to be compressed to conserve disk space enabling this option will ensure that even T-SQL backup statements that do not explicitly declare WITH COMPRESSION will be compressed. This can still be over-ridden if the statement includes the WITH NO_COMPRESSION statement.

Real World Creating backups with compression is only supported in SQL Server 2008 Enterprise but any edition of SQL Server 2008 (Standard, Express, etc.) can restore compressed backups. Creating, and presumably restoring, compressed backups is a CPU intensive operation and before you do this you should make considerations for server load and utilization as it will affect the performance of SQL Server.

[1] – Pg. 71 - Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
[2] - Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
[3] – Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009

Bibliography
---
Daley, Kalen Microsoft SQL Server 2008 Internals, Microsoft Press; 1 edition, March 11, 2009
Itzik Ben-Gan , Etal. Inside Microsoft SQL Server 2008: T-SQL Querying, Microsoft Press; 1 edition, March 25, 2009
Russinovich, Mark and Solomon, David. Windows Internals: Including Windows Server 2008 and Windows Vista, Fifth Edition. Microsoft Press; 5 edition, June 17, 2009