SQL Server 2005 Server Configuration Performance Audit Checklist : Part 1

Performance Audit Checklist

Enter your results in the table above.

Most SQL Server 2005 Configuration Settings Should Not Be Changed

In this section, we are going to take a look at some of the . These are SQL Server-specific settings that can be modified using Management Studio, SP_CONFIGURE, and in some cases, the Surface Area Configuration Tool.

As the title of this section says, in most cases, you should not modify the default SQL Server 2005 configuration settings. This is because most of the default settings provide the optimum performance for most SQL Servers. And most of all, if you are not exactly sure of what the implications are of changing a setting, it is possible to hurt your server's performance instead of boosting it.

If this is the first time you have dealt with a particular SQL Server, one of your first steps to become familiar with it should be to review the various configuration settings and compare them to the default settings to see which ones, if any, have been changed from the defaults. Once you have identified any of the changed settings, your next goal should be to find out why they were changed. If you can't find out why, or if you do find out why, but the reasoning behind the change is flimsy, then you may want to change the settings back to the default values. Once you have done this, your next step is to review all of the other settings (those that were set to default when you started) and evaluate each one in order to see if there might be a benefit of changing the value from the default value to a more appropriate value for your server’s unique needs (if it has any).

In SQL Server 2000, there were 36 different SQL Server configuration settings. In SQL Server 2005, there are now 63 configuration settings, a huge jump. Why the big increase? It’s because DBAs like you have been asking Microsoft for more “knobs to tweak” so that you can have more granular control of your servers. Microsoft has responded to this request by giving you lots of choices. Maybe too many choices, as tweaking any of these settings has both the potential to boost, or hurt, the performance of your server. The more settings you can change, the higher the possibility you may make a mistake if you are not careful.

As part of this performance audit checklist, we will not be covering all 63 settings. This is because many of them are not directly performance related. Our focus is examining those SQL Server configuration settings that can most significantly influence overall SQL Server 2005 performance.

Getting Started

The easiest way to begin your audit of a SQL Server's configuration settings is to run the following command, for each of the SQL Servers you want to audit, using Management Studio.

SP_CONFIGURE

When you do, you will get a result similar to the one below. The configured value (config_value) and the actual running value (run_value) you see below may be different than your server. In addition, you may not see all 63 configuration options as you see below, instead only seeing 14 configuration options. For the moment, ignore this.

