Comment donner des ordres de priorité à mes requêtes ?

**aloisio11** · 26/11/2015, 10h11

Bonjour à tous,

J'utilise Microsoft SQL Server 2014 Standard Edition (64-bit) sur un serveur.
J'ai des applications et un intranet qui communiquent avec ce SQL Server.
Sur une de mes application j'ai un traitement qui est très lourd et dure quelques minutes, hélas pendant ce temps l'intranet est très lent et les timeouts fréquents

J'aimerais donc donner une importance/priorité plus faible à l'application pour qu'elle laisse plus de ressource à l'intranet.
J'ai vu qu'il existait un "Gouverneur de ressources" mais hélas indisponible sur la version Standard.

Existe-il une solution à mon problème ?

Merci d'avance

**julien94320** · 26/11/2015, 10h19

Bonjour,

Votre probleme est vague il nous manque des éléments mais j'ai déjà néamoins quelques pistes qui vous feront répondre :

Le traitement long a t'il été optimisé ? Est il possible de diminuer les ressources accaparé par ce traitement (Nombre d'io / CPU ... )
Est il possible de sortir ce traitement sur un autre environnement ?
Avez vous identifiés la ou les requetes consommatrices ? Et a quel niveau ?
Est il impératif de faire tourner ce traitement en journée ?

**aloisio11** · 26/11/2015, 10h26

Merci pour ta réponse.

Le traitement long a t'il été optimisé ? Est il possible de diminuer les ressources accaparé par ce traitement (Nombre d'io / CPU ... )
Oui je savais que ce traitement allait être lourd, j'ai donc beaucoup travaillé l'optimisation.
Qu'entends tu pars nombre d'IO ?

Est il possible de sortir ce traitement sur un autre environnement ?
Pas impossible mais difficile, car les données devrons être rapidement à jour d'un environnement à l'autre.

Avez vous identifiés la ou les requêtes consommatrices ? Et a quel niveau ?
Oui mais elles sont optimisées et indispensables.

Est il impératif de faire tourner ce traitement en journée ?
Hélas oui, c'est un calcul de prix d'articles.

**julien94320** · 26/11/2015, 10h51

