Problème de lenteur SQL Serveur

**funkyjul** · 20/05/2014, 09h41

Bonjour,

Suite à des problèmes de que je rencontre dans mon entreprise je fais appel à vos connaissances pour m'aider à trouver ce qui cloche ou du moins m'aider à me mettre sur la voie. En gros notre application de gestion est souvent lente et ce de façon aléatoire. La simple navigation entre différente lignes d'une commande qui est normalement instantanée peut prendre des fois plus de 30 secondes. Peut importe en fait ce que les utilisateurs font. Il arrive que ça soit très lent et que mon téléphone fasse une crise cardiaque tellement il sonne. Je ne sais plus quoi faire. Voici quelques détails sur notre infrastructure.

Nous avons migré il y a peu notre serveur SQL Serveur 2008R2 vers une version standard de 2012. Il tourne sur une machine virtuelle VMWare sous windows server 2012 avec 24 Go de RAM et 6 CPU virtuel. Bon je ne pense pas que ça soit ça qui provoque ces lenteurs car en observant de plus près la RAM n'est jamais utilisée à fond (contrairement à SQL Serveur 2008R2 qui mangeait toute la RAM qu'il avait sous la main), ni même le processeur. Nous avons 2 autre VM hébergées sur le même serveur physique, voilà pour la matos.

Sinon notre application de gestion (base SQL Serveur et interface MS Access) tourne sur une bonne vingtaine de postes. J'ai mis à jour les drivers ODBC pour SQL Serveur de ces postes, idem pour le runtime d'access 2010.

Et voilà rien ne change et je vous avoue que n'étant pas un grand spécialiste de SQL Serveur je galère un peu pour essayer de trouver des solutions ou même des pistes pour mes recherche. Ce qui m'embête le plus c'est que lors de ces lenteurs, je ne vois aucun verrous sur la base de données (via SP_WHO), ce qui n'était pas le cas sur 2008 R2 où je pouvais très facilement identifier le bloqueur et régler le problème. La plupart du temps aucun job de l'agent ne tourne, ni la RAM ni les CPU ne sont au max. Je regarde bien le moniteur d'activité de la base mais là aussi j'avoue qu'il m'est bien difficile de dire :"ça je peux m'en fiche... ça c'est pas bon". C'est bien beau d'avoir des outils quand on ne peut en interpréter les résultats correctement...

J'ai par contre pu identifier grâce à ce moniteur d'activité quelques requêtes bien couteuse, que j'ai pu améliorer mais cela ne suffit malheureusement pas.

J'ai également contrôler l'allocation des ressource du serveur à SQL, ici non plus pas de limite, le moteur SQL est sensé prendre tout ce dont il a besoin sans restriction.

J'ai pensé également à d'éventuels problèmes au niveau du réseau mais je ne pense pas non plus que je trouverai mon problème ici car l'accès aux fichiers sur notre réseau est nickel.

Pour info encore, nous avons une autre application (gestion de prod) qui elle tourne uniquement sous access (base et interface) et qui elle aussi est assez lente. Mais ici c'est tout le temps. J'ai donc passé les utilisateurs principaux en TS sur notre serveur TS et là plus de problème!

Voilà je crois que j'ai tout dis... si quelqu'un donc à quelques pistes pour m'aider ou me guider je prends de suite.

D'avance merci

Bonne journée à toutes et tous.

**rudib** · 20/05/2014, 10h04

Hello,

la première chose à faire est d'utiliser le profiler pour tracer le comportement des requêtes au moment où les ralentissements se produisent. Tu peux avoir une introduction au profiler dans mon article : http://rudi.developpez.com/sqlserver...misation/#LI-A, tu as aussi une vidéo gratuite sur le sujet dans ma formation vidéo chez video2brain : https://www.video2brain.com/fr/forma...oft-sql-server.

En attendant, peux-tu poster le résultat de ces trois requêtes :

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
SELECT TOP 20 wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, wait_time_ms / COALESCE(NULLIF(waiting_tasks_count, 0), 1) as moyenne
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY moyenne DESC;
 
SELECT TOP 20
	t.text, 
	execution_count, 
	total_logical_reads, 
	last_logical_reads, 
	min_logical_reads, 
	max_logical_reads,
	total_rows, 
	last_rows, 
	min_rows, 
	max_rows
FROM sys.dm_exec_query_Stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY execution_count DESC
 
SELECT TOP 20
	t.text, 
	execution_count, 
	total_logical_reads, 
	last_logical_reads, 
	min_logical_reads, 
	max_logical_reads,
	total_rows, 
	last_rows, 
	min_rows, 
	max_rows
FROM sys.dm_exec_query_Stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY last_logical_reads DESC

**funkyjul** · 20/05/2014, 10h29

Bonjour rudib, merci pour ta réponse.

Voici les résultats des requêtes que tu m'as indiquées :

la 1ère :

Code :

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

