The Final Word on How to Perform a SQL Server Performance Audit
If you have gotten this far, you have done a lot of reading. In this final article on how to perform a SQL Server Audit, we will take a look at some best practices about how to best implement a SQL Server Performance Audit. You will want to read this before you begin any actual performance audits on your SQL Servers.
Develop Your Own Custom Performance Audit
At this point, I assume you have read, or at least skimmed through all of the suggested audit steps. And I imagine that you may have read some things that don’t really apply to you. This only makes sense since most SQL Server installations are somewhat different. Because of this, I recommend that you customize this audit for your particular circumstances, adding or deleting steps that better meet your needs.
Use Microsoft Word or Excel to Maintain Your Audit Checklist
As you perform audits on each of your SQL Servers, you will need a way to track the results. While you have lots of options, cutting the applicable checklists from this article series and pasting them into a Word or Excel document is a quick way to get started. You will probably want to create a separate checklist for every server. If you decide to use Excel for your audit worksheet, you can enter all of the checklist items on their own row, and then create a separate column for each server being audited. This way, you can quickly view the results of each of your SQL Servers.
Prioritize Your SQL Servers and Databases
If you manage a lot of SQL Servers and databases, you may not know where to start the performance audit. Ideally, you should prioritize your SQL Servers, and their databases, into the ones that need the most performance help now, and others that may not need as much help. This will help you determine where to start. Most likely, you won’t be able to audit them all at once. Instead, audit them when you can, in the order from most important to least important.
Keep the Focus of This Performance Audit in Mind
As you perform the audit on your SQL Server, keep in mind that the goal is to identify and fix the easy problems. But, as you can imagine, you will probably also identify some more difficult to resolve issues. To keep you sane, and to help you better manage your limited time, you will want focus on the easy ones now, and save the hard ones until all of the easy ones are taken care of first. So as you perform the audit and identify issues, prioritize them into easy and difficult categories, and save the difficult ones for when you have time to focus on them.
Don’t Jump the Gun
As you perform the audit, you will be tempted to make fixes and changes as you run across them. In most cases, doing so probably shouldn’t be a problem. But ideally, it is better to first perform the audit, then based what you found, decide on a formal approach to resolving the issues you identified, and them implement them in a methodical manner.
A Recommended Step, But Perhaps Too Much to Ask
In a perfect world, with lots of time, it would be a good idea to perform a performance benchmark on your server, perform the audit, make any needed changes, and then perform another performance benchmark to see what happened. This will let you know immediately if what you did was helpful or not, and in some cases, not the right thing to do. While this suggestion is highly recommended, it may not be practical from a time perspective. But if you do have the time, then you should seriously consider it.
Another Recommended Step, But Perhaps Also Too Much to Ask
After performing an audit, you may find that just a change or two on a single SQL Server is all that is needed, but on others, perhaps dozens of changes need to be made. If there are a lot of changes to be made, it is probably a wise choice to not implement them all at once, but one at a time, or several at a time. This way, you can see the effect of each change, or set of changes, makes to the server. If you were to make many changes at once, and then experience a problem, you wouldn’t know which change caused the problem, requiring you to undo all of the changes, and then try them one by one until you discover the culprit.
This Recommendation is Not to Much to Ask
If the server you need to make changes to is a mission-critical production server, you will want to be very careful about any changes you make. Ideally, you should test any changes on a test SQL Server before implementing them on a production server. If this is not practical, then make only one change at a time, and be sure you know how to reverse the change should there be any problems. In addition, try to pick a less busy time of the day to make the changes, in case there are problems.
Have a Backout Plan
Most of the changes you will be making because of the audit will be easy to reverse. But some may not be so easy. In those cases, you need to have a backout plan just in case you need it. For example, backup your system and user databases before you make any critical changes. That way, if there should be a problem, then you can restore your server to the state it was in before you made the change. I don’t want to scare you away from making changes, but you should always be prepared.
Document All Changes
As you make changes based on your performance audit, be sure that you document all changes. This way, if there are any problems later, it will be much easier to identify what went wrong. Probably, the easiest way to document your changes is to add them to your audit spreadsheet, or other document you used to collect the audit information.
Perform SQL Server Performance Audits Yearly
Over time, many SQL Servers (but not all) change. Settings change, service packs are added, and even data changes. All of these can affect performance. The best way to ensure optimum performance on your SQL Servers is do an annual performance audit.
After I Complete an Audit and Make the Changes, What Do I Do Next?
Take it easy? Nope. Just the opposite. Remember, this audit is designed to catch the obvious and easy to correct SQL Server performance issues. Once you have done this, next, you need to identify and correct the hard to correct problems. The performance audit, as previously mentioned, may have identified some difficult problems, and others you may have to discover as they occur. In any event, you will mostly likely be spending much more time identifying and correcting the hard problems that you did on the initial performance audit. But like anything else, focus on those problems that cause the biggest performance problems, and then work your way through them as time permits. Good luck!
]]>