Esat Erkec
Use SQL Profiller to track SQL query recompilations

SQL Query recompilations and query performance effects

September 1, 2021 by

This article will mention in which conditions the query optimizer decides to recompile to queries and how it affects the SQL query performance.

The Boss: Query optimizer

In a basic manner, when we submit a query to SQL Server it performs 3 essential phases:

  • Query Parsing: In this phase, the correctness of the query syntax is checked. In this phase, a parse tree is generated to send the next phase
  • Query Binding (Algebrizer): The main duty of this phase is to validate whether the columns, tables, and other objects exist in the database. At the same time, it checks the user has permissions to objects that exist in the query
  • Query Optimization: This step is the most complicated and query performance-related phase of query processing because the query plan is shaped in this step. SQL Server query optimizer performs a cost-based optimization therefore it evaluates different query plan candidates and decides the query plan that has the lowest cost. After the first execution of a query, the generated query plan is stored in plan cache to use for the next execution of the same query. At this point, different reasons can be caused to recompile the queries that are located in the query plan cache

The result of the following query gives us all possible query recompilations reasons.

Query recompilations and query performance

  • Schema changed
  • Statistics changed
  • Deferred compile
  • Set option change
  • Temp table changed
  • Remote rowset changed
  • For browse permissions changed
  • Query notification environment changed
  • PartitionView changed
  • Cursor options changed
  • Option (recompile) requested
  • Parameterized plan flushed
  • Test plan linearization
  • Plan affecting database version changed
  • Query Store plan forcing policy changed
  • Query Store plan forcing failed
  • Query Store missing the plan

Monitor the query recompilations

SQL Server Extended Event is a system monitoring tool that helps to monitor the database performance metrics and collects different events. So that, we can easily use extended events to resolve the query performance problems. Moving from this idea, using an extended event to monitor the recompilations seems a suitable solution. The sql_statement_recompile event can capture and report when a statement-level recompilation has occurred. The following query will create and start an extended event that helps to report when a query is recompiled.

On the other hand, SQL Profiler is another tool to monitor the recompilations, and SQL: StmtRecompile event class reports when a recompilation occurs. To enable this event class in SQL Profiler we need to select them in the Trace Properties. So, we can use SQL Profiler to monitor query performances but notice that this SQL is deprecated. In the Adventureworks database, we will execute the following query and it will contain the OPTION(RECOMPILE) query hint. Due to this hint, the executed query will be recompiled by the optimizer.

When we look at the created extended event, it will capture the query recompilation and what reason causes the recompilation.

Capture query recompilations with extended event

At the same time, the SQL Profiler shows a report after the execution of the query and it includes query recompilation reason.

Use SQL Profiler for query recompilations

Schema changings and query recompilations

Sometimes, we require to change the design of the tables in the database. Such as we can add new columns or change the data type of an existing column. At the same time, we can create, alter or remove indexes on the tables. These types of changes will cause query recompilations. For example, we will change the Description column data type of the ProductDescription table and then re-execute the query.

As we can see, a query recompilation occurred because of the column data type changing operation.

Schema changings can cause to query recompilations

Index rebuilds and query recompilations

Indexes are very special database objects and they help to retrieve data fastly from the databases. Because of this feature, they increase the query performances. However, data modifications may corrupt the logical orders of the indexes. In this type of fragmentation, the new page isn’t in the same order as the physical order so logical fragmentation occurs. In order to resolve this problem, we can rebuild the logical order of the index pages. Through the following query, we can rebuild all indexes of the Product table.

When we re-execute the sample query after the index rebuilding operations, we will see that the query will be recompiled by the query optimizer due to schema changed reasons.

Index rebuildings can cause query recompilations

SQL Server statistics and query recompilations

SQL Server statistics plays a critical role in query performance because the query optimizer uses statistics to estimate how many rows will return from a query. The query optimizer can create statistics for an individual column during the execution of the query if we enable the Auto Create Statistics option of the database. After creating statistics, the query optimizer recompiles the executed query, assuming there will be more up-to-date statistics data. Now let’s learn this concept more deeply with an example. At first, we will create a table and then execute a very basic query.

After the execution of this query, SQL Server stores its execution plan into plan cache.

Now, we will insert some rows into the TestNewProduction table.

As the last step, we will execute the sample query again. In this case, the query optimizer will decide to create new statistics for the Name column and recompile the query.

The extended event captures the recompile event of the query optimizer and it shows the recompile reason.

SQL Server statistics and query performance

On the other hand, SQL Profiler shows all events more clearly and it can provide more data for the query performance metrics.

Use SQL Profiller to track SQL query recompilations

After execution of the query the following steps are performed:

  1. Query optimizer recompile the query due to the statistics changed reason
  2. A new statistic creates for the Name column because this column is used in the where condition
  3. A new statistic creates for the PModelID column because this column is used in the join statement
  4. Finally, the query is completed

As we can see in this scenario, resolving the query performance issues requires understanding the behavioral effects of the query optimizer.

Another point about statistics is that data modifications cause statistics to become stale. SQL Server Statistics stores the data distributions in the histograms but after the data modifications, histogram data will become out of date. Manually or automaticly updating the statistics causes query recompilations. For example, let’s add 500 new rows to the TestNewProduction table and understand how this scenario happens.

To capture the auto-update statistics event, we need to make some modifications in our extended event therefore we can easily observe what is happening behind the scene. The auto_stats event can capture when automatic updating of column statistics events has occurred.

After recreating the extended event, we will execute our sample query again and check out the captured events.

As seen in the image, the extended event has captured a query recompilation event as we expect.

Auto_stats event of the SQL Server extended event

The auto_stats indicates statistics-related events. When we click the first one we can see all details about this event.

Auto update statistics

SET options and query recompilations

The SET options enables us to change the session-level behavior of the SQL Server so that we can modify various options with these options. Another important point about the set option is may cause query recompilation. Such as, SQL Server Management Studio (SSMS) and application connection options can be different and it causes different execution plans. For example, if we change the ARITHABORT and NUMERIC_ROUNDABORT connection options, the query optimizer will decide to recompile the query.

Query performance may related to set options of the connection level

The extended event shows us the recompile reason and it is clear that set option change can cause query recompilations.

Conclusion

In this article, we have explored which reasons can cause query recompilations and their interaction with query performance. At the same time, we have learned how we can monitor the query recompilation events with different two methods.

Esat Erkec
Latest posts by Esat Erkec (see all)
615 Views