>Sql Server:Performance Counter to Count Stored Procedure Re-compilations
- 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.
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.