Discussion :

[Hiken]

Bonjour à tous,

J'ai parcouru et lu pas mal de documentation sur les index que je connaissais assez basiquement.

J'ai besoin de votre aide pour la problématique suivante :

J'ai une base de données SQL Sever 2008 R2 contenant une dizaine de tables de "référence" sans aucune relation existantes entre elles.

Ces tables sont pour le moment simplement constituées d'une clé primaire int en identity true auto incrémentée et d'un index auto généré sur cette PK. (pour info : ces tables servent pour un chargement ETL).
Il y a également un trigger on before delete permettant d'historiser les lignes supprimées (donc traitées) dans une autre table historique purgée à fréquence régulière.
Ces tables peuvent être assez volumineuse : plusieurs millions de records

Je cherche à rester dans les best practice d'implémentation de ce genre de base de données, et j'aimerai donc ajouter des index sur ces tables par rapport aux différentes procédure stockées qui pointent dessus.

Ma question est :
Si j'ai 6 requêtes différentes efféctuant un select avec une clause where contenant la combinaison de plusieurs champs : ex :

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
 
SELECT FIELD1, FIELD2, FIELD3, FILEDN, ... 
FROM TABLE1 
WHERE 
FIELD1 = "test"
 
SELECT FIELD1, FIELD2, FILEDN, ... 
FROM TABLE1 
WHERE 
FIELD1 = "test" AND FIELD2 = "test2"
 
SELECT FIELD1, FIELD2, FILEDN, ... 
FROM TABLE1 
WHERE 
FIELD1 = "test" AND FIELD3 = "test3"
 
ETC...

Dans ce cas, dois je créer les index suivant :
- INDEX CLUSTER ASC FIELD1
- INDEX CLUSTER ASC FIELD1, ASC FIELD2
- INDEX CLUSTER ASC FIELD1, ASC FIELD3

Donc en clair, dois-je créer un index pour toutes les combinaisons de requête que j'estime importante ? Cela ne risque pas d'etre contre performant ? Et au niveau du storage ?

Merci d'avance pour vos lumières !!

Hiken.

[SQLpro]

1)
dans :
- INDEX CLUSTER ASC FIELD1
- INDEX CLUSTER ASC FIELD1, ASC FIELD2
- INDEX CLUSTER ASC FIELD1, ASC FIELD3
Votre premier index est inclus dans les deux autres. Donc inutile

2) étudiez les demandes d'index faites par le serveur et mutualisez les.
Pour obtenir les demande d'index :

Code :

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

1
2
SELECT *
FROM sys.dm_db_missing_index_details

A +

[Hiken]

Merci SQLpro pour votre réactivité et cette réponse.

Je me permet de compléter mon exemple pour être sur de bien comprendre.
Dans le cas ou j'ai les requêtes suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT FIELD1, FIELD2
FROM TABLE1 
WHERE 
FIELD1 = "test" AND FIELD2 = "test2"
 
SELECT FIELD1, FIELD2
FROM TABLE1 
WHERE 
FIELD1 = "test" AND FIELD2 = "test2" AND  FIELD3 = "test3"

Dans ce cas devrai-je créer les deux index suivants :
1 - INDEX CLUSTER ASC FIELD1, ASC FIELD2
2 - INDEX CLUSTER ASC FIELD1, ASC FIELD2, ASC FIELD3

Ou seul l'index 2 : INDEX CLUSTER ASC FIELD1, ASC FIELD2, ASC FIELD3 suffira ?
En fait, est-ce que la combinaison de la clause WHERE entre en compte ou pas ?
Suffit-il donc de créer un index sur toutes les colonnes présente dans les clauses WHERE de mes requêtes gourmandes ou faut il les créer en fonction des combinaisons ?

Sinon, je ne suis plus devant ma base, j’essayerai des demain la commande pour déterminer les index manquants.

Encore merci et bonne soirée

Hiken

[SQLpro]

Tant que votre clause WHERE est en "AND" et pas en "OR", vous pouvez ajouter les colonnes à l'index et ne pas vous préoccuper d'en créer tout un tas.
Il en va différemment s'il y a du OR !

Il faut voir si le prédicat est "sargable".
A me lire : http://blog.developpez.com/sqlpro/p1...sql_sargable_c

A +

[Hiken]

Merci SQLPro

