Question

What problems do query plan monitoring techniques fix?

Answer

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.

Related Terms

Justin Stoltzfus
Contributor

Justin Stoltzfus is an independent blogger and business consultant assisting a range of businesses in developing media solutions for new campaigns and ongoing operations. He is a graduate of James Madison University.Stoltzfus spent several years as a staffer at the Intelligencer Journal in Lancaster, Penn., before the merger of the city’s two daily newspapers in 2007. He also reported for the twin weekly newspapers in the area, the Ephrata Review and the Lititz Record.More recently, he has cultivated connections with various companies as an independent consultant, writer and trainer, collecting bylines in print and Web publications, and establishing a reputation…