title: Why adding one more parameter to the Stored Procedure makes it very slow
body: <p>I have a SP with 5 parameters <code>@State,@StartDate,@EndDate,@AsOfDate,@SICCode</code>. If I execute SP and supply parameters it returns the data ~ 1 sec. 
  Then if I add another parameter  <code>@Coverage</code> then it takes ~ 17 sec to execute it. </p>
  
  <p>I looked at the Cache Plan with and without <code>@Coverage</code> parameter and it seems like it looks the same.
  I used tuning adviser and created all possible indexes.</p>
  
  <p>If I run same code as <code>SELECT</code> statement and provide the same values as parameters in <code>WHERE</code> clause - then I got the result for ~1 sec. 
  So why only when I add this exact one parameter @Coverage it slows down performance so significantly?</p>
  
  <p>If it a parameter sniffing then how can I be sure that it is? Is any ways to check? </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
  @State varchar(50),
  @StartDate datetime,
  @EndDate datetime,
  @AsOfDate datetime,
  @SICCode nvarchar(max),
  @Coverage varchar(100)
  
  AS
  BEGIN
  SET NOCOUNT ON;
      CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
      INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM PlazaInsuranceWPDataSet WHERE SICCode IN (SELECT * FROM [dbo].[StringOfStringsToTable](@SICCode,','))-- ('0161','0173','0179','0181','0191','0722','0752','0761','0782','1440','1441','1442','1521','1522','161','1611','1629','1711','172','1731','1742') 
      CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber) 
      ; WITH Earned_to_date AS (
         SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
      ), policy_data AS (
          SELECT
              PolicyNumber,
              Coverage
      ,       Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
      ,       Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
      ,       WrittenPremium
      --,     --State
              FROM PlazaInsuranceWPDataSet piwp
              WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber) 
                      AND Coverage IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))
                      AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,','))     
      )...
  </code></pre>
  
  <p>And this is the data I am working with:</p>
  
  <p><a href="https://i.stack.imgur.com/KHJJC.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KHJJC.png" alt="enter image description here"></a></p>