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 seven 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” of 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 you 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 then 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 series 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
- SQL Server Database Job Performance Checklist
- Using Profiler to Identify Poorest Performing Queries
- How to Best Implement a SQL Server Performance Audit
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.