[MSSQL] Problème de deadlock

**elsuket** · 24/06/2011, 11h38

ElSuket, j'ai essayé votre code et malheureusement cela ne fonctionne pas.

Héhé d'accord, mais quel est le problème ?
Est-ce que vos valeurs de chaîne se terminent bien par ...

AM'.
AUTO'.
MANU'.
HS'.
S'.

... c'est à dire apostrophe et point compris ?

En outre :

Code :

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

OR    (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558)

C'est pas beau, parce que SQL Server ne maintient pas de statistiques sur DATEDIFF(dd, TimeStmp, GETDATE()), mais sur TimeStmp.

En écrivant votre requête de la sorte, vous ne permettez pas à SQL Server d'estimer combien de lignes vérifient le prédicat.

En revanche si vous écrivez :

Code :

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

OR	TimeStmp > DATEADD(year, -7, GETDATE())

Là c'est mieux.

Le problème demeure encore puisque :

- Vous êtes dans un trigger
- le prédicat est à base de OR

@++

**pitipilot** · 27/06/2011, 08h00

Héhé d'accord, mais quel est le problème ?
Est-ce que vos valeurs de chaîne se terminent bien par ...

AM'.
AUTO'.
MANU'.
HS'.
S'.

... c'est à dire apostrophe et point compris ?

Mes chaînes se terminent bien par ces caractères.

En outre :
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

OR (DATEDIFF(dd, TimeStmp, GETDATE()) > 2558)

C'est pas beau, parce que SQL Server ne maintient pas de statistiques sur DATEDIFF(dd, TimeStmp, GETDATE()), mais sur TimeStmp.

En écrivant votre requête de la sorte, vous ne permettez pas à SQL Server d'estimer combien de lignes vérifient le prédicat.

En revanche si vous écrivez :
Code :
Sélectionner tout - Visualiser dans une fenêtre à part

OR TimeStmp > DATEADD(year, -7, GETDATE())

Là c'est mieux.

D'acord mais ai-je le même problème que les DATEDIFF pour travailler sur les années ? Je m'explique :

un DATEDIFF en années entre 31/12/2011 et 01/01/2004 renverrait 7 ans alors qu'en vérité cela fait 6 ans et 1 jours.

Sinon j'ai pensé à fonctionner autrement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Create TRIGGER Filtre_Donnes_entrantes
 
ON Action_Ope
 
INSTEAD of INSERT
 
as
 
IF  NOT (messagetext like '%AM''.'or messagetext like '%AUTO''.')
INSERT INTO Audit_Trail (TimeStmp, MessageText, Location, UserID, UserFullName)
SELECT TimeStmp, MessageText, Location, UserID, UserFullName
FROM Action_Ope
GO

Enfaite les données sont insérées dans une table temporaire qui elle renvoi ces données dans une table principale donc je pense que ça peut grandement limiter les deadlocks?

Cependant j'ai ce message d'erreur sur la ligne du IF NOT :
Msg 207, Level 16, State 1, Procedure Filtre_Donnes_entrantes, Line 9
Nom de colonne non valide*: 'messagetext'.
Msg 207, Level 16, State 1, Procedure Filtre_Donnes_entrantes, Line 9
Nom de colonne non valide*: 'messagetext'.

alors que ma colonne est valide je ne comprends pas.

Merci pour votre aide.

Bonne journée.

**elsuket** · 27/06/2011, 14h44

C'est à dire que je vais créer une table contenant mes mots clés inutiles et l'autre va vérifier si les enregistrements ne contiennent pas ceux ci ? Pouvez vous me guider dans cette démarche car cela dépasse le peu de connaissances en sql actuelles.

Oui, il vous suffit de créer une table de mots "noirs", que j'appelle mot_noir dans l'exemple qui suit :

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
CREATE TRIGGER TR_IOF_I_Action_OPE
	ON Action_Ope
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Action_OPE
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		DISTINCT RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	CROSS JOIN	dbo.mot_noir AS MN
	WHERE		I.messagetext NOT LIKE '%' + MN.mot_noir + '%' 
