Problème performance suite à épuration base de données

Version imprimable

15/03/2017, 19h04
yal001

Problème performance suite à épuration base de données

Bonsoir,

sous 2008 R2 je rencontre le problème suivant, une requête qui mettait une minute à tourner en met maintenant 20 depuis que nous avons épuré les tables de la base (suppression d'enregistrements historisés dans une autre base par ailleurs). Je fait les essais sur la même machine, avec deux bases (une avant/après épuration).

Après recherches j'ai procédé à une réindexation de la base et à une mise à jour des statistiques, mais rien n'y fait.

Je suis à court d'idées, merci par avance pour votre aide.

Edit : je me rends compte que j'ai posté ça par erreur dans la partie développement je voulais poster dans administration :oops:
15/03/2017, 19h11
SQLpro

il faudrait recalculer toutes les statistiques de colonnes.

A +
15/03/2017, 19h46
yal001

Bonsoir et merci pour votre réponse

j'ai exécuté le code suivant

Code:

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH COLUMNS'

Malheureusement sans amélioration.
16/03/2017, 09h15
SQLpro

Code:

UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS

A +
16/03/2017, 09h26
samirCA007

Pourquoi le EXEC sp_MSforeachtable ?

Faite l'option mentionner par SQLPro que sur la table Qui a subit votre Opération du delete
16/03/2017, 10h52
yal001

Citation:

Envoyé par samirCA007

Pourquoi le EXEC sp_MSforeachtable ?

Faite l'option mentionner par SQLPro que sur la table Qui a subit votre Opération du delete

Parce que nous avons épuré toutes les tables.

J'ai restauré ma base et exécuté l'option indiquée mais malheureusement je ne constate toujours pas d'amélioration :weird: :calim2:
16/03/2017, 11h34
aieeeuuuuu

Bonjour,

afin de cibler l'origine du problème, le mieux serait de comparer les deux plans d’exécution.

Postez les si vous le pouvez.
16/03/2017, 13h59
yal001

2 pièce(s) jointe(s)

Bonjour,

ci-joint les deux plans d'éxécution.
Le premier est le plus court et l'autre est le plus long.

D'ailleurs j'ai remarqué que pour la table 10, dans un cas un index est utilisé, et dans un autre une clé primaire.
17/03/2017, 00h04
elsuket

1 pièce(s) jointe(s)
Bonjour,

Citation:

D'ailleurs j'ai remarqué que pour la table 10, dans un cas un index est utilisé, et dans un autre une clé primaire.

Je comprends ce que vous dites, mais il faut mettre les choses au point : la contrainte est une chose, l'index en est une autre.
Dans SQL Server, la création d'une contrainte de clé primaire implique par défaut la création d'un index cluster ... mais on peut spécifier qu'il soit non-cluster; dans certains cas c'est avantageux.
L'index cluster d'une table organise les données physiquement suivant l'ordre logique des valeurs des colonnes qui constituent sa clé (attention la clé d'un index n'a rien à voir avec une contrainte), et encore ce n'est pas absolument vrai : il existe certain cas particuliers ou l'ordre physique ne suit pas pas l'ordre logique. Il ne peut y avoir qu'un seul index cluster par table. Son niveau feuille est constitué des pages de données de la table. En ce sens, d'une certaine manière, il est un doublon de la table.

Pour voir la différence entre un index non-cluster et un index cluster, vous pouvez lire ce petit billet.
D'ailleurs, sous SQL Server 2014 et 2016, on peut créer un index cluster columnstore : cette structure de stockage ne supporte pas les clés primaires (ni d'unicité).

Ces clarifications faites, explorons les différences entre les deux plans. Pouvez-vous nous dire s'il s'agit de plans d'exécution réels ou estimés svp ?
Le premier plan a une exécution rapide : à en juger d'une part par l'épaisseur des flèches et d'autre part par les opérateurs, c'est normal. Nous avons :
- Une lecture complète de la table Table8
- Une recherche d'index cluster (Clustered Index Seek) sur la table Table6
- Des jointures par hachage (Hash Match) entre la table Table8 et Table6
Généralement, la recherche d'index est l'opération la plus rapide d'accès aux données.
Les jointures par hachage ne parcourent qu'une seule fois les données des deux jeux de données qu'elle prend en entrée : si vous passez le curseur de la souris sur cet opérateur, il est probable que vous voyiez s'afficher dans la bulle à apparaître un nombre d'exécution égal ou légèrement supérieur à 1. Cet algorithme est efficace pour traiter des volumes de données importants.

Le deuxième plan a une exécution lente. Là encore l'épaisseur des flèches nous indique clairement qu'il y a un problème, avec une explosion du nombre de ligne traité (plan réel) ou à traiter (plan estimé) pour la jointure entre la table Table8 et Table6. Par ailleurs, on s'aperçoit que le plan n'a recours qu'à des jointures par boucles imbriquées (Nested Loops Join). Cet algorithme recherche pour chaque ligne de sa table externe les lignes correspondantes dans sa table interne. C'est donc extrêmement efficace sur un faible volume de données, mais pas sur les gros volumes. C'est bien là que nous avons un problème.

Je suppose donc que la requête que vous avez soumis filtre sur une/des valeurs qui n'est / ne sont pas dans l'histogramme de la statistique sous-jacente à / aux index.
En conséquence, le moteur estime, à tort, qu'il n'a qu'une seule (ou très peu) de lignes à traiter, et sélectionne donc l'algorithme de jointure par boucles imbriquées.
Il serait donc intéressant de comparer le plan d'exécution estimé au plan d'exécution réel : il est fort probable que l'épaisseur des flèches et le nombre d'exécutions réels et estimés présentent un écart pour le moins important.
Dans SQL Sentry Plan Explorer, le bouton Show Estimated Plan, qui ne sera pas grisé si le plan d'exécution exposé est réel, vous montrera cette différence très visuellement.

