Discussion :

[Tiffado]

Bonjour à tous,

Je travaille en ce moment sur un projet avec une Base SQL Server qui vient d’être upgrade de 2008 à 2012 et malheureusement, nous d'avons pas de DBA dans l'équipe...

On a pas mal de problème de performance sur notre base alors que la machine en elle même est surdimensionné d'après notre architecte.

Je suis en train de suivre la piste des index :
Pour le moment, on a 45Go de données sur notre base + 100 Go d'index. Ce qui me parait carrément aberrant.

Je me demandais s'il y avait des habitudes à prendre au niveau des index afin d'améliorer les perfs sans pour autant avoir 2 fois plus d'index que de données...
On a récemment passé un script pour réorganiser les index avec une fragmentation entre 6% et 30%, et on a reconstruit ceux >30%.
On a créé tous les index manquants avec comme condition :

Code :

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

CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) > 100

avec

Code :

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

sys.dm_db_missing_index_group_stats AS ddmigs

Ce sont des scripts qu'on a plus ou moins récupérer sur le net, mais bon, pourquoi 6% ? pourquoi 30 % ? Pourquoi >100 ? Je ne sais pas trop, j'espérais justement que vous puissiez m'aider la dessus.


Auriez-vous des recommandations à ces sujets ?
- Comment faire pour supprimer les index inutiles ? La table "dm_db_missing_index_group_stats" donne de bonne infos, mais à partir de quand est-ce qu'un index est réellement à considérer comme inutile ? Et comment savoir la taille en Mo que prennent ces index ?
J'envisageais de partir sur une suppression des index avec comme condition :

Code :

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

WHERE (IUS.user_seeks + IUS.user_scans + IUS.user_lookups) <= 1

avec

Code :

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

dm_db_index_usage_stats AS IUS

Par contre, je n'arrive pas à savoir à l'avance l'espace disque que cela me fera gagner.

- Faut-il reconstruire les index régulièrement, genre toutes les nuits ? Si oui quels paliers faut-il utiliser ?

- D'autres choses indispensables à savoir ?

Merci d'avance pour votre aide

[escartefigue]

Bonjour

Selon la façon dont le DDL a été généré, il est fort possible que tous les index par défaut aient été construits, auquel cas vous en avez une flopée qui ne servent à rien, commencez par analyser et détruire ceux là.
Vous pouvez vous appuyer sur le nombre de valeurs de clefs distinctes pour avoir une présomption d'utilité/inutilité
Exemple : si vous avez un index sur le code sexe de votre tables individus, cet index est inutile car il ne prendra au plus que 2 ou 3 valeurs
Pistez aussi les index redondants, exemple I1=C1 asc+C2 asc+C3 asc et I2=C1 asc+C2 asc
Les index les plus couteux sont bien sur les index multiples et ce d'autant plus qu'ils sont multi-colonnes, à chasser en premier donc

Attention lors de vos analyses :
- le SQL dynamique échappe aux études d'impact, certains index sont peut être utilisés par ce type de requêtes, difficile d'analyser ces cas
- certains index sont peut être très utiles pour des traitements de masse à périodicité longue (genre clôture annuelle, purge etc...), il faut donc être prudent si l'on analyse à partir de la date d'utilisation de l'index. Tout index non utilisé récemment n'est pas forcément inutile

[Tiffado]

Merci pour ta réponse,

Le soucis, c'est qu'on a en fait hérité d'un projet qui avait été développé pour de multiples clients et que nous, on ne s'en sert que pour un.
De ce fait, il y a énormément de tables inutiles/vides et des tables utiles mais avec des colonnes inutiles/vides, et nous ne sommes pas capable pour le moment de faire le tri de manière simple. C'est un travail qu'on a en fils rouge, on y va petit à petit.

Par exemple, on a des requêtes de partout qui font des LEFT JOIN sur des tables inutiles dans notre cas, on ne peut donc pas simplement faire le vide.

On arrive donc à une base de 1500 tables et faire le tri des index un par un me parait inimaginable.
C'est pourquoi j'étais plutôt à la recherche de stats sur ces index. Après, c'est vrai qu'on ne peut pas se fier à 100% sur ces stats, mais ça me parait être une bonne base de départ pour faire le tri.
On maîtrise globalement tous les JOBs et le côté fonctionnel de l'appli, donc on pourra être à même de cibler les traitement à faible fréquence.

[Boubou2020]

merci de penser a faire une mis a jour de vos statistics

vu que vous avez fait un upgrade du version vers 2012 il se peut que vos statistics sont obsolète

[SQLpro]

Bonjour à tous,

Je travaille en ce moment sur un projet avec une Base SQL Server qui vient d’être upgrade de 2008 à 2012 et malheureusement, nous d'avons pas de DBA dans l'équipe...

On a pas mal de problème de performance sur notre base alors que la machine en elle même est surdimensionné d'après notre architecte.

Je suis en train de suivre la piste des index :
Pour le moment, on a 45Go de données sur notre base + 100 Go d'index. Ce qui me parait carrément aberrant.

Effectivement anormal mais probablement lié à des tables obèses.
A vérifier : http://blog.developpez.com/sqlpro/p1...mances_petites

