Lenteur sur une requête d'extraction

**Boubou2020** · 22/02/2016, 13h54

bonjour

j'ai eu aujord'huit une réclamation sur plantage successive d'application de gestion dédier pour gérer le Flux entré/sortie Magazin et du lenteur de traitement du donées
en lanancant une trace de profiler sur le serveur SGBD je vien de détecter que le plantage s'apparait pendant l'execution de cette requéte

Code :

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

1
2
3
select p.id_PickList ,p.Num_PickList,l.id_LigneProduction ,l.CodeLigneProduction,s.id_Status,s.Code,p.Magasin ,(select COUNT(*) from Detail_PickList as d where d.id_PickList=p.id_PickList ) as 'Nombre Article', p.TypePickList ,UnitesFabrication.Code,UnitesFabrication.MAPA,p.[DateCreation],p.CodeProduit,p.[DateLivraison]  
from PickLists as p inner join Status as s ON s.id_Status = p.id_Status  inner join LigneProduction as l ON l.id_LigneProduction = p.id_LigneProduction   inner join UnitesFabrication on l.id_UniteFabrication= UnitesFabrication.id_UniteFabrication  
where s.Code='DEMANDER' AND (p.Magasin in ('VIC','INC') )

en lancant une mesure d'attente je viens de détecter la présence de compteux CXPACKET ---->j'ai décider de désactiver le parallélisme ok
mainteant je pause cette question pour nos experts qui a une idé sur une action pour optimiser cette requéte (temps d'execution 1000ms)
ci joint le schéme des tables utlisées

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
USE [SuiviMatiere]
GO
 
/****** Object:  Table [dbo].[PickLists]    Script Date: 22/02/2016 1:42:25 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[PickLists](
	[id_PickList] [int] IDENTITY(1,1) NOT NULL,
	[Num_PickList] [nvarchar](50) NULL,
	[id_LigneProduction] [int] NULL,
	[id_Status] [int] NULL,
	[Magasin] [nvarchar](200) NULL,
	[DateCreation] [datetime] NULL,
	[DateMAj] [datetime] NULL,
	[TypePickList] [nvarchar](50) NULL,
	[CodeProduit] [nvarchar](50) NULL,
	[DateLivraison] [datetime] NULL,
	[DateServi] [datetime] NULL,
	[NbUSServi] [int] NULL,
	[NbUSRecept] [int] NULL,
	[Hostame] [nvarchar](50) NULL,
	[Observation] [nvarchar](250) NULL,
	[id_CauseServi] [int] NULL,
 CONSTRAINT [PK_PickLists] PRIMARY KEY CLUSTERED 
(
	[id_PickList] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 4) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[PickLists] ADD  CONSTRAINT [DF_PickLists_DateCreation]  DEFAULT (getdate()) FOR [DateCreation]
GO
 
ALTER TABLE [dbo].[PickLists] ADD  CONSTRAINT [DF_PickLists_DateMAj]  DEFAULT (getdate()) FOR [DateMAj]
GO
 
ALTER TABLE [dbo].[PickLists] ADD  CONSTRAINT [DF_PickLists_NbUSServi]  DEFAULT ((0)) FOR [NbUSServi]
GO
 
ALTER TABLE [dbo].[PickLists] ADD  CONSTRAINT [DF_PickLists_NbUSRecept]  DEFAULT ((0)) FOR [NbUSRecept]
GO
 
ALTER TABLE [dbo].[PickLists] ADD  CONSTRAINT [DF_PickLists_Hostame]  DEFAULT (host_name()) FOR [Hostame]
GO
 
ALTER TABLE [dbo].[PickLists]  WITH CHECK ADD  CONSTRAINT [FK_PickLists_CauseServi] FOREIGN KEY([id_CauseServi])
REFERENCES [dbo].[CauseServi] ([id_CauseServi])
GO
 
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_CauseServi]
GO
 
ALTER TABLE [dbo].[PickLists]  WITH CHECK ADD  CONSTRAINT [FK_PickLists_LigneProduction] FOREIGN KEY([id_LigneProduction])
REFERENCES [dbo].[LigneProduction] ([id_LigneProduction])
GO
 
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_LigneProduction]
GO
 
ALTER TABLE [dbo].[PickLists]  WITH CHECK ADD  CONSTRAINT [FK_PickLists_Status] FOREIGN KEY([id_Status])
REFERENCES [dbo].[Status] ([id_Status])
GO
 
ALTER TABLE [dbo].[PickLists] CHECK CONSTRAINT [FK_PickLists_Status]
GO

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
USE [SuiviMatiere]
GO
 
/****** Object:  Table [dbo].[Detail_PickList]    Script Date: 22/02/2016 1:42:54 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Detail_PickList](
	[id_PickListDetail] [int] IDENTITY(1,1) NOT NULL,
	[id_PickList] [int] NULL,
	[Id_Produit] [int] NULL,
	[Emplacement] [nvarchar](500) NULL,
	[Quantite_demande] [int] NULL,
	[id_statut] [int] NULL,
 CONSTRAINT [PK_Detail_PickList] PRIMARY KEY CLUSTERED 
(
	[id_PickListDetail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 4) ON [PRIMARY]
) ON [PRIMARY]
 
GO

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
USE [SuiviMatiere]
GO
 
/****** Object:  Table [dbo].[LigneProduction]    Script Date: 22/02/2016 1:43:35 PM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[LigneProduction](
	[id_LigneProduction] [int] IDENTITY(1,1) NOT NULL,
	[CodeLigneProduction] [nvarchar](50) NULL,
	[id_UniteFabrication] [int] NULL,
	[etat] [bit] NOT NULL,
	[Observation] [nvarchar](50) NULL,
	[RobotTraitement] [nvarchar](50) NULL,
	[DateCreation] [datetime] NULL,
	[DateMaj] [datetime] NULL,
	[Hostname] [nvarchar](50) NULL,
 CONSTRAINT [PK_LigneProduction] PRIMARY KEY CLUSTERED 
(
	[id_LigneProduction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[LigneProduction] ADD  CONSTRAINT [DF_LigneProduction_etat]  DEFAULT ((1)) FOR [etat]
GO
 
ALTER TABLE [dbo].[LigneProduction] ADD  CONSTRAINT [DF_LigneProduction_DateCreation]  DEFAULT (getdate()) FOR [DateCreation]
GO
 
ALTER TABLE [dbo].[LigneProduction] ADD  CONSTRAINT [DF_LigneProduction_DateMaj]  DEFAULT (getdate()) FOR [DateMaj]
GO
 
ALTER TABLE [dbo].[LigneProduction] ADD  CONSTRAINT [DF_LigneProduction_Hostname]  DEFAULT (host_name()) FOR [Hostname]
GO
 
ALTER TABLE [dbo].[LigneProduction]  WITH CHECK ADD  CONSTRAINT [FK_LigneProduction_UnitesFabrication] FOREIGN KEY([id_UniteFabrication])
REFERENCES [dbo].[UnitesFabrication] ([id_UniteFabrication])
GO
 
ALTER TABLE [dbo].[LigneProduction] CHECK CONSTRAINT [FK_LigneProduction_UnitesFabrication]
GO

ci joint le plan d'execution du requéte

merci

**elsuket** · 22/02/2016, 20h59

Bonjour,

Ce n'est pas parce que l'on a le type d'attentes CXPACKET qui sort dans le type ayant cumulé le total d'attentes le plus élevé que c'est un problème.
Par ailleurs, le cumul est global à l'instance, donc il se peut que d'autre requêtes soient la source du problème que vous avez.
Ensuite cela dépend aussi du type de la charge de travail que l'instance supporte : plutôt OLTP, ou OLAP / entrepôt de données ?
On peut tout à fait avoir un nombre d'attentes élevé mais de courte durée; l'inverse est effectivement un problème.
Auriez-vous la sortie de la DMV sys.dm_os_wait_stats SVP ?

Est-ce que désactiver la parallélisation au niveau de la requête à l'aide d'OPTION (MAXDOP 1) a rendu votre requête plus rapide à s'exécuter ?
Si tel est le cas, il faut comprendre pourquoi l'optimiseur a fait le choix de la paralléliser.

Merci d'avoir mis le DDL des tables et le plan; malheureusement :

Sans les données qui sont dans ces tables, on ne peut pas comprendre précisément ce qui s'est passé
Il se peut que même en ayant les données, nous n'ayons pas les mêmes statistiques de colonne et d'index que les vôtres
Une capture du plan d'exécution graphique ne permet pas de rentrer dans les détails de celui-ci

Je vous invite donc à lire ce petit billet
Si vous ne pouvez pas partager le plan sur le forum pour des raisons de confidentialité, ce qui est tout à fait normal, vous pouvez me le passer par message privé, et je le regarderai.

@++

**Boubou2020** · 23/02/2016, 08h46

Bonjour
il s'agit d'une base OLTP
L'opération maxdop(1) a été changer au niveau de serveur
la résulat de la requéte sys.dm_os_wait_stat pointe sur ces deux compteur SOS_SCHEDULER_YIELD et CXPACKET

Juste une remarque dans le programe il y beaucoup d'appel au requétes adhoc car pas de proc stock dans la base
je vous communique la résultat du compteur compilations/sec

Code :

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

1
2
select * from sys.dm_os_performance_counters
where counter_name='SQL Compilations/Sec'

Code :

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

1
2
object_name	counter_name	instance_name	cntr_value	cntr_type
SQLServer:SQL Statistics                                                                                                        	SQL Compilations/sec                                                                                                            	                                                                                                                                	114048077	272696576

La plupart des requêtes sont des select (avec beaucoup d'union)
alors je m oriente vers ce piste et je pause cette question Quel est l'impact sur la perfermence si tout mes requétes sont des requétes adhoc et est ce que ce ci explique cette charge de CPU ??

Lenteur sur une requête d'extraction

Administration SQL Server

Discussions similaires

Partager

Partager