Different execution count for queries in a stored procedure
<p>I've executed a stored procedure with the following T-SQL code:</p>

<pre><code>exec myStoredProcedure
go 10
</code></pre>

<p>After the execution of the procedure I reviewed the information in <code>sys.dm_exec_query_stats</code> and observed that for some queries in the stored procedure, the value in <code>Execution Count</code> is different.</p>

<p><a href="https://i.stack.imgur.com/Umrtm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Umrtm.png" alt="enter image description here"></a></p>

<p>Apparently, some execution plans have been ran only a number of times, 7 out of 10, for some queries in the stored procedure.</p>

<p>The data in the above screenshot is being returned with the following query:</p>

<pre><code>select ...
    qs.execution_count [Execution Count]
from sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
    cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where st.objectid = object_id('myStoredProcedure')  
</code></pre>

<p>And as you can see, there is no other execution plan stored for this procedure where the execution count is <code>3</code>, thinking that the Optimizer decided to run a query with another plan.</p>

<p>Truth is some of the queries that have an execution count of <code>7</code> are inserts into different temporary tables, with <code>SELECT INTO #temptable</code>, but not all of them.</p>

<p>So, <strong>my question is</strong> why do some queries have a plan which has been executed a smaller number of times than others and how did those queries execute and with what plan?</p>

<p><em>I'd like to mention that there is no logic in the stored procedure that would generate different execution flows so that some queries do not get executed. (no <strong>IFs</strong>)</em></p>

<p><strong><em>Also</strong>, no statistics update or DML queries have been ran in the meantime so that row-count or index changes have occurred.</em> </p>

<p>Is my query which goes over the <code>DMV</code> not correct and does not pick up these "rogue" plans? Or has the data been cached in memory / tempdb for the temporary tables and read from there on subsequent executions?</p>

<p><strong>Update:</strong></p>

<p><em>Added a screenshot containing the column with <code>plan_generation_num</code> requested by <code>@MartinBrown</code></em></p>

<p><a href="https://i.stack.imgur.com/SybT5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SybT5.png" alt="enter image description here"></a></p>