The first column, "name," is the name of the SQL Server configuration setting. The second column, "minimum," is the smallest legal value for the setting. The third column, "maximum," is the largest legal value for the setting. The fourth column, "config_value," is what the setting has been set to (but may or may not be what SQL Server is actually running now. Some settings don't go into effect until SQL Server has been restarted, or until the RECONFIGURE WITH OVERRIDE option has been run, as appropriate.) And the last column, "run_value," is the value of the setting currently in effect. If you have not changed any of these values since the last time you restarted SQL Server, then the values in the last two columns will always be the same.

Unfortunately, the default values for these settings are not listed when you run SP_CONFIGURE. For your convenience, this article lists the default values of those configuration settings (see the chart at the very top of this article).

How to Change SQL Server Configuration Settings

Most, but not all of the SQL Server configuration settings can be changed using Management Studio or the Surface Area Configuration Tool. But one of the easiest ways to change any of these settings is to use the SP_CONFIGURE command, like this:

SP_CONFIGURE ['configuration name'], [configuration setting value]
GO
RECONFIGURE WITH OVERRIDE
GO

where:

configuration name = The name of the configuration setting (see the name in the table above). Note that the name must be enclosed in single quote marks (or double quote marks, depending on Management Studio’s configuration).

configuration setting value = The numeric value of the setting (with no quote marks).

Once SP_CONFIGURE has run, you must perform one additional step. You must either run the RECONFIGURE option (normal settings) or the RECONFIGURE WITH OVERRIDE option (used for settings that can get you into trouble if you make a mistake), otherwise your setting change will not go into effect. Rather than trying to remember when to use each different version of the RECONFIGURE command, it is easier to just use RECONFIGURE WITH OVERRIDE all the time, as it works with all configuration settings. If you use Management Studio or the Surface Area Configuration Tool to change a setting, it will execute RECONFIGURE WITH OVERRIDE automatically, so you don't have to.

Once you do this, most, but not all, settings go into effect immediately. For those that don't go into effect after RECONFIGURE WITH OVERRIDE, the SQL Server service has to be stopped and restarted. Be sure you don’t do this during the day on a production system with active users. The chart at the first of the article tells you which options require a SQL Server restart before they take effect.

Before we are finished with this topic, there is one more thing you need to know. Some of the configuration settings are considered "advanced" settings. Before you can view or change these options using the SP_CONFIGURE command, you must first change one of the SQL Server configuration settings to allow you to view and change advanced settings. For example, if you ran SP_CONFIGURE and only 14 options, not all 63 were listed, you need to run the following command. The command to do this is:

SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO

Only after you have run the above code may you now run SP_CONFIGURE to view and to change any of the 63 SQL Server configuration setting.

Now that you know how to change SQL Server configuration options, let's take a look at those that are related to performance.

Affinity I/O Mask

This is a new setting added in SQL Server 2005.

The affinity I/O mask configuration option binds SQL Server disk I/O to a specified subset of CPUs in a server. In busy SQL Server OLTP environments, this feature may enhance the performance of SQL Server threads issuing I/Os.

The value for the affinity I/O mask specifies which CPUs in a multiprocessor computer are eligible to process SQL Server disk I/O.

The values for affinity I/O mask are as follows:

• A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.
• A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.
• A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.
• A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.

A 1 bit in the affinity I/O pattern specifies that the corresponding CPU is eligible to perform SQL Server I/O operations; a 0 bit specifies that no SQL Server I/O operations should be scheduled for the corresponding CPU. When all bits are set to zero, or an affinity I/O mask is not specified, SQL Server I/O is scheduled to run on any of the CPUs eligible to process SQL Server threads.

When specifying the affinity I/O mask, you must use it with the affinity mask configuration option. Do not enable the same CPU in both the affinity I/O mask switch and the affinity mask option. The bits corresponding to each CPU should be in one of these three states:

• 0 in both the affinity I/O mask option and the affinity mask option.
• 1 in the affinity I/O mask option and 0 in the affinity mask option.
• 0 in the affinity I/O mask option and 1 in the affinity mask option.

Because setting the SQL Server affinity I/O mask option is very specialized, it should be used only when necessary. In most cases, the default affinity provides the best performance.

Affinity Mask

When SQL Server is run under Windows Server, a SQL Server thread can move from one CPU to another. This feature allows SQL Server to run multiple threads at the same time on multiple CPUs, generally resulting in better load balancing among the CPUs in the server. The only downside to this process is that each time a thread moves from one CPU to another, the processor cache has to be reloaded, which can hurt performance in some cases.

In cases of heavily-loaded servers with more than 4 CPUs, and running either more than one instance of SQL server, or one instance of SQL Server and other applications, performance can often be boosted by specifying which processor(s) should run a specific thread. This reduces the number of times that the processor cache has to be reloaded, helping to eek out a little more performance from the server. For example, you can specify that SQL Server will only use some of the CPUs, not all of the CPUs that are available to it in a server.

The default value for the "affinity mask" setting, which is "0," tells SQL Server to allow the Windows Scheduling algorithm to set a thread's affinity. In other words, the operating system, not SQL Server, determines which threads run on which CPU, and when to move a thread from one CPU to another CPU. In any server with 4 or less CPUs, the default value is the best overall setting. And for servers with more than 4 CPUs and running only a single instance of SQL Server, and that are not overly busy, the default value is also generally the best overall setting for optimum performance.

But for servers with more than 4 CPUs, that are heavily loaded, and are running multiple instances of SQL Server, or one instance of SQL Server and other applications, then you might want to consider changing the default value for the "affinity mask" option to a more appropriate value. Please note that if SQL Server is the only application running on the server, then using the "affinity mask" to limit CPU use could hurt performance, not help it.

For example, let's say you have a server that is running SQL Server, along with IIS. Let's also assume that the server has 8 CPUS and is very busy. By reducing the number of CPUs that can run SQL Server from 8 to 4, what will happen is that SQL Server threads will now only run on 4 CPUs, not 8 CPUs. This will reduce the number of times that a SQL Server thread can jump CPUs, reducing how often the processor cache has to be reloaded, helping to reduce CPU overhead and potentially boosting performance somewhat. The remaining 4 CPUs will be used by the operating system to run the non-SQL Server applications, helping them also to reduce thread movement and boosting performance.

For example, if you have an 8 CPU system, the value you would use in the SP_CONFIGURE command to select which CPUs that SQL Server should only run on are listed below:

Decimal Value Allow SQL Server Threads on These Processors

1 0
3 0 and 1
7 0, 1, and 2
15 0, 1, 2, and 3
31 0, 1, 2, 3, and 4
63 0, 1, 2, 3, 4, and 5
127 0, 1, 2, 3, 4, 5, and 6
255 0, 1, 2, 3, 4, 5, 6, and 7

Specifying the appropriate affinity mask is not an easy job, and you should consult the SQL Server Books Online before doing so for additional information. Also, you should test what happens to your SQL Server's performance before and after you make any changes to see if the value you have selected hurts or helps performance. Other than trial and error, there is no easy way to determine the optimum affinity mask value for your particular server.

As part of your audit, if you find that an affinity mask is being used, try to find out why. If there are no good answers, remove it, and return to the default value.

Awe Enabled

This section only applies to the 32-bit version of SQL Server 2005.

If you are using SQL Server 2005 and if your server has 4GB or less of RAM, the "awe enabled" option should always be left to the default value of 0, which means that AWE memory is not being used.

If your server has SQL Server 2005 Standard or Enterprise Edition, the amount of RAM it can access depends on the OS Edition your server is running, along with how much physical RAM is available in the server.

For example, below is a list of how much maximum RAM is supported by the various editions of Windows Server 2003.

• Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (GB).
• Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
• Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

So if you are running SQL Server 2005 Standard or Enterprise Edition under Windows 2003 Server Standard Edition, the maximum amount of RAM available to SQL Server is 4 GB. But if you are running SQL Server 2005 Standard or Enterprise Edition, then it can access up to 32 GB if your are running Windows 2003 Enterprise Edition, or access up to 64 GB of RAM if you are running Windows Server 2003 Datacenter Edition.

In order for SQL Server 2005 Standard and Enterprise Editions to be able to access more than 4 GB of RAM (besides having the correct editions of the OS), you must make two setting changes: one in the OS and the other in SQL Server. More on this soon.

The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2003 Enterprise Server or Windows 2003 Datacenter Server, to access more than 4GB of RAM. Both SQL Server 2005 Standard and Enterprise Editions are AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2003 Enterprise Server, SQL Server 2005 Standard and Enterprise Editions can use up to 32 GB of RAM. If the operating system is Windows 2003 Datacenter Server, SQL Server 2005 Standard and Enterprise can use up to 64GB of RAM.

Exactly how you configure AWE memory support depends on how much physical RAM your server has. Essentially, to configure Windows 2003 to use AWE memory, you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:

4GB RAM: /3GB
8GB RAM: /3GB /PAE
16GB RAM: /3GB /PAE
16GB + RAM: /PAE

The /3GB switch is used to tell the OS to allow SQL Server 2005 (Standard or Enterprise Editions) to take advantage of 3GB out of the base 4GB of RAM that Windows 2003 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

AWE memory technology is used only for the RAM that exceeds the base 4GB of RAM, that's why the /3GB switch is needed to use as much of the RAM in your server as possible. If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to fully take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has 16GB or more of RAM. If less than 16GB of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.

Once this step is done, the next step is to set the "awe enabled" option to 1, and then restart the SQL Server service. Only at this point will SQL Server be able to use the additional RAM in the server.

As part of your audit process, you will want to check what these settings are and then determine if the setting matches your server's hardware and software configuration. If not, then change the settings appropriately.