SQL Performance Tuning for Slow Queries: Finding their Causes

Performance problems are known to arise in SQL for a number of reasons. One of the most common of these is new software releases as they often result in changes to the code or database schema.

Database performance can also take a hit after a patch or version upgrade takes place in Oracle or SQL Server. However, there are times when the database provides poor performance without any apparent reason.

In this blog, we will explore one of the major reasons behind this problem, how to identify it, and a few steps to overcome it using SQL performance tuning.

Identifying the Cause Behind Inadequate Database Performance

Database professionals may remember a situation like this all too well: everything’s going well – the system is working fine, and the DBA has done it all to make sure they continue to do so, including query tuning, index insertion, refreshing the statistics, and so on. One fine moment, however, they suddenly start receiving alerts and notifications from seemingly everywhere at once complaining about one process or another running slow.

Despite being on top of things, they have little clue of the possible causes behind this issue. In such cases, here’s what they can do to determine what’s wrong before beginning Oracle SQL performance tuning and optimization:

• Analyze existing queries to locate the costliest ones when the database has slowed down. Find out what was going on inside the database to slow things to this rate. SQL Monitor is wired to gather this type of data, so look for a long-running query alert. You can also use Extended Events to manually collect the diagnostic information before setting up the necessary alert conditions for future alerts.

slowest that require SQL performance tuning

Once you open SQL Monitor, locate the affected instance and assess the interactive activity graph for it. You will find a list of the most expensive statements in the chosen time period of the interactive graph along with all the associated details regarding their runtime characteristics.

• Assess the following for the queries you have found to be the most expensive or the slowest that require SQL performance tuning:
  • Query plan
  • Execution statistics
  • Wait-types

You can do this by clicking on the query, though you will have to use a command for Dynamic management views and the Query Store if you need to collect this data manually. A T-SQL query, courtesy of SQL Monitor, enables users to look at and fetch the query plan from cache.

• Go through the statement’s performance when the performance dipped. Were there any changes in the execution plan as well?

SQL Monitor

As you may know by now, SQL Monitor can help you gain the exact details, including the executions per minute. This is where you will start to get a picture of what went wrong. It could be something harmless, such as a procedure that stopped being executed by the application. On the other hand, it could be something bigger, so you will need a bit more information for clarity on the issue.

For this, you will have to look for any changes that the execution plan had undergone around the time the issue took place. It will be wise to implement Oracle SQL performance tuning and optimization after you have done so.

• Look into the fluctuations in resource usage and performance during both “normal” and “poor” performance to uncover any possible changes and their effects

SQL performance tuning

Compare the server activity with the help of the baselines in SQL Monitor over both “normal” and “slow” performance. This will help you confirm whether the database has, in fact, gotten sluggish. This will help you even more in case the issue takes place at particular times of the day, such as during planned task execution. You can also evaluate various metrics for different time periods or the same time period over different days. There is a catch, though – baselines are no good for query metrics; they only track server and database metrics.

• Confirm your suspicions regarding slow queries and begin SQL performance tuning or SQL server tuning tools. All of the above steps will help you uncover the real cause behind slow database performance. Generally, database professionals are able to uncover minute changes in query speeds that led to a sudden drop in their execution speed. The most common culprits are changes in the execution plan, missing indexes, and improper index use, among others.

Checking on the statistics will lead you to further clarity on the issue if there isn’t enough already. You may find the optimizer switched the plan after deciding that the data access method isn’t the most efficient one anymore.

To Conclude

The main reason behind dramatic performance shifts can vary to a large extent. Whether it is a missing index or something worse, the procedure to identify the root cause remains the same, more or less.

Using the SQL Monitor often proves the best way to help diagnose the reasons behind performance dips. That is because of the wealth of information it is capable of sharing with the DBA, such as resource metrics, query plans, and other statistics. All of these prove valuable in picking out changes in the database that require SQL performance tuning.

Also Read: igfxpers exe application error