Discussion :

[tiamat249]

Bonjour,

D'habitude, avec une petite recherche et la consultation de quelques articles, je trouve assez rapidement des pistes pour résoudre mes problèmes, mais là, je ne comprends vraiment pas...

J'ai 4 bases de données, de structure quasi-identique parce qu'il s'agit des bases de données sur lesquelles travaille un logiciel de production unique (même si nous avons 4 instances du logiciel parce que 4 activités différentes donc 4 paramétrages différents... enfin je peux pas trop rentrer dans le détail, mais l'idée est là ^^).

Pour un atelier, j'ai besoin de données extraites des 4 bases (ALU, PVC, VB, VR).

J'avais donc jusqu'à la semaine dernière une vue faisant l'UNION de 4 sous-requêtes pratiquement identiques, et qui s'exécutait en quelques secondes.
Cette semaine, la vue a décidé de ne plus rendre service, et nous offre en dédommagement des expirations de délai.

On l'a donc partagée en 4 vues différentes, pour faire l'UNION sur une cinquième, pour identifier le problème.

Chacune des vues met entre 1 et 4 secondes à renvoyer un résultat, ce qui est correct, nos serveurs étant complètement saturés en ce moment...

Mais tout se complique quand on recommence à faire des UNION ou même des UNION ALL...

En effet, si l'on concatène les résultats des deux premières vues, ALU et PVC, tout va bien : 1 seconde. Idem pour les deux autres vues, VB et VR.

Mais dès qu'on mixe ces deux "groupes", avec une union de ALU et VB, par exemple, on a l'impression de lui parler chinois, et une exécution super lente commence : un affichage laborieux par blocs de 256 lignes, et une attente de plus de 3 minutes !

On a essayé de trouver des différences dans les types des champs, mais rien n'y fait, impossible de comprendre ce qui différencie ALU et PVC de VB et VR, sachant que ces 4 bases sont utilisées par la même appli, et ont été créées de la même façon...

Si vous avez des pistes de recherche, je suis preneuse...

J'espère que mes explications confuses vous paraîtront claires; j'ai essayé de vous donner un maximum d'informations pour comprendre...

Dans l'attente de petits indices...

Bon appétit !

Merci d'avoir pris le temps de lire.

Delphine

[Ptit_Dje]

Bonjour,

Y a t'il eu un chargement de données important dans vos bases ?
Vos index sont ils correctement défragmentés ? Vos statistiques à jour ?
Y a t'il des transactions posant des verrous sur vos bases et plus particulièrement sur les tables sources utilisées ?

Bonne journée

[tiamat249]

Les flux de données dans nos bases sont les mêmes que ceux que l'on observe tous les jours; rien de particulier à signaler.

Nos index ? euh... joker ?
Je ne sais pas exactement comment ça marche, mais il y a les mêmes dans les 4 bases puisqu'ils sont définis par l'éditeur du logiciel, et on ne s'en occupe jamais, donc ça n'expliquerait pas la différence de performance... si ?

Nos statistiques ? Alors là, je n'ai carrément aucune idée de ce que c'est...

Concernant les verrous, je ne sais pas si ça répond à la question, mais en voyant la chute des performances hier en arrivant, j'ai rajouté des WITH (NOLOCK) partout immédiatement en pensant que je bloquais tout, mais sans succès...


La seule différence a priori qu'il y ait entre ALU/PVC et VB/VR est que ALU et PVC ont été créées il y a longtemps avec la version SQL Server 2000, puis on a upgradé le server pour passer sur du 2005, et alors seulement on a étendu notre activité et ajouté les bases VB et VR.
Je ne sais pas si cette différence de version à la création peut avoir une influence... Mais ça n'expliquerait encore pas pourquoi d'un seul coup la vue qui fonctionnait correctement la semaine dernière est devenue inexploitable...

En tous cas, merci d'essayer de me donner des pistes...

Bonne après-midi, et je prends volontiers d'autres idées ! ^^

[Ptit_Dje]

Je ne sais pas exactement comment ça marche, mais il y a les mêmes dans les 4 bases puisqu'ils sont définis par l'éditeur du logiciel, et on ne s'en occupe jamais, donc ça n'expliquerait pas la différence de performance... si ?

