SQL Server Cache and Stored Procedure Recompilations - Microsoft SQL Server 2012 Performance Tuning - page 182

We then reset the value of Duration to 60 and the value of Maximum to 25 , so that the
graph's maximum CPU usage value is 25 percent (on the vertical axis) and graph's maximum
time length is 60 seconds (on the horizontal axis).
After setting up Reliability and Performance Monitor, we start SQL Server
Management Studio and execute the T-SQL script that creates the stored procedure
dbo.usp_GetSalesOrderDetail_ProductID . The script first checks whether the
stored procedure already exists. If it exists, it is dropped before it is recreated. The stored
procedure accepts a value for the @ProductID parameter, and based on the ProductID
value, it retrieves data from the Sales.SalesOrderDetail table.
We executed the stored procedure dbo.usp_GetSalesOrderDetail_ProductID by
passing 764 as ProductID . Because the stored procedure was executed for the first time,
it was compiled and then executed.
Because we wanted to cause recompilation, we rebuild an existing non-clustered index,
that is, IX_SalesOrderDetail_ProductID , which has been defined on the ProductID
column. Rebuilding an index on a table that is referenced by a stored procedure causes that
stored procedure to recompile.
After rebuilding the index, we executed dbo.usp_GetSalesOrderDetail_ProductID
a second time. Because the index IX_SalesOrderDetail_ProductID was rebuilt, the
stored procedure got recompiled and then executed.
Finally, we saw that the Reliability and Performance Monitor tool gathered statistics related
to compilation and recompilation events for the performance counters that we had added. If
the statistics/frequency of recompilation events is found to be very high, you can determine
that you are probably facing a recompilation issue.
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.