As a SQL Server consultant, I often get requests to “look over” a SQL Server and to offer suggestions on how to boost its performance. Sometimes this task is easy, and other times it is difficult. Based on experience, I have discovered that my time is best spent looking first for the “easy-to-identify-and-solve” performance problems, and then, only if I have to, spend time looking for the “harder-to-identify-and-solve” performance problems. This is because I have discovered that many SQL Server-related performance issues can be easily identified and resolved, assuming you know where to look.
To make this task easier, and more systematic, I have developed a series of checklists I follow when I perform a Performance Audit of a SQL Server. I call this checklist a SQL Server Performance Audit because the goal of the checklist is to quickly identify and resolve any “easy-to-identify-and-solve” performance problems. And by taking a checklist approach, I don’t forget any important steps.
This SQL Server Performance Audit Checklist is designed for both beginner and experienced DBAs alike. For beginners, this checklist will provide you with the right place to start. For experienced DBAs, this checklist will help you not to forget to check out all important areas of SQL Server.
Is this Performance Audit Checklist the ultimate source for performance tuning SQL Server? Of course not. This is not my goal. My goal is to provide you with a systematic and repeatable approach to identifying and resolving common SQL Server 2005 performance problems. Once you have mastered this material, you are well on your well to becoming a master at resolving SQL Server performance problems using your own personal knowledge and experience to guide you.
If you are a long-time member of SQL-Server-Performance.Com, you may remember that I wrote a version of this performance audit article series for SQL Server 2000, which is still available on this website. For this version, I have updated the performance audit for SQL Server 2005.
The SQL Server Performance Audit
One of the advantages of using this checklist to perform a SQL Server performance audit is that it will not only tell you what you can do to help boost the current performance of your SQL Server instances, you can use it to validate what you have done so far. In other words, if you think you have done a good job optimizing performance on your SQL Servers, but are not 100% sure, hopefully this checklist will demonstrate to you what you already know.
As you read this Performance Audit article series, keep in mind that every recommendation I make may not apply 100% to you and your unique circumstances. While I have strived to include only common best practices in this Performance Audit, there may be some cases where my advice may not be the exact best advice for your particular situation. Unfortunately, I can’t tell you what this is. The point I want to make is that if you decide to use or not to use a specific performance tuning technique that I discuss, that’s fine, especially if you know exactly why you made the choice you have. On the other hand, if you don’t understand my recommendations, and you don’t have time to research them for yourself, you can rest assured that following any of my recommendations will not hurt your server. The worst that could happen is that you may not have fully optimized your server as fully as you could have if had better understood the implications of my recommendations.
Ideally, you should perform this audit on each of the SQL Server instances under your care. If you have many of them, this could take some time. I would suggest that you start on the instances that are currently producing the most performance problems, and the working your way through the rest of the servers as you have time.
Once you complete your performance audit, you still aren’t done. Remember, the recommendations discussed in this Performance Audit 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, assuming you have any. And that’s another article series for another time.