Proper SQL performance tuning begins with SQL Server monitoring. With it you will be able to see how your SQL Server database is performing under specified conditions: peak, off peak, day parts, etc. Performance monitoring enables administrators to identity issues that may require changes, repairs, upgrades, or other interventions.
Once these performance issues are identified, DBAs can then create a performance tuning plan to address them.
6 Steps for Proper SQL Performance Monitoring
Before you can create a SQL performance tuning plan, you must first gather performance data. The following steps will help you capture the necessary data:
- Define monitoring goals – Don’t just randomly capture data. You must have a plan and something to look for. Clearly define your goals for improved performance.
- Choose the appropriate tool – Will you use OEM tools or third-party tools?
- Identify components to monitor – Once you define your goals, it is an easy step to determine which performance issue you will need to monitor.
- Select and define performance metrics – Set both maximum and minimum performance metrics for your alerts. Depending upon what you are measuring underperformance can be just as bad as over utilization.
- Monitor the server – Now that you have defined how and what to measure it’s time to monitor your server’s performance. Measure over several defined periods of time: peak usage, off peak, day to day, week to week, etc. Begin by establishing a performance baseline that will show how the system operates during the times you are monitoring. The more data you have, the easier it is to spot trends.
- Analyze the data – Look for trends and consistent performance data. How does the system perform during peak usage? Is it slowing down beyond acceptable metrics? If so, what may be causing this slow down? What can be done to solve the issue?
You might also add two more items to this list for ongoing improvement:
- Fix issues – You monitored performance and isolated potential causes of performance issues. Now you can create a SQL performance tuning plan to improve performance.
Repeat steps 1-7 – SQL performance tuning is not an isolated activity that you do once and are finished with. Some actions you take on a daily basis, while others, like we’ve outlined here, require a plan of action and must be repeated for optimum performance. Each time you repeat the process, you fine tune your databases more and more.
Content provided by Josh Stein on behalf of Confio, an expert in SQL Performance Tuning and Monitoring.