What problems do query plan monitoring techniques fix?

Query plan monitoring techniques fix problems with high wait times, bottlenecks and general blockages in a database management system. Specifically, the problems that query plan monitoring techniques solve are related to situations where poorly constructed SQL code results in more cost or effort, and significant barriers or bottlenecks in the system.

As a declarative language, SQL is procedural. Query plan or query execution plan monitoring looks at an ordered set of steps or lines of code describing specific ways for a query to retrieve information from the database or otherwise work with data sets.

In most cases, there is more than one way to collect a certain type of information. So query optimizing tools assess the various options, and show which are most efficient. However, some of these automation tools require some manual oversight.

The semantics and terminology around various types of SQL and query analysis can vary according to factors such as the focus of a project. Query plan monitoring can also be closely related to SQL performance monitoring. In addition, efforts to review a query plan can be called “database tuning.” In all of these diagnostic techniques, engineers are looking at the best ways to employ SQL queries to perform some kind of database task.

One way to explain this is that query execution plan monitoring looks at poorly performing SQL queries. It evaluates the use of particular SQL queries by certain benchmarks – for instance, it might identify longest times for queries in the system, or identify the queries that use the most CPU resources or require the most input/output. By scanning an entire traditional or virtualized server network, the plan monitoring tools can provide data on where to look for bottlenecks.

Query execution plan monitoring tools might also make use of features within a database management resource, such as a “plan table” that shows cost and time for each individual query. Commands such as EXPLAIN PLAN can lay out query plan data for analysis.

Automation tools are being built to do more when it comes to improving automatic SQL diagnostics and analysis, and making sure that the best queries are opening up systems to operate at peak efficiency. Either automated systems or human engineers might assess wait times, which are the times where a system is not executing, but instead waiting behind a process. These are the types of problems that query plan monitoring and other SQL diagnostic techniques are meant to solve, in conjunction with other types of analysis and implementation tools in today's sophisticated database management world.