// Week 11: Recompilations

Today I want to talk about Recompilations in the SQL Server Performance Tuning series. A recompilation happens when you execute a query, and another activity within SQL Server invalidates the remaining part of the execution plan. In that case SQL Server has to ensure the correctness of your execution plan, and a recompilation is triggered. Recompilations are introducing an additional CPU overhead to your SQL Server.

What are Recompilations?

In the first step I want to lay out the differentiation between compilations and recompilations in SQL Server. 2 weeks ago we have talked about compilations in SQL Server. A compilation always happens in SQL Server when the Query Optimizer translates the submitted query into a physical execution plan. This means that the compilations happens before query execution starts.

A recompilation on the other hands happens during the execution of your query. For that reason SQL Server recompiles the remaining part of the execution plan to ensure its correctness. Imagine you reference an index in the execution plan, which was in the mean time dropped. This would lead to unacceptable results. SQL Server triggers recompilations based on the following two assumptions:

1. Correctness-based Recompilations
2. Optimality-based Recompilations

Let’s have a more detailed look on both. A Correctness-based Recompilation happens when a plan is not correct anymore. Imagine you are changing your database schema (adding & dropping indexes, dropping statistics), or when you change one of your SET options. In that case a recompilation has to happen so that your execution plan stays correct.

A Optimality-based Recompilation happens because your statistics have changed, because SQL Server has auto updated your statistics, or you have triggered a manual update of the statistics. In that scenario it could be the case that a Bookmark Lookup is now over the Tipping Point, and SQL Server has to introduce a complete Table or Clustered Index Scan.

Let’s have now a more detailed look on a specific common scenario that triggers a lot of different recompilations during the execution of a query - Temp Tables!

Temp Tables

You read correct: when you are working with Temp Tables, you are causing recompilations in SQL Server. Let’s have a look on a very simple stored procedure definition:

CREATE PROCEDURE DoWork
AS
BEGIN
   CREATE TABLE #TempTable
   (
      ID INT IDENTITY(1, 1) PRIMARY KEY,
      FirstName CHAR(4000),
      LastName CHAR(4000)
   )
   INSERT INTO #TempTable (FirstName, LastName)
   SELECT TOP 1000 name, name FROM master.dbo.syscolumns
 
   SELECT * FROM #TempTable
END
GO

The stored procedure creates a simple Temp Table, inserts some rows into it, and finally retrieves the rows from it. Simple, isn’t it? The problem is that this stored procedure triggers 2 recompilations during execution:

The first recompilation is triggered, because you are creating a new Temp Table. By creating a Temp Table you are changing your database schema. This triggers a Correctness-based Recompilation. The second recompilation is triggered, when you execute the SELECT statement. Previously you have inserted some rows into the Temp Table, therefore SQL Server has updated your statistics. You are introducing here a Optimality-based Recompilation. How can you avoid both recompilations? You can use Table Variables instead of Temp Tables. With a Table Variable you are not changing your database schema (it’s only a variable), and Table Variables have no statistics. Both recompilations are gone. But of course, you can introduce another performance problems with Table Variables: because they have no statistics, SQL Server always estimates just one row, so your Cardinality Estimation can be completely screwed up.

For that reason Table Variables have only specific use cases in SQL Server: when you are dealing with a small amount of data. When you are dealing with a larger amount of data, you should still use Temp Table, because they give you accurate statistics, and you are also able to index them. The drawback are the recompilations that are triggered by them.

Summary

Today we have talked about Recompilations in the SQL Server Performance Tuning series. As you have seen Recompilations are happening always, because SQL Server has to ensure the correctness of your execution plans. We have also looked on one specific scenario, where recompilations are always happening - Temp Tables.

These recompilations can be resolved by using Table Variables, but you have to be aware of the side-effects you are introducing here. In the next week I’m talking about Parallel Execution Plans in SQL Server, where the real fun happens. Enjoy your next 7 days, and see you very soon.