Home arrow SQL Server arrow How to Perform a SQL Server Performance Audit

How to Perform a SQL Server Performance Audit
(Page 1 of 9 )

If you have been a DBA for long, then you will know that performance tuning SQL Server is not an exact science. And even if it were, it would still be difficult to find the "optimal" configuration for the "optimal" performance. This is because there are few absolutes when it comes to performance tuning. For example, while one particular performance-tuning tip may help boost performance one way, at the same time it might hurt performance in another way.

Over the last five years that I have been a SQL Server DBA, I have put together a mental checklist that I use when it comes to performance tuning SQL Servers. I use this checklist as a guide when I perform a "performance audit" a (new to me) SQL Server for the first time. I often get requests to "look over" a SQL Server and to offer suggestions on how to boost its performance. Until now, I have never really written down the process I go through. But as I have begun to do more and more performance tuning consulting, I now have decided that it is time to put this performance audit checklist on paper. Hopefully, you will find it as useful to you as I find it is to me.

The SQL Server Performance Audit

The goal of this performance audit checklist is to help you, in a quasi-scientific way, is to help you identify any obvious performance problems with your SQL Server. As I mentioned above, SQL Server performance tuning can be difficult. What I am trying to do with this checklist is to identify all of the "easy" SQL Server performance problems, leaving the hard ones for a later time.

I am doing this because is it easy to confuse the easy and the hard SQL Server performance tuning decisions. By creating a list of the "easy" performance tuning areas, it is easier to focus on getting the easy ones out of the way, and once they are out of the way, then you can focus on the more difficult ones.

One of the advantages of using this checklist to perform a SQL Server performance audit is that is will not only tell you what can do to easily boost current performance, it also can be used to help you know what you have already done correctly.

In some cases, the choices you have made for your SQL Server may be deliberately different than the recommendations found on this checklist. In other words, you have purposely made a specific decision not to follow common SQL Server performance tuning practices. In some cases, you may be right. Not all SQL Server performance recommendations are applicable to all situations. In other cases, you may have made a decision based on resource limitations, such as not having the money to purchase the necessary hardware to carry your load.

If that's the case, then you have no choice but to live with this. And in other cases, the decisions you have made may be due to political reasons, which may or may not be able to be changed. In any event, you need to do what you can, using this performance audit to identify those areas that you can change, and making those changes to boost your SQL Server's performance.

Ideally, you should perform this audit on each of your SQL Servers. If you have many of them, this could take some time. I would suggest that you start on the servers that are currently producing the most performance problems, and the working your way to the rest of the servers as you have time.

Once you complete your performance audit, you still aren’t done. Remember, these are the easy ones. Once you have the easy ones out of the way, then you can begin to devote your time to working on the harder performance issues. And that’s another article for another time.

How to Conduct Your SQL Server Performance Audit

To make your SQL Server Performance Audit easy to perform, I have divided it into several sections. They include:

  • Using Performance Monitor to Identify SQL Server Hardware Bottlenecks
  • Server Hardware Performance Checklist
  • Operating System Performance Checklist
  • SQL Server 2000 Configuration Performance Checklist
  • Database Configuration Settings Performance Checklist
  • Index Performance Checklist
  • Application and Transact-SQL Performance Checklist
  • Using Profiler to Identify Poor Performing Queries
  • Once the Performance Audit is Over, What Do I Do?

The best way to conduct your SQL Server Performance Audit is to first review each of the above sections, and to print them out. From there, complete each section, writing down your results as you collect them. You may perform the audit in any order you like. I have only listed the above steps the way I have because this is the way I generally approach a Performance Audit.

Once you have completed your audit, you will be ready to decide what approach you want to take to implement the various recommendations discovered from taking the audit. You will learn more about this in the last section.

If you would like to provide me feedback on my SQL Server Performance Audit, please contact me at webmaster@sql-server-performance.com .

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