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).