Microsoft’s SQL Server is an excellent database management system and it offers many features for simplified management. However, the out-of-the-box performance is not always acceptable and you may have to tune the system for specific scenarios. If you are experiencing high processor utilization on a SQL Server installation (which can be determined through the Task Manager, Resource Monitor or Performance Monitor), you can tune the system in three ways:

1. Install more processing power
2. Tweak the server settings
3. Optimize the databases

I won’t spend a lot of time on the first option in this post. Installing more processing power is a hardware issue (unless you are virtualizing your SQL Server) and should be considered a last resort. We want to tweak and tune our way to better performance when we can so that we avoid excessive hardware costs.

Tweaking server settings is mostly performed through SQL Server Management Studio (SSMS) and various Windows Server administration tools. When tuning a SQL Server for improved performance, you cannot forget the Windows Server on which it runs. Let’s start there.

Tuning the server begins with service management. You want to disable any unnecessary services. This includes SQL Server services. For example, if you are not running any named instances, make sure the SQL Server Browser service is not running. It is only used to resolve instance names to TCP ports and is unnecessary when only a default instance is used. Check your services (by running services.msc from the Start > Run dialog (on Windows Server 2003 R2 and earlier) or run it from the Start > Search field) and ensure that no unnecessary services are running.

Interestingly, to ensure you get acceptable processor performance, make sure you have sufficient RAM in the system. When you have insufficient RAM, virtual memory must be used. Reading and writing data from and to hard drives is an I/O intensive task, but it can also become processor intensive. Before you assume your processor is too slow for the job, make sure it has enough memory.

I like to use a simple analogy to illustrate this. Think of the processor (or CPU, if you prefer) as the worker. Think of the RAM as the worker’s skill set. Imagine the worker has won gold medals at the Olympics for several race categories. He is now working for a manufacturing company as an assembler. If the worker lacks the appropriate skill set, he will struggle to get the job done in a timely manner, regardless of how “fast” he is. In the same way, the fastest processors in the world can hit a performance roadblock if they are not provided with sufficient supporting resources, including RAM.

The next step for improving processor performance is to tweak the actual SQL Server instance settings. You can begin by viewing your current configuration settings. This can be done by right-clicking on the server you want to manage in SSMS and selecting Properties. From here, you will navigate through the different pages to see all the settings. Alternatively, you can open a New Query windows in SSMS and then execute the sp_configure command with no parameters. This will dump your configuration settings as a query result set. You will see the setting name, minimum and maximum values, the config_value and the run_value. The minimum value is the smallest value to which the setting may be set. The maximum value is the largest value to which it may be set. The config_value is the value stored for the named setting and the run_value is the current value in effect (this may be different than the stored configuration).

To change any configuration setting, modify it in the GUI of SSMS or using the sp_configure command with the following syntax:

sp_configure setting_name, setting_value

After executing the sp_configure command, you will have to execute the RECONFIGURE WITH OVERRIDE command. For example, to turn off nested triggers, execute the following code:

sp_configure ‘nested triggers’, 0
go
reconfigure with override
go

Some of the settings that you should consider modifying for processor performance impact are:

• Affinity mask (http://msdn.microsoft.com/en-us/library/ms180943.aspx)
• AWE enabled (http://msdn.microsoft.com/en-us/library/ms190731.aspx)
• Lightweight pooling (http://msdn.microsoft.com/en-us/library/ms178074.aspx)
• Priority boost (http://msdn.microsoft.com/en-us/library/ms180943.aspx)
• Locks (http://msdn.microsoft.com/en-us/library/ms175978.aspx)
• Default trace enabled (http://msdn.microsoft.com/en-us/library/ms175513.aspx)

Because I do not want to repeat useful information in this post, I’ve linked to MSDN blog posts that describe each of the above referenced settings. However, I should clarify why one particular setting is included in the list and that is the default trace enabled setting. This is a trace log file that is enabled by default. If you are really seeking to tune the SQL Server for the best performance results you can get, all logging (aside from security logs) should be disabled. You can always turn this log back on before making large scale configuration changes or to troubleshoot a recurring problem.

Now, the last component is database optimization. In this post, I will not go into details on database optimization because I will address it in a separate post. For now, just know that proper indexing results in improved performance; however, improper indexing results in poor performance. To blindly create random indexes is to beg for a performance disaster. Ok, that’s a hint. In my next post, I’ll explain why this is true. Until then, be sure to read through the posted articles I’ve referenced here and learn who you can tune these settings to improve your SQL Server performance.

By: Tom Carpenter has been working in IT for almost 20 years and has been training since 1997. He has written several books on various certification technologies such as network infrastructure technologies as well as server administration, SQL server, and will soon be releasing a book on SharePoint. Tom has taught over 30,000 students and currently teaches Windows 7 administration courses, Server administration courses, and Microsoft SQL Server administration courses.