Select qui n'utilise pas un index

Version imprimable

30/07/2015, 15h03
bonbledfb

Select qui n'utilise pas un index
Bonjour,

j'ai une table avec X record par date extraction, avec un index sur la zone de date d'extraction.
Code:

1 2 3 SELECT [fk_extract_date], count( pk_broadcast ) FROM [RMBDW_DEV].[dbo].[f_broadcast] group by [fk_extract_date]
```
fk_extract_date    (No column name)
20140131    233480
20140801    1091005
20141231    1809616
20150109    183188
20150116    211225
20150130    185851
20150206    216804
20150213    238989
20150220    268964
20150227    289685
20150306    321151
20150313    346335
20150320    359229
20150327    384923
20150403    413655
20150410    438869
20150417    467610
20150424    494460
20150501    520254
20150508    645015
20150515    665559
20150522    702123
20150529    730841
20150605    764339
20150612    787066
20150619    823706
20150626    858059
20150703    890583
20150710    917796
20150713    925899
20150714    929909
20150715    934274
20150716    941102
20150717    945510
20150720    945535
20150721    945763
20150722    949220
20150723    956129
20150724    960656
20150727    967244
```
lorsque je lance un select avec certaine valeur sur fk_extract_date :

Code:

select id_break from rmbdw_dev.dbo.f_broadcast where fk_extract_date = 20150206

--> il utilise bien l'index , et la réponse est immédiate.

et le même select sur d'autre valeur de fk_extract_date, l'index n'est pas utilisé , et l'explain plan donne un scan complet la table.

Code:

select id_break from rmbdw_dev.dbo.f_broadcast where fk_extract_date = 20150720

si je force l'utilisation de l'index, cela fonctionne de nouveau très bien.

Code:

select id_break from rmbdw_dev.dbo.f_broadcast with( INDEX (IDX_f_broadcast_extract)) where fk_extract_date = 20150720

Quelqu'un aurai-il une idée sur la raison de la non utilisation automatique de l'index pour chaque valeur de fk_extract_date ?

Merci.

Franck.
30/07/2015, 15h49
aieeeuuuuu

Bonjour,

1/ est-ce que les statistiques sont a jour ?

2/ peut-on voir le CREATE de l'index

Citation:

Envoyé par aieeeuuuuu

Bonjour,

1/ est-ce que les statistiques sont a jour ?

2/ peut-on voir le CREATE de l'index

1/ le problème continue après recalcul des statistiques sur cette table.

2/
Code:

1 2 3 4 5 CREATE NONCLUSTERED INDEX [IDX_f_broadcast_extract] ON [dbo].[f_broadcast] ( [fk_extract_date] 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) GO
merci pour vos conseils.

30/07/2015, 16h19
mikedavem

Attendons effectivement les informations demandés par aieeeuuuuu mais de loin il semble que la distribution de tes données dans ton cas fait que SQL Server choisisse ou non une recherche d'index.

En regardant rapidement le nombre d'enregistrements par date, on voit déjà que pour la date 20150720 SQL Server devra ramener 945535 lignes alors que pour la date 20150206 seulement 216804 ce qui représente 77% de données en moins. A voir également si tes statistiques reflètent la réalité. L'optimiseur peut se tromper si les statistiques ne sont pas à jour.

Tu peux nous fournir le plan d'exécution réel dans les 2 cas stp . Tu n'aurais pas un key / RID lookup dans tes plans d'exécution par hasard?

++
30/07/2015, 16h20
SQLpro

Créez l'index suivant :

Code:

CREATE INDEX ??? ON [RMBDW_DEV].[dbo].[f_broadcast] ([fk_extract_date]) INCLUDE (pk_broadcast);

A +
30/07/2015, 16h32
bonbledfb

2 pièce(s) jointe(s)

Citation:

Envoyé par mikedavem

Attendons effectivement les informations demandés par aieeeuuuuu mais de loin il semble que la distribution de tes données dans ton cas fait que SQL Server choisisse ou non une recherche d'index.

En regardant rapidement le nombre d'enregistrements par date, on voit déjà que pour la date 20150720 SQL Server devra ramener 945535 lignes alors que pour la date 20150206 seulement 216804 ce qui représente 77% de données en moins. A voir également si tes statistiques reflètent la réalité. L'optimiseur peut se tromper si les statistiques ne sont pas à jour.

