Temps d'exécution différents sur une requête avec CTE entre deux environnements

**crystal_palace** · 13/09/2023, 09h46

Bonjour,

Je viens de recevoir de la part d'un développeur la requête ci-dessous (que j'ai du anonymisé pour cette publication).
Actuellement cette requête fonctionne en +/- 3 mn sur mon environnement de test , par contre sur l'environnement de production elle dure plusieurs heures, je ne comprends pas pourquoi...

Voici les principales spécifications des environnements:

UAT : VM - CPU : 2 sockets - Virtual processors : 2 - RAM 16 GB - MaxDOP : 2
Production : Serveur physique - CPU : 2 sockets - Cores : 8 - Logical processors : 8 - RAM : 128 GB - MaxDOP : 4

Est-ce que vous auriez des idées ou des points à vérifier pour je trouve une explication à cette différence ?
Merci d'avance pour vos lumières !

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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
WITH 
Object3 AS (
SELECT DISTINCT 
       LEFT(Column5,?) as Column11, 
       Column1,
       Column3
       FROM Object1
       WHERE  Column1 = ?
       AND (Column6 = ? AND (Column7 > (SELECT Function3(Column7) FROM Object2))) OR (Column6 = ? AND (Column8 > (SELECT Function3(Column7) FROM Object2)))
),
 
Object4 AS (
       SELECT 
             LEFT(Column5,?) as Column11,
             Object1.Column1,
             Object1.Column3,
             Object1.Column2,
             Column9,
             Column10,
             Column4,
 
             CASE 
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
             ELSE ?
             END as Column12,
 
             CASE 
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
                    WHEN LEFT(Object1.Column2,?) in (?) THEN Function2(?,Object1.Column3)
             ELSE ?
             END as Column13
 
       from Object1
       inner join Object3 ON Object3.Column11 = left(Column5,?) AND Object3.Column1 = Object1.Column1 AND Object1.Column3 = Object3.Column3
       WHERE Object1.Column1 = ?
)
 
 
,Object5 AS (
       SELECT DISTINCT
             Object4.Column11,
             Object4.Column1,
             Object4.Column3,
             Object4.Column2,
             Column12,
             Column13,
 
             Column14,
             Column15,
             Column16
 
       from Object4
 
       left outer join 
             (SELECT Column11,
                          Column1,
                          Column2,
                          Function4(Column9) as Column14
             FROM Object4
             GROUP BY Column11,
                          Column1,
                          Column2
             ) as Object6
       ON Object4.Column11 = Object6.Column11 AND Object4.Column1 = Object6.Column1 AND Object4.Column2 = Object6.Column2
 
       left outer join 
             (SELECT Column11,
                          Column1,
                          Column2,
                          Function4(Column10) as Column15
             FROM Object4
             GROUP BY Column11,
                          Column1,
                          Column2
             ) as Object7
       ON Object4.Column11 = Object7.Column11 AND Object4.Column1 = Object7.Column1 AND Object4.Column2 = Object7.Column2
 
       left outer join 
             (SELECT Column11,
                          Column1,
                          Column2,
                          Function4(Column4) as Column16
             FROM Object4
             GROUP BY Column11,
                          Column1,
                          Column2
             ) as Object8
       ON Object4.Column11 = Object8.Column11 AND Object4.Column1 = Object8.Column1 AND Object4.Column2 = Object8.Column2
 
)
 
