title: Stored Procedure runs fast after recompile
body: <p>I have a very weird issue with a stored procedure on SQL Server 2008 R2. Sometimes, about once every month, I have one procedure that becomes very slow, takes about 6sec to run instead of a few milliseconds. But if I simply recompile it, without changing anything, it runs fast again.
  It does not happen on all stored procedure, only one (there are a few hundreds on the server).</p>
  
  <p>My guess is when the sp is compiled, it is cached and this cache is reused every time I call it, and this cached version gets corrupted for some reason.</p>
  
  <p>I hoped maybe some people already faced this kind of issue, or could at least point me in the right direction, like what configuration of SQL Server or IIS could affect the stored procedure cache ?</p>
  
  <p>Here's the code:</p>
  
  <pre><code>USE [MyBaseName]
  GO
  /****** Object:  StoredProcedure [dbo].[Publication_getByCriteria]    Script Date: 05/29/2013 12:11:07 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[Publication_getByCriteria]
      @id_sousTheme As int = null,
      @id_theme As int = null,
      @nbPubli As int = 1000000,
      @bActuSite As bit = null,
      @bActuPerso As bit = null,
      @bActuNewsletter As bit = null,
      @bActuChronique As bit = null,
      @bActuVideo As bit = null,
      @bActuVideoBuzz As bit = null,
      @bActuOpportunite As bit = null,
      @id_contact As int = null,
      @bOnlyPublished As bit = 0,
      @bOnlyForHomePage as bit = 0,
      @id_contactForTheme As int = null,
      @id_newsletter As int = null,
      @ID_ActuChronique As int = null,
      @sMotClef As varchar(500) = null,
      @sMotClefForFullText as varchar(500) = '""',
      @dtPublication As datetime = null,  
      @bParlonsFinance As bit = null,
      @bPartenaires as bit = null,
      @bUne As bit = null,
      @bEditoParlonsFinance As bit = null,
      @bEditoQuestionFonds as bit = null,
      @dtDebPublication As datetime = null,
      @dtFinPublication As datetime = null,
      @bOnlyActuWithDroitReponse As bit = 0,
      @bActuDroitReponse As bit = null
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @dtNow As datetime
      SET @dtNow = GETDATE()
  
      SELECT TOP (@nbPubli) p.id_publication, p.sTitre, p.sTexte, p.sTexteHTML, p.dtPublication, p.id_linkedDroitReponse,
          si.id_actusite, pe.id_actuPerso, ne.id_actuNewsletter, ac.id_actuChronique, av.id_actuVideo, ap.id_actuOpportunite, ad.id_actuDroitReponse,
          c.ID_Contact, c.sPhotoCarre, NULL As sTypePubli, n.id_newsletter, 
          dbo.Publication_get1Theme(p.id_publication) As theme,
          CAST(CASE WHEN ad.id_actuDroitReponse IS NULL THEN 0 ELSE 1 END As bit) As bIsDroitReponse,
          coalesce(Personne.sNom, Societe.sNom) as sNom, Personne.sPrenom
      FROM Publication p
          LEFT OUTER JOIN ActuSite si ON p.id_publication = si.id_publication
          LEFT OUTER JOIN ActuPerso pe ON p.id_publication = pe.id_publication
          LEFT OUTER JOIN ActuNewsletter ne ON p.id_publication = ne.id_publication
          LEFT OUTER JOIN ActuChronique ac ON p.id_publication = ac.id_publication
          LEFT OUTER JOIN ActuVideo av ON p.id_publication = av.id_publication
          LEFT OUTER JOIN ActuOpportunite ap ON p.id_publication = ap.id_publication
          LEFT OUTER JOIN ActuDroitReponse ad ON p.id_publication = ad.id_publication
          LEFT OUTER JOIN Contact c ON p.id_contact = c.ID_Contact
          LEFT OUTER JOIN Personne ON Personne.id_contact = c.id_contact
          LEFT OUTER JOIN Societe ON Societe.id_contact = c.id_contact
          LEFT OUTER JOIN Newsletter n ON ne.id_actuNewsletter = n.id_actuNewsletter
      WHERE p.bSupp = 0
      AND (@bOnlyPublished = 0 Or (@bOnlyPublished = 1 AND p.dtPublication IS NOT NULL AND p.dtPublication &lt; @dtNow))
      AND (@id_sousTheme IS NULL Or p.id_publication IN(SELECT id_publication FROM PubliSousTheme WHERE id_soustheme = @id_sousTheme))
      AND (@id_theme IS NULL Or p.id_publication IN(SELECT id_publication FROM PubliTheme WHERE id_theme = @id_theme))
      AND ((@bActuSite = 1 AND si.id_actusite IS NOT NULL)
              OR (@bActuPerso = 1 AND pe.id_actuPerso IS NOT NULL)
              OR (@bActuNewsletter = 1 AND ne.id_actuNewsletter IS NOT NULL)
              OR (@bActuChronique = 1 AND ac.id_actuChronique IS NOT NULL)
              OR (@bActuVideo = 1 AND av.id_actuVideo IS NOT NULL)
              OR (@bActuVideoBuzz = 1 AND av.id_actuVideo IS NOT NULL and coalesce(av.sBuzz, '') &lt;&gt; '' )
              OR (@bActuOpportunite = 1 AND ap.id_actuOpportunite IS NOT NULL)
              OR (@bActuDroitReponse = 1 AND ad.id_actuDroitReponse IS NOT NULL))
      AND (@id_contact IS NULL Or p.id_contact = @id_contact)
      AND (@id_contactForTheme IS NULL Or 
              (p.id_publication IN(SELECT id_publication FROM PubliSousTheme 
                  WHERE id_soustheme IN(SELECT id_soustheme FROM ContactSousTheme WHERE id_contact = @id_contactForTheme)))
              Or (p.id_publication IN(SELECT id_publication FROM PubliTheme 
                  WHERE id_theme IN(SELECT id_theme FROM ContactTheme WHERE id_contact = @id_contactForTheme)))
              )
      AND (@ID_ActuChronique is NULL or id_actuChronique = @ID_ActuChronique)
      AND (@id_newsletter IS NULL Or p.id_publication IN(SELECT id_publication FROM ListActuNewsletter WHERE id_newsletter = @id_newsletter))
      AND (@sMotClef IS NULL 
          or contains((p.sTexte, p.sTitre), @sMotClefForFullText)
          Or Personne.sNom LIKE '%' + @sMotClef + '%' COLLATE Latin1_General_CI_AI
          Or Personne.sPrenom LIKE '%' + @sMotClef + '%' COLLATE Latin1_General_CI_AI
          Or Societe.sNom LIKE '%' + @sMotClef + '%' COLLATE Latin1_General_CI_AI
          )
      AND (@dtPublication IS NULL Or p.dtPublication &gt;= @dtPublication)
      AND (
          @bParlonsFinance IS NULL Or
          (@bParlonsFinance = 0 AND p.id_publication NOT IN(SELECT id_publication FROM PubliTheme 
                  WHERE id_theme IN(SELECT id_theme FROM Theme WHERE bParlonsFinance = 1)))
          Or (@bParlonsFinance = 1 AND p.id_publication IN(SELECT id_publication FROM PubliTheme 
                  WHERE id_theme IN(SELECT id_theme FROM Theme WHERE bParlonsFinance = 1))))
      AND (
          @bPartenaires IS NULL Or
          (@bPartenaires = 0 AND p.id_publication NOT IN(SELECT id_publication FROM PubliTheme 
                  WHERE id_theme IN(SELECT id_theme FROM Theme WHERE 0 = 1)))
          Or (@bPartenaires = 1 AND p.id_publication IN(SELECT id_publication FROM PubliTheme 
                  WHERE id_theme IN(SELECT id_theme FROM Theme WHERE 0 = 1))))
      AND (
          @bUne IS NULL
          Or p.bUne = @bUne)
      AND (@bEditoParlonsFinance IS NULL
          Or p.bEditoParlonsFinance = @bEditoParlonsFinance)
          AND (@bEditoQuestionFonds IS NULL
          Or p.bEditoQuestionFonds = @bEditoQuestionFonds)
      AND (@dtDebPublication IS NULL Or p.dtPublication &gt;= @dtDebPublication)
      AND (@dtFinPublication IS NULL Or p.dtPublication &lt;= @dtFinPublication)
      AND (@bOnlyActuWithDroitReponse = 0 Or (@bOnlyActuWithDroitReponse = 1 AND p.id_linkedDroitReponse IS NOT NULL))
      and (@bOnlyForHomePage = 0 or (@bOnlyForHomePage = 1 and ac.bHomePage = 1))
      ORDER BY coalesce(p.dtPublication, p.dtCreate) DESC, p.id_publication DESC
  END
  </code></pre>