重新编译后存储过程运行速度快 [英] Stored Procedure runs fast after recompile
问题描述
我对 SQL Server 2008 R2 上的存储过程有一个非常奇怪的问题.有时，大约每个月一次，我有一个程序变得非常慢，运行大约需要 6 秒而不是几毫秒.但是如果我简单地重新编译它，不做任何改变，它会再次快速运行.不是所有的存储过程都会发生，只有一个(服务器上有几百个).
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).
我的猜测是在编译 sp 时，它会被缓存，并且每次我调用它时都会重用这个缓存，而这个缓存版本由于某种原因而损坏.
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.
我希望也许有些人已经遇到过这种问题，或者至少可以为我指明正确的方向，比如 SQL Server 或 IIS 的哪些配置会影响存储过程缓存?
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 ?
代码如下:
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
推荐答案
当您第一次编译存储过程时，它的执行计划会被缓存.
When you first compile a stored procedure, its execution plan gets cached.
如果 sproc 的参数的定义可以显着改变包含的查询的执行计划(例如索引扫描与搜索)，则存储过程的缓存计划可能不适用于所有参数定义.
If the sproc has parameters whose definitions can significantly change the contained query's execution plan (e.g. index scans vs seeks), the stored procedure's cached plan may not work best for all parameter definitions.
避免这种情况的一种方法是在 CREATE PROCEDURE 语句中包含一个 RECOMPILE 子句.
One way to avoid this is to include a RECOMPILE clause with the CREATE PROCEDURE statement.
示例:
CREATE PROCEDURE dbo.mySpro
@myParam
WITH RECOMPILE
AS
BEGIN
-- INSERT WORKLOAD HERE
END
GO
通过这样做，每次调用该过程时都会生成一个新计划.如果 重新编译时间 <由于使用错误的缓存计划而损失的时间，值得使用WITH RECOMPILE.在您的情况下，每次您注意到此过程执行缓慢时，它还会为您节省手动重新编译此过程所需的时间/计划.
By doing this, a new plan will be generated each time the procedure is called. If recompile time < time lost by its using the wrong cached plan, this is worth using WITH RECOMPILE. In your case, it will also save you the time/planning required to manually recompile this procedure every time you notice it is executing slowly.
这篇关于重新编译后存储过程运行速度快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
