Is recompilation good or bad?

I am assuming you all know what recompilation means here. In simple words for those who don't know, SQL Server recompiles the statement (in sql server 2008) or procedure when it realises that Plan existing in cache is no longer beneficial or aged out due to specific reasons.

Recompilation is useful when new execution strategy is require to  process the query but as recompilation involves cost of extra CPU cycle, too often recompilation of a store procedure can be expensive.

You have to make sure you know the cause of recompilation of store procedure. There can be various reasons behind this .Few of them are:

·         Schema changes to the temporary tables define inside the body of procedure

·         General DDL changes to the local tables

·         Change in statistics of table due to lot of insertions or distribution of data

·         Force recompilation by using recompile hint

·         Changing the SET option

If you know the cause of too much recompilation (I am using the word too much as occasional recompilation is not concern here) then you should look to rectify that cause. For example if the frequent recompilation is happening due to change in distribution of data or statistic change then you can use Keepfixed plan hint to avoid so.

If you are not dealing with large amount of data sets then use table variables instead of temporary tables. Unlike temporary tables, table variables do not create statistics hence you can save unnecessary recompilations caused because of temporary tables.

Using brilliant tool like profiler, you can capture the statements causing recompilation. Once you know which statements are causing recompilation, you can dig into the cause and work on it.

So next time if your trace shows lot of recompiles you know what to look for :)