Pièce jointe 254912

Attention à l'épaisseur des flèches : elles indiquent le nombre de lignes traités, pas le nombre de lignes de la table / des opérateurs en entrée.

L'onglet Parameters de SQL Sentry Plan Explorer vous permettra de voir avec quelles valeurs de variable le plan a été compilé puis exécuté. Il se peut que ce soit les mêmes.
A partir de là, il vous faut rechercher l'histogramme de statistique qui n'a pas la valeur de paramètre par laquelle vous filtrez.
Si vous y parvenez, vous avez donc affaire avec un problème de reniflage de valeur de paramètre (parameter sniffing dans la littérature).

Vous pouvez, à titre expérimental, essayer les indicateurs de requête suivants :
1. OPTION (OPTIMIZE FOR UNKNOWN) : indique au moteur qu'il ne doit pas tenir compte de la valeur des paramètres pour compiler le plan. Il s'en remet alors à la densité de la statistique sous-jacente, et à des règles d'optimisation différentes.
2. OPTION (OPTIMIZE FOR (@variable UNKNOWN) : indique au moteur qu'il ne doit pas tenir compte de la valeur d'un paramètre en particulier
3. OPTION (OPTIMIZE FOR (@variable = valeur_particulière) : indique au moteur qu'il doit compiler la requête avec une valeur particulière pour une variable précise, quelle que soit la valeur de la variable à l'exécution
4. OPTION (HASH JOIN) : indique au moteur de compiler un plan de requête utilisant exclusivement des jointures par hachage. Il est possible qu'il ne parvienne pas à compiler un plan, ce qui génère une erreur à l'exécution.
N'utilisez ces indicateurs dans le code de votre application ou de vos procédures stockées qu'en ultime et dernier recours, s'il n'y a pas d'autre choix.
Si cela devait être le cas, il faudra trouver le moyen de vous rappeler de revoir ces indicateurs dans quelques temps, car il est possible qu'avec l'évolution des données et la vie de la base de données, il produisent un effet néfaste sur l'exécution de la requête.

@++ ;)
17/03/2017, 10h34
yal001

Bonjour et merci pour votre réponse,

je vais essayer de bien comprendre tout ça.

Citation:

Pouvez-vous nous dire s'il s'agit de plans d'exécution réels ou estimés svp ?

Ce sont les plans d'exécution réels.

EDIT:
Grâce à l'analyse (merci eskulet!) je comprends mieux ce qui se passe. Lors de l'épuration de la base, des données saisies par le client en 2100 (allez comprendre pourquoi :aie: ) représentaient 40% des enregistrements d'une table au lieu de 5% avant.
Après avoir supprimé ces enregistrements, j'ai relancé les index/statistiques mais sans amélioration.
Par contre, avec l'option OPTIMIZE en restreignant mes variables (c'est une date de début et de fin), sur une période "courante", mes temps d'exécution sont tout à fait bons.
17/03/2017, 14h15
SQLpro

Citation:

Envoyé par yal001

Par contre, avec l'option OPTIMIZE en restreignant mes variables (c'est une date de début et de fin), sur une période "courante", mes temps d'exécution sont tout à fait bons.

Ha oui, on est bête, on y avait pas pensé, mais une recompilation du plan en cache aurait sans doute suffit !

Moralité un DBCC FREEPROCACHE juste après le vidage de multiples tables me parait, de bon aloi !

A +

Avez-vous fait un DBCC SHOW_STATISTICS ('dbo.laTable', laStatistique) ?

Si non, pour vous aider :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT		T.name AS table_name
		, ST.name AS stat_name
		, LEFT(CL.column_list, LEN(CL.column_list) - 1) AS column_list
		, STATS_DATE(ST.object_id, ST.stats_id) AS last_stat_update
		, 'DBCC SHOW_STATISTICS (''' + T.name + ''', ''' + ST.name + ''') WITH HISTOGRAM' AS dbcc_show_statistics_sql
FROM		sys.stats AS ST
CROSS APPLY	(
			SELECT		C.name + ', '
			FROM		sys.stats_columns AS STC
			INNER JOIN	sys.columns AS C
						ON C.object_id = STC.object_id
						AND C.column_id = STC.column_id
			WHERE		STC.object_id = ST.object_id
			AND		STC.stats_id = ST.stats_id
			ORDER BY	STC.stats_column_id
			FOR		XML PATH('')
		) AS CL(column_list)
INNER JOIN	sys.tables AS T
			ON ST.object_id = T.object_id
INNER JOIN	sys.schemas AS S
			ON S.schema_id = T.schema_id
WHERE		S.name = 'dbo'
AND		T.name = 'maTable'

L'instruction à exécuter pour la colonne que vous cherchez est dans la colonne la plus à droite ;)

@++ ;)

17/03/2017, 15h01
yal001

Citation:

Envoyé par SQLpro

Ha oui, on est bête, on y avait pas pensé, mais une recompilation du plan en cache aurait sans doute suffit !

Moralité un DBCC FREEPROCACHE juste après le vidage de multiples tables me parait, de bon aloi !

A +

J'avais également essayé mais ça n'avait pas marché non plus. Quoiqu'il se peut que j'ai essayé juste après l'épuration mais après la suppression des données parasites, j'ai un peu perdu le fil à force :aie:

Merci elsuket, j'ai regardé ce qui m'a permis de voir que j'avais oublié une brebis galeuse ;)