Les index doivent être régulièrement reorganisés ou recréer afin de garantir de bonnes performances. (Sur les tables sujettes à des modifications de données)
Vérifiez si vous avez un job mis en place qui s'occupe de cette tache de manière automatique.
Si pas, songez à en mettre un en place, tournant dans les heures de charge creuses de vos serveurs.
Je ne sais pas si d'un jour à l'autre les performances peuvent être radicalement différentes à cause de cela, néanmoins c'est une bonne pratique qui aidera d'une facon ou d'une autre à obtenir de meilleures performances.

Nos statistiques ? Alors là, je n'ai carrément aucune idée de ce que c'est...

Les statistiques sont utiles à l'optimisateur de requêtes pour choisir le meilleur plan d'exécution d'un ordre SQL.
Voyez : sp_updatestats dans la documentation pour mettre celles-ci à jour.

Concernant les verrous, je ne sais pas si ça répond à la question, mais en voyant la chute des performances hier en arrivant, j'ai rajouté des WITH (NOLOCK) partout immédiatement en pensant que je bloquais tout, mais sans succès...

Voyez si d'autres process que les votres posent des verrous sur les objets.

La seule différence a priori qu'il y ait entre ALU/PVC et VB/VR est que ALU et PVC ont été créées il y a longtemps avec la version SQL Server 2000, puis on a upgradé le server pour passer sur du 2005, et alors seulement on a étendu notre activité et ajouté les bases VB et VR.
Je ne sais pas si cette différence de version à la création peut avoir une influence... Mais ça n'expliquerait encore pas pourquoi d'un seul coup la vue qui fonctionnait correctement la semaine dernière est devenue inexploitable...

A priori non.

En tous cas, merci d'essayer de me donner des pistes...

De rien

Bonne après-midi, et je prends volontiers d'autres idées ! ^^

Jetez un oeil au plan d'exécution de vos requètes, ca peut vous fournir des informations utiles à la résolution de votre problème.

[elsuket]

Bonjour,

même si nous avons 4 instances du logiciel parce que 4 activités différentes donc 4 paramétrages différents

Ce n'est pas une raison suffisante pour avoir 4 bases de données, qui engendrent la maintenance d'au moins 8 fichiers de base de données, et peut-être des écritures supplémentaires sur disque.
Vous auriez du à la place utiliser une colonne qui serve de discriminant dans les tables où cela est nécessaire.
Imaginez que demain votre entreprise décide d'augmenter ses activités en transformant une centaine de matériaux ... pas sûr que vous parviendrez à maintenir 100 bases de données pour y refaire par exemple le même traitement sur chacune d'entre-elles

En tous cas je rejoins les propos de Pti_Dje concernant les index et les statistiques.
Quelles sont les options des bases de données pour le ré-échantillonnage des statistiques (pour faire simple, elles renseignent sur la sélectivité des valeurs dans les colonnes, c'est à dire les valeurs et leur nombre d'occurrence dans une colonne) ?

Code :

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

1
2
3
4
5
SELECT is_auto_create_stats_on
	, is_auto_update_stats_on
	, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'maBD'

Quel est la taille de vos bases de données ?

Vous pouvez utiliser la requête suivante pour voir quand les statistiques ont été mises à jour pour la dernière fois :

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
SELECT		T.name AS nom_table
		, S.name AS nom_statistique
		, I.name AS nom_index
		, C.name AS nom_colonne
		, STATS_DATE(S.object_id, S.stats_id) AS derniere_MAJ
		, S.auto_created
		, S.user_created
FROM		sys.tables AS T
INNER JOIN	sys.stats AS S
			ON T.object_id = S.object_id
INNER JOIN	sys.stats_columns AS SC
			ON S.object_id = SC.object_id
			AND S.stats_id = SC.stats_id
INNER JOIN	sys.columns AS C
			ON SC.object_id = C.object_id
			AND SC.column_id = C.column_id
LEFT JOIN	sys.indexes AS I
			ON S.object_id = I.object_id
			AND S.stats_id = I.index_id
ORDER BY T.name

La colonne nom_index ne prend de valeur que si la statistique correspond à celle d'un index.
La colonne derniere_MAJ vous renseignera sur la dernière fois que les statistiques ont été mises à jour pour une colonne.
Cela ne signifie pas directement qu'elles sont obsolètes si la colonne est rarement mise à jour.