Je me demandais s'il y avait des habitudes à prendre au niveau des index afin d'améliorer les perfs sans pour autant avoir 2 fois plus d'index que de données...
On a récemment passé un script pour réorganiser les index avec une fragmentation entre 6% et 30%, et on a reconstruit ceux >30%.
On a créé tous les index manquants avec comme condition :

Code :

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

CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) > 100

avec

Code :

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

sys.dm_db_missing_index_group_stats AS ddmigs

Pas bon. Il faut mutualiser la demanjde d'index, car même avec cela vous aller créer des index inclus et quasi redondants !
Traquez les avec : http://blog.developpez.com/sqlpro/p9...s_index_anorma

Ce sont des scripts qu'on a plus ou moins récupérer sur le net, mais bon, pourquoi 6% ? pourquoi 30 % ? Pourquoi >100 ? Je ne sais pas trop, j'espérais justement que vous puissiez m'aider la dessus.

30 % pour reconstruire, 10 % pour défragmenter à condition que l'index fasse au moins 96 pages. En effet les pages sont regroupées par blocs de 8 appelées extensions. Si vous avez 8 blos de 8 pages qet que l'un des blocs n'est utilisé que pour une page, cela fait : 8*8 = 64 pages por une utilisation de 7*8 + 1 = 57. Bref une fragmentation irréfragable de (64-57)/64, soit 11% !
Pour plus d'info, voir notre livre sur SQL Server 2014...

Auriez-vous des recommandations à ces sujets ?
- Comment faire pour supprimer les index inutiles ? La table "dm_db_missing_index_group_stats" donne de bonne infos, mais à partir de quand est-ce qu'un index est réellement à considérer comme inutile ? Et comment savoir la taille en Mo que prennent ces index ?
J'envisageais de partir sur une suppression des index avec comme condition :

Code :

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

WHERE (IUS.user_seeks + IUS.user_scans + IUS.user_lookups) <= 1

avec

Code :

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

dm_db_index_usage_stats AS IUS

ATTENTION : ces métriques (DMV) sont en mémoire et RAZ à chaque démarrage. Il faut au moins 32 jours de rémanence sinon vous risquez de supprimez des index cruciaux qui ne sont utilisé que mensuellement.... Pour voir depuis quand le serveur à démarré :
SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Par contre, je n'arrive pas à savoir à l'avance l'espace disque que cela me fera gagner.

- Faut-il reconstruire les index régulièrement, genre toutes les nuits ? Si oui quels paliers faut-il utiliser ?

OUI ! 30, 10, 96 !

- D'autres choses indispensables à savoir ?

Recalculer les statistiques qui le nécessite en FULL SCAN.

Merci d'avance pour votre aide

A +

[escartefigue]

Pour en revenir au post initial ,

malheureusement, nous d'avons pas de DBA dans l'équipe...

Ne pouvez vous pas demander à minima une prestation externe, ne serait-ce que de quelques jours, pour faire une analyse rigoureuse
ou mieux, l'ouverture d'un poste de DBA

On a pas mal de problème de performance sur notre base alors que la machine en elle même est surdimensionné d'après notre architecte.
Je suis en train de suivre la piste des index

il faut bien commencer par quelque chose, mais d'autres pistes pouront être explorées : parallélisme des traitements, partitionnement, taille des verrous et niveaux de verrouillage, tables obèses, présence de colonnes varchar/longvarchar inutiles, longueur des identifiants, qualité des requêtes (prédicats sargables, tris inutiles, colonnes inutiles notamment select *....), purge des données obsolètes, réorg, runstat/rebind, etc...

[Tiffado]

...

Merci pour toutes ces réponses, je connaissais certains des risques mais ca ne coute rien de se les faire rappeler.

Et effectivement, on a une table avec 4Go de données pour 30Go d'index à elle seule. Les missing index ont été créé en lot, ce qui fait qu'ils sont pour la plupart redondants. On va fouiller a ce niveau la et faire un peu de tri.

...

On a eu quelques difficultés pour en trouver, on a eu des DBA Oracle de passage, mais bon, du coup le SQL Server n'était pas trop sa spécialité.
Nous en sommes en cours de rechercher pour trouver un intervenant qui ne devrait pas trop tarder à faire un diagnostique.

[SQLpro]

Merci pour toutes ces réponses, je connaissais certains des risques mais ca ne coute rien de se les faire rappeler.

Et effectivement, on a une table avec 4Go de données pour 30Go d'index à elle seule. Les missing index ont été créé en lot, ce qui fait qu'ils sont pour la plupart redondants. On va fouiller a ce niveau la et faire un peu de tri.


On a eu quelques difficultés pour en trouver, on a eu des DBA Oracle de passage, mais bon, du coup le SQL Server n'était pas trop sa spécialité.
Nous en sommes en cours de rechercher pour trouver un intervenant qui ne devrait pas trop tarder à faire un diagnostique.

Avant de passer par l'étape intervenant lisez les 5 documents d'optimisation que j'ai posté au sujet de mon expérience passée en audit et conseil dans différentes entreprise clientes :
http://www.sqlspot.com/Voulez-vous-optimiser-votre.html

A +