Recompilation may not happen if you are writing a simple select as in - select 1,2. If you are selecting from a table or tables and there is filtering involved, the SP will get recompiled at run time. This is because the optimizer did not have a plan for the dynamic part of the SP and has to generate a plan at run time. When the query is like select 1, 2 the values are supplied inline.

Check this out with the following query. Check the last execution time column. My guess is that you will see multiple rows for a single execution of the SP, probably two rows.

SP recompilation will also happen if you use temp table in the SP and insert a certain number (6 IIRC) of records in the temp table. You may want to flush cache before running the script.

DBCC freeproccache

DBCC dropcleanbuffers

SELECT TOP 10 qs.*

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.sql_handle) qp

WHERE st.text LIKE '%select ''me''%'