1
2
3
4
SELECT TOP 20 wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, wait_time_ms / COALESCE(NULLIF(waiting_tasks_count, 0), 1) AS moyenne
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY moyenne DESC;

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
wait_type	waiting_tasks_count	wait_time_ms	max_wait_time_ms	signal_wait_time_ms	moyenne
BROKER_EVENTHANDLER	1879	1457152751	46708316	2165	775493
BROKER_RECEIVE_WAITFOR	217	50778410	599720	68	234001
XE_DISPATCHER_WAIT	12242	1468872632	120276	0	119986
CLR_AUTO_EVENT	61758	3222715986	93623677	4512	52182
PWAIT_ALL_COMPONENTS_INITIALIZED	3	100771	33603	0	33590
LCK_M_IX	92	2226885	280097	16	24205
CLR_SEMAPHORE	138246	2269829583	20159	5000	16418
BROKER_TASK_STOP	33436	178576341	10044	2479	5340
REQUEST_FOR_DEADLOCK_SEARCH	293757	1468992212	5209	1468992212	5000
XE_TIMER_EVENT	330453	1468952433	5286	1468952433	4445
SQLTRACE_INCREMENTAL_FLUSH_SLEEP	367196	1469010758	4255	121	4000
CHKPT	1	3053	3053	0	3053
SLEEP_SYSTEMTASK	1	3053	3053	0	3053
SLEEP_MASTERDBREADY	1	2535	2535	0	2535
LCK_M_IS	37	58713	10775	5	1586
ASYNC_IO_COMPLETION	1174	1529907	116436	498	1303
BROKER_TO_FLUSH	717092	734452904	1622	26162	1024
LOGMGR_RESERVE_APPEND	7	7031	1029	0	1004
LAZYWRITER_SLEEP	1468653	1468999481	1340	56526	1000
SLEEP_DCOMSTARTUP	2	1633	944	0	816

la seconde :

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 TOP 20
	t.text, 
	execution_count, 
	total_logical_reads, 
	last_logical_reads, 
	min_logical_reads, 
	max_logical_reads,
	total_rows, 
	last_rows, 
	min_rows, 
	max_rows
FROM sys.dm_exec_query_Stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY execution_count DESC

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
163
164
165
166
167
168
169
170
171
172
173
174
175
text	execution_count	total_logical_reads	last_logical_reads	min_logical_reads	max_logical_reads	total_rows	last_rows	min_rows	max_rows
(@Param000004 int,@Param000005 int,@Param000006 int,@Param000007 int,@Param000008 smalldatetime,@Param000009 smalldatetime,@Param000010 real,@Param000011 real,@Param000012 float,@Param000013 real,@Param000014 real,@Param000015 nvarchar(50),@Param000016 smalldatetime,@Param000017 bit,@Param000018 ntext,@Param000019 smalldatetime,@Param000020 nvarchar(30),@Param000021 bit,@Param000022 float,@Param000023 float,@Param000024 float,@Param000025 smalldatetime,@Param000026 bit)INSERT [cimproductimport].[dbo].[RJP]([IdRJP],[IdOFOpe],[IdOperateur],[IdRess],[DateDebut],[DateFin],[Reglage],[TempsDemontage],[Qte],[Avancement],[Duree],[NumLot],[DateStoppe],[Cloture],[Remarque],[DateExport],[CodeImport],[HorsPlanning],[QteImport],[NbOperateurs],[TpsOperateurs],[DateRemarque],[UpdateFlag]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015,@Param000016,@Param000017,@Param000018,@Param000019,@Param000020,@Param000021,@Param000022,@Param000023,@Param000024,@Param000025,@Param000026)	54575695	60721343	1	1	27	54575695	1	1	1
(@Param000004 int,@Param000005 int,@Param000006 int,@Param000007 real,@Param000008 float,@Param000009 float,@Param000010 real,@Param000011 real,@Param000012 int,@Param000013 smallint,@Param000014 bit,@Param000015 nvarchar(30))INSERT [CimproductImport].[dbo].[PiecesOperationRess]([Id],[IdOperation],[IdRess],[TempsMontage],[CadenceH],[DureeU],[TempsDemontage],[DureeLot],[QteMini],[Preference],[DevisPrincipale],[CodeImport]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015)	4435715	32342714	7	7	44	4435715	1	1	1
SELECT "IdTiers"  FROM "dbo"."tiers" 	608944	40837966	68	67	68	1025714061	1688	1682	1688
SELECT spinReports2Print.*, srsp_devicename FROM spinReports2Print
LEFT JOIN spinReportsServerPrinters ON spinReportsServerPrinters.srsp_id=spinReports2Print.srsp_id
WHERE spinReports2Print.sr2p_done=0
AND spinReports2Print.srsp_id in (select srsp_id from spinReportsServerPrinters where srs_id=8);
	293664	41731399	143	0	293	1861	0	0	6
