A bit more on recompilation in SQL Server

SQL Server takes care of compilation and recompilation automatically, as we all know. The first time a statement is executed, the SQL Server query optimizer checks if there is a plan already cached, looks at the statistics related to the objects involved in the statement and uses a sophisticated algorithm to decide whether it should use the existing cached plan (if it exists) or if it should generate a new one.

So far so good. But recently I received an email from a friend of mine who was asking me about a ‘strange behavior’ of a stored procedure in their system. At times the stored procedure was taking 2 minutes to execute, at other times it was taking less than a second.

After some debugging and tracing, it turned out that SQL Server was using a sub-optimal query plan for the execution of the stored procedure, and once the ‘bad’ plan was cached it was reused for some time before the query optimizer had to recompile the stored procedure and create a ‘better’ plan.

There can be several reasons for this behavior, and I will write a separate blog post on this.

For now, though, I would like to mention the possibility to manually force recompilation.

The SQL Server database engine supports a system stored procedure sp_recompile, which is used to force recompilations. It can be used by passing a @object parameter. The object name can be

  • stored procedure
  • trigger
  • table
  • view

If the object passed is of a type table or trigger, then the object is marked for recompilation and the next time it is invoked, the execution plan is recreated.

If the object passed is table or a view, then all stored procedures and views referencing the object will be recompiled next time they are run.



Comments are closed.