Discussion :

[elsuket]

Bonjour,

Je voudrais écrire une requête qui me donne les processus qui tournent depuis trop longtemps ou qui ont font beaucoup d'I/O, et en même temps obtenir la requête SQL qui en est à l'origine.
Pour l'instant je n'ai pas défini de limite pour le temps ou pour le nombre d'I/O.

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
 
DECLARE @ACTIVE BIT; SET @ACTIVE = 1;
DECLARE @WITH_SQL BIT; SET @WITH_SQL = 1;
 
DECLARE @LIST_PROCESSES TABLE
(
	SPID INT,
	Hostname SYSNAME,
	Status SYSNAME,
	Command SYSNAME,
	[CPU Time] INT,
	[Disk I/O] INT,
	Blocked INT,
	[Database] SYSNAME,
	[Login] SYSNAME,
	[Last batch] DATETIME,
	Program SYSNAME
);
 
INSERT @LIST_PROCESSES
SELECT
	SPID,
	HOSTNAME,
	UPPER(STATUS),
	UPPER(CMD),
	CPU,
	PHYSICAL_IO,
	BLOCKED,
	DB_NAME(DBID),
	CONVERT(SYSNAME, RTRIM(LOGINAME)) AS LOGINNAME,
	LAST_BATCH,
	PROGRAM_NAME
FROM MASTER.DBO.SYSPROCESSES WITH (NOLOCK)
WHERE SPID > 50
AND SPID != @@SPID;
 
IF @ACTIVE = 1
	DELETE @LIST_PROCESSES
	WHERE STATUS = 'SLEEPING'
	AND COMMAND = 'AWAITING COMMAND'
	AND BLOCKED = 0
 
IF @WITH_SQL = 1
BEGIN
	DECLARE @SQL VARCHAR(64);
 
	DECLARE @PROCESSES TABLE
	(
		ID INT IDENTITY,
		SPID INT,
		Hostname SYSNAME,
		Status SYSNAME,
		Command SYSNAME,
		[CPU Time] INT,
		[Disk I/O] INT,
		Blocked INT,
		[Database] SYSNAME,
		[Login] SYSNAME,
		[Last batch] DATETIME,
		Program SYSNAME,
		SQL VARCHAR(256)
	);
 
	INSERT @PROCESSES
	SELECT *, NULL -- SQL
	FROM @LIST_PROCESSES;
 
	DECLARE @I INT; SET @I = 1;
	DECLARE @HISTO_SPID INT; SET @HISTO_SPID = 1;
	DECLARE @NB_PROCESSES INT; SELECT @NB_PROCESSES = COUNT(*) FROM @PROCESSES;
 
	SET NOCOUNT ON;
	WHILE @I <= @NB_PROCESSES
	BEGIN
		DECLARE @SP_ID INT;
 
		SELECT @SP_ID = SPID
		FROM @PROCESSES
		WHERE ID = @I;
 
		IF @HISTO_SPID != @SP_ID
		BEGIN;
			DECLARE @DBCC_INPUT_BUFFER TABLE
			(
				EventType SYSNAME,
				Parameters INT,
				EventInfo TEXT
			);
 
			SET @SQL = 'DBCC INPUTBUFFER (' + CONVERT(VARCHAR, @SP_ID) + ') WITH NO_INFOMSGS';
			SELECT @SQL;
 
			INSERT @DBCC_INPUT_BUFFER
			EXEC (@SQL);
 
			BEGIN TRY
				UPDATE @PROCESSES
				SET SQL = (SELECT SUBSTRING(Eventinfo, 1, 256) FROM @DBCC_INPUT_BUFFER)
				WHERE SPID = @SP_ID
			END TRY
			BEGIN CATCH
				SELECT @SP_ID, SUBSTRING(Eventinfo, 1, 256) FROM @DBCC_INPUT_BUFFER;
			END CATCH;
 
			SET @HISTO_SPID = @SP_ID;
		END;
		SET @I = @I + 1;
	END;
	SET NOCOUNT OFF;	
END;
 
SELECT *
FROM @PROCESSES

Dans la majorité des cas, ce batch fonctionne proprement, mais il arrive que j'aie 2 requêtes pour le même SPID, ce que je ne comprends pas.
Dans ce cas je passe dans le catch et j'obtiens quelque chose du style:

Pour le SPID a:
--------------
msdb.dbo.sp_readrequest;1
(@P1 nvarchar(4000) ,@P2 nvarchar(4000) , .....

Pour le SPID b:
--------------
msdb.dbo.sp_readrequest;1
(@P1 nvarchar(4000) ,@P2 nvarchar(4000) , .....
(@P1 nvarchar(4000) ,@P2 nvarchar(4000) , .....

les (@P1 nvarchar(4000) ,@P2 nvarchar(4000) , ..... sont strictement identiques ...

Quelqu'un peut m'expliquer pourquoi j'ai ce résultat là et jamais quand je le fais à la main ?

En vous remerciant

[kagemaru]

il semble que ce soit lié au database mail, cette requête ferait un waitfor de longue durée donc bien que longue, la durée d'exec est à ignorer. Il vaut mieux regarder le nb d'i/o ou la conso cpu.
Sans regarder la sortie de ta requête, est-ce que ta machine est très chargée lorsque ce code s'exécute ?

[SQLpro]

pour tes deux requêtes regarde si c'est pas compilation / execution...

A +