(@Param000004 datetime,@Param000005 int,@Param000006 int,@Param000007 nvarchar(12),@Param000008 nvarchar(50),@Param000009 int,@Param000010 nvarchar(50),@Param000011 nvarchar(4),@Param000012 nvarchar(50),@Param000013 nvarchar(255),@Param000014 float,@Param000015 money,@Param000016 money,@Param000017 float,@Param000018 money,@Param000019 money,@Param000020 money,@Param000021 money,@Param000022 money,@Param000023 bit,@Param000024 money,@Param000025 float,@Param000026 bit,@Param000027 nvarchar(4),@Param000028 float,@Param000029 nvarchar(20),@Param000030 int,@Param000031 nvarchar(12),@Param000032 nvarchar(50),@Param000033 nvarchar(20),@Param000034 nvarchar(12),@Param000035 nvarchar(10),@Param000036 int,@Param000037 int,@Param000038 bit,@Param000039 bit,@Param000040 int,@Param000041 nvarchar(4),@Param000042 int,@Param000043 bit,@Param000044 int,@Param000045 datetime,@Param000046 nvarchar(50),@Param000047 datetime,@Param000048 smallint,@Param000049 bit,@Param000050 bit,@Param000051 int,@Param000052 int,@Param000053 int,@Param000054 bit,@Param000055 datetime,@Param000056 int,@Param000057 datetime,@Param000058 smallint,@Param000059 nvarchar(50),@Param000060 nvarchar(50),@Param000061 nvarchar(50),@Param000062 nvarchar(50),@Param000063 nvarchar(50),@Param000064 nvarchar(50),@Param000065 nvarchar(50),@Param000066 nvarchar(50),@Param000067 nvarchar(50),@Param000068 nvarchar(28),@Param000069 nvarchar(28),@Param000070 nvarchar(28),@Param000071 nvarchar(28),@Param000072 nvarchar(21),@Param000073 nvarchar(35),@Param000074 nvarchar(30),@Param000075 nvarchar(30),@Param000076 nvarchar(30),@Param000077 nvarchar(30),@Param000078 nvarchar(30),@Param000079 nvarchar(30),@Param000080 nvarchar(30),@Param000081 nvarchar(30),@Param000082 nvarchar(30),@Param000083 int,@Param000084 smallint,@Param000085 datetime,@Param000086 datetime)INSERT [cptalignes]([LI_Date],[LI_JOCpt],[LI_Cpt],[LI_Journal],[LI_Tri],[LI_No],[LI_Numero],[LI_TypeJournal],[LI_NoCompte],[LI_Lib],[LI_Qte],[LI_MontantHT],[LI_MontantHTMB],[LI_CumulQte],[LI_CumulPO],[LI_CumulVal],[LI_CumulPOME],[LI_CumulValME],[LI_CumulRegr],[LI_PosteOuvert],[LI_CumulRegrME],[LI_SaisieTech],[LI_Previsionnelle],[LI_Monnaie],[LI_Taux],[LI_User],[LI_Secriture],[LI_NoPiece],[LI_NoCcpar],[LI_NoFact],[LI_NoAffaire],[LI_DateLettrage],[LI_NoEcrCG],[LI_NoLettrage],[LI_VentAnalytique],[LI_Analytique],[LI_SecrAna],[LI_TVA],[LI_TypeEcrCG],[LI_VentTVA],[LI_NoSoumisTVA],[LI_DateEcheance],[LI_NoFacture],[LI_DateRappel],[LI_NivRappel],[LI_ARolluper],[LI_Bloque],[LI_NoPmtAuto],[LI_NoPmtAutoDeb],[LI_NoBVR],[LI_Systeme],[LI_DateReeval],[LI_NoFACO],[LI_DateBouclement],[LI_TypePaiement],[LI_NoComptePostal],[LI_Clearing],[LI_Reference],[LI_NoCompteBancaire],[LI_Ben1],[LI_Ben2],[LI_Ben3],[LI_Ben4],[LI_Ben5],[LI_Motif1],[LI_Motif2],[LI_Motif3],[LI_Motif4],[LI_CodeSWIFT],[LI_CodeIBAN],[LI_BanqueBen1],[LI_BanqueBen2],[LI_BanqueBen3],[LI_BanqueBen4],[LI_BanqueBen5],[LI_InstrPart1],[LI_InstrPart2],[LI_InstrPart3],[LI_InstrPart4],[LI_DoCpt],[LI_Frais],[LI_Now],[LI_SYSCreate]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015,@Param000016,@Param000017,@Param000018,@Param000019,@Param000020,@Param000021,@Param000022,@Param000023,@Param000024,@Param000025,@Param000026,@Param000027,@Param000028,@Param000029,@Param000030,@Param000031,@Param000032,@Param000033,@Param000034,@Param000035,@Param000036,@Param000037,@Param000038,@Param000039,@Param000040,@Param000041,@Param000042,@Param000043,@Param000044,@Param000045,@Param000046,@Param000047,@Param000048,@Param000049,@Param000050,@Param000051,@Param000052,@Param000053,@Param000054,@Param000055,@Param000056,@Param000057,@Param000058,@Param000059,@Param000060,@Param000061,@Param000062,@Param000063,@Param000064,@Param000065,@Param000066,@Param000067,@Param000068,@Param000069,@Param000070,@Param000071,@Param000072,@Param000073,@Param000074,@Param000075,@Param000076,@Param000077,@Param000078,@Param000079,@Param000080,@Param000081,@Param000082,@Param000083,@Param000084,@Param000085,@Param000086)	187753	488362	2	2	25	187753	1	1	1
 
                                            declare @BatchID uniqueidentifier
 
                                            set @BatchID = NEWID()
 
                                            UPDATE [Event] WITH (TABLOCKX)
                                                SET [BatchID] = @BatchID,
                                                [ProcessStart] = GETUTCDATE(),
                                                [ProcessHeartbeat] = GETUTCDATE()
                                            FROM (
                                                SELECT TOP 6 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
                                                ) AS t1
                                            WHERE [Event].[EventID] = t1.[EventID]
 
                                            select top 6
	                                            E.[EventID],
	                                            E.[EventType],
	                                            E.[EventData]
                                            from
	                                            [Event] E WITH (TABLOCKX)
                                            where
	                                            [BatchID] = @BatchID
                                            ORDER BY [TimeEntered]
                                            	145465	290948	2	2	20	0	0	0	0
 
                                            declare @BatchID uniqueidentifier
 
                                            set @BatchID = NEWID()
 
                                            UPDATE [Event] WITH (TABLOCKX)
                                                SET [BatchID] = @BatchID,
                                                [ProcessStart] = GETUTCDATE(),
                                                [ProcessHeartbeat] = GETUTCDATE()
                                            FROM (
                                                SELECT TOP 6 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
                                                ) AS t1
                                            WHERE [Event].[EventID] = t1.[EventID]
 
                                            select top 6
	                                            E.[EventID],
	                                            E.[EventType],
	                                            E.[EventData]
                                            from
	                                            [Event] E WITH (TABLOCKX)
                                            where
	                                            [BatchID] = @BatchID
                                            ORDER BY [TimeEntered]
                                            	145465	290930	2	2	2	0	0	0	0
 
                                    declare @BatchID uniqueidentifier
 
                                    set @BatchID = newid()
 
                                    UPDATE [Notifications] WITH (TABLOCKX)
                                        SET [BatchID] = @BatchID,
                                        [ProcessStart] = GETUTCDATE(),
                                        [ProcessHeartbeat] = GETUTCDATE()
                                    FROM (
                                        SELECT TOP 6  [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
	                                    (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered]
                                    ) AS t1
                                    WHERE [Notifications].[NotificationID] = t1.[NotificationID]
 
                                    select top 6
		                                    -- Notification data
		                                    N.[NotificationID],
		                                    N.[SubscriptionID],
		                                    N.[ActivationID],
		                                    N.[ReportID],
		                                    N.[SnapShotDate],
		                                    N.[DeliveryExtension],
		                                    N.[ExtensionSettings],
                                            N.[Locale],
		                                    N.[Parameters],
		                                    N.[SubscriptionLastRunTime],
		                                    N.[ProcessStart],
		                                    N.[NotificationEntered],
		                                    N.[Attempt],
		                                    N.[IsDataDriven],
		                                    SUSER_SNAME(Owner.[Sid]),
		                                    Owner.[UserName],
		                                    -- Report Data
		                                    O.[Path],
		                                    N.[ReportZone],
		                                    O.[Type],
		                                    SD.NtSecDescPrimary,
                                            N.[Version],
                                            Owner.[AuthType]
	                                    from 
		                                    [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID]
		                                    inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID
		                                    left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
	                                    where 
		                                    N.[BatchID] = @BatchID
                                    ORDER BY [NotificationEntered]
                                    	145448	290940	2	2	46	0	0	0	0
 
                                    declare @BatchID uniqueidentifier
 
                                    set @BatchID = newid()
 
                                    UPDATE [Notifications] WITH (TABLOCKX)
                                        SET [BatchID] = @BatchID,
                                        [ProcessStart] = GETUTCDATE(),
                                        [ProcessHeartbeat] = GETUTCDATE()
                                    FROM (
                                        SELECT TOP 6  [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
	                                    (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered]
                                    ) AS t1
                                    WHERE [Notifications].[NotificationID] = t1.[NotificationID]
 
                                    select top 6
		                                    -- Notification data
		                                    N.[NotificationID],
		                                    N.[SubscriptionID],
		                                    N.[ActivationID],
		                                    N.[ReportID],
		                                    N.[SnapShotDate],
		                                    N.[DeliveryExtension],
		                                    N.[ExtensionSettings],
                                            N.[Locale],
		                                    N.[Parameters],
		                                    N.[SubscriptionLastRunTime],
		                                    N.[ProcessStart],
		                                    N.[NotificationEntered],
		                                    N.[Attempt],
		                                    N.[IsDataDriven],
		                                    SUSER_SNAME(Owner.[Sid]),
		                                    Owner.[UserName],
		                                    -- Report Data
		                                    O.[Path],
		                                    N.[ReportZone],
		                                    O.[Type],
		                                    SD.NtSecDescPrimary,
                                            N.[Version],
                                            Owner.[AuthType]
	                                    from 
		                                    [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID]
		                                    inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID
		                                    left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
	                                    where 
		                                    N.[BatchID] = @BatchID
                                    ORDER BY [NotificationEntered]
                                    	145448	1163668	8	8	92	0	0	0	0
(@Param000004 int,@Param000005 int,@Param000006 nvarchar(50),@Param000007 nvarchar(69),@Param000008 nvarchar(24),@Param000009 nvarchar(30),@Param000010 float,@Param000011 nvarchar(50),@Param000012 int,@Param000013 int,@Param000014 float,@Param000015 int,@Param000016 nvarchar(30),@Param000017 nvarchar(16),@Param000018 bit,@Param000019 real,@Param000020 float,@Param000021 float,@Param000022 bit,@Param000023 float,@Param000024 float,@Param000025 float,@Param000026 float,@Param000027 int,@Param000028 float,@Param000029 nvarchar(20),@Param000030 nvarchar(20),@Param000031 nvarchar(30),@Param000032 ntext,@Param000033 bit,@Param000034 bit,@Param000035 real,@Param000036 float,@Param000037 smallint,@Param000038 smalldatetime,@Param000039 nvarchar(50),@Param000040 nvarchar(50),@Param000041 smallint,@Param000042 smallint,@Param000043 float,@Param000044 float,@Param000045 float,@Param000046 float,@Param000047 nvarchar(1),@Param000048 smallint,@Param000049 bit,@Param000050 nvarchar(15),@Param000051 smallint,@Param000052 int,@Param000053 real,@Param000054 nvarchar(100),@Param000055 float,@Param000056 float,@Param000057 float,@Param000058 float,@Param000059 nvarchar(30),@Param000060 bit,@Param000061 nvarchar(30))INSERT [cimproductimport].[dbo].[Piece]([IdPiece],[IdAtelier],[RefPiece],[NomPiece],[RefPieceCli],[CodeImport],[PxHT],[Plan],[IdClient],[FamOrdre],[Categorie],[IdMatiere],[NumDossier],[NoCarteControle],[Standart],[DureePreparation],[CoutMatiere],[CoutPreparation],[MatiereFournie],[TarifMontage],[TarifFacon],[PoidsNet],[PoidsBrut],[IdSerieLot],[LotMiniEco],[Nuance],[Forme],[Debit],[Remarque],[FActive],[Verifie],[NomenDecalJour],[NomenDecalRef],[FlEdition],[DateMaj],[NomFicLie],[NomEtat],[ANGHELICE],[ANGCOUPE],[D1],[L2],[D2],[L1],[GD],[Z],[JEU],[COLLET],[NbExDoc],[NbPieceParDoc],[QteConditionnement],[Image],[RAYON],[CHANFREIN],[ANGLE],[PLAT_POINTE],[SPECIFICITES],[WELDON],[ANGPOINTE]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015,@Param000016,@Param000017,@Param000018,@Param000019,@Param000020,@Param000021,@Param000022,@Param000023,@Param000024,@Param000025,@Param000026,@Param000027,@Param000028,@Param000029,@Param000030,@Param000031,@Param000032,@Param000033,@Param000034,@Param000035,@Param000036,@Param000037,@Param000038,@Param000039,@Param000040,@Param000041,@Param000042,@Param000043,@Param000044,@Param000045,@Param000046,@Param000047,@Param000048,@Param000049,@Param000050,@Param000051,@Param000052,@Param000053,@Param000054,@Param000055,@Param000056,@Param000057,@Param000058,@Param000059,@Param000060,@Param000061)	129151	253667	1	1	7	129151	1	1	1
SELECT "IdRess"  FROM "dbo"."ressources" 	114156	228312	2	2	2	34703424	304	304	304
(@Param000004 int,@Param000005 int,@Param000006 nvarchar(50),@Param000007 nvarchar(69),@Param000008 nvarchar(24),@Param000009 nvarchar(30),@Param000010 float,@Param000011 nvarchar(50),@Param000012 int,@Param000013 int,@Param000014 float,@Param000015 int,@Param000016 nvarchar(30),@Param000017 nvarchar(16),@Param000018 bit,@Param000019 real,@Param000020 float,@Param000021 float,@Param000022 bit,@Param000023 float,@Param000024 float,@Param000025 float,@Param000026 float,@Param000027 int,@Param000028 float,@Param000029 nvarchar(20),@Param000030 nvarchar(20),@Param000031 nvarchar(30),@Param000032 bit,@Param000033 bit,@Param000034 real,@Param000035 float,@Param000036 smallint,@Param000037 smalldatetime,@Param000038 nvarchar(50),@Param000039 nvarchar(50),@Param000040 smallint,@Param000041 smallint,@Param000042 float,@Param000043 float,@Param000044 float,@Param000045 float,@Param000046 nvarchar(1),@Param000047 smallint,@Param000048 bit,@Param000049 nvarchar(15),@Param000050 smallint,@Param000051 int,@Param000052 real,@Param000053 nvarchar(100),@Param000054 float,@Param000055 float,@Param000056 float,@Param000057 float,@Param000058 nvarchar(30),@Param000059 bit,@Param000060 nvarchar(30),@Param000061 ntext)INSERT [cimproductimport].[dbo].[Piece]([IdPiece],[IdAtelier],[RefPiece],[NomPiece],[RefPieceCli],[CodeImport],[PxHT],[Plan],[IdClient],[FamOrdre],[Categorie],[IdMatiere],[NumDossier],[NoCarteControle],[Standart],[DureePreparation],[CoutMatiere],[CoutPreparation],[MatiereFournie],[TarifMontage],[TarifFacon],[PoidsNet],[PoidsBrut],[IdSerieLot],[LotMiniEco],[Nuance],[Forme],[Debit],[FActive],[Verifie],[NomenDecalJour],[NomenDecalRef],[FlEdition],[DateMaj],[NomFicLie],[NomEtat],[ANGHELICE],[ANGCOUPE],[D1],[L2],[D2],[L1],[GD],[Z],[JEU],[COLLET],[NbExDoc],[NbPieceParDoc],[QteConditionnement],[Image],[RAYON],[CHANFREIN],[ANGLE],[PLAT_POINTE],[SPECIFICITES],[WELDON],[ANGPOINTE],[Remarque]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015,@Param000016,@Param000017,@Param000018,@Param000019,@Param000020,@Param000021,@Param000022,@Param000023,@Param000024,@Param000025,@Param000026,@Param000027,@Param000028,@Param000029,@Param000030,@Param000031,@Param000032,@Param000033,@Param000034,@Param000035,@Param000036,@Param000037,@Param000038,@Param000039,@Param000040,@Param000041,@Param000042,@Param000043,@Param000044,@Param000045,@Param000046,@Param000047,@Param000048,@Param000049,@Param000050,@Param000051,@Param000052,@Param000053,@Param000054,@Param000055,@Param000056,@Param000057,@Param000058,@Param000059,@Param000060,@Param000061)	104731	423623	2	2	16	104731	1	1	1
(@P1 nvarchar(12))SELECT "CL_Code" ,"dbo"."Clients"."CL_Code" FROM "dbo"."Clients"  WHERE ("CL_Code" = @P1)	104030	208060	2	2	2	104030	1	1	1
(@1 tinyint,@2 varchar(8000))SELECT [srsp_id] FROM [dbo].[spinReportsServerPrinters] WHERE [srs_id]=@1 AND [srsp_devicename]=@2	88032	352128	4	4	4	88032	1	1	1
SELECT "dbo"."spinReports2Print"."sr2p_id","MS1"."srsp_id" FROM {oj "dbo"."spinReports2Print" LEFT OUTER JOIN "dbo"."spinReportsServerPrinters" "MS1" ON ("dbo"."spinReports2Print"."srsp_id" = "MS1"."srsp_id" ) } WHERE (("dbo"."spinReports2Print"."sr2p_done" = 0 ) AND (("dbo"."spinReports2Print"."srsp_id" )= ANY (SELECT "MS2"."srsp_id"  FROM "dbo"."spinReportsServerPrinters" "MS2" WHERE ("MS2"."srs_id" = 8 ) ))) 	85378	12145605	143	140	185	2305	0	0	6
()
 select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [SSISDB].[sys].[filetable_updates_2105058535] with (readpast) order by table_id	73404	0	0	0	0	0	0	0	0
()
 select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [ReportServer$XSSQL].[sys].[filetable_updates_2105058535] with (readpast) order by table_id	73404	0	0	0	0	0	0	0	0
()
 select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [cimproductimport].[sys].[filetable_updates_1410104064] with (readpast) order by table_id	73404	0	0	0	0	0	0	0	0
()
 select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [ReportServer$XSSQLTempDB].[sys].[filetable_updates_2105058535] with (readpast) order by table_id	73404	0	0	0	0	0	0	0	0
()
 select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [tc_dms_lbsa].[sys].[filetable_updates_2021582240] with (readpast) order by table_id	73404	0	0	0	0	0	0	0	0

enfin la 3ème :

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 TOP 20
	t.text, 
	execution_count, 
	total_logical_reads, 
	last_logical_reads, 
	min_logical_reads, 
	max_logical_reads,
	total_rows, 
	last_rows, 
	min_rows, 
	max_rows
FROM sys.dm_exec_query_Stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY last_logical_reads DESC

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
163
164
165
166
167
text	execution_count	total_logical_reads	last_logical_reads	min_logical_reads	max_logical_reads	total_rows	last_rows	min_rows	max_rows
SELECT li_cpt, li_docpt, LI_Document, LI_No, LI_TypeDoc, LI_Article, LI_Client, LI_Qte, LI_Solde, LI_MontantHT, LI_PosteOuvert, LI_Unite, LI_User, LI_PrixHT, LI_Monnaie, CL_Nom, CL_PNom, CL_Societe, CL_Departement, LI_Lib, LI_Fournisseur, LI_RefFournisseur, LI_PVTTCLot, LI_NoLot, LI_DateEch, LI_Cpt, LI_Date, LI_NoSemaine, LI_PrixBrut, LI_RabaisBrut, LI_PrixNet, LI_RabaisNet, LI_SousDossier, LI_DateIntervention, LI_Remarque, LI_NoStock, LI_MontantHTMB,li_delaitexte, li_lieu, li_taille, li_lettrage FROM Lignes WITH (NOLOCK) INNER JOIN Clients WITH (NOLOCK) ON clients.cl_code=lignes.li_client  WHERE LI_Previsionnelle=0 and li_article is not null AND LI_Lib LIKE '%1235-2%'  ORDER BY LI_DATE DESC, LI_SYS_CREATION DESC	1	13428842	13428842	13428842	13428842	25	25	25	25
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND stock <= ar_stockmin 
         ORDER BY  ar_code) AS sub1  ORDER BY  ar_code DESC) AS sub2  ORDER BY  ar_code	1	6939185	6939185	6939185	6939185	20	20	20	20
