SQL Server Reference Guide

SQL Server runs on Windows. It doesn’t run on anything else (if you don’t count Windows Mobile), so before you even install SQL Server there are some things you should tune in Windows to make the system run fast and stable.

And of course Windows runs on hardware. So even before you tune Windows, you need to have the best hardware you can afford for your system.

If you’re getting ready to install SQL Server on a new Windows system — stop. Read this article, open the links I mention, and take time to fully understand what your needs are. If you already have SQL Server installed on a system, that’s OK, you can read along as well. You might find some things you can change to tune your system.

Before I begin, it’s important to understand that this article is VERY specific to three things: the hardware I’m setting up today, the Windows 2008 Enterprise operating system, and SQL Server 2008 Enterprise with Service Pack 1. Does that mean you can’t use this information if you’re not using exactly the same hardware I am, you’re running another version of Windows, or have a different version or edition of SQL Server? Not at all — but it does mean you shouldn’t apply what you’ve learned here without understanding it first. Much of this information will be very useful to you even with different hardware, operating systems or SQL Server versions and editions. It’s just that you should balance what you learn here with what I have.

Speaking of that configuration let me briefly explain my system and the goals I have in this exercise.

The Hardware

As I’ve mentioned before, if you’re going to implement mission-critical software (and almost all software is, these days) then you should use server-class equipment. Yes, the same processor, memory and network cards can be found in workstation class machines, and yes, those are a LOT cheaper. But everything from the motherboards to the power supplies on a true server-class system is thicker, stronger, lasts longer, and is designed not to be turned off — ever. And I always install 64-bit processors — it’s even hard to find a server that isn’t anyway. The speed, memory and other architectures here just make that decision a no-brainer.

But sometimes you have to play the hand you’re dealt. In my case I’m implementing a proof-of-concept system, meaning that we want to go to the next level of testing (we already made sure the application really works) for a single department to run the application so that we can get some load testing and so on. I chose to go with a faster workstation class machine than an older server, since I will have the same kind of processors and memory in the server that I do in this machine. I wasn’t able to specify the machine, but I was able to get more memory and another hard drive in it at least.

I’m have a Dell Precision WorkStation T7400 using a Xeon Dual Processor, with 8 GB of RAM and two 250GB internal hard drives. It has a Broadcom NetXtreme Gigabit Ethernet Adaptor (built in). I use this system in a small department using database On-Line Transaction Processing (OLTP) workloads on a standard Gigabit Ethernet network, and I’m the administrator on the system. I am responsible for configuring everything, but I didn’t get to specify the purchase of this system.

Figure 1

When we do get ready for production, I’ll take the data from the monitoring I do here and design the final hardware. It will have a high speed CPU, and I’ll decide then how many I need. I’ll also set the memory size, speed and type at that time, all based on my monitoring. The most difficult choices in a server are the network interfaces and the I/O subsystem, or storage.

You might think that the network cards choice is easy, because almost all servers have them built right in to the motherboard. But the reason that the choice is difficult choice is that almost all servers have them built right in to the motherboard, and they don’t always do what I want. So I have to argue for another NIC, even thought the company has already paid for one or more of them with the purchase of the server. But I have some specific needs for mine — I want a card that has a lot of options, and one that is 64-bit compatible. I like the PCI-E specification (as of this writing) for my bus at a minimum.

For the I/O (or storage) subsystem, there are two considerations. One is the internal storage, the other is the external storage. In the case of external storage, the world of SANs, NAS and iSCSI is another topic entirely, and something the DBA doesn’t always get to specify anyway.

Moving on to the internal (or at least Directly Attached Storage) area, there are more things to control. At the very least, the drives should be very fast — the faster the spin rate the better. They should also have a wide access path, so whether it is a SCSI, PCI or even Flash drive, ensure that the throughput is the highest you can afford. And as always, more drives are better — it allows the system to work on multiple things at once, and that’s a good thing for one of the slowest parts of your system.

I’ll explain what I’ve done with all of these on my system as I progress through this article.

Preparing for the Operating System Installation

In almost any decision, I try to start with the end in mind. I know that the application will work best with some of the features in SQL Server, so that’s my back-end. Several of those features are specific to SQL Server 2008, so that’s my version. I looked into that version and found that I wanted features like Resource Governor, the Data Collector, Transparent Encryption and Compression, so I chose the Enterprise Edition.

With those choices made (quite logically, I hope!) I researched the Operating System optimizations that would benefit SQL Server the most. I found this article that talked about the performance enhancements in Windows 2008 that SQL Server 2008 leveraged: http://msdn.microsoft.com/en-us/library/dd263442.aspx. I then researched that Operating System, and determined that the Enterprise Edition was what I needed here as well, so that factored into the cost of the solution. I want to be sure I can present a realistic budget to management, so that they can calculate the Return On Investment (ROI) for this solution. Sure, I can make the budget look better by using lower SKUs of both Windows and SQL Server, but this is the best way to go in my estimation of the project. Again, I’ll work with what I have at the end of the day.

Next, I found this fantastic whitepaper on Windows Server 2008 tuning: http://www.microsoft.com/whdc/system/sysperf/Perf_tun_srv.mspx. It actually forms the basis for a lot of the decisions I’ll implement in this article. I read it cover to cover and highlighted the parts that mattered to me.

The point of all this is that even if you’re installing another version of the operating system or SQL Server, this is a process you can follow. Lay out your requirements, and then research the back-end and OS to match up what will work. Lots of reading, lots of searching. But the result is worth it.

The next step was to layout the drives. For the optimal performance, I would need a LOT more drives, since I like to separate the following (at a minimum):

