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
Preparing (or Tuning) a Windows System for SQL Server, Part 2
Last updated Mar 28, 2003.
I’m continuing my two-part article on tuning your Windows system for SQL Server. As I mentioned in that previous article, it’s important to note that not everything I cover here will fit your particular situation. You might have a different operating system version or edition, and a different SQL Server version and edition. You might have more or less memory than I do in this example or you might have a faster or even different architecture on your CPU.
So does that mean this information doesn’t apply? Well, yes in a manner of speaking. You should not take the settings I mention in these two articles and blindly apply them to your systems. But does that mean you can’t use the information here? Absolutely not! Many of the settings I explain here are generic to many types of systems, and even many versions of the operating system and SQL Server.
The point is, you should read and understand these settings, and then research them to see if they are applicable to your situation. Even when you believe they are, you should follow a strict methodology of measuring, implementing, measuring again and then evaluating to see if these changes work for you. And you should always do that on a test system. To be truly scientific, you would make a single change at a time and measure it, but by and large that’s a bit of overkill in this situation. I’ll make several changes at once and then show you the results of the testing.
So let me get back to my test system here, so that you can see if the system improved after my changes.
When I left off in the last article, I had been given some hardware to use, and didn’t have a lot of say in the matter. You might have a similar situation – that’s fine, any system can be tuned, even when it isn’t optimal. Use what you have, as I’ve done here.
I then selected the operating system I felt best served this environment (Windows Server 2008) and the platform I thought would work best (SQL Server Enterprise Edition) based on requirements, need and so on. I researched the operating system enhancements I could make and the interaction with the SQL Server environment, and laid out a plan. From there I installed the operating system per that plan.
I laid out the drives as best I could, even though I only have two of them. I picked out my virus software and latest drivers, and then installed the updates to the operating system. Notice the order the virus software comes first, before I hit the web. I don’t need any nasties on this system before I even start! And remember, this is all before I install SQL Server that comes later, after I tune and test.
With everything installed, I could now move on to the configuration steps. First, I tuned the network and the I/O subsystem. Since those two things represent potential bottlenecks, it is very important to set them up properly. They are also the most specific while memory is normally similar in many systems as well as processors, network cards and I/O systems are not. While my settings make sense in my system, they may not make sense in yours.
A word here about the testing. I once tuned a (test) system’s operating system that already had SQL Server installed, and found everything working faster. But when I tried those same settings in production, a few obscure functions within the client application stopped working. Strangely enough, it had to do with a security setting I changed something that should have had no bearing at all on the application. But it did. It took a while to find it, but happily I had a good back-out plan for production, so we were only down a few moments. I tried the app with those functions on the test system, found the issue, re-tested and then implemented the new improvements. The key here is testing and having a good back-out plan for your environment as well. I can’t emphasize that enough.
After all that I tuned the memory, and now I’m ready to tune the other components. I’ll continue with the Processor.
Tuning the CPU
This is a bit of a misnomer you don’t actually “tune” the CPU, you change the settings within the operating system for how it interacts with the CPU. But this is where the Windows Operating System stands apart from many others you can make changes that affect the CPU and how it works.
Before I tune the operating system, I’ll enter the system’s BIOS and make sure I don’t have a Hyper-Threading (HT) chip, or if it is I’ll turn it off. This is a personal preference, since in my experience I’ve had issues with SQL Server and HT. You can do a web search to find out more about this argument, but in my case I’m choosing not to enable it if it exists.
Next I’ll tune the operating system for how it engages the scheduling for threads on the processor. If you’re not familiar with that terminology, you can check out this article for more
To make the change, I click Start, then click Run, and then type sysdm.cpl in the Run box. In the System Properties dialog box, I click the Advanced tab, and then I click Settings under the Performance area.
In the Performance Options dialog box, I click the Advanced tab, and set the Background services option in Processor scheduling. While I’m here, by the way, I set my video performance to be optimal for performance, not for best display. Hey, this is a server, not a gaming machine! From there, I click OK, and then click OK again.
Configure Services and Applications
With all of the hardware tuned, I move on to the Windows environment. First, I stop or disable any of the Windows services that are not needed, and I remove all roles I don’t need, such as the web server. If I’m not doing file and print sharing, I get rid of that too. Keep in mind, some features in SQL Server need this function, so don’t turn it off until you understand if it is needed or not.
I’ll make sure I don’t have any high-resolution background graphics, and I’ll make sure that I don’t have a screen saver set. I do all that in the Control Panel.
While I’m here, and for this testing system, I’m going to change the power settings for this system. Now, Microsoft has done a great job of making the “Balanced” power plan very high performing, I want to eke out just a little more, and since this is a test system I will actually cut it off and on. In the real world, the cost savings and longer life that comes from the Balanced power plan is probably the better bet, but I’ll change it here.
I start by double-clicking the Power Options applet in the control panel, and then I choose the High performance setting.
Running the Tests
I actually ran this test once before I made all the operating system changes, and then ran it again after I finished.
I’ll start in the Reliability and Performance Monitor, which replaces the older Windows System Monitor In previous server operating systems.
By clicking the Monitoring Tools item and then the Performance Monitor, I can set a baseline like I’ve done in previous operating systems. But Windows 2008 includes a new set of tools that makes things a lot easier.
By expanding the Data Collector Sets item and then the System object, I can right-click the System Performance line and select Start the Data Collector Set.
This will run for 60 seconds, collecting performance data. From there I can click the Reports object, then System, then System Performance. Underneath that object is a set of reports that match the date and time of when I start the Data Collector. I do this not only before I make a change, but after each major set of changes. That creates a very rich report set that allows very rich comparisons. I can also right-click that report file and open the directory that holds it, which contains the .blg (binary log files for Perfmon) files that allows me to do even deeper analysis. I use this feature constantly on my systems.
Well, that’s it. Now I’m ready to install SQL Server!
InformIT Articles and Sample Chapters
If you’re new to Windows 2008, check out the basics in Windows Server 2008 Technology Primer by Chris Amaris, Omar Droubi, Ross Mistry, Rand Morimoto, and Michael Noel.
Books and eBooks
To go further with Windows Performance tuning, this book, called The Complete Guide to Windows Server 2008, by John Savill, will serve you well.
One of the better resources I’ve found for the Reliability and Performance Monitor is here even though it is for Vista the data is accurate for Windows 2008 as well.