DELETE FROM PiecesOperationRess	14	58454905	4185794	4163483	4185794	4434032	317509	315823	317509
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ar_code) AS sub1  ORDER BY  ar_code DESC) AS sub2  ORDER BY  ar_code	1	2300974	2300974	2300974	2300974	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 140  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641788	1641788	1641788	1641788	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 120  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641773	1641773	1641773	1641773	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 100  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641772	1641772	1641772	1641772	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 220  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641771	1641771	1641771	1641771	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 160  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641771	1641771	1641771	1641771	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 200  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641771	1641771	1641771	1641771	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 240  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641771	1641771	1641771	1641771	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 180  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641771	1641771	1641771	1641771	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 40  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641769	1641769	1641769	1641769	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 60  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641769	1641769	1641769	1641769	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 80  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1641769	1641769	1641769	1641769	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '%%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999 AND a_produire!=0 
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	2	3283536	1641769	1641767	1641769	40	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '111-3%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999  
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	1	1560058	1560058	1560058	1560058	20	20	20	20
SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 40  *, consorevYear+consoCurYear as ctot FROM vlbsa_reap_mainboard 
        	WHERE 
        		isnull(ar_modereappro,0) = CASE WHEN -1='-1' THEN isnull(ar_modereappro,0) ELSE -1 END 
        	AND	isnull(artcar_special,0) = CASE WHEN '0'='@all' THEN isnull(artcar_special,0) ELSE '0' END
        	AND	isnull(ar_tdreapro,'') = CASE WHEN '@all'='@all' THEN isnull(ar_tdreapro,'') ELSE '@all' END
  			 AND revet is null
		AND ar_code LIKE '111-3%'
        	AND isnull(artcar_dim_d1,0) between 0 
        	AND 999  
         ORDER BY  ctot ASC) AS sub1  ORDER BY  CTOT DESC) AS sub2  ORDER BY  ctot ASC	2	3120082	1560034	1560034	1560048	40	20	20	20