Pour voir l'état de vos indexes, il vous faut considérer leur fragmentation.
Je vous laisse vous amuser avec les deux vues suivantes :

Code :

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

1
2
FROM sys.indexes IDX
JOIN sys.dm_db_index_physical_stats

Regardez les colonnes avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count de la vue sys.dm_db_index_physical_stats.
Pour un faible nombre de pages pas d'inquiétude sur le pourcentage de fragmentation.
En revanche Microsoft recommande d'avoir au plus 30% de fragmentation sur les tables volumineuses.

@++

[tiamat249]

Bonjour,
Ce n'est pas une raison suffisante pour avoir 4 bases de données. [...]
Vous auriez du à la place utiliser une colonne qui serve de discriminant dans les tables où cela est nécessaire.
Imaginez que demain votre entreprise décide d'augmenter ses activités en transformant une centaine de matériaux ...

Malheureusement, nous n'avons pas développé le logiciel de production que nous utilisons tous les jours, et l'éditeur a visiblement estimé qu'il valait mieux créer plusieurs bases de données plutôt que de modifier son code pour prendre en compte les différentes activités...
En revanche, nous produisons des menuiseries en ALU et en PVC, et des Volets Roulants et Battants : les modes de production sont complètement différents, mais l'activité est suffisamment spécifique (et aujourd'hui suffisamment étendue) pour que le risque de la voir se développer comme vous le décrivez soit négligeable...

En tous cas je rejoins les propos de Pti_Dje concernant les index et les statistiques.[...]

Je vous laisse vous amuser avec les deux vues suivantes :

Code :

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

1
2
FROM sys.indexes IDX
JOIN sys.dm_db_index_physical_stats

Regardez les colonnes avg_fragmentation_in_percent, avg_page_space_used_in_percent, page_count de la vue sys.dm_db_index_physical_stats.

Bon ben voilà mes devoirs de la semaine ^^
Ca tombe bien, j'avais prévu de m'ennuyer !

Plus sérieusement, merci pour toutes ces informations, je vais tout de suite commencer à regarder comment ça marche et ce qu'on trouve comme informations...

Comme je l'ai dit dans mon post précédent, mes collègues ont estimé que fournir 4 vues réglait le problème puisque l'utilisateur récupérait toutes ses données, et ça supprimait le problème de performances...

Pour moi, l'affaire n'est pas réglée puisque le problème a été contourné et non réglé, et je pense qu'avec les outils que vous m'avez fournis, c'est sur un gain de performance général, qu'on peut travailler, et non sur simplement la résolution de ce mystère (je n'ai toujours pas compris pourquoi du jour au lendemain, certaines unions sont réalisées dans la seconde alors que les autres flinguent complètement le système!).

Donc encore merci pour ces éléments de recherche : je vais creuser, et quand j'aurai trouvé ce qui bloquait, je vous tiendrai au courant !

Merci à tous les deux, et bonne journée !

[tiamat249]

c'est une bonne pratique qui aidera d'une facon ou d'une autre à obtenir de meilleures performances.

J'en parlerai à mon chef : lui saura mieux me dire ce qui est mis en place et ce qui "réfléchit" la nuit.

Les statistiques sont utiles à l'optimisateur de requêtes pour choisir le meilleur plan d'exécution d'un ordre SQL.
Voyez : sp_updatestats dans la documentation pour mettre celles-ci à jour.

Je ne sais pas s'il prendra le temps de s'occuper des index, mais si une "simple" mise à jour peut faire gagner en performance, il y a des chances pour qu'il m'accorde quelques minutes d'attention !

Voyez si d'autres process que les votres posent des verrous sur les objets.

A priori aucun.

Jetez un oeil au plan d'exécution de vos requètes, ca peut vous fournir des informations utiles à la résolution de votre problème.

C'est ce qu'on a fait hier après-midi, mais apparemment rien n'en ressort et mon collègue en a déduit qu'il fallait juste ne plus faire l'UNION, et plutôt fournir 4 jeux de données aux utilisateurs...
C'est une autre façon de régler les problèmes...

Donc merci, je vais essayer de leur donner ces informations.
J'espère qu'ils en tiendront compte : un "petit" problème sur une petite vue, ca cache probablement d'autres problèmes...