Home arrow SQL Server arrow Page 4 - How to Perform a SQL Server Performance Audit

How to Perform a SQL Server Performance Audit - Operating System Performance Checklist
(Page 4 of 9 )

Performance Audit Checklist

Operating System Performance Considerations Your Configuration
Are the disk partitions formatted using NTFS 5.0?  
Is "NTFS data file encryption and compression" turned off?  
Does your server have the latest service pack?  
Does your server have the most current, Microsoft-certified hardware drivers?  
Is the Windows 2000 server configured as a stand-alone server?  
Are the physical files on the server overly fragmented?  
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?

Enter your results in the table above.

Configuring Your Windows 2000 Server is Easy, But Critical

The focus of this section of our performance audit is the base operating system, and how to optimize it in order to get the best performance out of SQL Server. While SQL Server can run under both Windows NT 4.0 Server and Windows 2000, the focus here is on Windows 2000, as it is now the prevalent operating system.

Like SQL Server, Windows 2000 is mostly self-tuning. But like SQL Server, there are things we can do to help optimize Windows 2000's performance. And every time we help boost the performance of Windows 2000, we are at the same time boosting the performance of SQL Server.

Are the Disk Partitions Formatted Using NTFS 5.0?

If your server is new and Windows 2000 has been recently installed, then any drives that have been formatted with it have been formatted using NTFS 5.0. But, if the server is older, and previously ran Windows NT 4.0 Server, and the drives have not been reformatted since upgrading to Windows 2000, the disks most likely were formatted using NTFS 4.0.

While there is not a lot of difference between NTFS 4.0 and 5.0, there is some. NTFS 5.0 includes some new performance enhancements, which mean fewer disk accesses to find files, and generally overall faster disk reads. Before Windows 2000, some DBAs formatted the drives or disk arrays dedicated to log files as FAT because it had a small performance benefit over NTFS 4.0. This is no longer true under NTFS 5.0, so all disks for all SQL Server should be formatted using NTFS 5.0 for best performance.

If you currently have a production SQL Server that is using NTFS 4.0 formatted partitions under Windows 2000, it may be difficult for you to convert them to NTFS 5.0. In most cases, I would recommend that you not worry about this, as the performance hit is not huge. But if you are upgrading from Windows NT 4.0 server to Windows 2000, you will definitely want to reformat your drives using NTFS 5.0 to take advantage of every little performance benefit you can eek out of your server.

Is "NTFS Data File Encryption and Compression" Turned Off?

NTFS 5.0 under Windows 2000 supports both file encryption and compression, and by default, this is turned off on a newly installed Windows 2000 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 is very I/O intensive, and anything that increases disk I/O hurts SQL Server's performance. Both file encryption and compressions significantly increase disk I/O 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 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 2000. 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 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 Windows 2000 Server Configured as a Stand-Alone Server?

A Windows 2000 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.

Are the Physical Files on the Server Overly Fragmented?

Excessive physical file fragmentation on a Windows 2000 server can significantly affect disk I/O, hurting the performance of SQL Server. While NTFS tries to minimize file fragmentation, it is impossible to prevent. Periodically, you should use Windows 2000's built-in defragmentation utility, or use a third-party defragmentation utility, to see how badly the physical files have been fragmented. And if they are badly fragmented, they should be defragged for improved disk I/O performance.

Unfortunately, fragmentation utilities cannot defrag open files, such as SQL Server database files, unless the SQL Server database files are closed. This means that the SQL Server services will have to be stopped when defragmentation occurs.

Ideally, a defrag utility should be run periodically to ensure the best I/O performance from your server. This is the only way to ensure overall best performance.

Is the "Application Response" Setting, Set to "Optimize Performance" for "Background Services?"

Under the "Advanced" tab of the "System" icon in "Control Panel", you can configure what is called the "Application Response" setting. You can choose to optimize performance for either "Applications" or "Background Services". The default setting is "Background Services," and should remain so.

What this setting does is to tell Windows 2000 to give background services, such as the SQL Server services, a higher priority than foreground applications. On a dedicated SQL Server, this is the optimum setting for the best performance.

Has Security Auditing Been Turned On?

Windows 2000 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 should be turned on, as this will increase I/O activity, competing with SQL Server for the same I/O. Of course, if you have to have auditing turned on (because some manager says so), try to limit it 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. 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 50MB larger than the minimum size.

The PAGEFILE.SYS setting can be viewed and changed by right clicking on "My Computer", choosing "Properties", clicking on the "Advanced" tab, clicking on "Performance Options", and clicking on the "Change" button under "Virtual Memory". If you change the virtual memory settings, you will have to reboot your server for the new settings to go into affect.

Have Unnecessary Services Been Turned Off?

For best performance, turn off any Windows 2000 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, if they are not used. 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 started when needed, and then to turn themselves 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 if you are 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.

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 changes, if you can.

Once you have completed this part of the performance audit, you are now ready to audit your SQL Server's configuration.

blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials