In a recent series of posts to this blog I outlined a dozen SQL rules of thumb to follow as you design and code your database applications. These articles can be found at the following links:
- A Dozen SQL Rules of Thumb, Part 1
- A Dozen SQL Rules of Thumb, Part 2
- A Dozen SQL Rules of Thumb, Part 3
Rules of thumb are great but let’s face it, we don’t always follow them. And then we get performance degradation. That means we have to track down the root cause of the problem, analyze the situation and apply corrective measures. This can be difficult without a performance management tool or two.
Database performance management tools can be used to identify bottlenecks and points of contention. These tools can help to show locking, I/O, CPU and other problems as your applications are running; or historically at a point in time if you have appropriate traces started and/or have implemented a performance data warehouse.
Such tools can be beneficial for determining where resources are being used. For example, do you know which programs consume the most database resources? Which consumes the most CPU, takes the most I/Os, issues the most locks, deletes the most data, etc.? Performance monitors can help you to accurately identify all of these issues. You can monitor system and database workload and throughput and manage the resources being used to satisfy your workload.
Database performance management tools can be broken up into three specific functional groupings based on what is being monitored and managed. Some of these tools offer functionality in only one of the following areas, whereas others deliver combined functionality:
- The DBMS. Tools that manage DBMS performance are focused more on database system resources and performance. They provide information about performance at the system level, showing things like CPU and memory consumption, buffer usage, locking issues, storage and other hardware information, configuration parameters, and other details.
- Database structures. The design of databases, tables and indexes can also impact database performance. Issues that must be monitored and managed include the physical design of the database, number of tables, index design and DDL, disk usage, and so on. How the data is organized must also be managed. And as data is modified in the database, its efficiency will degrade. Reorganization and defragmentation are required to periodically remedy disorganized data.
- SQL and application code. Coding efficient SQL statements can be complicated because there are many different ways to write SQL that return the same results. But the efficiency and performance of each formulation can vary significantly. DBAs need tools that can monitor the SQL code that’s being run, show the access paths it uses and provide guidance on how to improve the code.
I recently wrote a four part series of articles for TechTarget on database performance management offerings that delves into more details on these issues. If interested, you can check them out at the following links:
- What you need to know about database performance software
- Three indicators that could signal database performance issues.
- Examining the functions and features of database performance tools.
- Tips on selecting the right database performance tools.
Good luck as you embark upon your database performance tuning and management efforts!