SELECT
       Object5.Column11,
       Object5.Column1,
       Column2,
       Object5.Column3,
       CAST(Column14 AS decimal(15,2)) AS Column14,
       CAST(Column15 AS decimal(15,2)) AS Column15,
       CAST(Column16 AS decimal(15,2)) AS Column16,
       CASE 
             WHEN Column17 > ? THEN CAST((Column14 / Column17) AS decimal(15, 10))
       ELSE ?
       END as Column18,
 
       CASE 
             WHEN Column19 > ? THEN CAST((Column15 / Column19) AS decimal(15, 10))
       ELSE ?
       END as Column20,
 
       CASE 
             WHEN Column21 > ? THEN CAST((Column16 / Column21) AS decimal(15, 10))
       ELSE ?
       END as Column22
 
       FROM Object5
 
       LEFT OUTER JOIN
             (SELECT 
                    Column1,
                    Column11,
                    Column3,
                    Function4(Column14) as Column17
             FROM Object5
             GROUP BY 
                    Column1,
                    Column11,
                    Column3
             ) as Object9
       ON Object5.Column11 = Object9.Column11 AND Object5.Column3 = Object9.Column3 AND Object5.Column1 = Object9.Column1
 
       LEFT OUTER JOIN
             (SELECT 
                    Column1,
                    Column11,
                    Column12,
                    Function4(Column15) as Column19
             FROM Object5
             GROUP BY 
                    Column1,
                    Column11,
                    Column12
             ) as Object10
       ON Object5.Column11 = Object10.Column11 AND Object5.Column12 = Object10.Column12 AND Object5.Column1 = Object10.Column1
 
       LEFT OUTER JOIN
             (SELECT 
                    Column1,
                    Column11,
                    Column13,
                    Function4(Column16) as Column21
             FROM Object5
             GROUP BY 
                    Column1,
                    Column11,
                    Column13
             ) as Object11
       ON Object5.Column11 = Object11.Column11 AND Object5.Column13 = Object11.Column13 AND Object5.Column1 = Object11.Column1

**licardentaistor** · 13/09/2023, 10h10

Il y a tellement de choses à vérifier....

- même version SQL server sur les 2 environnements?
- même configuration SQL des 2 côtés?
- le mode de compatibilité des 2 bases sur les 2 environnements
- les index sont les mêmes des 2 côtés?

**crystal_palace** · 13/09/2023, 10h44

Hello licardentaistor, et merci pour ton interet à mon problème !

voici les informations à tes questions:

même version SQL server sur les 2 environnements?

Oui, elles sont identiques : Microsoft SQL Server Standard 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64)

- même configuration SQL des 2 côtés?

Oui, pas de différence au niveau des paramétres de la configuration de l'instance

- le mode de compatibilité des 2 bases sur les 2 environnements

Toutes les deux en 150

- les index sont les mêmes des 2 côtés?

Oui, la base en test est une restauration d'un backup de la production

**escartefigue** · 13/09/2023, 11h25

Les données sont elles les mêmes de part et d'autre (volume, valeur des clefs, répartition des clefs...)
Les statistiques ont elles été faites à la même date de part et d'autre
Les réorgs sont elles les mêmes de part et d'autre
La réponse est probablement non, il est très rare que deux environnements soient strictement identiques

**crystal_palace** · 13/09/2023, 14h47

Les données sont semblables de part et d'autre voici quelques requêtes pour plus d'informations. Au niveau de la maintenance, les indexes sont rebuilds ou reorganize chaque soir suivant la fragmentation, et les statistiques sont également recalculés journalièrement sur toute la base.
Tout est fait avec les scripts de Ola Hallengren.

Il faut noter que les plans d'exécutions sont également différents sur les deux serveurs, je vais essayer de les fournir si besoin, je dois juste faire l'anonymisation du XML Plan.

Nombres de lignes sur la table (la petite différence est à cause de la vie de la DB en production ce jour) :

Code :

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

1
2
3
4
5
SELECT count(*)
  FROM [MA_BASE].[dbo].[Object1]
 
uat :           49859134 rows
production : 49874788 rows

Fragmentation de l'index sur la table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
AND I.name LIKE 'PK_Object1'

Code :

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

1
2
3
4
5
6
7
8
9
10
 
uat :
 
Schema     Table        Index              avg_fragmentation_in_percent    page_count
dbo        Object1    PK_Object1      3.06225901684124                   761464
 
prod :
 
Schema    Table    Index    avg_fragmentation_in_percent    page_count
dbo    Object1    PK_Object1    0.457538821873638    761684

