La procédure stockée s'exécute rapidement après la recompilation

J'ai un étrange problème avec une procédure stockée dans SQL Server 2008 R2. Parfois, environ une fois tous les mois, j'ai une procédure qui devient très lent, prend environ 6sec pour exécuter au lieu de quelques millisecondes. Mais si j'ai simplement recompiler, sans rien changer, il court vite de nouveau.
Il ne se fait pas sur l'ensemble de la procédure stockée, une seule (il y a quelques centaines sur le serveur).

Ma conjecture est que lorsque le ps est compilé, il est mis en cache et le cache est réutilisé à chaque fois que je l'appelle, et cette version en cache est corrompu pour une raison quelconque.

J'espérais peut-être que certaines personnes ont déjà été confrontés à ce genre de problème, ou pourrait au moins me diriger dans la bonne direction, comme ce que la configuration de SQL Server ou IIS pourrait affecter la procédure stockée en cache ?

Voici le 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 < @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

source d'informationauteur Michael

  1. 23

    Lorsque vous compilez une procédure stockée, son plan d'exécution, mise en cache.

    Si la procédure stockée a des paramètres dont les définitions peuvent considérablement modifier le contenu de la requête d'exécution du plan (p. ex. analyses à index vs cherche), la procédure stockée en cache de plan peut ne pas fonctionnent le mieux pour toutes les définitions de paramètres.

    Une manière d'éviter cela est d'inclure une RECOMPILE la clause de l' CREATE PROCEDURE déclaration.

    Exemple:

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

    Ce faisant, un nouveau plan est généré chaque fois que la procédure est appelée. Si recompile time < time lost by its using the wrong cached plancela vaut la peine de l'aide WITH RECOMPILE. Dans votre cas, il sera également vous faire économiser temps/planification nécessaire pour recompiler manuellement cette procédure chaque fois que vous remarquez qu'il est en cours d'exécution lentement.

  2. 2

    Pour une procédure stockée avec que de nombreux paramètres, il serait judicieux d'ajouter OPTION(OPTIMIZE FOR UNKNOWN) à la fin de la requête pour indiquer au compilateur de ne pas optimiser le plan d'exécution pour certains paramètres.

    Ce que SQL Server n'est la première fois qu'il exécute une procédure stockée est d'optimiser le plan d'exécution(s) pour les paramètres qui lui ont été transmis. Ceci est fait dans un processus qui est appelé Parameter Sniffing.

    En général, les plans d'exécution sont mis en cache par SQL Server, de sorte que SQL Server n'a pas à recompiler à chaque fois pour la même requête. La prochaine fois que la procédure est exécutée, SQL Server ré-utiliser le plan d'exécution(s) pour les requêtes en... Cependant, le plan d'exécution(s) peut être totalement inefficace si vous appelez la procédure stockée avec des paramètres différents.

    D'ajouter l'option que j'ai mentionnés seront dites pour le SQL compilateur que le plan d'exécution ne doit pas être optimisé pour spécifiques paramètres, mais plutôt pour tout paramètre qui est passé à la procédure stockée. À partir de la la documentation:

    OPTIMISER POUR L'INCONNU

    Indique à l'optimiseur de requête à utiliser des données statistiques au lieu des valeurs initiales pour toutes les variables locales lorsque la requête est compilé et optimisé, y compris les paramètres créés avec un paramétrage forcé.

    La réponse par @sion_corn recommande l'ajout d' WITH RECOMPILE à la définition de la procédure stockée, cependant cela oblige à recompiler de l'intégralité de la déclaration à chaque fois que la procédure stockée est exécutée. Ce pourrait encourir une inacceptable surcharge si la procédure est appelée très souvent.