Is there any way to overcome parameter sniffing in SQL Server?
<p>I came across parameter sniffing when one of my queries took a much longer time to execute than expected. When I delved a little deeper into this problem I came to know that:</p>

<blockquote>
  <p>When first time query gets executed it (SQL Server) creates execution plan for that query and for other n number of times same query executed and if it has large variance in result set with first time execution then parameter sniffing problem occurs".</p>
</blockquote>

<p>This was in my scenario.</p>

<p>Now my question is, is there any way or workaround to overcome parameter sniffing in SQL Server in these scenarios?</p>

<ul>
<li><p>I know by running <code>sp_updatestats</code> I can verify whether it is happening or not.</p></li>
<li><p>And also I know to catch the problem, I need to monitor the procedure cache, by <code>query_hash</code> and <code>query_plan_hash</code> fields of <code>sys.dm_exec_query_stats</code> I can do this.</p></li>
<li><p>I don't want to use <code>RECOMPILE</code> in <code>SET</code> variable section as this will create new execution plans each time query is executed.</p></li>
</ul>

<p>But instead of verifying the problem I want to overcome it by doing something in query itself, I mean for example "Detect the problem at run time and create new execution plan only as and when required(Not every time)".</p>

<p>I face parameter sniffing problem quite frequently, so every helpful suggestion and help will be greatly appreciated. Thanks in advance!</p>