Discussion :

[mathieu_BO]

Hello,
j'aimerais revoir les indexes sur une BDD, je vous présente un cas que je trouve étrange.

N tables
Table_A
Table_B
Table_C
.. Table_P

1 vue couvrant toutes ces tables (union)

1 contrainte de valeur sur chaque table : Table_A.Col = "A", Table_B.Col = "B" etc.
Les requête SELECT passent par la vue.
Il y'a pleins d'indexes basées cette colonne, ca sert à quelque chose ? Je serais d'avis de retirer cette colonne de tous les indexes, vous en pensez quoi ?

Merci pour votre aide

Cheers

[Blacknounou]

Bonjour,

Regardez quels sont les index non lus, ça vous aidera à dégrossir un peu .

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
 
USE MaBase
GO
SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
 
GO

@ ++

[mathieu_BO]

En fait on peut avoir pleins d'indexes très utilisés basé sur cette colonne et d'autre (3 à 5 autres colonnes).
A partir du moment ou on a une contrainte de type egalité pour moi ca semble inutile de la mettre dans l'indexe car c'est tout le temps la même valeur. Le moteur doit le savoir non ?

Je compte pas gagner en performance de rapidité des SELECT mais en réduisant la taille des indexes et donc leurs mise à jour.

[escartefigue]

Si les tables sont différentes au niveau DDL et que la colonne n'a qu'une valeur unique dans chaque table par construction, alors il est inutile et néfaste de créer un index sur cette colonne !

Si par contre les tables ont toutes le même DDL (même description), alors il eut été plus judicieux de faire une seule table, avec un partitionnement sur cette colonne, et là l'index a un sens.

[escartefigue]

A partir du moment ou on a une contrainte de type egalité pour moi ca semble inutile de la mettre dans l'indexe car c'est tout le temps la même valeur. Le moteur doit le savoir non ?

L'optimiseur le sait, mais s'il n'y a pas d'autre index avec les mêmes colonnes sauf celle inutile, alors cet index est requis
Ca alourdit donc inutilement la base de données et les requetes

[mathieu_BO]

Superb
merci pour l'info c'est claire

[SQLpro]

Bonjour,

Regardez quels sont les index non lus, ça vous aidera à dégrossir un peu .

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
 
USE MaBase
GO
SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
 
GO

@ ++

Votre requête est totalement foireuse !

1) il n'y a pas le schema des objets

2) vous additionnez des chous des carottes et des navets pour mesurer on ne sait quoi ! En effet un scan peut consommer 10000 pages, tandis qu'un SEEK, va en consommer 3 sur le même objet... tandis qu'un LOOKUP va en faire 50 !!!

3) les métriques sont cumulative depuis :
1.1) le dernier redémarrage du serveur
1.2) la création de l'index si celle-ci a eu lieu après le dernier redémarrage

4) vous oubliez les vues indexées

5) les performances des fonctions OBJECT_NAME et OBJECTPROPERTY sont catastrophiques alors que vous avez ces informations dans les vues systèmes...

Il faut donc éliminer de ces métriques les index créés récemment et ne pas tenir compte des résultats, si le serveur a été redémarré depuis moins d'un mois !

Une requête plus intelligente est la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, i.name AS INDEX_NAME,
       st.*
FROM   sys.dm_db_index_usage_stats AS st
       JOIN sys.indexes AS i ON st.object_id = i.object_id AND st.index_id = i.index_id
	   JOIN sys.objects AS o ON st.object_id = o.object_id
	   JOIN sys.schemas AS s ON  o.schema_id = s.schema_id
WHERE  database_id = DB_ID()
  AND  o."type" IN ('U', 'V')
ORDER  BY user_seeks, user_lookups, user_scans, user_updates DESC;

A +

[Blacknounou]

Merci pour ces corrections SQLpro.

J'en prends note.

@ ++

[elsuket]

Bonjour,

Lorsque vous faites un SELECT colonnes FROM laVue WHERE Col = 'D', est-ce que le plan montre que seule la table qui répond a cette caractéristique est accédée ?
Normalement la contrainte CHECK sur chaque table évince celles qui ne répondent pas au prédicat du filtre.

Cela est distinct des index qui sont posés sur la table, et auxquels plusieurs colonnes peuvent participer.
Cela étant, intuitivement il ne paraît pas utilie que la colonne Col participe à la clé de l'index, pour la raison évoquée ci-dessus.
Le plan de requête vous indique si c'est le cas.

@++