Degré max du parallélisme

Version imprimable

03/09/2015, 16h55
Boubou2020

Degré max du parallélisme

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

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:

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 +

03/09/2015, 19h20
Boubou2020

merci bien SQLpro

2 pièce(s) jointe(s)

Citation:
Envoyé par 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:

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 ?

04/09/2015, 19h59
SQLpro

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

Attention à DTA...

A +

la requête en question

Code:

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:

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:

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:

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:

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]

Citation:
Envoyé par abdallah_mehdoini
la requête en question
Code:

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 +