【SQLpassion Performance Tuning Training Plan】Week 11: Recompilations

Today I want to talk about Recompilations in the SQLpassion Performance Tuning Training Plan. 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 introduce an additional CPU overhead to your SQL Server.

What are Recompilations?

In the first step I want to lay out the difference between compilations and recompilations in SQL Server. 2 weeks ago we 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 happen before query execution starts.

 

A recompilation on the other hand 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, but which was dropped in the mean time. This would lead to unacceptable results. SQL Server triggers recompilations of two types:

 

 

 

• Correctness-based Recompilations
• Optimality-based Recompilations

 

 

 

Let's have a more detailed look at each. A Correctness-based Recompilation happens when a plan is not correct anymore. Imagine you change 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. 

 

An 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 a more detailed look now at a specific and common scenario that triggers a lot of different recompilations during the execution of a query - Temp Tables!

Temp Tables

You read that right: when you work with Temp Tables, you are causing recompilations in SQL Server. Let's have a look at 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. Here you are introducing an Optimality-based Recompilation.

 

How can you avoid both recompilations? You can use Table Variables(adj. 变量的；可变的；) 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 may introduce another performance problems with Table Variables: because they have no statistics, SQL Server always estimates just one row, so your Cardinality(n. 基数；基准；) 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 deal with a larger amount of data, you should still use Temp Tables, because they give you accurate(adj. 精确的) statistics, and you are also able to index them. The drawback(n. 缺点，不利条件；) is that recompilations are triggered by them.

Summary

Today we have talked about Recompilations in the SQLpassion Performance Tuning Training Plan. As you have seen Recompilations are always happen because SQL Server has to ensure the correctness of your execution plans. We have also looked at one specific scenario where recompilations always happen - 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.