title: Debugging a strange scenario in SQL Server 2016 with regards to stored procedure execution
body: <p>In our organization, we have SQL Server VM on Azure with always on availability group with 2 nodes.</p>
  
  <p><strong>Scenario:</strong></p>
  
  <ol>
  <li><p>We have one procedure called "SP_xyz" and it contains one select query with few inner joins to get list of credential holders. After some load, this stored procedure (SP) started running slow and hence we have optimized this and put that SP back in production and it was running fine for some time.</p></li>
  <li><p>After couple of months as load increased, again there is slowness issue in this SP and again we analysed this SP and optimized. Now the mystery comes, Just to cross verify the new optimized SP, we created the same SP with _test in production. The new SP is "SP_xyz_Test".</p></li>
  <li><p>When we ran this new _Test SP in prod with same set of parameters for which old SP (SP_xyz) was running slow, the new optimized SP gave results in milliseconds against few seconds of older SP. </p></li>
  <li><p>To our surprise, the next movement when we ran the old SP, it also started giving results in milliseconds. This really scared us as where all this kind of issue would be there in production, as we have around 300+ SQL stored procedures.</p></li>
  </ol>
  
  <p>We did analyse few things that we could think of to find the root cause:</p>
  
  <ol>
  <li>Index rebuild</li>
  <li>Stats update</li>
  </ol>
  
  <p>Also as we know the SP execution plan would be specific to SP name. But here how the old SP has become faster is what we are wondering.</p>
  
  <p>But all these things have been scheduled and were running in production and old SP started running slow. But the movement the new _test SP ran, it has become very fast.</p>
  
  <p>Have we missed anything here, and has anybody has faced this issue before?</p>