• Operating System Binaries
• OS Page Files (don’t need this to be on a fault-tolerant system)
• SQL Server Binaries
• SQL Server Data Files (usually even multiple drives here)
• SQL Server Log Files
• SQL Server Indexes
• SQL Server Backup Files
• SQL Server Filestream, Replication, ETL, etc. files

But of course I don’t have that many drives, so I’m going to put the Operating System, Page Files, SQL Server Binaries and SQL Server Logs on one drive, and everything else on the other. That will allow me to at least do some representative testing.

Installing and Configuring the Operating System

With everything in place, the installation of the Operating System was pretty much next-next-finish. But I did follow the best practices on the Installation as recommended by the install documentation. The only thing I ensure here (and it really isn’t out of the ordinary anyway) is that I only use the NTFS file system. I think this is pretty obvious, but no server should use anything else.

After the installation, I installed my virus-scan software. I do this even before I connect to the network, because I don’t want anything strange to happen to my system.

And important note here is that I configure my virus scan software to exclude my SQL Server files. They are going to be on a protected directory, and they are owned and controlled by the account that starts SQL Server, so they shouldn’t be held to the overhead of being scanned. Even though it isn’t installed yet, I configure the MDF, LDF extensions and so on so that they are not scanned.

Now I go online and pull down all the updates to the Operating System, the virus software, and then — one of the most important steps — the proper drivers. You should get the latest, certified, tested, approved drivers from the manufacturer for every hardware device you have, right down to the monitor. It makes a difference — I’ve seen a poorly performing driver trash the performance on a SQL Server.

I’m still not ready for that installation yet. Not by a long shot. With all the drivers installed, I go through another round of updates. Every time you install new software (even a driver) there may be updates for it from Windows, so after I update the drivers I hot the web for an update scan again — and reboot as required.

Finally everything is installed, and I can start configuring.

Tuning the Network

Now that the drivers are in place, I can set the parameters that will make it faster. Again, don’t do this blindly — I am NOT saying that these are blanket changes that will automatically help any system — in fact, they could possibly cause your system to slow down. Research each of these options to see how they could help, and then test — on a test system of course.

For my system, the following settings will help the throughput for my network card, so I turn them on:

• Checksum offload
• Segmentation offload
• TCP offload engine (TOE
• Receive-side scaling (RSS)

To do that, I opened the Control Panel and then double-clicked the Network Connections applet.

Figure 2

I then right-clicked my network card and selected Properties.

Figure 3

From there I clicked the Configure button.

Figure 4

Then I set all of the options I listed. To use the TOE feature, I had to enable that option in the operating system by running the following in a command prompt:

netsh int tcp set global chimney = enabled

The final setting I made was to ensure that the speed and duplex were fixed to the highest speed I trusted on my network.

Tuning the I/O

Next I moved on to the I/O subsystem. I’m using NTFS, and I have the best layout I can with these drives.

In Windows Server 2008, you can specify an internal priority level on individual I/Os. The storage code that manages these priorities has some overhead, so for the SQL Server drives, I disabled the I/O priority management by setting the following registry entry for them to zero:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\DeviceClasses\{Device GUID}\
DeviceParameters\Classpnp\IdlePrioritySupported

Be careful here — you’re toying with the Registry, which can be a bad thing. Once again, research, understand, test, use a test system.

Also on my drives, and possibly even on my production server (assuming I trust the batter backup REALLY well) I’ll enable the system to believe that the data is written to the drive, even when it’s just to the cache.

To do that, I opened the Computer object in Windows.

Figure 5

I right-clicked each drive and selected the Hardware tab.

Figure 6

I clicked the Properties button for each drive, and navigated to the Policies tab.

Figure 7

That’s where I set the options for the performance gains. Before you do this — you guessed it — research, understand, test, use a test system.

Tuning the Memory

More memory is better, of course, specifically with 64-bit operating systems. And there’s an entirely different setup for memory within SQL server, which I’m not going to cover here.

The tuning for the memory really has to do with the paging file. The paging file is where Windows writes out older memory when there isn’t enough to keep everything in physical RAM. In extreme cases, it has to write out memory it’s using to that file, and a file is immensely slower than memory. So even having to use a paging file is bad, but in the case of low memory it’s unavoidable. A common setting when you don’t have a lot of RAM (say below 2-4GB) is to make the page file 1.5 times the size of that RAM. When you have a lot, you may not even need a page file. But I always set one, even if I don’t think I’ll need it.

For this test system, I’ll go with 8GB or so. By default, Windows 2008 “manages” this size dynamically, but I don’t want it to do that. So I’ll set a lower and upper boundary at the same amount, so that the system can set it and forget it. The less CPU cycles it spends on something like this the better.

I start off in the Control Panel again, this time in the System applet.

I click the Advanced system settings link on the left, and then the Advanced tab.

Figure 8

I then click the Change... button on the Virtual Memory area.

Figure 9

To finish off, I click the Custom size value, and then set 8499 as the top and bottom value, placing them on drive C:. I click OK everywhere, and then reboot.

Is this the same setting you need? Nope. You know the drill by now - research, understand, test, use a test system. Even though you don’t have a setting to go on, setting an upper and lower boundary at the same number is a good practice.

Well, that’s it for this article. In the final installment, I’ll finish this up, and show you a little of the testing I set up to be ready for the SQL Server installation.

InformIT Articles and Sample Chapters

If you’re new to Windows 2008, check out the basics in Windows Server 2008 Technology Primer.

Books and eBooks

To go further with Windows Performance tuning, The Complete Guide to Windows Server 2008, by John Savill, will serve you well.

Online Resources

If you’re using Windows 2008 Server R2, check out this whitepaper for tuning.