Cher(e)s ami(e)s DBA
Cet article fait suite à l’article sur les compteurs de performances windows ,après la collecte et l’analyse des résultats fournis par les compteurs de performances,
j’ai remarqué qu’il y avait un nombre important de recompilations
des procédures , une des procédures identifiées comme la source de ces recompilations excessives est présente ci-dessous :
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
—DEBUT
—Import Salaire
declare @now datetime = getdate()
–declare @DateRef datetime = dateadd(minute, -(datepart(minute, @now ) %10)-5,dateadd(MS , -datepart(s, @now) *1000 -datepart(ms, @now),@now))
declare @DateRef datetime = dateadd(minute, -(datepart(minute, @now ) %20)-20,dateadd(MS , -datepart(s, @now) *1000 -datepart(ms, @now),@now))
declare @DateBloquage datetime = dateadd(month, IIF (day(getdate()) >= (select Lib_Valeur from Gestion.Libelle where Lib_Reference like ‘Blocage_Saisie_Jour’) ,0,-1),
cast(dateadd(day,-day(GETDATE())+1,GETDATE()) as date ))
create Table #SalaireTmp ( Con_id decimal ,Sal_id varchar(50) ,Sal_Date_Debut datetime,Sal_Date_Fin datetime, Sal_Salaire_Brut float,Sal_Base int ,Sal_Type int )
declare @Con_idSAVE decimal, @Date_DebutSAVE datetime,@Date_FinSAVE datetime,@Salaire_BrutSAVE float,@TypeResSAVE int, @Sal_BaseSAVE float
declare @Date_DebutOld datetime,@Date_FinOld datetime
declare @Con_id decimal, @Date_Debut datetime,@Date_Fin datetime,@Salaire_Brut float, @Sal_Base float
DECLARE @New_Odm AS TT_NewOdm
DECLARE SALAIRE_CUR CURSOR FOR
SELECT [oid]
,[dateSRrs]
,[dateERrs]
,[salYRrs]
,[durywkm]
FROM [dbo].[Vue_SalaireASA]
ORDER BY oid,dateSRrs,salYRrs,durywkm — Evitez les order by
OPEN SALAIRE_CUR
FETCH NEXT FROM SALAIRE_CUR
INTO @Con_id, @Date_Debut,@Date_Fin,@Salaire_Brut, @Sal_Base
set @Con_idSAVE =@Con_id
set @Date_Debutsave=@Date_Debut
set @Date_Finsave=@Date_Fin
set @Salaire_Brutsave=@Salaire_Brut
SET @sal_Basesave =@sal_Base
set @Date_DebutOld = dateadd(month,-1,@date_debut)
set @Date_finOld = dateadd(second,-1, @date_debut )
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@Con_id != @Con_idsave) or (@Salaire_Brut != @Salaire_Brutsave) or (@Sal_Base!= @Sal_Basesave) or (@Date_Debut != cast (cast ( @Date_finOld as date)as datetime )+1 ) –(@Date_Debut != dateadd(month,1,@Date_Debutold))
BEGIN
insert into #SalaireTmp( Con_id ,sal_id,Sal_Date_Debut ,Sal_Date_Fin , Sal_Salaire_Brut,sal_Base,sal_type )
values (@Con_idsave
, cast(cast( @Date_Debutsave as integer) as varchar) + CAST(@Con_idsave as varchar)
,@Date_Debutsave,@Date_FinOld,@Salaire_Brutsave,@sal_BaseSave/8,1 )
set @Con_idSAVE =@Con_id
set @Date_Debutsave=@Date_Debut
set @Date_Finsave=@Date_Fin
set @Salaire_Brutsave=@Salaire_Brut
SET @sal_Basesave =@sal_Base
END
set @Date_DebutOld = @date_debut
set @Date_finOld = @date_fin
FETCH NEXT FROM SALAIRE_CUR
INTO @Con_id, @Date_Debut,@Date_Fin,@Salaire_Brut,@Sal_Base
END
insert into #SalaireTmp( Con_id ,sal_id,Sal_Date_Debut ,Sal_Date_Fin , Sal_Salaire_Brut,sal_Base,sal_type )
values (@Con_idsave
, cast(cast( @Date_Debutsave as integer) as varchar) + CAST(@Con_idsave as varchar)
,@Date_Debutsave,@Date_FinOld,@Salaire_Brutsave,@sal_BaseSave/8,1 )
CLOSE SALAIRE_CUR;
DEALLOCATE SALAIRE_CUR;
truncate table _Salaire
insert into _Salaire(Con_Id, Sal_Id , Sal_Date_Debut , Sal_Date_Fin , Sal_Salaire_Brut , Sal_Base, Sal_Type )
SELECT salarie.oid AS Con_Id
, cast(cast(MIN(salaire.dateSRrs) as integer) as varchar) + CAST(salarie.oid as varchar) as Sal_Id
, MIN(salaire.dateSRrs) AS Sal_Date_Debut
, MAX(salaire.dateERrs) AS Sal_Date_Fin
, salaire.salYRrs AS Sal_Salaire_Brut
, durywkm/8 as Sal_Base
, 1 as Sal_Type
FROM [afd-si-sql].asa.asa.t_rsource AS salarie INNER JOIN
[afd-si-sql].asa.asa.t_rowres AS salaire ON salaire.rrsRes = salarie.oid
inner join [afd-si-sql].asa.asa.t_workmode AS mode ON mode.oid = salaire.rrswkm
WHERE (salarie.nameRes IS NOT NULL)
AND ((salarie.dateERes IS NULL) OR (YEAR(salarie.dateERes) > 2010))
and salarie.typeres = 0
and salaire.dateSRrs < isnull((select min(Sal_Date_Debut) from msoat.salairegenerique s where s.Sal_Salaire_Brut > salaire.salYRrs and Con_Id = salarie.oid), dateerrs)
–and salaire.dateSRrs < CONVERT(datetime, ’01/07/2013′)–bornage pour éviter les doublons de salaire en attendant qu’everwin corrige le problème
GROUP BY salarie.oid, salaire.salYRrs, salarie.nameRes, salarie.typeRes,durywkm
having datediff(month,MIN(salaire.dateSRrs) ,max(salaire.dateERrs) )+1 = count(1)
union all
SELECT distinct salarie.oid AS Con_Id —
, cast(cast((select min(datesRet) from [afd-si-sql].asa.asa.t_restrans where retres = salarie.oid and amtexpret = salaire.amtexpret group by amtexpret) as integer) as varchar) + CAST(salarie.oid as varchar) as Sal_Id
,min( salaire.dateSRet) AS Sal_Date_Debut
–, (select min(datesRet) from [afd-si-sql].asa.asa.t_restrans where retres = salarie.oid and amtexpret = salaire.amtexpret group by amtexpret) Sal_Date_Debut
–, (select max(dateERet) from [afd-si-sql].asa.asa.t_restrans where retres = salarie.oid and amtexpret = salaire.amtexpret group by amtexpret) Sal_Date_Fin
,max( salaire.dateERet )AS Sal_Date_Fin
, salaire.amtexpret*8*10 AS Sal_Salaire_Brut
, 10 as Sal_Base
, 1 as Sal_Type
FROM [afd-si-sql].asa.asa.t_rsource AS salarie
inner join [afd-si-sql].asa.asa.t_restrans AS salaire ON salaire.retres = salarie.oid
WHERE (salarie.nameRes IS NOT NULL)
AND ((salarie.dateERes IS NULL) OR (YEAR(salarie.dateERes) > 2010))
and salarie.typeres = 2
and salaire.dateSRet < isnull((select min(Sal_Date_Debut) from msoat.SalaireGenerique s where s.Sal_Salaire_Brut > salaire.amtexpret and Con_Id = salarie.oid), salaire.dateeRet)
group by salarie.oid,amtexpret
having datediff(month,MIN(salaire.dateSRet) ,max(salaire.dateERet) )+1 = count(1)
union all
select Con_id ,sal_id,Sal_Date_Debut ,Sal_Date_Fin , Sal_Salaire_Brut,sal_Base,sal_type from #salaireTmp
drop table #salaireTmp
exec Update_Entite_financiere
END
GO
Explication des points en rouges dans la procédure
#SalaireTmp :
Cette table est supprimée à la fin de la procédure. Il y a donc une recompilation du plan d’exécution tous les 20 min puisque cette procédure est appelée par un job .
L’utilisation des instructions DDL dans les procédures stockées réduit également la possibilité de réutiliser le plan d’exécution
exec Update_Entite_Financiere :
Afin d’éviter toutes confusions pour le moteur sql server Il faut utiliser le nom complet de la procédure.
{nom_serveur}.{nombase}.{nomproprietaire}.{nomobjet}
sp_import :
Ne pas utiliser le prefixe sp_ pour faire appel aux procédures, car le moteur va d’abord chercher la procédure dans la base system
Salaire_Cur :
Ne pas déclarer les curseurs qui font référence à une table temporaire.
Le curseur utilise beaucoup de ressources pour le traitement des frais généraux afin de maintenir la position d’enregistrement actuelle dans un RecordSet,
ce qui diminue les performances. Si nous devons traiter les enregistrements un par un dans une boucle, alors nous devrions utiliser la clause WHILE
Nous pouvons remplacer le curseur en utilisant la table variable .
Recommandation apportée à la procédure :
1) création d’une table permanente : ainsi on aura une compilation et un chargement en mémoire
La taille sera supprimée durant le dernier job du soir et recréée de nouveau le matin
durée du traitement avant ==>3 min
durée du traitement après ==>6 second
A noter que j’ai eu à créer aussi des index sur la nouvelle table
2) passage de SP3
Pour finir voici les règles à appliquer pour une optimisation des procédure stockée sql servers :
– Evitez d’utiliser les tables temporaires
– UQtilisez les index appropriés
– Appelez les procédures stockées en utilisant leur nom complet
– N’utilisez pas le préfixe sp_ dans le nom de procédure stockée
– Évitez d’utiliser les curseurs
– Évitez distint et order by
– Essayez d’éviter IN
– utilisez try-Catch pour la gestion des erreurs
– Essayez d’éviter SQL dynamique
– Eviter d’utiliser l’agrégat count () dans une sous-requêtes
– Utilisez SET NOCOUNT ON
– Utilisez la procédure sp_executesql au lieu de l’instruction EXECUTE
– Inclure la liste des colonnes dans l’instruction d’insertion
– Évitez la fonction Scalaire Appelant dans la liste des colonnes et la clause where
– Évitez d’utiliser DDL
– Essayez d’utiliser UNION pour mettre en œuvre une opération « OU »
– Décomposez une procédure stockée très importante en plusieurs procédures stockées sous
sqlserverementvotre