Tu peux nous fournir le plan d'exécution réel dans les 2 cas stp . Tu n'aurais pas un key / RID lookup dans tes plans d'exécution par hasard?

++

Explain plan avec la sélection automatique de l'index.

Pièce jointe 184327

Explain plan sans la sélection de l'index

Pièce jointe 184328
30/07/2015, 16h35
bonbledfb

Citation:

Envoyé par SQLpro

Créez l'index suivant :

Code:

CREATE INDEX ??? ON [RMBDW_DEV].[dbo].[f_broadcast] ([fk_extract_date]) INCLUDE (pk_broadcast);

A +

Même résultat après création de l'index avec l'INCLUDE pk_broadcast
30/07/2015, 16h46
aieeeuuuuu

C'est plutôt la colonne id_break qu'il faudrait inclure, car c'est sur elle que porte le SELECT.

l'index deviendra couvrant, et devrait donc être utilisé systématiquement.
30/07/2015, 16h52
bonbledfb

Citation:

Envoyé par aieeeuuuuu

C'est plutôt la colonne id_break qu'il faudrait inclure, car c'est sur elle que porte le SELECT.

l'index deviendra couvrant, et devrait donc être utilisé systématiquement.

Oui, mais j'ai simplifié le select pour expliquer le problème. A l'origine, je dois retourner d'autres zones de la table , et dans certain select, faire aussi des agrégations
30/07/2015, 17h08
mikedavem

Citation:

Oui, mais j'ai simplifié le select pour expliquer le problème. A l'origine, je dois retourner d'autres zones de la table , et dans certain select, faire aussi des agrégations

Tu as le principe mais dans ce cas il nous faut ta requête et la description de tes index + table si tu veux que l'on t'aide

++
30/07/2015, 17h18
aieeeuuuuu

Notez également que, d'après vos capture d’écran, SQL Serveur semble vous indiquer l'index qu'il vous faudrait.

L'idée est donc d'inclure les colonne nécessaire à la requete, afin que le moteur n'ait plus besoin d'aller voir dans la table.
Si ces colonne sont nombreuses (et en fonction de leur type/contenu) cela peut alourdir l'index et donc les mises à jour de la table.

Le choix dépendra donc du contexte. Cette requête va-t-elle est exécutée fréquemment ? y a-t-il beaucoup de mises à jour sur cette table ? ...

il faudrait en effet en savoir plus.

Les autres requêtes exécutées sur cette table aussi ! il pourrait par exemple être intéressant de passer l'index sur la date en cluster, si vous faites beaucoup de restrictions sur cette colonne... Mais cela pourra aussi avoir des conséquences néfastes sur d'autres requete. C'est donc bien toute la difficulté pour trouver les index optimum : il faut avoir une vue d'ensemble.

Merci pour vos conseils.

Voici donc la structure de la table :

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
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
USE [RMBDW]
GO
 
