As anyone responsible for database performance knows all too well— performance tuning can be one of the most challenging and in-demand requirements of database management. Often overlooked is a critical function to this process—database monitoring.
Database monitoring is how you identify the right SQL to tune.
How you determine the right way to tune it.
And, whether SQL is the right thing to tune in the first place.
Why database monitoring and performance tuning go hand in hand
When you check your email and dashboards in the morning and find that your company’s database applications have slowed to a crawl and users are complaining, does this seem like the right time to focus on monitoring? Of course not. It’s the right time to tune performance, but how can you tune if you haven’t been monitoring the applications in question for any given amount of time?
The historical perspective you gain from monitoring performance provides the data needed to determine what should and should not be tuned, and how to tune it properly. Tuning is not only easier, but more effective when database monitoring is in place to provide a detailed background of database metrics you can review. Not to mention, building out that historical background is the best way to avert performance problems before they impact other areas of the business.
There are three parts required in performance management:
- Monitoring — Putting in place the tools required to collect performance data from every area in your database environment
- Analysis — As the performance data accumulates, analyzing it regularly to look for patterns in resource consumption, workloads and business cycles
- Tuning — Making changes when you can and should, instead of when you think you need to
Finding the right area to tune
We recently described how database monitoring is a multi-layer pursuit, requiring monitoring on four different levels: the SQL level, the instance/database level, the infrastructure level and the user/session level.
Different problems can arise at each of these levels, so performance tuning means something different at each level of the database. For example, adding storage capacity addresses problems at the infrastructure level but not necessarily at the SQL level, and creating the right non-clustered index can help at the SQL level, but if the problem is at the infrastructure level, it won’t help at all.
For truly smart performance monitoring, building up a series of performance snapshots over time is required. You can write scripts that create a monitoring table, periodically collect moment-in-time statistics about the database and append them to the table.
In our eBook The Fundamental Guide to SQL Query Optimization, we describe how you can use several scripts to capture and store wait time data for analysis. With similar techniques, you can fill monitoring tables with database metrics like row counts, logical reads, wait events and locked objects. By applying those metrics, you can then set alerts to apprise you of ominous trends such as low disk space, insufficient memory and excessive logical reads.
Once you’ve automated the process of collecting the statistics and your tables have grown, it’s time to really analyze the historical performance of them. It may seem premature to look for problems when your databases are up and running smoothly, but the best time to look for them is before they become problems. Why? Because that’s when you have the time to tune without the duress of taking the database offline or rebooting the server.
Database monitoring requirements
To use database monitoring as a foundation for performance tuning, keep several requirements in mind:
- Coverage at all levels — Database performance depends on resources in the operating system, virtual machine and storage system. If your problems lie in those levels and you haven’t been monitoring and studying trends there, you could easily start your troubleshooting in the wrong place.
- Transaction workload — The database is linked to the application by transactions. Measuring the database transaction workload (number of users, batch jobs, automated tasks) is the only way to report accurately on database performance.
- Time horizon — As noted above, your best insights come from studying trends over a long period of time, starting months or years ago and leading up to five minutes ago. A long horizon of data will tell you what’s normal for your environment, which applications are most/least active, when instances are at peak volume and when they were backed up.
- Granularity — The rate of collection (weekly, daily, hourly) determines the level of granularity with which you can judge a potential threat to performance. Plan to increase the frequency of your sampling when transaction volume changes or when you’re in the thick of studying a problem.
- Overhead —Studying anything, be it an animal or a database, inherently affects the way it behaves. Keep overhead low to ensure that the cost of collection doesn’t affect your conclusions or exceed the value of the data you’ve collected.
- Adaptive alerts — “Normal” is relative, and it rarely stays normal for long. Make sure your thresholds for warnings and alerts change with time, transaction volume, business conditions and resource capacity. Otherwise, your monitoring tools will cry wolf all the time and be less useful to you.
- Actionable output — Common symptoms of database performance bottlenecks include excessive logical reads, long wait times, index fragmentation, and too few/many indexes. SQL tuning usually breaks those logjams. But database monitoring is also the process of making sure that SQL is what you should be tuning in the first place. Are the VMs under the databases healthy? Has the operating system been patched? Do the storage subsystems require maintenance? Your analysis should tell you not only what the problem is but also which part of the environment to address.
Database monitoring first, performance tuning second
It’s tempting to jump in at the first sign of trouble and start making changes to improve performance. Maybe your many years as a database professional or developer has allowed you to rely on your own experience to figure out what’s wrong and fix it.
For the rest of us, there’s database monitoring: the steady work of accumulating performance data over time and studying it often enough to spot trends and patterns. It’s always better when you can choose the time to resolve performance tuning problems (or avert them altogether) versus living in a reactionary state of red alerts and user complaints.
