Discussion :

[Boubou2020]

bonjour a tous
si je rencontre un problème du parallélisme (présence d'un valeur très élevé du compteur CXPACKET) et j'ai pris la décision de modifier la valeur du max dégréé of parallélisme
quel est la formule ou la valeur que je doit le mettre
par exemple plusieurs personnes recommandent une valeur de « max dégréé of parallélisme » égale au nombre de processeurs divisés par 2 Exemple pour 16 processeurs, maxdop = 8
Est ce que cette formule est toujours correctes ?
merci

[SQLpro]

Non ! il faut faire des tests.

Prenez les 10 requêtes les plus couteuses ayant un plan parallélisé et rajoutez y l'option MAXDOP en testant progressivement de 0 à n. Lorsque vous avez atteint en moyenne le nombre de CPU, revenez un peu en arrière de 1 ou 2 CPU.

Pour trouver les requêtes ayant fait du parallélisme, utilisez cette requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT TOP 10 text AS SQL_REQUEST, query_plan AS EXECUTION_PLAN
FROM   sys.dm_exec_query_stats AS s WITH(NOLOCK)
       CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
       CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS q
WHERE  p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                           max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY total_worker_time/execution_count DESC
OPTION (MAXDOP 1);

A +

[Boubou2020]

merci bien SQLpro

[Boubou2020]

Non ! il faut faire des tests.

Prenez les 10 requêtes les plus couteuses ayant un plan parallélisé et rajoutez y l'option MAXDOP en testant progressivement de 0 à n. Lorsque vous avez atteint en moyenne le nombre de CPU, revenez un peu en arrière de 1 ou 2 CPU.

Pour trouver les requêtes ayant fait du parallélisme, utilisez cette requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT TOP 10 text AS SQL_REQUEST, query_plan AS EXECUTION_PLAN
FROM   sys.dm_exec_query_stats AS s WITH(NOLOCK)
       CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
       CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS q
WHERE  p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                           max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY total_worker_time/execution_count DESC
OPTION (MAXDOP 1);

A +

je reviens vers ce sujet pour vous poser un deuxième question , je viens de tester vos script pour identifier les requêtes qui sont très coûteuse et qui présente un phénomène du parallélisme aprés leur identification j'ai analyser leu avec advisor l'assitant ma demander d'ajouter un index qui présente un taille de 10GO
alors je reviens vers vous pour poser ce question quel serai la meilleure solution pour moi ajouter cette index ou désactiver le parallélisme ?

[SQLpro]

Sans la requête et la description DDL des tables en jeu, impossible de vous répondre !

Attention à DTA...

A +

[Boubou2020]

la requête en question

Code :

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

1
2
3
4
5
select  distinct  serialisation.no_serie,serialisation.*  
		from livraison,livraison_serialisation,serialisation
		where livraison.no_bl=livraison_serialisation.no_bl and
		livraison_serialisation.no_serie=serialisation.no_serie and 
		livraison.no_bl='84928102' and no_materiel='253604795' order by no_up,no_um

le code ddl du table serialisation

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
CREATE TABLE [dbo].[SERIALISATION](
	[No_Serie] [varchar](20) NOT NULL,
	[No_UP] [varchar](20) NULL,
	[No_UM] [varchar](20) NULL,
	[No_Materiel] [varchar](18) NULL,
 CONSTRAINT [PK_SERIALISATION] PRIMARY KEY CLUSTERED 
(
	[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_SERIALISATION_MATERIEL] FOREIGN KEY([No_Materiel])
REFERENCES [dbo].[MATERIEL] ([No_Materiel])
GO
 
ALTER TABLE [dbo].[SERIALISATION] CHECK CONSTRAINT [FK_SERIALISATION_MATERIEL]
GO

la table livraison

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
CREATE TABLE [dbo].[LIVRAISON](
	[No_BL] [varchar](10) NOT NULL,
	[Date_Insertion] [datetime] NULL,
	[Division] [char](4) NULL,
	[Date_BL] [datetime] NULL,
	[Date_Prelevement] [datetime] NULL,
	[Date_SM] [datetime] NULL,
	[Date_Reception] [datetime] NULL,
	[Prelevement] [bit] NULL,
	[SM] [bit] NULL,
	[Nom_Fichier_Flux] [varchar](100) NULL,
	[No_Client] [varchar](10) NULL,
	[No_TRSP] [varchar](10) NULL,
	[Statut] [varchar](50) NULL,
	[No_Cde] [varchar](10) NULL,
	[Date_Liv] [datetime] NULL,
	[Region] [varchar](20) NULL,
	[Pays] [varchar](15) NULL,
	[NoteBL] [varchar](25) NULL,
 CONSTRAINT [PK_LIVRAISON] PRIMARY KEY CLUSTERED 
(
	[No_BL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[LIVRAISON]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_CLIENT] FOREIGN KEY([No_Client])
REFERENCES [dbo].[CLIENT] ([No_Client])
GO
 
ALTER TABLE [dbo].[LIVRAISON] CHECK CONSTRAINT [FK_LIVRAISON_CLIENT]
GO
 
ALTER TABLE [dbo].[LIVRAISON]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_TRANSPORTEUR] FOREIGN KEY([No_TRSP])
REFERENCES [dbo].[TRANSPORTEUR] ([No_TRSP])
GO
 
ALTER TABLE [dbo].[LIVRAISON] CHECK CONSTRAINT [FK_LIVRAISON_TRANSPORTEUR]
GO

la table livraison_serialisation

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
 
CREATE TABLE [dbo].[LIVRAISON_SERIALISATION](
	[No_BL] [varchar](10) NOT NULL,
	[No_Serie] [varchar](20) NOT NULL,
	[No_Poste] [varchar](6) NULL,
 CONSTRAINT [PK_LIVRAISON_SERIALISATION] PRIMARY KEY CLUSTERED 
(
	[No_BL] ASC,
	[No_Serie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_SERIALISATION_LIVRAISON] FOREIGN KEY([No_BL])
REFERENCES [dbo].[LIVRAISON] ([No_BL])
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION] CHECK CONSTRAINT [FK_LIVRAISON_SERIALISATION_LIVRAISON]
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION]  WITH NOCHECK ADD  CONSTRAINT [FK_LIVRAISON_SERIALISATION_SERIALISATION] FOREIGN KEY([No_Serie])
REFERENCES [dbo].[SERIALISATION] ([No_Serie])
GO
 
ALTER TABLE [dbo].[LIVRAISON_SERIALISATION] CHECK CONSTRAINT [FK_LIVRAISON_SERIALISATION_SERIALISATION]
GO

ci joint ce qui donne le DTA comme recommandation

Code :

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

1
2
3
4
5
6
7
8
9
SET ANSI_PADDING ON
 
CREATE NONCLUSTERED INDEX [_dta_index_SERIALISATION_7_661577395__K5_K1_K2_K3] ON [dbo].[SERIALISATION]
(
	[No_Materiel] ASC,
	[No_Serie] ASC,
	[No_UP] ASC,
	[No_UM] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

[SQLpro]

la requête en question

Code :

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

1
2
3
4
5
select  distinct  serialisation.no_serie,serialisation.*  
		from livraison,livraison_serialisation,serialisation
		where livraison.no_bl=livraison_serialisation.no_bl and
		livraison_serialisation.no_serie=serialisation.no_serie and 
		livraison.no_bl='84928102' and no_materiel='253604795' order by no_up,no_um

Requête dégueulasse :
1) on utilise des alias
2) on fait des jointures normalisées, ça existe depuis 1992 !
3) on fait pas du SELECT * on met les colonnes nécessaires

A +