RSS Feed (xml)
SQL Server 2005 Operating System Configuration Checklist:
Which OS version are you running?
Are the disk partitions formatted using the most recent version of NTFS?
Is "NTFS data file encryption and compression" turned off?
Do you have at least 20% free space on each of your logical disk drives?
Does your server OS have the latest service pack?
Does your server have the most current, Microsoft-certified hardware drivers?
Is the server configured as a stand-alone server?
Is the "Application Response" setting, set to "Optimize Performance" for "Background Services?"
Has security auditing been turned on?
How large is the server's PAGEFILE.SYS swap file?
Have unnecessary services been turned off?
Have all unnecessary network protocols been turned off?
Is antivirus software being used?
Which OS Version are You Running?
The focus of this section of our performance audit is the base operating system (OS), and how to optimize it in order to get the best performance out of SQL Server.
Like SQL Server, Windows Server is mostly self-tuning. But like SQL Server, there are things we can do to help optimize Window Server’s performance. Every time we help boost the performance of Windows Server, we are at the same time boosting the performance of SQL Server.
In some organizations, as a DBA, you may not have a lot of control over the operating system you are running SQL Server under. If this is the case, you still want to audit these settings, and then discuss them with the person responsible for the OS, working out any differences so that the OS is properly tuned to get the most out of SQL Server 2005.
While many of the suggestions in this section may seem trivial, most aren’t. You would be surprised how many server resources would be wasted if were to set each of these options to the opposite of the recommendations.
Selecting the Best Performing OS
As you may be aware, every new version of Windows Server comes with new features that help boost the stability, fault tolerance, and performance of SQL Server. With this in mind, it is always a good idea to run SQL Server 2005 on the most recent version of the OS that is currently available. When Windows Server 2008 becomes available, you will want to seriously consider upgrading to it in order to take advantage of the new performance features it offers.
Besides selecting the most current version of the OS to run SQL Server on, you have two other considerations to make. First, you must decide whether or not you want to use the 32-bit or 64-bit version of the OS. Of course, if you want to use the 64-bit version of SQL Server 2005, then this is an easy decision. On the other hand, if you have not decided on which version of SQL Server 2005 to use, selecting the appropriate version of the OS is harder. One thing to keep in mind when selecting 32-bit over 64-bit of either SQL Server or Windows Server is that one version is not always better than the other. Just because you are using a 64-bit solution does not mean than it will be faster or better meet your needs. It depends on your application. So don’t automatically opt for a 64-bit solution until you have carefully weighted the pros and cons of your options.
The second OS consideration you have to make is which edition of the OS to go with: Standard, Enterprise, and Data Center. Each has their pros and cons, and you need to select the best version to match your SQL Server 2005 needs. To make the decision, first decide on which version of SQL Server 2005 you need to best meet your application’s needs, and then select the least expensive version of the OS that supports the SQL Server 2005 edition you have chosen. There is no benefit in purchasing a more powerful, and more expensive version of the OS than SQL Server 2005 can take advantage of.
Are the Disk Partitions Formatted Using the Most Recent Version of NTFS?
Many DBAs aren’t aware that the NTFS file format used by the various editions of Windows Server can be different, with each offering different performance benefits. As you may assume, the more recent version of the OS you use, the newer the version of NTFS you are using, and the better it will perform under SQL Server.
Normally, you don’t have to give much though to the NTFS version as the most recent one is automatically used when you install the latest version of Windows Server. But there is something that might surprise you. If your physical server has been upgraded in place from an older version of the OS to a newer version of the OS, that the NTFS version is not automatically upgraded. It is only upgraded as a separate step, assuming the IT professional remembered to do this.
So what doe this mean to you, as the DBA? Essentially, if your physical SQL Server has had an OS upgrade, it would be wise for you to find out of the NTFS format was also upgraded, and if not, have it upgraded. While there is not a lot of difference between NTFS versions, there is enough performance benefit to make the upgrade worth your while. The newest versions of NTFS include some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads.
Is "NTFS Data File Encryption and Compression" Turned Off?
Windows supports both file encryption and compression, and by default, these two features are turned off on a newly installed Windows 2003 server. While these features do provide some benefits under limited circumstances, they do not provide any benefits for SQL Server. In fact, using one or both of these features can greatly hurt performance.
As you know, SQL Server can be very resource intensive, and anything else running on a SQL Server can hurt its performance. Both file encryption and compression significantly increase disk I/O and CPU utilization, as data files have to be manipulated on the fly as they are used. So if either file encryption or compression is used on SQL Server files, performance will greatly suffer.
If you become the DBA of a currently existing SQL Server, and are not familiar with it, check to see if anyone mistakenly have turned on either of these functions. If so, and you turn them off, you will become a performance hero to all of the server's users.
Does Your Server Have the Latest OS Service Pack?
Every service pack I have ever seen has one or more performance enhancements. These could be because of tuning done by Microsoft, or because some previous bug has been fixed that boosts performance.
While you may not want to rush right out and install a new service pack the day it is released from Microsoft, once it has been tested positively in the real world, you should install the service pack.
Does Your Server Have the Most Current, Microsoft-Certified Hardware Drivers?
On more than one occasion, I have seen older, buggy hardware drivers cause performance problems with Windows Server. Most commonly, these are disk- or network-related drivers.
Periodically, you should check to see that your server has the most recent, Microsoft-certified hardware drivers. You can do this by going to the hardware vendor's website, or, to a more limited degree, by using Microsoft's Update service. In some cases, you may find a new driver that is available from the vendor, but has yet to be certified by Microsoft. I recommend that you be patient and wait (assuming this is practical) for the Microsoft-certified version. While increased performance is important, software stability is even more important.
Is the Server Configured as a Stand-Alone Server?
A Windows server can be configured as either a stand-alone server or as a domain controller. For best performance, SQL Server should only run on a stand-alone server. This is because a domain controller has a lot of overhead that takes away server resources from SQL Server, hurting performance.
Is the "Application Response" Setting, Set to "Optimize Performance" for "Background Services?"
In Windows 2003, under the "Advanced" tab of the "System" icon in "Control Panel," click on the "Setting" button under "Performance," the click on the "Advanced" tab. Here, you can change the performance to favor either "Programs" or "Background services." You should choose "Background services" for best SQL Server performance, as this tells the OS that you want to favor background applications, such as SQL Serve, over foreground applications.
Also, here, you can change the memory allocation to favor either "Programs" or the "System cache." For best SQL Server performance, select "Programs." This tells the OS to give more memory to applications, such as SQL Server, rather than the system cache.
In many cases, these settings will probably be correct. But if they are not, they should be changed for optimum SQL Server performance. After making these changes, you will most likely have to reboot your server.
Has Security Auditing Been Turned On?
Windows Server has the ability to audit virtually any activity on a server. By default, most security auditing is turned off. For best performance, no additional auditing (other than the default) should be turned on, as this will increase CPU and I/O activity, competing with SQL Server for these same resources. Of course, if you have to have auditing turned on (because some manager says so), try to limit the amount of resources audited as much as possible in order to reduce its negative effect on performance.
How Large is the Server's PAGEFILE.SYS Swap File?
Microsoft recommends that the PAGEFILE.SYS file be set to 1.5 times the amount of physical RAM in the server. The exact amount you need depends on what additional SQL Services you may be running. For example, if you are running Full-Text Search service, Microsoft recommends that your PAGEFILE.SYS file be three times physical RAM.
Microsoft's recommendations are a good starting point, but the best way to size the PAGEFILE.SYS is to monitor how much of it is used during production using the Performance Monitor Page File Object: % Usage counter, and then resize the PAGEFILE.SYS with a minimum size just slightly larger than the amount that is actually being used (based on the Performance Monitor counter), and with a maximum size of 500 MB larger than the minimum size.
In Windows 2003, the PAGEFILE.SYS setting can be viewed and changed under the "Advanced" tab of the "System" icon in "Control Panel," click on the "Setting" button under "Performance," the click on the "Advanced" tab, and then by clicking on the "Change" button under "Virtual memory."
Have Unnecessary Services Been Turned Off?
For best performance, turn off any Windows Server system services that aren't needed. This conserves both RAM and CPU cycles, helping to boost the overall performance of SQL Server.
Below are some of the operating system services (not a complete list) that are generally considered non-essential and can be turned off, assuming they are not used for a specific purpose. Some of these services may not be installed on your server, and others will already be set to "Disabled" or "Manual," depending on how the server was installed and configured. Some of the services set to "Manual" are designed to only be started when needed, and then turned off when no longer needed.
Alerter
Application Management
Clipbook
Distributed Link Tracking Server
Fax Service
File Replication
FTP Service
Indexing Service
Internet Connection Sharing
Intersite Messaging
Kerberos Key Distribution Center
License Logging Service
Logical Disk Manager Administrative Service
Messenger
Microsoft Search
NetMeeting Remote Desktop Sharing
Network DDE
Network DDE DSDM
Print Spooler Service (if you won't be printing from this server)
QoS RSVP
Remote Access Auto Connection Manager
Remote Procedure Call (RPC) Locator
Routing and Remote Access
RunAsService
Smart Card
Smart Card Helper
SMTP Service
Telnet
Utility Manager
Windows Installer
World Wide Web Service
Generally, I turn off these services (assuming they are currently on) and ensure that their "Startup Type" setting is set to "Manual." Of course, if you have a need for any of these services, you don't have to turn them off.
Have All Unnecessary Network Protocols Been Turned Off?
Generally, the only network protocol you need is TCP/IP for your server if you are only running SQL Server on it. Removing unnecessary network protocols on your SQL Servers helps by reducing the load on the server and by reducing unnecessary network traffic.
Is Antivirus or Antispyware Software Being Used?
Real-time antivirus and antispyware software creates a big resource hog for SQL Server, and is not recommended on production SQL Servers, especially clusters.
If you are worried about viruses or spyware, you can do remote scanning against your SQL Servers on a daily basis, preferably during off hours.
Now What?
Your goal should be to perform this part of the performance audit, described on this page, for each of your SQL Servers, and then use this information to make appropriate changes, assuming you can.
Once you have completed this part of the performance audit, you are now ready to audit your SQL Server's configuration.
No comments:
Post a Comment