SQL Server Performance tuning is usually not a planned activity. Often, performance problems will happen at the worst times and this prompts an “emergency” performance tuning effort by the DBA. During the emergency intervention, any of the following changes could be applied on the server.
- Manually creating or dropping indexes
- Creating indexes and statistics via SQL Server Database Tuning Advisor (DTA)
- Changes to SQL Server settings like Maximum Degree of Parallelism or SQL Server Memory
- Modifications to stored procedures, views, user defined functions or other DB objects
- Changes to operating system configuration like page file location
- Hardware changes like memory upgrades or adding faster disks
While the intention of these types of changes is good, in the best case it often results in not knowing which change fixed the problem, or in the worst case degrading performance even more.
Even though it is difficult to have the discipline to work in a controlled methodological way when you are under pressure to fix SQL Server performance problems quickly the “one step at a time, one success at a time” approach is the best way to go. Performance tuning should be an iterative process where a first measurement is taken, major bottlenecks are identified and fixed, a second measurement is taken, and the impact of the change is analyzed by comparing the two measurements.
Using this approach to performance tuning has several advantages:
- By keeping the amount of changes made during one cycle to a minimum, it becomes very easy to know what change caused performance to improve or degrade.
- If performance degrades, you can quickly roll back the changes
- You steadily progress towards the goal of improving performance without falling into a “one step forwards, two steps backwards” pattern.
There can be several difficulties in the process described above:
- On complex systems it is not always obvious what should be measured.
- What are the thresholds that will allow us to identify the bottlenecks?
- How do we compare measurements over time?
- How do we keep track of baseline measurements over a time. Often, all data is discarded when a problem is fixed or it isn’t stored in an easily accessed way.
Here at instaDBA we addressed these problems by developing our own solution. The solution consists of:
- An easy way to collect the relevant performance data using our SQL Server baseline tool.
- The “instaSQLAudit” reports which will identify the major bottlenecks found in the baseline
- The “MeasureImprove” report which compares two baselines taken over time (even across different servers or Amazon RDS)
- Store the baselines in a central repository so that they can be easily accessed for comparison reasons later on.