SELECT ArticlesCaracteristiques.*, ArticlesCaracteristiques.ar_code as hide_ar_code, cast(isnull(libln, libfr) as nvarchar(500)) as libelle , rev_nom as revetement  , fullarticle,  CASE WHEN isnull(listk.stk,0)<=0 THEN 0 ELSE listk.stk END as stk , CASE  WHEN (len(isnull(rev_code,''))=0 AND ('@all'='@none')) THEN 100  WHEN rev_code='@all' THEN 100  WHEN (len(isnull(rev_code,''))>0 AND ('@all'='@rev')) THEN 100  ELSE 0 END as cfl_highlight_prct   FROM ArticlesCaracteristiques with (nolock) LEFT JOIN (SELECT la_article, la_lib as libfr FROM libart with (nolock) WHERE la_langue='f') as tlibfr on tlibfr.la_article =ArticlesCaracteristiques.ar_code LEFT JOIN (SELECT la_article, la_lib as libln FROM libart WITH (nolock) WHERE la_langue=(SELECT cl_langue FROM clients 
with (nolock) WHERE cl_code='0013')) as tlibln on tlibln.la_article = ArticlesCaracteristiques.ar_code  LEFT JOIN (SELECT lignes.li_article as fullarticle, sum(li_qte * case when li_stock=1 then 1 else -1 end) as stk, CASE WHEN (ascii((right(li_article,1)))>64) THEN LEFT(li_article,len(li_article)-2) ELSE li_article END as li_article FROM lignes with(nolock) where li_stock!=0 AND len(li_article)>2 and li_previsionnelle=0 AND li_nostock IN (SELECT 0 UNION  SELECT ts_no FROM stocksclients WHERE cl_code='0013') GROUP BY  lignes.li_article) as listk  ON listk.li_article=articlescaracteristiques.ar_code LEFT JOIN lbsa_revetements with (nolock)  on lbsa_revetements.rev_code = right(isnull(listk.fullarticle,'xx'),2)  WHERE  artcar_dim_d1 =17.0000 AND artcar_reference ='117-1' AND ((len(isnull(rev_code,''))=0) OR (isnull(stk,0)>0 AND len(isnull(rev_code,''))>0)) AND ((isnull(stk,0)>0) AND (left(ar_code,3)='pla') or (left(ar_code,3)!='pla'))   ORDER BY artcar_reference, artcar_dim_d1, isnull(artcar_dim_l1,0),isnull(artcar_dim_e,0), isnull(artcar_dim_D,0),  isnull(artcar_dim_l,0), isnull(artcar_dim_z,0), isnull(artcar_dim_d2,0), artcar_special, rev_nom 	1	1453839	1453839	1453839	1453839	5	5	5	5
SELECT ArticlesCaracteristiques.*, ArticlesCaracteristiques.ar_code as hide_ar_code, cast(isnull(libln, libfr) as nvarchar(500)) as libelle , rev_nom as revetement  , fullarticle,  CASE WHEN isnull(listk.stk,0)<=0 THEN 0 ELSE listk.stk END as stk , CASE  WHEN (len(isnull(rev_code,''))=0 AND ('@all'='@none')) THEN 100  WHEN rev_code='@all' THEN 100  WHEN (len(isnull(rev_code,''))>0 AND ('@all'='@rev')) THEN 100  ELSE 0 END as cfl_highlight_prct   FROM ArticlesCaracteristiques with (nolock) LEFT JOIN (SELECT la_article, la_lib as libfr FROM libart with (nolock) WHERE la_langue='f') as tlibfr on tlibfr.la_article =ArticlesCaracteristiques.ar_code LEFT JOIN (SELECT la_article, la_lib as libln FROM libart WITH (nolock) WHERE la_langue=(SELECT cl_langue FROM clients 
with (nolock) WHERE cl_code='0434')) as tlibln on tlibln.la_article = ArticlesCaracteristiques.ar_code  LEFT JOIN (SELECT lignes.li_article as fullarticle, sum(li_qte * case when li_stock=1 then 1 else -1 end) as stk, CASE WHEN (ascii((right(li_article,1)))>64) THEN LEFT(li_article,len(li_article)-2) ELSE li_article END as li_article FROM lignes with(nolock) where li_stock!=0 AND len(li_article)>2 and li_previsionnelle=0 AND li_nostock IN (SELECT 0 UNION  SELECT ts_no FROM stocksclients WHERE cl_code='0434') GROUP BY  lignes.li_article) as listk  ON listk.li_article=articlescaracteristiques.ar_code LEFT JOIN lbsa_revetements with (nolock)  on lbsa_revetements.rev_code = right(isnull(listk.fullarticle,'xx'),2)  WHERE  artcar_dim_d1 =1.2000 AND artcar_reference ='352' AND ((len(isnull(rev_code,''))=0) OR (isnull(stk,0)>0 AND len(isnull(rev_code,''))>0)) AND ((isnull(stk,0)>0) AND (left(ar_code,3)='pla') or (left(ar_code,3)!='pla'))   ORDER BY artcar_reference, artcar_dim_d1, isnull(artcar_dim_l1,0),isnull(artcar_dim_e,0), isnull(artcar_dim_D,0),  isnull(artcar_dim_l,0), isnull(artcar_dim_z,0), isnull(artcar_dim_d2,0), artcar_special, rev_nom 	1	1451447	1451447	1451447	1451447	6	6	6	6

