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

We saw, in SQL Server Profiler, that the second execution of the stored procedure
caused stored procedure recompilation. This resulted in raising SP:Recompile and
SQL:StmtRecompile events, and can be seen in SQL Trace. This happened due to index
creation just before executing the stored procedure for the second time.
In SQL Trace, you can even examine the cause of recompilation by looking at the
EventSubClass column. The EventSubClass column shows 1 = Schema Changed, which
means that the recompilation has occurred due to change in schema. The TextData column
indicates the SQL statement that caused the recompilation.
In the last script that we executed in this recipe, we executed two identical queries that
retrieved data from the Sales.SalesOrderHeader table, based on the SalePersonID
value as 279 . The only difference between the two queries in this script is that the second
query uses the OPTION (RECOMPILE) query hint. The OPTION (RECOMPILE) query hint
instructs SQL Server to remove the query plan from the procedure cache, after its execution,
and forces recompilation the next time the query is executed. When we executed the script,
we noticed that, in SQL Trace, the SQL:StmtRecompile event was fired.
Examine the value in the EventSubClass column, which happens to be 11 - Option
(recompile). This suggests that the query hint OPTION (RECOMPILE) was used.
From the other columns, such as DatabaseName and ApplicationName, you can find out in
exactly which database recompilations occurred, and from which application.
There's more...
For further recompilation analysis, you can use the following dynamic management views
and functions:
F sys.dm_exec_cached_plans
F sys.dm_exec_sql_text()
F sys.dm_exec_query_stats
You can further analyze the causes of recompilations, based on the result of given DMVs and
DMFs. By using these DMVs and DMFs in combination, you can identify the queries whose
query plans are reused frequently.
For more information on query compilation and recompilation, you can refer to two good
whitepapers that can be found at the following locations:
F http://msdn.microsoft.com/en-us/library/
ee343986%28v=sql.100%29.aspx
F http://technet.microsoft.com/library/Cc966425
However, these whitepapers refer to older versions of SQL Server, though the information is
applicable to SQL Server 2012 as well.