END

Ou encore :

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
CREATE TRIGGER TR_IOF_I_Action_OPE
	ON Action_Ope
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Action_OPE
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	CROSS APPLY	(
				SELECT	CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag
				FROM	(
						SELECT	CASE WHEN I.MessageText LIKE '%' + M.libelle + '%' THEN 1 ELSE 0 END AS exclude_flag
						FROM	dbo.mot_noir AS M
					) AS S
			) AS E
	WHERE		E.exclude = 0
END

Qui me semble mieux.

Mes chaînes se terminent bien par ces caractères.

D'accord, mais cela ne nous dit pas le problème que vous rencontrez.
Donnez des chaînes exemples, et le message d'erreur ou le problème auquel vous êtes exposé.

un DATEDIFF en années entre 31/12/2011 et 01/01/2004 renverrait 7 ans alors qu'en vérité cela fait 6 ans et 1 jours.

Non, c'est bien le cas : entre le 1er Janvier 2004 et le 31 décembre 2011 il y a 7 ans ... et 364 jours.
notez que les valeurs de type datetime sont à minuit

D'autre part rien ne vous empêche d'écrire :

Code :

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

DATEADD(day, -2558, GETDATE())

Sinon j'ai pensé à fonctionner autrement :

L'erreur qui suit s'explique tout à fait, puisque SQL Server ne sait pas de quelle table provient la colonne MessageText : vous ne l'indiquez pas.
Cela vient du fait que vous avez considéré que SQL Server traite les lignes une par une, ce qui ne doit jamais être le cas dans votre code, et qui n'est pas le cas du trigger que je vous ai proposé.

@++

**pitipilot** · 27/06/2011, 15h55

Après plusieurs tests, ça ne marche pas. Je vais décrire ma procédure pour voir si je fais quelque chose de mal :

I - Je crée une table "mot_noir" contenant la colonne "mot". J'ajoute des enregistrements : Set, Display,..

II - Je crée une table "Table_RECP" qui va recevoir l'enregistrement de la supervision.

III - Je créer un trigger associeé à "Table_RECP"

Donc là vos deux solutions la première :

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
 
CREATE TRIGGER TR_IOF_I_Table_RECP
	ON Table_RECP
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Table_RECP
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		DISTINCT RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	CROSS JOIN	dbo.mot_noir AS MN
	WHERE		I.messagetext NOT LIKE '%' + MN.mot + '%' 
END

La supervision envoie les enregistrements et ceux contenants Set, Display,... sont toujours présents.

La deuxiè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
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
 
CREATE TRIGGER TR_IOF_I_Table_RECP
	ON Table_RECP
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Table_RECP
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	CROSS APPLY	(
				SELECT	CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag
				FROM	(
						SELECT	CASE WHEN I.MessageText LIKE '%' + M.mot + '%' THEN 1 ELSE 0 END AS exclude_flag
						FROM	dbo.mot_noir AS M
					) AS S
			) AS E
	WHERE		E.exclude = 0
END

J'ai comme message d'erreur :"Exclude n'est pas un nom de colonne valide"

En tout cas merci beaucoup de votre patience car sans vous je serais dans une sacré galère.

**elsuket** · 27/06/2011, 17h20

En tout cas merci beaucoup de votre patience car sans vous je serais dans une sacré galère.

Aucun problème, on va tordre le coup à ce problème

C'est vraiment bizarre parce que j'ai testé des deux triggers ...
Comme j'ai modifié les libellés de colonne avant de publier :

Code :

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

WHERE		E.exclude = 0

Il faut le changer en :

Code :

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

WHERE		E.exclude_flag = 0

Je vais refaire un test pour le reste.

@++

**aieeeuuuuu** · 27/06/2011, 17h28

Bonjour,

Pour la première solution, j'aurai plutôt vu :

Code SQL :

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 TRIGGER TR_IOF_I_Table_RECP
	ON Table_RECP
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.Table_RECP
	(
		RecordID
		, ParentID
		, SequenceNumber
		, Chaintime
		, TimeStmp
		, MessageText
		, Audience
		, Severity
		, Verbosity
		, Location
		, Provider
		, UserID
		, UserFullName
	)
	SELECT		DISTINCT RecordID
			, ParentID
			, SequenceNumber
			, Chaintime
			, TimeStmp
			, MessageText
			, Audience
			, Severity
			, Verbosity
			, Location
			, Provider
			, UserID
			, UserFullName
	FROM		inserted AS I
	WHERE NOT EXISTS (
		SELECT *
		FROM dbo.mot_noir AS MN
		WHERE		I.messagetext LIKE '%' + MN.mot + '%' 
	)

**elsuket** · 27/06/2011, 17h46

Bon, voici mon test, réalisé sous SQL Server 2008 :

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
SET NOCOUNT ON
GO
 
CREATE TABLE mot_noir
(
	id_mot_noir int IDENTITY NOT NULL CONSTRAINT XPKtest_mot_noir PRIMARY KEY
	, mot_noir varchar(32) NOT NULL CONSTRAINT UQtest_mot_noir UNIQUE
)
GO
 
INSERT	INTO dbo.mot_noir (mot_noir)
VALUES	('server'), ('computer'), ('machine')
GO
 
CREATE TABLE resultat
(
	id_resultat int IDENTITY NOT NULL CONSTRAINT XPKresultat PRIMARY KEY
	, sentence varchar(100) NOT NULL
)
GO
 
CREATE TRIGGER TR_IOF_I_resultat
	ON dbo.resultat
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT		INTO dbo.resultat (sentence)
	SELECT		I.sentence
	FROM		inserted AS I
	CROSS APPLY	(
				SELECT	CASE WHEN SUM(exclude_flag) > 0 THEN 1 ELSE 0 END AS exclude_flag
				FROM	(
						SELECT	CASE WHEN I.sentence LIKE '%' + M.mot_noir + '%' THEN 1 ELSE 0 END AS exclude_flag
						FROM	dbo.mot_noir AS M
					) AS S
			) AS E
	WHERE		E.exclude_flag = 0
END
GO
 
INSERT	dbo.resultat (sentence) VALUES ('The server is burning !')
INSERT	dbo.resultat (sentence) VALUES ('le serveur prend feu !')
INSERT	dbo.resultat (sentence) VALUES ('This computer will soon pass through the window !')
INSERT	dbo.resultat (sentence) VALUES ('Cet ordinateur ne va pas tarder à passer à travers la fenêtre !')
 
SELECT	*
FROM	dbo.resultat

Affiche :

Code :

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

1
2
3
id_resultat	sentence
1	le serveur prend feu !
2	Cet ordinateur ne va pas tarder à passer à travers la fenêtre !

Donc pour moi c'est bon. Désolé pour l'erreur sur le libellé de colonne.

Le premier trigger ne fait pas ce que vous recherchez car il filtre les phrases qui ne contiennent pas le mot noir, mais garde les autres ... Désolé encore pour cette bourde.

En conclusion, vous pouvez corriger le second trigger avec la correction que je vous ai donné dans le post n°25.

@++

**pitipilot** · 28/06/2011, 08h38

C'est bon ça marche parfaitement

. C'est vraiment très très efficace.

Sinon pour supprimer les enregistrements de plus de 7 ans comment je peux faire pour effectuer ça en fin de journée par exemple ?

En tout cas merci énormément car là finis les foutus deadlocks

**WOLO Laurent** · 28/06/2011, 09h39

Est ce que ca ne serait pas plus interressant d'ouvrir un nouveau topic, c'est plus profitable. N'est ce pas ?
Sinon, il est recommandé de faire une tâche planifiée via SQL Agent.

**mikedavem** · 28/06/2011, 09h54

En sus de ce qu'a dit WOLO Laurent, prévoyez une maintenance de vos index et statistiques après cette suppression massive.

++

[MSSQL] Problème de deadlock

MS SQL Server

Discussions similaires

Partager

Partager