Je vais également m'ateler à consulter précautionneusement les liens que tu m'as donné afin de "profiler" les requêtes... Déjà un grand merci

Heu par contre je me rends compte que les résultats ne sont pas très lisibles... comment pourrais-je te les donner plus "lisiblement"?

**rudib** · 20/05/2014, 10h41

Oui, ce serait bien une feuille excel ou LibreOffice Calc.

Juste encore le résultat de ceci :

Code :

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

1
2
3
4
SELECT TOP 20 wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, wait_time_ms / COALESCE(NULLIF(waiting_tasks_count, 0), 1) AS moyenne
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY waiting_tasks_count DESC;

Première hypothèse en voyant le code de tes requêtes : Il y a des verrous forcés sur la table (exemple : UPDATE [Event] WITH (TABLOCKX)) à plusieurs endroits, donc vous générez un blocage probablement fort, ce qui explique les attentes irrégulières.

Ce que tu vas faire pour vérifier ça :
1/ activer l'option "blocked process threshold" dans l'instance (mets-la à 10 secondes par exemple) : http://technet.microsoft.com/fr-fr/l.../ms181150.aspx
2/ lancer une session du profiler pour récupérer les événements "blocked process report" : http://technet.microsoft.com/fr-fr/l.../ms191168.aspx

**funkyjul** · 20/05/2014, 10h58

wow cool tout plein d'informations! Merci. Bon il y a un peu de lecture, je fais au plus vite.

Par contre pour te mettre les résultats sur un tableur j'ai un peu peur que ça ne donne pas nom plus super bien pour la présentation. Il y a à mon avis des retour chariot dans le résultat des requête et du coup ça fausse complètement le copier coller dans excel...

Je fais au mieux

**funkyjul** · 20/05/2014, 11h20

je te donne le résultats des requêtes 1 et 4 que tu m'as données.

Malheureusement je ne pense pas pourvoir te mettre les 2 autre mieux présentées, à moins que je trouve un moyen de supprimer les retour chariot directement dans l'exécution des requêtes....

Je regarderai pour les blocked process threshold en tout début d'après-midi car là malheureusement je dois me sauver.

Problème de lenteur SQL Serveur

Administration SQL Server

Vue hybride

Discussions similaires

Partager

Partager