Full scan d'index : à éviter absolument ?

**Batou69** · 17/08/2015, 14h10

Bonjour,

Nous avons actuellement sur notre serveur de production deux tables définies comme cela:

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 TABLE [PLAN_TRI](
	[NO_PLAN_TRI] [INT] IDENTITY(1,1) NOT NULL,
	[DATE_CREATION] [DATETIME] NOT NULL,
	[CODE_PLAN_TRI] [VARCHAR](50) NOT NULL,
	[LIBELLE] [VARCHAR](50) NULL,
 CONSTRAINT [PK_PLAN_TRI_NO_PLAN_TRI] PRIMARY KEY CLUSTERED 
(
	[NO_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE TABLE [EVENEMENT_PLAN_TRI](
	[NO_EVENEMENT_PLAN_TRI] [INT] IDENTITY(1,1) NOT NULL,
	[NO_PLAN_TRI] [INT] NOT NULL,
	[ETAT_PLAN_TRI] [TINYINT] NOT NULL,
	[DATE_EVENEMENT] [DATETIME] NOT NULL,
	[NO_UTILISATEUR] [INT] NULL,
 CONSTRAINT [PK_EVENEMENT_PLAN_TRI_NO_EVENEMENT_PLAN_TRI] PRIMARY KEY CLUSTERED 
(
	[NO_EVENEMENT_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [EVENEMENT_PLAN_TRI]  WITH CHECK ADD  CONSTRAINT [FK_EVENEMENT_PLAN_TRI_ETAT_PLAN_TRI] FOREIGN KEY([ETAT_PLAN_TRI])
REFERENCES [ETAT_PLAN_TRI] ([ETAT_PLAN_TRI])
GO
 
ALTER TABLE [EVENEMENT_PLAN_TRI] CHECK CONSTRAINT [FK_EVENEMENT_PLAN_TRI_ETAT_PLAN_TRI]
GO
 
ALTER TABLE [EVENEMENT_PLAN_TRI]  WITH CHECK ADD  CONSTRAINT [FK_EVENEMENT_PLAN_TRI_NO_PLAN_TRI] FOREIGN KEY([NO_PLAN_TRI])
REFERENCES [PLAN_TRI] ([NO_PLAN_TRI])
GO
 
ALTER TABLE [EVENEMENT_PLAN_TRI] CHECK CONSTRAINT [FK_EVENEMENT_PLAN_TRI_NO_PLAN_TRI]
GO

L'idée est d'avoir une table contenant l'ensemble des plans de tri et la seconde contient les evènements historisés sur chacun d'entre eux. On n'utilise plus de champ 'etat' directement dans la table.

Les indexes posé sur cette table sont :

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
 
 
CREATE NONCLUSTERED INDEX [IX_EVENEMENT_PLAN_TRI_ETAT_PLAN_TRI] ON [EVENEMENT_PLAN_TRI]
(
	[ETAT_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_EVENEMENT_PLAN_TRI_NO_PLAN_TRI] ON [EVENEMENT_PLAN_TRI]
(
	[NO_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_DATE_EVENEMENT] ON [EVENEMENT_PLAN_TRI]
(
	[NO_PLAN_TRI] ASC
	[DATE_EVENEMENT]ASC
)
INCLUDE (ETAT_PLAN_TRI)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Le problème c'est que les applications utilisant la base on besoin de connaitre très souvent le type du dernier evenement survenu sur un plan de tri.
Pour cela on effectue une requete du genre (codée dans une vue pour utiliser toujours la même dans les applications):

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
		WITH
		CTE_EPT AS
		( SELECT
			EPT.NO_PLAN_TRI
			, MAX( EPT.DATE_EVENEMENT ) MAX_DATE_EVENEMENT
		FROM
			EVENEMENT_PLAN_TRI EPT
		INNER JOIN
			PLAN_TRI PTR
		ON
			PTR.NO_PLAN_TRI = EPT.NO_PLAN_TRI
		GROUP BY
			EPT.NO_PLAN_TRI
		)
		, CTE_PTR AS
		( SELECT
			EPT.NO_PLAN_TRI
		FROM
			CTE_EPT
		INNER JOIN
			EVENEMENT_PLAN_TRI EPT
		ON
			CTE_EPT.NO_PLAN_TRI = EPT.NO_PLAN_TRI
			AND CTE_EPT.MAX_DATE_EVENEMENT = EPT.DATE_EVENEMENT
			AND EPT.ETAT_PLAN_TRI = 2 -- Etat en cours
		)
	SELECT
		PTR.NO_PLAN_TRI
		, PTR.DATE_CREATION
		, PTR.CODE_PLAN_TRI
		, PTR.LIBELLE
	FROM
		PLAN_TRI PTR
	INNER JOIN
		CTE_PTR
	ON
		CTE_PTR.NO_PLAN_TRI = PTR.NO_PLAN_TRI

Et le problème c'est que nous n'arrivons pas à supprimer le full scan lors de cette requete.
L'index utilisé pour cela est bien celui sur la date mais il y a invariablement un scan...

J'ai également essayé de supprimer le MAX et d'utiliser un row_number, mais le résultat est le même, voire moins performant...

peut on l'éviter ou bien est ce impossible à cause du max ?

Merci pour votre aide

**SQLpro** · 17/08/2015, 20h09

Envoyé par Batou69

Bonjour,

Nous avons actuellement sur notre serveur de production deux tables définies comme cela:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE [PLAN_TRI](
	[NO_PLAN_TRI] [INT] IDENTITY(1,1) NOT NULL,
	[DATE_CREATION] [DATETIME] NOT NULL,
	[CODE_PLAN_TRI] [VARCHAR](50) NOT NULL,
	[LIBELLE] [VARCHAR](50) NULL,
 CONSTRAINT [PK_PLAN_TRI_NO_PLAN_TRI] PRIMARY KEY CLUSTERED 
(
	[NO_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Déjà il serait pas idiot d'avoir une contrainte d'unicité sur CODE_PLAN_TRI ! Cela vous ajoute un index sémantique...

L'idée est d'avoir une table contenant l'ensemble des plans de tri et la seconde contient les evènements historisés sur chacun d'entre eux. On n'utilise plus de champ 'etat' directement dans la table.

Les indexes posé sur cette table sont :

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
 
 
CREATE NONCLUSTERED INDEX [IX_EVENEMENT_PLAN_TRI_ETAT_PLAN_TRI] ON [EVENEMENT_PLAN_TRI]
(
	[ETAT_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_EVENEMENT_PLAN_TRI_NO_PLAN_TRI] ON [EVENEMENT_PLAN_TRI]
(
	[NO_PLAN_TRI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [IX_DATE_EVENEMENT] ON [EVENEMENT_PLAN_TRI]
(
	[NO_PLAN_TRI] ASC
	[DATE_EVENEMENT]ASC
)
INCLUDE (ETAT_PLAN_TRI)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Vous avez un index inclus dans l'autre. Supprimez l'index IX_EVENEMENT_PLAN_TRI_NO_PLAN_TRI...

Le problème c'est que les applications utilisant la base on besoin de connaitre très souvent le type du dernier evenement survenu sur un plan de tri.
Pour cela on effectue une requete du genre (codée dans une vue pour utiliser toujours la même dans les applications):

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
		WITH
		CTE_EPT AS
		( SELECT
			EPT.NO_PLAN_TRI
			, MAX( EPT.DATE_EVENEMENT ) MAX_DATE_EVENEMENT
		FROM
			EVENEMENT_PLAN_TRI EPT
		INNER JOIN
			PLAN_TRI PTR
		ON
			PTR.NO_PLAN_TRI = EPT.NO_PLAN_TRI
		GROUP BY
			EPT.NO_PLAN_TRI
		)
		, CTE_PTR AS
		( SELECT
			EPT.NO_PLAN_TRI
		FROM
			CTE_EPT
		INNER JOIN
			EVENEMENT_PLAN_TRI EPT
		ON
			CTE_EPT.NO_PLAN_TRI = EPT.NO_PLAN_TRI
			AND CTE_EPT.MAX_DATE_EVENEMENT = EPT.DATE_EVENEMENT
			AND EPT.ETAT_PLAN_TRI = 2 -- Etat en cours
		)
	SELECT
		PTR.NO_PLAN_TRI
		, PTR.DATE_CREATION
		, PTR.CODE_PLAN_TRI
		, PTR.LIBELLE
	FROM
		PLAN_TRI PTR
	INNER JOIN
		CTE_PTR
	ON
		CTE_PTR.NO_PLAN_TRI = PTR.NO_PLAN_TRI

Et le problème c'est que nous n'arrivons pas à supprimer le full scan lors de cette requete.
L'index utilisé pour cela est bien celui sur la date mais il y a invariablement un scan...

J'ai également essayé de supprimer le MAX et d'utiliser un row_number, mais le résultat est le même, voire moins performant...

peut on l'éviter ou bien est ce impossible à cause du max ?

Merci pour votre aide

Normal; la colonne ETAT_PLAN_TRI ne figure dans aucun index !

Créez cet index :

Code :

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

1
2
 
CREATE INDEX X001 ON EVENEMENT_PLAN_TRI (ETAT_PLAN_TRI, NO_PLAN_TRI, DATE_EVENEMENT);

Récrivez votre requête comme ceci :

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
WITH
CTE_EPT AS
( 
SELECT EPT.NO_PLAN_TRI, MAX(EPT.DATE_EVENEMENT) MAX_DATE_EVENEMENT
FROM   EVENEMENT_PLAN_TRI AS  EPT
       INNER JOIN PLAN_TRI AS PTR
             ON PTR.NO_PLAN_TRI = EPT.NO_PLAN_TRI
WHERE  EPT.ETAT_PLAN_TRI = 2 -- Etat en cours
GROUP  BY EPT.NO_PLAN_TRI
), 
CTE_PTR AS
(
SELECT	EPT.NO_PLAN_TRI
FROM	CTE_EPT
        INNER JOIN EVENEMENT_PLAN_TRI AS EPT
              ON CTE_EPT.NO_PLAN_TRI = EPT.NO_PLAN_TRI
	             AND CTE_EPT.MAX_DATE_EVENEMENT = EPT.DATE_EVENEMENT
)
SELECT PTR.NO_PLAN_TRI, 
       PTR.DATE_CREATION, 
	   PTR.CODE_PLAN_TRI, 
	   PTR.LIBELLE
FROM   PLAN_TRI PTR
       INNER JOIN CTE_PTR
             ON	CTE_PTR.NO_PLAN_TRI = PTR.NO_PLAN_TRI;

pas testé car vous n'avez pas respecté la charte de postage.

A +

**Batou69** · 18/08/2015, 08h55

Tout d'abord merci beaucoup pour votre aide.

Je ne comprend pas le sens de votre remarque :

Envoyé par SQLpro

Déjà il serait pas idiot d'avoir une contrainte d'unicité sur CODE_PLAN_TRI ! Cela vous ajoute un index sémantique...

Fonctionnellement, il n'est pas idiot de mettre une unicité, mais je n'ai pas de requêtes faisant directement de recherche sur CODE_PLAN_TRI... alors y a t il une raison plus générale pour ajouter un index sur ce genre de colonne ?
En général, les applications liée à cette base affichent le CODE_PLAN_TRI mais les recherchent s'effectuent sur la clef

Envoyé par SQLpro

Vous avez un index inclus dans l'autre. Supprimez l'index IX_EVENEMENT_PLAN_TRI_NO_PLAN_TRI...

Effectivement je n'avais pas réalisé que le premier est redondant avec le second.
J’hésite toutefois à le supprimer car la vue dm_db_index_usage_stats indique qu'il est utilisé (pas de scan mais environ 900 seeks pour 700 updates)
En revanche le second IX_DATE_EVENEMENT est vraiment utile mais il a 318Millions de seek pour 317Millions de scans.... (toujours pour 700 updates)

Envoyé par SQLpro

Normal; la colonne ETAT_PLAN_TRI ne figure dans aucun index !
...

L'index que vous me proposez est différent certes, mais il existe bien un index sur ETAT_PLAN_TRI (IX_EVENEMENT_PLAN_TRI_ETAT_PLAN_TRI) qui n'est peut être pas suffisant. Mais :

Envoyé par SQLpro

Récrivez votre requête comme ceci :

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
WITH
CTE_EPT AS
( 
SELECT EPT.NO_PLAN_TRI, MAX(EPT.DATE_EVENEMENT) MAX_DATE_EVENEMENT
FROM   EVENEMENT_PLAN_TRI AS  EPT
       INNER JOIN PLAN_TRI AS PTR
             ON PTR.NO_PLAN_TRI = EPT.NO_PLAN_TRI
WHERE  EPT.ETAT_PLAN_TRI = 2 -- Etat en cours
GROUP  BY EPT.NO_PLAN_TRI
), 
CTE_PTR AS
(
SELECT	EPT.NO_PLAN_TRI
FROM	CTE_EPT
        INNER JOIN EVENEMENT_PLAN_TRI AS EPT
              ON CTE_EPT.NO_PLAN_TRI = EPT.NO_PLAN_TRI
	             AND CTE_EPT.MAX_DATE_EVENEMENT = EPT.DATE_EVENEMENT
)
SELECT PTR.NO_PLAN_TRI, 
       PTR.DATE_CREATION, 
	   PTR.CODE_PLAN_TRI, 
	   PTR.LIBELLE
FROM   PLAN_TRI PTR
       INNER JOIN CTE_PTR
             ON	CTE_PTR.NO_PLAN_TRI = PTR.NO_PLAN_TRI;

Je crois que j'ai mal expliqué ce que je veux. Ici vous sélectionnez dans le premier CTE tous les derniers événements de type 2.
Mais ce 'est pas ce que je veux. Je cherche tous les derniers événement par plan de tri et ensuite je filtre uniquement les plans qui sont à l'état 2.
Plus généralement, cette table enregistre les changements d'états d'un plan de tri. Ce que je cherche c'est l'état actuel de tous les plans de tris.

En fait c'est uniquement le premier CTE de ma requete qui me pose problème. Il effectue systématiquement un scan de l'index IX_DATE_EVENEMENT et je ne sais pas comment y remédier...

Envoyé par SQLpro

pas testé car vous n'avez pas respecté la charte de postage.

Heuuu désolé. Pouvez vous m'indiquer quels points aurai-je omis ?

Merci !!

**SQLpro** · 18/08/2015, 15h45

Envoyé par Batou69

T
Heuuu désolé. Pouvez vous m'indiquer quels points aurai-je omis ?

Merci !!

les données !!!!

Comment voulez vous que l'on reproduise vos requêtes sans un minimum de données ?

A +

**Batou69** · 18/08/2015, 15h56

Envoyé par SQLpro

les données !!!!

Comment voulez vous que l'on reproduise vos requêtes sans un minimum de données ?

Oupps.. je suis désolé...

Voici de quoi remplir les tables :

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
INSERT dbo.PLAN_TRI		( DATE_CREATION, CODE_PLAN_TRI, LIBELLE )
VALUES	( GETDATE(), 'PTR1', 'Plan de tri 1' ),
		( GETDATE(), 'PTR2', 'Plan de tri 2' ),
		( GETDATE(), 'PTR3', 'Plan de tri 3' )
 
 
INSERT dbo.EVENEMENT_PLAN_TRI
		( NO_PLAN_TRI
		, ETAT_PLAN_TRI
		, DATE_EVENEMENT
		, NO_UTILISATEUR )
SELECT NO_PLAN_TRI, 0, '2015-18-08 08:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR1'
UNION ALL SELECT NO_PLAN_TRI, 2, '2015-18-08 09:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR1'
UNION ALL SELECT NO_PLAN_TRI, 6, '2015-18-08 10:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR1'
UNION ALL SELECT NO_PLAN_TRI, 0, '2015-18-08 10:30:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR2'
UNION ALL SELECT NO_PLAN_TRI, 2, '2015-18-08 11:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR2'
UNION ALL SELECT NO_PLAN_TRI, 0, '2015-18-08 12:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR3'
UNION ALL SELECT NO_PLAN_TRI, 2, '2015-18-08 13:00:00', null FROM dbo.PLAN_TRI WHERE CODE_PLAN_TRI = 'PTR3'

Et je cherche l'etat en cours de chaque plan de tri. Le résultat est :
PTR1 etat 6
PTR2 état 2
PTR3 etat 2

**SQLpro** · 20/08/2015, 09h37

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH 
T0 AS (SELECT NO_PLAN_TRI, MAX(DATE_EVENEMENT) AS LAST_DATE
       FROM   dbo.EVENEMENT_PLAN_TRI 
	   GROUP  BY NO_PLAN_TRI),
T1 AS (SELECT T.NO_PLAN_TRI, T.ETAT_PLAN_TRI 
       FROM   dbo.EVENEMENT_PLAN_TRI AS T 
	          JOIN T0 
			       ON LAST_DATE = DATE_EVENEMENT
			       AND T.NO_PLAN_TRI = T0.NO_PLAN_TRI)
SELECT T.*, T1.ETAT_PLAN_TRI 
FROM   dbo.PLAN_TRI AS T
       JOIN T1 
	        ON T1.NO_PLAN_TRI = T.NO_PLAN_TRI;

Avec l'index suivant :

Code :

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

CREATE INDEX X1 ON dbo.EVENEMENT_PLAN_TRI (NO_PLAN_TRI, DATE_EVENEMENT) INCLUDE (ETAT_PLAN_TRI);

Après le choix de l'index et de la stratégie d'accès c'est lui qui décide en fonction de la volumétrie des lignes à traiter et de la distribution des données.

A +

**Batou69** · 20/08/2015, 15h12

Yes c'est cela. C'est a peu de chose pres la même que celle que j'utilise.
L'index que vous proposez existe déjà.... mais on a toujours un scan complet.. je fini par croire que cela est parfois inévitable.