Statistiques de la table concerné (relancé aujourd'hui pour les besoins des tests):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT sp.stats_id, 
       name, 
       filter_definition, 
       last_updated, 
       rows, 
       rows_sampled, 
       steps, 
       unfiltered_rows, 
       modification_counter
FROM sys.stats AS stat
     CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = OBJECT_ID('[dbo].[Object1]');

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
 
 
UAT :
 
stats_id    name    filter_definition    last_updated    rows    rows_sampled    steps    unfiltered_rows    modification_counter
1    PK_Object1    NULL    2023-09-10 22:26:04.8900000    49859134    322636    153    49859134    0
2    _WA_Sys_00000002_0E04126B    NULL    2023-09-10 22:26:06.2866667    49859134    322636    2    49859134    0
3    _WA_Sys_00000006_0E04126B    NULL    2023-09-10 22:26:12.7733333    49859134    322636    200    49859134    0
4    _WA_Sys_00000003_0E04126B    NULL    2023-09-10 22:26:08.8100000    49859134    322636    200    49859134    0
5    _WA_Sys_00000004_0E04126B    NULL    2023-09-10 22:26:09.7033333    49859134    322636    31    49859134    0
6    _WA_Sys_00000007_0E04126B    NULL    2023-09-10 22:26:13.3933333    49859134    322636    180    49859134    0
7    _WA_Sys_00000008_0E04126B    NULL    2023-09-10 22:26:14.6200000    49859134    322636    188    49859134    0
8    _WA_Sys_00000009_0E04126B    NULL    2023-09-10 22:26:15.7766667    49859134    322636    174    49859134    0
9    _WA_Sys_00000010_0E04126B    NULL    2023-09-10 22:26:19.8000000    49859134    322636    2    49859134    0
10    _WA_Sys_00000005_0E04126B    NULL    2023-09-10 22:26:11.4133333    49859134    322636    178    49859134    0
11    _WA_Sys_0000000E_0E04126B    NULL    2023-09-10 22:26:16.6233333    49859134    322636    200    49859134    0
12    _WA_Sys_0000000F_0E04126B    NULL    2023-09-10 22:26:17.7700000    49859134    322636    195    49859134    0
 
PROD :
 
stats_id    name    filter_definition    last_updated    rows    rows_sampled    steps    unfiltered_rows    modification_counter
1    PK_Object1    NULL    2023-09-13 13:00:43.4500000    49874788    322350    151    49874788    0
2    _WA_Sys_00000002_0E04126B    NULL    2023-09-13 13:00:43.8833333    49874788    322350    2    49874788    0
3    _WA_Sys_00000006_0E04126B    NULL    2023-09-13 13:00:47.7533333    49874788    322350    200    49874788    0
4    _WA_Sys_00000003_0E04126B    NULL    2023-09-13 13:00:44.9333333    49874788    322350    200    49874788    0
5    _WA_Sys_00000004_0E04126B    NULL    2023-09-13 13:00:45.5200000    49874788    322350    31    49874788    0
6    _WA_Sys_00000007_0E04126B    NULL    2023-09-13 13:00:48.1633333    49874788    322350    186    49874788    0
7    _WA_Sys_00000008_0E04126B    NULL    2023-09-13 13:00:48.5466667    49874788    322350    196    49874788    0
8    _WA_Sys_00000009_0E04126B    NULL    2023-09-13 13:00:48.9166667    49874788    322350    177    49874788    0
9    _WA_Sys_00000010_0E04126B    NULL    2023-09-13 13:00:50.2233333    49874788    322350    2    49874788    0
10    _WA_Sys_00000005_0E04126B    NULL    2023-09-13 13:00:46.5900000    49874788    322350    166    49874788    0
11    _WA_Sys_0000000E_0E04126B    NULL    2023-09-13 13:00:49.1966667    49874788    322350    198    49874788    0
12    _WA_Sys_0000000F_0E04126B    NULL    2023-09-13 13:00:49.5733333    49874788    322350    196    49874788    0

**escartefigue** · 13/09/2023, 15h00

Le serveur de prod est il bien dédié à SQL server ou bien héberge-t-il d'autres applications ?

Dans la mesure du possible, publiez le script DDL des tables et index ainsi que le plan d'exécution.

Temps d'exécution différents sur une requête avec CTE entre deux environnements

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager