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

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

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, 09h10

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, 09h44

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, 10h25

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, 13h47

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, 14h00

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.

**escartefigue** · 13/09/2023, 14h12

De plus, en regardant la requête, il y a un truc qui me chagrine au niveau des parenthèses : une combinaison hasardeuse de AND/OR apparait dans la 1^re CTE "Object3". Ça saute au yeux en reformattant pour aligner les ouvrantes et les fermantes :

Code SQL :

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
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 = ?    --> OR combiné avec le AND qui précède SANS parenthèses... à vérifier
               AND (Column8 > (SELECT Function3(Column7) 
                               FROM Object2
                              )
                   )
              )
),

**crystal_palace** · 19/09/2023, 07h50

Envoyé par escartefigue

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.

Oui, le serveur est dédié et il ne possède qu'une instance, voici les plans d'exécution anonymisés au format xml :

https://drive.google.com/drive/folde...f4?usp=sharing

On peut y voir que les deux plans ne sont pas identiques, mais je n'arrive pas à trouver quelles sont les différences qui sont prises en charge par le moteur SQL entre les deux serveurs pour que les plans soient différents...

**SQLpro** · 19/09/2023, 18h58

Vos VM respectent-elles les préconisation d'architecture de VMWare pour MS SQL Server ?
https://www.vmware.com/content/dam/d...ices-guide.pdf

En règle général, 99% des VM que je voit chez les clients ne respectent jamais le moindre point de de document...

L'instance a t-elle été correctement paramétrée ?

Quelles sont les valeurs pour les paramètres suivants :
cost threshold for parallelism
max degree of parallelism
max server memory (MB)
optimize for ad hoc workloads

???

A +

**crystal_palace** · 20/09/2023, 14h11

Envoyé par SQLpro

Vos VM respectent-elles les préconisation d'architecture de VMWare pour MS SQL Server ?
https://www.vmware.com/content/dam/d...ices-guide.pdf

En règle général, 99% des VM que je voit chez les clients ne respectent jamais le moindre point de de document...

Bonjour,

Ici le temps d'execution est de +/- 3 minutes sur la VM, mais dure plusieurs sur un serveur physique d'ou mon étonnement.
Je vous remercie pour son partage et je vais en faire une lecture approfondi.

Envoyé par SQLpro

L'instance a t-elle été correctement paramétrée ?

Quelles sont les valeurs pour les paramètres suivants :
cost threshold for parallelism
max degree of parallelism
max server memory (MB)
optimize for ad hoc workloads

???

A +

cost threshold for parallelism
Production (physique) : 50
UAT (VM) : 50

max degree of parallelism
Production (physique) : 4 (CPU : 2 sockets - Logical processors : 8)
UAT (VM) : 2 (CPU : 2 sockets - Virtual processors : 2)

max server memory (MB)
Production (physique) : 122880 MB (total du serveur : 130976 MB)
UAT (VM) : 14336 MB (total du serveur : 16383 MB)

optimize for ad hoc workloads
Production (physique) : True
UAT (VM) : True

**SQLpro** · 21/09/2023, 15h23

Donc on peut dire que l'instance est à priori bien réglée.... Reste la VM...

Essayez en recalculant les statistiques en mode FULLSCAN :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000002_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000008_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000010_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([PK_Object1] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000003_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000009_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000004_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_0000000E_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000007_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_00000006_0E04126B] WITH FULLSCAN;
UPDATE STATISTICS Schema1.Object1 ([_WA_Sys_0000000F_0E04126B] WITH FULLSCAN;
 
UPDATE STATISTICS Schema1.Object2 ([_WA_Sys_0000000F_13BCEBC1] WITH FULLSCAN;

A +

**crystal_palace** · 26/09/2023, 10h05

Essayez en recalculant les statistiques en mode FULLSCAN

Je viens de lancer le recalcul et cela ne change rien.
Une question bête, est-ce que la version du système d'exploitation peut avoir une incidence ?
Le serveur physique de production qui pose problème est en Windows server 2016 alors que la VM d'UAT est en Windows server 2019.

**licardentaistor** · 26/09/2023, 11h23

pour moi non SQL 2019 est compatible 2016/2019, ce qui peut faire la différence c'est éventuellement serveur physique VS VM.

**SQLpro** · 27/09/2023, 13h32

Avez vous le même niveau de patch ?

Que dit SELECT @@VERSION sur chacune des instances ?

A +

**crystal_palace** · 27/09/2023, 14h36

Code :

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

SELECT @@version

VM UAT :
Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64) Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

PROD :
Microsoft SQL Server 2019 (RTM-CU22) (KB5027702) - 15.0.4322.2 (X64) Jul 27 2023 18:11:00 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

**SQLpro** · 28/09/2023, 11h10

Orientez vous vers les régalges de la VM....

A +

**Waldar** · 29/09/2023, 18h22

Envoyé par SQLpro

Orientez vous vers les réglages de la VM....

C'est sur le serveur physique que ça tourne mal.

**SQLpro** · 02/10/2023, 23h40

Voir s'il n'y a pas un défaut d'équilibrage NUMA...

Il faudrait voir le compteur PLE :

Code :

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

1
2
3
4
5
SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM   sys.dm_os_performance_counters WITH (NOLOCK)
WHERE  [object_name] LIKE N'%Buffer Node%' -- Handles named instances
   AND  counter_name = N'Page life expectancy' 
OPTION (RECOMPILE);

Si trop déséquilibré => probleme

A +

**SQLpro** · 04/10/2023, 16h01

Aurais tu les plans d'exécution après exécution pour voir les différences entre l'estimé et le réalisé ?

et utilises - tu le même modèle de cardinalité (version de la base et LEGACY_CARDINALITY_ESTIMATOR) ?

A +

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

Développement SQL Server

Discussions similaires

Partager

Partager