netsqlazman_CheckAccess Performance

Topics: SQL Database Schema
Jan 18, 2011 at 8:53 AM

More than 70% of all SP compilations are recompilations .

A Recompile occurs when SQL Server believes that the execution plan for a stored procedure that is currently executing may no longer be the best possible plan.

SQL Server pauses the query execution and compiles the stored procedure again.

This not only slows down the process that is executing the procedure, but adds extra CPU load on the server.

Lots of recompiles can add an excessive CPU load on SQL Server and slow down everything running on that machine.

In general, the fewer recompiles the better.

 

There are many conditions that can cause SQL Server to recompile a stored procedure. The most common are:

 

  1. Schema changes to any of the referenced objects, including adding or dropping indexes, constraints, defaults, or rules.
  2. A sufficient percentage of data changes in a table that is referenced by the stored procedure.
  3. Stored procedures with a mix of Data Definition Language (DDL) and Data Manipulation Language (DML) operations.
  4. Stored procedures performing certain operations on temporary tables.
  5. The use of the WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.
  6. Running sp_recompile against any table referenced in the stored procedure.
  7. Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
  8. High server activity causing the plan to be aged out of cache.