J'ai bien compris, je vais donc déjà mettre en place ces index, la plupart de mais requêtes sont en AND.

La commande dm_db_missing_index_details est magique ^^
Je retrouve bien les index manquants que j'avais déterminés manuellement.

Je pense maintenant pouvoir m'en sortir.

Merci encore pour l'aide.

Bonne continuation

Hiken

[Hiken]

Bonjour,

Je me permet de rouvrir ce post pour vous poser une question liée au mon topic initial :

Suite à la mise en place des différents index (déterminés grâce au missingindex détectés : combinaison d'equality, inequality et include columns).
J'ai rechargé mes tables avec plusieurs millions de lignes et je m’aperçois que la base, et plus précisément la fichier de log .ldf est beaucoup plus gros qu’auparavant. (mdf : 18GB, ldf : 45GB !!)

Je sais que la mise en place d'index a un coût au niveau du storage, cependant cette taille de log est elle liée à mes index ? Est-ce normal ? Cela peut il être contre performant ?
Je ne comprend pas d'ou provient ce problème ?

Merci encore.

Hiken

[darkelend]

Les indexs sont stockés dans ton fichier mdf.
Le ldf a grossi lors de la création des indexs.
Quel est le recovery mode de ta base ? SIMPLE/FULL ?
As tu un plan de maintenance ?

[Hiken]

Effectivement j'ai un plan de maintenance hebdomadaire de sauvegarde de deux bases (dont celle impliquée contenant les fameux index)...
Le mode de récupération est : FULL

En fait, les véritables alertes furent les suivantes, et c'est suite à ces alertes que j'ai constaté que le ldf était assez gros :

Could not allocate space for object 'dbo.TABLE1'.'PK__TABLE1_C__01' in database 'BASE1' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

The transaction log for database 'BASE1' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Merci

[SQLpro]

Le fichier du journal de transaction va toujours grossir car il empile les transactions depuis l'origine, sauf si :
1) vous êtes en mode de journalisation des transaction SIMPLE (auquel cas il boucle en réutilisant l'espace des anciennes transactions)
2) vous effectuez régulièrement des sauvegarde du journal de transaction (donc BACKUP LOG) afin de les archiver au cas ou...
On considère comme normal de maintenir le JT à 20 ou 30% de la taille de la base et pour cela, en mode FULL ou BULK LOGGED, vous devez procéder régulièrement à des purges du JT par exemple toutes les heures.

[Hiken]

Très bien, je comprends. Mes index ne sont donc pas en cause... ouf !
J'ai donc deux possibilités, soit je passe en mode SIMPLE ou soit je BackUp les log régulièrement (ce que j'avais d'ailleurs déjà fait en oneshot, en m'appuyant sur votre article SQLPro, je ne savait pas que c'était le mode qui était en cause).

J'aurai tendance à penser que de passer en SIMPLE est la solution la plus simple ^^
Pouvez vous m'aiguiller sur les impacts ou les différences significatives ?

Encore merci à vous pour vos explications

Hiken

[darkelend]

Tout dépend en fait de ce que tu es disposé à perdre
Si tu es en mode simple et que tu es obligé de restaurer ta base, tu devras reprendre ta dernière sauvegarde complète.
Si tu es en mode FULL, tu pourras restaurer ta sauvegarde complète + tes LOGS.
En simple, si tu fais une sauvegarde tous les jours à minuit et que ta base plante à 14h, tu perdras 14h de données.
En FULL, si tu fais une sauvegarde complète tous les jours à minuit + sauvegarde LOGS chaque 1/4 d'heure, si ta base plante à 14h02, tu ne perdras que 2 minutes.

[Hiken]

C'est trés clair merci !
Je dois réfléchir à ces deux options...
Encore merci pour votre aide précieuse !

[iberserk]

En FULL, si tu fais une sauvegarde complète tous les jours à minuit + sauvegarde LOGS chaque 1/4 d'heure, si ta base plante à 14h02, tu ne perdras que 2 minutes.

Voir moins s'il parvient à sauvegarder son dernier fichier de log...

[Hiken]

Bonjour à tous,

Je me permet de relancer le sujet car j'ai une autre question dans la continuité de ce topic.
Pour résumer j'ai donc une base de données contenant plusieurs tables et plusieurs index, cette base est en mode de récupération SIMPLE, un backup complet est fait tout les soirs, suivi d'une phase de shrinking afin de réduire la taille de fichier de log.

