The Benefits of What-If Analysis for Database Performance Management

by Mar 12, 2021

For data-driven organizations, there are many benefits of what-if analysis to take advantage of. A method of evaluating the outcome of certain actions and inputs, what-if analysis helps better inform the decision-making process. With what-if analysis, organizations have a methodology for assessing risk and recommending the optimal course of action.

The Benefits of What-If Analysis for DBAs

You might be wondering why what-if analysis should be used for database index optimization. The answer is simple. A what-if methodology enables DBAs to obtain maximum performance benefits while assuming the minimum amount of risk. That is a winning combination warmly welcomed by database team members.

What-if analysis benefits DBAs by:

Speeding Up SQL Statement Execution

Database administrators (DBAs) are constantly searching for methods that can help improve system performance. Focusing on the way indexes are defined and used in databases and applications can present opportunities for substantial performance gains. Optimized indexes can result in exponentially faster SQL statement execution and vastly improved response time for end-users. This makes what-if analysis a very useful method of tuning database indexes. is through the use of what-if analysis.

Enabling More Informed Optimizations and Limiting Mistakes

Database performance is closely associated with business transactions. Slow databases can severely impact the ability of enterprise systems to fulfill business requirements. Tinkering with the indexes used by SQL statements can improve performance, but the wrong moves can have an adverse effect on database response time. Analyzing the prospective changes from a what-if perspective greatly reduces the chances that the intended modifications will degrade database performance.

Database Tools with What-If Analysis Functionality

Not all database tools provide the functionality required to perform what-if analysis. Precise for Databases is a monitoring and optimizing solution that supports the SQL Server, Oracle, Sybase, and Db2 database platforms. Its what-if analytical capabilities give database teams a powerful tool for maintaining optimal performance. 

An in-depth IDERA Webinar is available that provides a hands-on demonstration of using what-if analysis to tune database indexes. It offers a great introduction to the tool and points out multiple ways in which incorrectly defined or unused indexes can degrade database performance. 

How Precise Improves Database Performance

Precise samples data at a minimum of once per second and maintains a historical repository from which to draw analytical insight. The volume of data the tool monitors and stores allows it to reliably perform what-it analysis. DBAs can then view a list detailing resource utilization of SQL queries to identify the statements that would most benefit from optimization. 

The application offers valuable recommendations that will result in better-performing databases. In many cases, performance issues that may have caused concern over hardware provisioning or database version can be alleviated by correctly tuning and using indexes. 

Following are some of the specific scenarios addressed by Precise for optimizing database indexes:

Modeling and analysis to trim the number of indexes

Precise measures object usage over time and can find indexes that are unused or barely used for query execution. This saves space and overhead otherwise spent in processing irrelevant data. The tool searches for indexes with few keys so DBAs can analyze their usefulness. What-if analysis lets teams know if they can safely drop indexes and evaluate the impact of changes on SQL queries. 

Recommend index/model changes

Index and model changes that may be beneficial to database performance are recommended by Precise. Identify long-running statements and perform what-if analysis based on recommendations that are considered for implementation. SQL statements that are used by specific application transactions can be identified for precision tuning.

Index recommendations on objects

Precise also offers index recommendations on objects that are useful for reporting and third-party apps where there is little control over SQL statements. Indexing reporting objects can result in big performance increases which allow reports to be generated on time.

The what-if index analysis available in Precise for Databases gives DBAs a reliable tool for tuning their databases. A free download can be arranged, allowing teams to test the functionality of the tool and see how beneficial it will be in their environment. We suggest giving it a shot.

Try Precise for FREE!

Additional resources:

Download the Datasheet to Learn More – Precise Overview

“Precise for” Applications: