We DBA’s have all experienced query recompilation in our SQL Server environment. The entire journey of a query, right from inception till the formation of the Query Plan followed by execution is very interesting and involves a lot of internal processes. Recompilation is nothing but the re-initiation of compilation of a query when a certain compilation check fails. So what is the underlying algorithm or mathematics that the SQL server uses to decide whether to recompile or not. Here is how it works:

ABS( colmodctr(current) – colmodctr(snapshot)) ) >= RT

1) RT = Each column has a recompilation threshold (RT) associated with it. RT is a function of the number of rows in a table. We will discuss how RT is calculated later in this article.

2) colmodctr = This counter tracks the number of modifications that a table has undergone. The counter is not transactional in nature, which means if 1000 rows were inserted into the table and then rolled back then the counter value would not be rolled back.

3) colmodctr(current) is the current value of the counter and colmodctr(snapshot) is the value of the counter when the query plan was last compiled.

So when this absolute value crosses the RT, it is then that the query is recompiled. The next quetion is how does the engine decides or calculates the RT. Here is how it is done:

1) For permanent table:If n<=500 then RT = 500.If n>500 then RT = 500 + 0.20 * n.

2) For temporary table: If n<6 then RT = 6.If n between 6 and 500 then RT = 500. If n>500 then RT = 500 + 0.20*n

3) For Table variable: RT does not exist. They are not recompiled.

Here n stands for numbers of rows in the table.

I hope this concise article was helpful in understanding the decision making process for Recompilation.

Tagged: Microsoft SQL Server, Plan Cache, Query Re-compilation