Ma question se porte sur les index : Mes traitements se déroulent bien mais j'ai l’impression que les temps de lecture sont de plus en plus long...
Je pense qu'il faudrait mettre en place une phase de reconstruction et/ou réorganisation des index.

J'ai cru comprendre qu'il y en avait une plus longue que l'autre...
Quelle est la best practice concernant ces actions sur les index ? Dois-je faire les deux ? et à quelle fréquence ?

Juste pour info : Certaines de mes tables servent uniquement de tampon pour le chargement de données via ETL, une fois le chargement terminé, les lignes traités sont supprimées. Je me retrouve donc tout les jours avec des jeux de données complètements différents ? (ces tables sont alimentées via un autre flux)
J'ai l'impression que c'est un éléments important pour gérer mes index.

Dois je donc mettre en place une action sur les index, quotidiennement, après chaque arrivé de nouvelles données ?

Merci encore de votre aide précieuse !!

Hik

[SQLpro]

Bonjour à tous,

Je me permet de relancer le sujet car j'ai une autre question dans la continuité de ce topic.
Pour résumer j'ai donc une base de données contenant plusieurs tables et plusieurs index, cette base est en mode de récupération SIMPLE, un backup complet est fait tout les soirs, suivi d'une phase de shrinking afin de réduire la taille de fichier de log.

Le SHRINK est hautement stupide... A lire : http://blog.developpez.com/sqlpro/p5..._fichiers_et_t notamment le paragraphe "Pire ! C’est possible…"

Ma question se porte sur les index : Mes traitements se déroulent bien mais j'ai l’impression que les temps de lecture sont de plus en plus long...
Je pense qu'il faudrait mettre en place une phase de reconstruction et/ou réorganisation des index.

oh que oui, et toutes les nuits !

J'ai cru comprendre qu'il y en avait une plus longue que l'autre...
Quelle est la best practice concernant ces actions sur les index ? Dois-je faire les deux ? et à quelle fréquence ?

Juste pour info : Certaines de mes tables servent uniquement de tampon pour le chargement de données via ETL, une fois le chargement terminé, les lignes traités sont supprimées. Je me retrouve donc tout les jours avec des jeux de données complètements différents ? (ces tables sont alimentées via un autre flux)
J'ai l'impression que c'est un éléments important pour gérer mes index.

Dois je donc mettre en place une action sur les index, quotidiennement, après chaque arrivé de nouvelles données ?

Le mieux étant de désactiver les index dans les tables tampon, avant insertion, puis de les reconstruite après

Merci encore de votre aide précieuse !!

Hik

A +

[elsuket]

Bonjour,

Quelques détails sur la différence entre réorganisation et reconstruction d'index ici

@++

[Hiken]

Merci SQLPro pour ces infos :

Effectivement je suis débutant dans l'administration de base de donnés, je viens du monde du développement .Net et je me suis lancé corps et âme dans cette nouvelle discipline. Donc je tiens à vous remercier pour votre aide et votre patience

Revenons au problème, j'ai lu l'article en question qui m'apporte d'ailleurs un nouvel élément : donner une taille fixe à ma base de données !!
Je dois donc paramétrer ma base avec une size et un filegrow par exemple :

Code :

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

1
2
3
4
5
 
CREATE DATABASE [DB] ON  PRIMARY 
( NAME = N'DB', FILENAME = N'E:\MSSQL\DATA\DB.mdf' , SIZE = 1000GB, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'DB_log', FILENAME = N'E:\MSSQL\DATA\DB_log.ldf' , MAXSIZE = 2048GB , FILEGROWTH = 10%)

Qu'en est il de la taille du ldf ? Et doit on aussi déterminer le paramètre maxsize ?

Concernant le Shrink, quelque chose m'échappe :

Nous avions discuté à ce sujet et j'avais compris qu'en mode de récupération COMPLET, j’aurai eu à sauvegarder mon fichier de log (historique des transactions) et ainsi réduire la taille du ldf à chaque sauvegarde.
Mais en mode SIMPLE, je n'ai plus la possibilité de sauvegarder les logs : BACKUP LOG db.
Je pensais donc que le seul moyen de réduire mon fichier ldf, qui représente une taille non négligeable (66GB pour une db de 33GB !!), était d'effectuer un SHRINK !!
Apparemment ce n'est pas la bonne pratique, mais que faire alors de ces logs qui grossisses sans cesse (même en mode SIMPLE) et qui ne sont pas sauvegardables et réutilisables ???


