Full scan d'index : à éviter absolument ?

Version imprimable

Bonjour,

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

Code:

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:

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:

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

Citation:
Envoyé par Batou69
Bonjour,

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

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...
Citation:
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:

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...
Citation:
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:

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:

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:

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 +

Tout d'abord merci beaucoup pour votre aide.

Je ne comprend pas le sens de votre remarque :

Citation:

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

Citation:

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)

Citation:

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 :
Citation:
Envoyé par SQLpro
Récrivez votre requête comme ceci :
Code:

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...

Citation:

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 !!

18/08/2015, 16h45
SQLpro

Citation:

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 +

Citation:

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:

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

Code:

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:

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 +

20/08/2015, 16h12
Batou69

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.
20/08/2015, 16h31
taibag

Bonjour,

Je vous propose de lire cet article : https://www.simple-talk.com/sql/perf...scan-problems/
20/08/2015, 17h16
Batou69

Citation:

Envoyé par taibag

Bonjour,

Je vous propose de lire cet article : https://www.simple-talk.com/sql/perf...scan-problems/

Merci ! l'article est en effet un bon rappel de ce qui se passe et comment diagnostiquer.