/****** Object:  Table [dbo].[f_broadcast]    Script Date: 31/07/2015 08:37:50 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[f_broadcast](
	[pk_broadcast] [int] IDENTITY(1,1) NOT NULL,
	[fk_extract_date] [int] NULL,
	[fk_broadcast_date] [int] NULL,
	[fk_resa_date] [int] NULL,
	[fk_sign_date] [int] NULL,
	[fk_annul_date] [int] NULL,
	[fk_valid_date] [int] NULL,
	[fk_cancel_date] [int] NULL,
	[fk_salesmgr] [int] NULL,
	[fk_planning_exec] [int] NULL,
	[fk_media_ag] [int] NULL,
	[fk_pub_ag] [int] NULL,
	[fk_client] [int] NULL,
	[fk_selling_company] [int] NULL,
	[fk_camp_broadcast] [int] NULL,
	[fk_camp_invoice] [int] NULL,
	[fk_contract] [int] NULL,
	[fk_prodadv] [int] NULL,
	[fk_program_before] [int] NULL,
	[fk_program_after] [int] NULL,
	[fk_break] [int] NULL,
	[fk_prodrmb] [int] NULL,
	[fk_prodrmbcomp] [int] NULL,
	[fk_medium] [int] NULL,
	[fk_selling_depart] [int] NULL,
	[fk_status_broadcast] [int] NULL,
	[fk_resatype] [int] NULL,
	[fk_message] [int] NULL,
	[fk_regr_dur] [int] NULL,
	[fk_stat_struct] [int] NULL,
	[extract_date] [date] NULL,
	[id_spot_broadcast] [int] NULL,
	[id_spot_broadcast2] [int] NULL,
	[broadcast_date] [date] NULL,
	[resa_date] [date] NULL,
	[sign_date] [date] NULL,
	[annul_date] [date] NULL,
	[valid_date] [date] NULL,
	[cancel_date] [date] NULL,
	[id_salesmgr] [int] NULL,
	[id_planning_exec] [int] NULL,
	[id_media_ag] [int] NULL,
	[id_pub_ag] [int] NULL,
	[id_client] [int] NULL,
	[code_selling_company] [nvarchar](12) NULL,
	[id_camp_broadcast] [int] NULL,
	[id_camp_invoice] [int] NULL,
	[id_contract] [int] NULL,
	[id_prodadv] [int] NULL,
	[id_program_before] [int] NULL,
	[id_program_after] [int] NULL,
	[id_break] [int] NULL,
	[id_prodrmb] [int] NULL,
	[id_prodrmbcomp] [int] NULL,
	[id_medium] [int] NULL,
	[id_depart_selling] [int] NULL,
	[id_status_broadcast] [int] NULL,
	[id_resatype] [int] NULL,
	[id_message] [int] NULL,
	[id_lnk_spot_broadcast] [int] NULL,
	[beginspot_real] [nvarchar](12) NULL,
	[fk_break_type_real_broadcast] [int] NULL,
	[id_layer_broadcast] [int] NULL,
	[name_layer_broadcast] [nvarchar](160) NULL,
	[id_tarif_type_broadcast] [int] NULL,
	[name_tarif_type_broadcast] [nvarchar](160) NULL,
	[id_duration_type_broadcast] [int] NULL,
	[name_duration_type_broadcast] [nvarchar](160) NULL,
	[id_position_asked] [int] NULL,
	[code_position_asked] [nvarchar](12) NULL,
	[name_position_asked] [nvarchar](30) NULL,
	[id_position_obtained] [int] NULL,
	[code_position_obtained] [nvarchar](12) NULL,
	[name_position_obtained] [nvarchar](30) NULL,
	[position_in_break] [int] NULL,
	[nbr_spot_int_break] [int] NULL,
	[best_position] [int] NULL,
	[base_turnover] [int] NULL,
	[jingle_pub] [int] NULL,
	[convention] [int] NULL,
	[comblage] [int] NULL,
	[price_nat_break] [numeric](16, 8) NULL,
	[price_tarif_break] [numeric](16, 8) NULL,
	[gross_pige_broadcast] [numeric](16, 8) NULL,
	[gross_nat_spot_broadcast] [numeric](16, 8) NULL,
	[gross_tarif_spot_broadcast] [numeric](16, 8) NULL,
	[gross_comm_nat_broadcast] [numeric](16, 8) NULL,
	[gross_comm_tarif_broadcast] [numeric](16, 8) NULL,
	[free_broadcast] [numeric](16, 8) NULL,
	[in_de_crease_broadcast] [numeric](16, 8) NULL,
	[net_comm_broadcast] [numeric](16, 8) NULL,
	[net_comm_prov_broadcast] [numeric](16, 8) NULL,
	[prov_broadcast] [numeric](16, 8) NULL,
	[net_comm_nc_broadcast] [numeric](16, 8) NULL,
	[duration_broadcast] [int] NULL,
	[nr_days_resa_broadcast] [int] NULL,
	[nr_days_annul_broadcast] [int] NULL,
	[nr_days_annul_broadcast_resa] [int] NULL,
	[at_source] [nvarchar](30) NULL,
	[at_etl_v] [nvarchar](30) NULL,
	[at_upddate] [datetime] NULL,
 CONSTRAINT [PK_f_broadcast] PRIMARY KEY CLUSTERED 
(
	[pk_broadcast] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
GO

et index

Code:

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE [dbo].[f_broadcast] ADD  CONSTRAINT [PK_f_broadcast] PRIMARY KEY CLUSTERED 
(
	[pk_broadcast] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
 
CREATE NONCLUSTERED INDEX [IDX_f_broadcast_extract] ON [dbo].[f_broadcast]
(
	[fk_extract_date] 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)
GO

Cette table reçoit chaque nuit l'activité de notre société en détail, actuellement +/- 900.000 record par jour. On retient les 15 derniers jours, et le vendredi des semaines antérieurs (effacement le dimanche des lundi-jeudi de la semaine -3). Cette table de fait est exploité par les commerciaux avec Bussines Object pour générer des rapports de comparaison de jour en jour suivant différents critères. Donc les select d'agrégations peuvent être généré sur pratiquement toute les zones de la table.

Mais l’une des constantes, c’est que l’on compare les chiffres d’un jour par rapport à un autre.

Avec ce select,

Code:

1
2
3
4
5
6
7
8
9
10
select id_camp_broadcast,
       id_medium, 
	   EOMONTH(broadcast_date), 
	   sum(gross_comm_tarif_broadcast) gross_comm_tarif_broadcast, 
	   sum(net_comm_broadcast) net_comm_broadcast 
from f_broadcast
where fk_extract_date = X and id_status_broadcast < 900
group by id_camp_broadcast,
       id_medium, 
	   EOMONTH(broadcast_date)

c'est lorsque l'on passe du 20150424 (494.460 reccord) au 20150501 (520.254 reccord) sur une table de 27.770.000 record que le problème survient.

En plus le problème est survenu depuis hier. Mercredi, l'index était automatiquement selectionné pour toutes les dates.

31/07/2015, 10h27
aieeeuuuuu

a priori, l'index cluster sur la clef primaire de cette table n'est donc pas forcément pertinent, mais ça resterait à vérifier par rapport à l'ensemble des requêtes exécutées.

Un index cluster sur la colonne fk_extract_date pourrait être intéressant (plus d'autres colonnes éventuellement,par exemple (fk_extract_date , id_status_broadcast) ).

La encore, pour les autres colonnes, c'est à vous de les définir, car il faudrait connaitre, en plus de toute les requêtes, la répartition des données voire la sémantique de chaque colonne.

Par exemple, id_status_broadcast n'est pas forcément le meilleur choix en deuxième colonne de l'index, si la condition id_status_broadcast < 900 n'est pas suffisament discriminante. Il pourrait alors être préférable de couvrir le GROUP BY.

Lancez le script suivant et comparez la différence :

1) ajout d'une colonne persistante pour la colonne de calcul de fin de mois
Code:

1 2 ALTER TABLE f_broadcast ADD end_of_month AS EOMONTH(broadcast_date) PERSISTED;
2) création d'un inex couvrant pour la requête
Code:

1 2 CREATE INDEX XXX ON f_broadcast (fk_extract_date, id_status_broadcast, id_camp_broadcast, id_medium, end_of_month) INCLUDE (gross_comm_tarif_broadcast, net_comm_broadcast);
3) récriture de la requête tenant compte de la colonne calculée et de l'index
Code:

1 2 3 4 5 6 7 8 9 10 select id_camp_broadcast, id_medium, end_of_month, sum(gross_comm_tarif_broadcast) gross_comm_tarif_broadcast, sum(net_comm_broadcast) net_comm_broadcast from f_broadcast where fk_extract_date = X and id_status_broadcast < 900 group by id_camp_broadcast, id_medium, end_of_month;
Normalement vous devriez diviser par 100 à 1000 les temps de réponse !

A +

31/07/2015, 17h44
aieeeuuuuu

normalement, il n'y a même pas besoin de réécrire la requete : le moteur saura utiliser l'index sur end_of_month même si la requête contient EOMONTH(broadcast_date)
01/08/2015, 11h34
elsuket

Bonjour,

Peut-être que l'on peut même ne pas persister la colonne calculée, mais seulement l'indexer, ce qui conservera un peu d'espace disque.
A tester pour voir la différence au niveau des plans de requête + sortie SET STATISTICS TIME, IO ON.

@++ ;)
05/08/2015, 17h43
SQLpro

Citation:

Envoyé par bonbledfb

Oui, mais j'ai simplifié le select pour expliquer le problème. A l'origine, je dois retourner d'autres zones de la table , et dans certain select, faire aussi des agrégations

Si vous ne nous dites pas tout, comment voulez vous que l'on vous aide !!!!!!

IL FAUT TOUT NOUS DONNER !!!!
C'est quand même pas compliqué de faire un copier coller ?

Vous nous faites perdre du temps ce qui est stupide !

A +