Monitoring recompilations using SQL Server Profiler

If you notice a large number of recompilations in the Reliability and Performance Monitor tool and suspect that your database server is facing a recompilation issue, you would want to confirm it by investigating the issue further, to identify which queries of which databases are causing the recompilation issues.

To investigate the root cause of the recompilation issue, we will use SQL Server Profiler. In this recipe, we will learn how we can trace recompilation events occurring on SQL server for individual SQL statements and stored procedures as well. With the help of SQL Server Profiler, we can identify the databases where recompilations occur and can identify queries and stored procedures that cause query recompilations. After identifying recompiling queries and stored procedures, you can look further into those queries and optimize them to minimize recompilation issues.

Getting ready

In this recipe, we will trace the following recompilation...