How to Improve Execution Plans During SQL Performance Tuning

SQL Server is designed to put considerable effort into creating an efficient execution plan at the time of statement execution, which can prove expensive. However, SQL Server tries to cache the generated plans to reduce costs, among other steps.

In this article, we will talk more about those steps and the benefits they bring during SQL performance tuning. For instance, this eliminates the need for subsequent calls to the same query to generate the plan.

Under the perfect conditions, the performance of each execution for the same statement would be consistently excellent with a single plan. But that isn’t always the case due to multiple factors affecting the outcome of the plan for various executions.

Here, we will focus on the ways to get around the causes rather than elaborate on them. We will also discuss the pros and cons of these methods.

1. Removing Bad Plans

Getting rid of a bad plan from the cache is one of the simplest ways to ensure proper Oracle SQL performance tuning and optimization. It’s especially useful in situations where parameter sniffing is involved as it increases the chances of success.

There are two ways of freeing bad plans from the cache:

● Through recompilation of a saved procedure (sp_recompile): Calling this command on a saved procedure that contains the statement with the poor plan often helps. It is because ‘sp_recompile’ results in the creation of a new plan for all the saved queries in the particular procedure.

● Using ‘dbcc freeproccache’ with a plan handle in the form of input is something experts recommend as it controls the plans you free from the cache. SQL Grease users can generate this command using the Copy flush command button.

2. Pushing Good Plans forward

This option is quite useful from the perspective of SQL performance tuning given that you have a good plan beforehand. It can help you null the effects of bad plans, which you can do in two ways:

1. Query Store — For this, your database needs to be an SQL Server 2016 or later. You will also need the query store enabled on it so you can force the use of a good plan for a statement specifically. Keep in mind that query store is disabled by default in nearly every database in use these days. However, it requires enabling during the utilization period of the good plan so it can force it.
2. Plan Guides — Not everyone feels inclined to create these are it involves a complicated process. On the other hand, these enable users to specify the execution plan they want as XML to force the statements to use it every time it is encountered.

You can generate a Plan Guide using different techniques that we will mention below. These are also recommended by experts in Oracle SQL performance tuning and optimization. An important thing to note before we proceed, however, is that you will have to drop the current Plan Guide (if you’ve applied one to a saved procedure) and carry out an update for the same procedure later on.

● Create Plan Guide command- A difficult approach that typically requires quite a bit of trial and error. It also requires some time and research through the Microsoft documentation.

● Handle Command- An easier way to achieve the task using an innovative feature in this database. A prerequisite for using this technique, however, is to have the plan of your choice in the cache when you carry out this command.

● Creating a Plan Guide using a Hint- Although this method depends on the Handle command, it is a simplified process that doesn’t require a good plan to exist in the plan cache.

Final Thoughts

A majority of situations involves the optimizer creating useful and productive execution plans. However, there are a few cases in which the optimizer cannot do so on a consistent basis. In such cases, you can make use of the techniques and tips mentioned above.