儲存過程 – 重新編譯後,儲存過程執行得很快

我在SQL Server 2008 R2上儲存過程有一個非常奇怪的問題.有時,每個月大約一次,我有一個程式變得非常慢,大約需要6秒執行而不是幾毫秒.但是,如果我只是重新編譯它,而不改變任何東西,它會再次執行.

所有儲存過程都不會發生,只有一個(伺服器上有幾百個).

我的猜測是當sp被編譯時,它被快取,這個快取在每次呼叫它時被重用,並且這個快取版本由於某種原因而被破壞.

我希望也許有些人已經面臨這種問題,或者至少可以指出我在正確的方向,像SQL Server或IIS的配置可能會影響儲存過程快取？

以下是程式碼：

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 < @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, '') <> '' )
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 >= @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 >= @dtDebPublication)
AND (@dtFinPublication IS NULL Or p.dtPublication <= @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

當您首次編譯儲存過程時,其執行計劃將被快取.

如果sproc具有引數,其定義可以顯著更改包含的查詢的執行計劃(例如,索引掃描vs查詢),則儲存過程的快取計劃可能對所有引數定義最有效.

避免這種情況的一種方法是將RECOMPILE子句包含在CREATE PROCEDURE語句中.

例：

CREATE PROCEDURE dbo.mySpro
@myParam
WITH RECOMPILE
AS
BEGIN
 -- INSERT WORKLOAD HERE
END
GO

通過這樣做,每當呼叫該過程時,將會生成一個新的計劃.如果重新編譯時間使用錯誤的快取計劃時間損失,這是值得使用WITH RECOMPILE.在您的情況下,它還會節省您每次發現其執行緩慢時手動重新編譯此過程所需的時間/計劃.