Concernant les index :

Reorganize ou Rebuild toutes les nuits sur mes tables "non-tampon".

Et pour les tables "tampon", je vais ajouter les étapes suivantes au niveau des traitements ETL :
- Avant chaque chargement ETL : Rebuild index (les données ont déjà été rechargées en amont)
- Apres chaque chargement ETL : Disable index (les données seront rechargées pour la prochaine itération)

Code :

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

1
2
3
4
5
6
7
8
9
 
USE db
GO
-- Désactiver Index
ALTER INDEX [IX_1] ON Table1 DISABLE
GO
----Reconstruire index
ALTER INDEX [IX_1] ON Table1 REBUILD
GO

Est-ce convenable ?

@elsuket :
Merci pour cet article très clair, dans mon cas je pense donc devoir utiliser des rebuild vu que le jeu de données change considérablement...

Merci encore

Hik

[iberserk]

Mais pourquoi diantre voulez vous le réduire?

[Hiken]

Car je n'ai pas assez de stockage !
Je ne peux pas assumer que mon fichier de log soit deux fois plus gros que ma base elle même !!
De plus, y a t il une limit de stockage des transaction en log ? pour l'instant il est à 2 fois mais il peut surement encore augmenter, tant que je ne fais aucune action !

Je cite SQLPro dans cette même discution :

On considère comme normal de maintenir le JT à 20 ou 30% de la taille de la base et pour cela, en mode FULL ou BULK LOGGED, vous devez procéder régulièrement à des purges du JT par exemple toutes les heures.

On parle de 20 ou 30%, ce qui est bon pour moi, mais comment purger ce fichier autrement que par des : BACKUP LOG db ou SHRINKFILE ?? De plus, en mode SIMPLE !

Merci encore

Hik

[SQLpro]

Qu'en est il de la taille du ldf ? Et doit on aussi déterminer le paramètre maxsize ?

Il faut faire de même en taillant le JT à 25 ou 30 % de la taille des données....

Concernant le Shrink, quelque chose m'échappe :

OUI : PAS DE SHRINK !!!!!! C'est juste une opération d'urgence à faire si vous avez oublié de surveillé vos volumes et que vous avez saturé vos disques !!!!!!

Nous avions discuté à ce sujet et j'avais compris qu'en mode de récupération COMPLET, j’aurai eu à sauvegarder mon fichier de log (historique des transactions) et ainsi réduire la taille du ldf à chaque sauvegarde.

NON : la sauvegarde du JT ne réduit pas le fichier.... Il le purge ! NUANCE... Quand vous purgez une chasse d'eau... La voyez-vous se dégonfler ???

Mais en mode SIMPLE, je n'ai plus la possibilité de sauvegarder les logs : BACKUP LOG db.

NORMAL : il est auto purgé....

Je pensais donc que le seul moyen de réduire mon fichier ldf, qui représente une taille non négligeable (66GB pour une db de 33GB !!), était d'effectuer un SHRINK !!

33 Go pour une base de 1 To, c'est bien trop peu... Je mettrais au moins 200 Go

Apparemment ce n'est pas la bonne pratique, mais que faire alors de ces logs qui grossisses sans cesse (même en mode SIMPLE) et qui ne sont pas sauvegardables et réutilisables ???


Concernant les index :

Reorganize ou Rebuild toutes les nuits sur mes tables "non-tampon".

Et pour les tables "tampon", je vais ajouter les étapes suivantes au niveau des traitements ETL :
- Avant chaque chargement ETL : Rebuild index (les données ont déjà été rechargées en amont)
- Apres chaque chargement ETL : Disable index (les données seront rechargées pour la prochaine itération)

Code :

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

1
2
3
4
5
6
7
8
9
 
USE db
GO
-- Désactiver Index
ALTER INDEX [IX_1] ON Table1 DISABLE
GO
----Reconstruire index
ALTER INDEX [IX_1] ON Table1 REBUILD
GO

Est-ce convenable ?

OUI !

@elsuket :
Merci pour cet article très clair, dans mon cas je pense donc devoir utiliser des rebuild vu que le jeu de données change considérablement...

Merci encore

Hik

A +