Sql Server:Performance Counter to Count Stored Procedure Re-compilations

In an ideal situation a stored procedure is compiled once and forth coming queries related to this stored procedure are satisfied with already created query plan. The following actions may cause recompilations of a stored procedure plan:

• Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.
• Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.
• Running sp_recompile for a table referenced by the procedure.
• Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
• Sufficient server activity causing the plan to be aged out of cache
• A sufficient percentage of data changes in a table that is referenced by the stored procedure.
• The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

These recompilations of stored procedure add overhead on the processor. We should closely monitor the occurrence of recompilation of these stored procedures.  Performance monitor counter Sql Re-Compilations/sec is very helpful counter for this recompilation monitoring task.

Recommended value for SQL Re-Compilations/sec is close to ZERO. If nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures. Sql Profiler is a nice tool for this task.

How to monitor Sql Re-Compilation/sec counter values.

Step 1

On database server machine  --  > click on RUN -- > type Perfmon -- > delete existing counters by clicking on cross button on top.

Step2

           Click on + button to add new counter. Add counter Form select “SQL Server: SQL Statistics”. Now select “SQL Re-Compilations/sec” from counters list. Click “Add “button . Click “Close” button to view graphical view of performance monitor.