Design d'indexes & constraints Check

Version imprimable

09/07/2015, 15h12
mathieu_BO

Design d'indexes & constraints Check

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

Bonjour,

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

Code:

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

@ ++

:D8-)

09/07/2015, 16h35
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.
09/07/2015, 16h43
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.
09/07/2015, 16h50
escartefigue

Citation:

Envoyé par mathieu_BO

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 :(
09/07/2015, 17h34
mathieu_BO

Superb
merci pour l'info c'est claire :)

Citation:
Envoyé par Blacknounou
Bonjour,

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

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
@ ++

:D8-)
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:

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 +

09/07/2015, 17h48
Blacknounou

Merci pour ces corrections SQLpro.

J'en prends note.

@ ++

8-);)
10/07/2015, 11h20
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.

@++ ;)