- Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- The DBA Survival Guide: The 10 Minute SQL Server Overview
- Preparing (or Tuning) a Windows System for SQL Server, Part 1
- Preparing (or Tuning) a Windows System for SQL Server, Part 2
- Installing SQL Server
- Upgrading SQL Server
- SQL Server 2000 Management Tools
- SQL Server 2005 Management Tools
- SQL Server 2008 Management Tools
- SQL Azure Tools
- Automating Tasks with SQL Server Agent
- Run Operating System Commands in SQL Agent using PowerShell
- Automating Tasks Without SQL Server Agent
- Storage – SQL Server I/O
- Service Packs, Hotfixes and Cumulative Upgrades
- Tracking SQL Server Information with Error and Event Logs
- Change Management
- SQL Server Metadata, Part One
- SQL Server Meta-Data, Part Two
- Monitoring - SQL Server 2005 Dynamic Views and Functions
- Monitoring - Performance Monitor
- Unattended Performance Monitoring for SQL Server
- Monitoring - User-Defined Performance Counters
- Monitoring: SQL Server Activity Monitor
- SQL Server Instances
- DBCC Commands
- SQL Server and Mail
- Database Maintenance Checklist
- The Maintenance Wizard: SQL Server 2000 and Earlier
- The Maintenance Wizard: SQL Server 2005 (SP2) and Later
- The Web Assistant Wizard
- Creating Web Pages from SQL Server
- SQL Server Security
- Securing the SQL Server Platform, Part 1
- Securing the SQL Server Platform, Part 2
- SQL Server Security: Users and other Principals
- SQL Server Security – Roles
- SQL Server Security: Objects (Securables)
- Security: Using the Command Line
- SQL Server Security - Encrypting Connections
- SQL Server Security: Encrypting Data
- SQL Server Security Audit
- High Availability - SQL Server Clustering
- SQL Server Configuration, Part 1
- SQL Server Configuration, Part 2
- Database Configuration Options
- 32- vs 64-bit Computing for SQL Server
- SQL Server and Memory
- Performance Tuning: Introduction to Indexes
- Statistical Indexes
- Backup and Recovery
- Backup and Recovery Examples, Part One
- Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
- SQL Profiler - Reverse Engineering An Application
- SQL Trace
- SQL Server Alerts
- Files and Filegroups
- Full-Text Indexes
- Read-Only Data
- SQL Server Locks
- Monitoring Locking and Deadlocking
- Controlling Locks in SQL Server
- SQL Server Policy-Based Management, Part One
- SQL Server Policy-Based Management, Part Two
- SQL Server Policy-Based Management, Part Three
- Microsoft SQL Server Programming
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server Configuration, Part 1
Last updated Oct 8, 2010.
SQL Server is a complex platform for applications, which means that configuring your environment isn't limited to the SQL Server software alone. You have to take all the factors into consideration, such as the hardware each server is running, the network, and even the architecture of the application. In this tutorial, I explain configuration options that are pertinent in architectures found in mainstream applications today. I’ll cover SQL Server versions 2000 through 2008 R2, but not for SQL Azure. SQL Azure starts your environment at the database level, not the Instance level. If you’re not familiar with the idea of a SQL Server Instance, check out this article first.
SQL Server is largely self-tuning. It's normally best to allow SQL Server to dynamically adjust the settings it maintains and not take the reins, but it can be useful to know what each setting does and how it affects the platform. Don't make changes to any server without understanding what the effect will be on your environment. In other cases, it’s important to make changes to environment based on your needs. In both cases, you need to know what the settings do to your server to make the right choice.
Once you understand the settings do and when you should use them, you can adjust the settings for SQL Server using either Transact-SQL commands or by graphically changing them using the graphical management tools. For SQL Server 2000 and lower, that’s Enterprise Manager, and in SQL Server 2005 and higher, it’s SQL Server Management Studio (SSMS). I’ll focus first on the graphical tools, and then explain the commands you can use once you understand what each graphical area does.
In this tutorial I’ll use SSMS, but to access these types of settings graphically in SQL Server 2000 and earlier, just use Enterprise Manager, right click the name of the Instance, and select “Properties” from the menu that appears. In SSMS, connect to the Instance you want to change, right-click the name of that Instance in the Object Explorer pane, normally found on the left-hand side of the SSMS tool. Once the menu appears, select “Properties” from the menu that appears. That will bring you to the “General” tab.
The General tab shows a lot of information about your server, such as how many processors it has and the OS level. (You can get some of this same information with the T-SQL command EXEC xp_msver) In SQL Server 2005 and higher, this is a read-only panel. You’ll notice the data on the right is grayed-out, and you can’t edit it.
In SQL Server 2000, you can set options such as whether the server will auto-start, as well as the SQL Server Agent and the MSDTC service. Depending on how your application is set, you probably want all three; in the case of 3-tier or N-tier architectures, you'll want MSDTC to auto-start. This service provides for Distributed Transactions, something those architectures depend on.
Also in version 2000, the Network Protocol section allows you to tie out the server to the protocols on which it will listen. Order isn't important here, since the server listens to whatever the client sends. Order is important at the client. Network and services functions are handled in the SQL Server Configuration Manager tool in SQL Server 2005 and higher.
The next tab is “Memory.” More than just about any other tuning you can do, the memory settings have the greatest affect on your system. I assume that you've got plenty of RAM on the server; 2GB is just about the lowest I recommend, even for small applications.
The number sections (2005+) and sliders (2000-) on this tab set the lower and upper boundaries that SQL Server will work with. If your server is only running SQL Server, I normally recommend a fixed setting (setting the upper and lower boundaries the same), leaving the OS at least 1 GB of RAM. You might be running the middle tier on this server if you're in a small environment. In that case, ensure that you leave enough RAM for that software to run.
If you do leave a minimum and maximum value for the memory, SQL Server will automatically use as much memory as it needs, and release that memory back to the operating system when the OS needs it or SQL Server does not. The reason I advocate setting a fixed amount on the RAM is so that SQL Server won't have to constantly adjust itself while it's running. It's got enough to do already, without adding work.
You can also set a fixed amount for queries, but it's best to leave this at the default. SQL Server is normally better at managing that. In SQL Server 2005 and higher, you can also change the memory used to create indexes, but once again the default (0, in this case) is most often the correct choice.
On this tab and most that follow, you also see that you can change the settings to reflect "running values." That means that SQL will look at what is in place at this moment, and adjust the sliders to those levels. Once you change the settings, they are the new “configured” values. Some of these need a restart for the new setting to take effect, and in others it doesn’t. Since this behavior changes from release to release, it’s best to read up on any setting you plan to change to find out whether that’s needed or not.
The next tab sets the processor options. The first option allows you to set which processors SQL Server uses.
This is the only Microsoft product that I'm aware of that has this setting, although with other products you can locate their “process” using the Windows Task Manager and change the processors there. I don’t recommend you do that in either place, unless you need a processor or two left out for use by other applications. I’ve also seen this setting altered when only a few of the processors have been licensed for SQL Server.
The Microsoft Windows operating systems use Symmetric Multi-Processing (SMP). This means that all processors in a system are treated as one. Other operating systems are Asymmetric Multi-Processing (AMP), meaning that programs must be coded to run on one or more processors specifically.
Both architectures have advantages. SQL Server claims its heritage from the UNIX world (when Microsoft licensed the engine from Sybase) and so has the ability to be tied to a particular processor.
I've seen shops use three of four processors in a system this way. Unless you've installed the middle-tier on the SQL Server, I don't recommend this. I think that using all the processors is the best way to go, something I've seen proven in experience.
In some cases you’ll see more processors than you physically have in the server, because of “multi-core” or hyper-threaded processors. As of this writing, SQL Server is licensed per physical socket, so you don’t have to pay extra for those “enhanced” processors. As always, this is subject to change, so be sure you check your licensing documentation so that you stay legal.
Another setting you can change here are worker threads, which is the number of threads the system uses for each connection. It's set by default to 255 in early versions of SQL Server, and 0 in later versions, which means the system manages the connections. Changing this setting means that each connection get a thread. After the number you specify, the system starts pooling the connections, which might drive up processor context switches, which can be bad. If you have a constant number of connections, adjust this number a bit higher than the connections. Any higher, and you're wasting resources; any lower, and you're creating too many threads.
If you're running in a 3-tier or N-tier architecture, make sure you average the connections over a long period of time. Connections from these architectures pool and break, so a single measurement, or even a few isn't adequate.
You can also boost the priority of SQL Server on the operating system, which at first might seem like a good thing. If you're using a third-party backup solution, it might be best to leave this alone so that the backup takes priority during the run and gets done faster.
The next option allows you to change from threads to fibers. Switching threads from user to kernel mode can be expensive from an operating system standpoint. Fibers make up threads, and aren't managed by the OS.
Setting this switch allows SQL Server to manage this switch, which is less expensive. But if you do, then you've added workload to SQL Server, which is usually bad. The best thing to do is watch the Performance Counter of Processor:Context Switches. If this number is into the tens of thousands, switch to fibers and see if performance improves. This isn't a necessary exercise, it's still usually best to leave this at the defaults.
In SQL Server 200 and lower, the final area on the Processor tab allows you to set the way that queries will parallelize. I recommend that you use all your processors to do this kind of work. If SQL Server determines that a query plan would benefit from parallelization, give it room to do so. Again, the defaults are normally best here.
You can also set how "deep" SQL Server will look to see if it can parallelize a query. The default of 5 is the right choice for almost every instance. If it has to look deeper than that, you might want to re-factor your query.
In the next tutorial, I'll continue the server settings with the rest of the tabs.
InformIT Articles and Sample Chapters
The article Windows Processes and Threads: Weaving It All Together discusses NT threads in depth.
Books and eBooks
I cover these settings in more depth for SQL Server 2005 in my book Administrator's Guide to SQL Server 2005.
Each setting is explained in more depth in Books Online.