Dans l'optimisation je vois que vous effectuez un calcul de prix n'avez vous pas essayé de le faire en .Net via du CLR pour soulager le serveur SQL (piste possible, ce n'est pas forcément la solution ultime) ?

Quel est le nombre d'IO remonté par votre requete consommatrice au niveau accès disque ? Avez vous le plan de la requete incriminé ?

Normalement il est imperatif de sortir les traitements de scoring / BI ... du transactionnel, l'ideal serait de transferer les données sur une autre instance via un systeme de réplication par exemple et d'effectuer les calcul dans un environnement dédié.

Autre point : Le parrallélisme est il activé sur votre machine ? Si oui votre requete consommatrice est elle élligible ? Comment se comporte le parrallélisme vis a vis de cette requete ? comment est solicité votre CPU au moment du passage de cette requete ?

Julien

**aieeeuuuuu** · 26/11/2015, 11h49

Bonjour,

Il faudrait savoir avant toutes choses si c'est bien un problème de charge sur le serveur. Dans votre cas, il me semble plus probable qu'il s'agisse de problème de verrouillage qui bloque les autres requête, lesquelles tombent alors en timeout.

La solution au problème ne sera alors pas du tout la même.

Il faudrait nous en dire plus sur le traitement, ce qu'il fait,...

**aloisio11** · 26/11/2015, 12h06

Oui il s'agit plus d'un verrouillage j'ai l'impression.

Le traitement parcours environ 10'000 articles et par article au moins 3 SELECT (dont 1 avec UNION) et un UPDATE sur environ 1 article sur 20.

**aieeeuuuuu** · 26/11/2015, 12h09

Envoyé par aloisio11

Le traitement parcours environ 10'000 articles et par article au moins 3 SELECT (dont 1 avec UNION) et un UPDATE sur environ 1 article sur 20.

ça sent le curseur ! est-ce le cas ?

Peut-on voir le code ?

est-ce qu'une transaction englobe le traitement ?

**julien94320** · 26/11/2015, 12h36

Dans votre cas il nous faudrait le code et le plan d'execution ...

**aloisio11** · 26/11/2015, 12h43

Tu entends quoi par "ça sent le curseur" ?

En gros ça donne ça.
Quand il y a des ... c'est que j'ai volontairement caché le code.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
vDrListe = GetReader("SELECT * FROM ...")

            vMoyenne(0) = 0
            vMoyenne(1) = 0
            vCpt = 0
            While vDrListe.Read
                Try
                    vOldPrix = CDbl(vDrListe("PRIX"))
                    vCode = vDrListe("CODE").ToString
                    vMarge = vMargeDefault
                    vMargeCrt = vMargeCrtDefault

                    'Récupération des marges pour la catégorie 2
                    If vOldC2 <> UCase(vDrListe("CATEG2").ToString) Or vOldC1 <> UCase(vDrListe("CATEG1").ToString) Then
                        vMargeCat2 = -1
                        vMargeCrtCat2 = -1

                        vDr = GetReader("SELECT CATEG2.Marge, CATEG2.MargeCrt" & _
                                " FROM ...'")
                        If vDr.Read Then
                            If IsNumeric(vDr("Marge")) Then vMargeCat2 = CDbl(vDr("Marge"))
                            If IsNumeric(vDr("MargeCrt")) Then vMargeCrtCat2 = CDbl(vDr("MargeCrt"))
                        End If
                        vDr.Close()

                        vOldC3 = ""
                    End If

                    'Récupération des marges pour la catégorie 3
                    If vOldC3 <> UCase(vDrListe("CATEG3").ToString) Or vOldC2 <> UCase(vDrListe("CATEG2").ToString) Or vOldC1 <> UCase(vDrListe("CATEG1").ToString) Then
                        vMargeCat3 = -1
                        vMargeCrtCat3 = -1

                        vDr = GetReader("SELECT CATEG3.Marge, CATEG3.MargeCrt" & _
                                " FROM ...")
                        If vDr.Read Then
                            If IsNumeric(vDr("Marge")) Then vMargeCat3 = CDbl(vDr("Marge"))
                            If IsNumeric(vDr("MargeCrt")) Then vMargeCrtCat3 = CDbl(vDr("MargeCrt"))
                        End If
                        vDr.Close()

                        vOldC3 = ""
                    End If

                    If vMargeCat2 > 0 Then vMarge = vMargeCat2
                    If vMargeCat3 > 0 Then vMarge = vMargeCat3
                    If IsNumeric(vDrListe("Marge")) Then vMarge = CDbl(vDrListe("Marge"))

                    If vMargeCrtCat2 > 0 Then vMargeCrt = vMargeCrtCat2
                    If vMargeCrtCat3 > 0 Then vMargeCrt = vMargeCrtCat3
                    If IsNumeric(vDrListe("MargeCrt")) Then vMargeCrt = CDbl(vDrListe("MargeCrt"))

                    vMarge = 1 + (vMarge / 100)
                    vMargeCrt = vMargeCrt / 100

                    vPA = BestFour(vCode) 'Fonction qui retourne le meilleur prix d'achat (1 requette SQL avec un INNER JOIN)

                    vLigne.Remove(0, vLigne.Length)

                    If vPA > 0 Then
                        vLigne.Append(vDrListe("...").ToString & Chr(9) & _
                                        vDrListe("...").ToString & Chr(9) & _
                                        vDrListe("...").ToString & Chr(9) & _
                                        vDrListe("...").ToString & Chr(9) & _
                                        vDrListe("...").ToString & Chr(9))

                            vPrix2 = BestCrt(vCode)  'Fonction qui retourne un autre prix d'achat (2 requette SQL dont une avec plusieurs UNION)

                            vNewPrix = vPA * CDbl(vAppSettings("TVA")) * vMarge

                            If vPrix2 > 0 Then
                                vNewPrix = vPrix2
                            End If

                            'Stock la marge pour le calcul de la marge moyenne
                            vMoyenne(0) += Math.Round(((vNewPrix / CDbl(vAppSettings("TVA"))) - vPA) / vPA, 4)

                            vNewPrix = Math.Round(vNewPrix, 1)

                            vLigne.Append(vNewPrix.ToString & Chr(9) & _
                                          "" & Chr(9) & _
                                          ... & Chr(9) & _
                                          ... & Chr(9) & _
                                          ... & Chr(9) & _
                                          "" & Chr(9) & _
                                          ... & Chr(9) & _
                                          ... & Chr(9))

                            'Mise à jour du prix s'il est différent
                            If vNewPrix <> vOldPrix Then
                                vSQL = "UPDATE ..." & _
                                        " SET PRIX = " & vNewPrix & _
                                       " WHERE ... = '" & vCode & "';"
                                ExecuteSQL(vSQL)

                                vLigne.Append("1")
                            Else
                                    vLigne.Append("0")
                            End If

                            If CInt(vDrListe("...")) > 0 Then
                                vSQL &= "UPDATE ..." & _
                                       " SET ... = 0" & _
                                       " WHERE ... = '" & vCode & "';"
                                ExecuteSQL(vSQL)
                            End If

                        vWriter.WriteLine(vLigne.ToString)

                        vMoyenne(1) += 1

                    vOldC1 = UCase(vDrListe("CATEG1").ToString)
                    vOldC2 = UCase(vDrListe("CATEG2").ToString)
                    vOldC3 = UCase(vDrListe("CATEG3").ToString)
                Catch ex As Exception
                    __FONCTIONS.LogAdd("CalculPrix 1 : ERREUR : " & ex.ToString & vbCrLf)
                End Try

                vCpt += 1

                If vCpt Mod 100 = 0 Or vCpt = vNb Then
                    __FONCTIONS.AfficheLog(LogCalculPrix, "  " & Math.Floor(vCpt / vNb * 100).ToString & " %", True)
                End If
            End While

Et non, pas de transaction.

**SQLpro** · 26/11/2015, 13h02

Envoyé par aloisio11

Bonjour à tous,

J'utilise Microsoft SQL Server 2014 Standard Edition (64-bit) sur un serveur.
J'ai des applications et un intranet qui communiquent avec ce SQL Server.
Sur une de mes application j'ai un traitement qui est très lourd et dure quelques minutes, hélas pendant ce temps l'intranet est très lent et les timeouts fréquents

J'aimerais donc donner une importance/priorité plus faible à l'application pour qu'elle laisse plus de ressource à l'intranet.
J'ai vu qu'il existait un "Gouverneur de ressources" mais hélas indisponible sur la version Standard.

Existe-il une solution à mon problème ?

Merci d'avance

Globalement vous pouvez faire cela par le "resource governor" : https://msdn.microsoft.com/en-us/library/bb933866.aspx

A +

**aloisio11** · 26/11/2015, 13h02

Pas disponible dans la version Standard hélas

**aieeeuuuuu** · 26/11/2015, 13h28

ok, je pensais que vous faisiez cela dans une procédure stockée...
Je n'ai pas lu votre code en détail mais :

1/ il s'agit finalement surement bien de problème de performances. a priori, vous éxécutez pas moins de 20000 requetes...
2/ vous devriez réécrire en procédure stockée. je pense qu'une seule requete UPDATE devrait suffire, et ne durera probablement que quelques secondes.
3/ vous pourriez même envisager une vue indexée, si c'est possible et pertinent... (a quelle fréquence est exécutée cette procédure, et que fait-elle ?)

**aloisio11** · 26/11/2015, 14h04

Oui je peux regrouper les updates mais ils ne sont pas exécuté pour chaque article. Uniquement sur 5%/10%.

Cette routine passe toutes les 2 heures et dure entre 8 et 9 minutes.
Elle calcule les prix des articles.

**julien94320** · 26/11/2015, 14h25

Effectivement encapsuler les mise a jour dans une procédure stockée sera beaucoup plus performant ...

Il faut :

Identifier les données a mettre a jour surtout c'est juste 5 a 10 % ça ira très vite
Mettre en place le syteme de mise a jour
Au besoin utiliser des fonctions CLR pour le calcul

Si vous avez un DBA's SQL Server sous le coude essayez de vous rapprocher de lui pour qu'il vous auguille sur la bonne démarche.

**aloisio11** · 26/11/2015, 14h34

Selon vous c'est plus les UPDATE qui posent problème ?

Hélas le DBA ... c'est moi

**julien94320** · 26/11/2015, 14h53

Il faut analyser les plans de requetes pour en etre certains.

Mais pourquoi ne pas faire une procédure permettant d'identifier les lignes a modifier et les modifier ?

**aieeeuuuuu** · 26/11/2015, 15h03

Envoyé par aloisio11

Cette routine passe toutes les 2 heures et dure entre 8 et 9 minutes.
Elle calcule les prix des articles

vos prix changent toutes les deux heures ?

**aloisio11** · 26/11/2015, 15h40

Entre 5 et 10 % oui ...

Mais ça c'est pas moi qui choisit

**aieeeuuuuu** · 26/11/2015, 16h43

est-ce que des nouvelles données sont importées toutes les deux heures pour le recalcul des prix, ou est-ce que les données changent au fil de l'eau (modification manuelles par exemple)
Dans ce deuxième cas, une vue indexée pour remplacer votre table contenant les prix - si toutefois elle est possible - serait sans doute la meilleure solution : vos prix seraient alors à jour en temps réel.
Si vous êtes dans la première situation (import de nouvelle données) ou si la vue indexée n'est pas possible, alors une seule requete UPDATE serait déjà nettement mieux.