Discussion :

[Bronzato]

Bonjour à tous et bonne année

Alors voilà, j'ai une application qui effectue toute une série de calculs au démarrage en lançant des querry. Lorsque je me branche sur mon serveur de production cette procédure met plus de 40 secondes pour se terminer. J'ai fait un backup de cette base de donnée de production et je l'ai installée en local sur ma machine. La première exécution me donne des temps de 20 secondes et les exécutions suivantes me donnent des temps de moins de 7 secondes!

Dois-je comprendre qu'en travaillant avec une base de donnée locale, l'ordinateur met en cache toutes les infos (cela expliquerait les temps de réponse meilleurs)?

Dois-je comprendre qu'une base de données sur un serveur accessible sur le réseau ne peut pas me donner des temps de réponse aussi bon qu'une base de donnée locale?

Je m'excuse par avance si ce sont des questions stupides mais j'aime bien comprendre. Une fois que j'aurai compris, je pourrai peut être envisager une optimisation quelconque.

Encore une fois, une bonne année à tous.

[elsuket]

Bonjour,

Dois-je comprendre qu'en travaillant avec une base de donnée locale, l'ordinateur met en cache toutes les infos (cela expliquerait les temps de réponse meilleurs)?

SQL Server possède un cache de procédures et un cache de plans de requête.
Donc que vous soyez sur votre ordinateur ou bien sur un serveur distant, c'est la même chose : SQL Server met autant de données que possible en cache

Dois-je comprendre qu'une base de données sur un serveur accessible sur le réseau ne peut pas me donner des temps de réponse aussi bon qu'une base de donnée locale?

Oui, parce que le serveur réseau peut être occupé par d'autres tâches, mais surtout il doit vous retourner les données à travers le réseau.
Donc si vous vous retournez un ensemble de données volumineux, cela peut prendre plus de temps ...

Si vous recherchez un problème de performances sur une requête, postez le DDL de vos tables et de vos index, et votre requête.
Si cela n'est pas envisageable, regardez le plan de requête (CTRL+L sous SQL Server Management Studio)

@++

[Bronzato]

Merci elsuket pour votre réaction.

Vous dites que le serveur sql est peut etre occupé à autre chose, ou que le réseau met un certain temps pour ramener les données sur l'ordinateur en local. C'est sans doute une explication. Maintenant je me demande si il n'y a pas aussi un problème de fragmentation des index? Et peut être que le fait d'avoir fait un export, puis un import dans une nouvelle db (que j'ai créé en local) n'a pas simplement permis de défragmenter mes index. Qu'en pensez-vous?

Je vais faire le test suivant la semaine prochaine quand je me rendrai chez mon client: je vais faire un export de la db, créer une nouvelle db de test sur le même serveur et ré-importer les données dedans. De cette manière je pourrai vérifier si le problème de lenteur vient principalement des index fragmentés.

Est-ce cohérent ce que je suggère?

Dans un second temps, je tacherai de repenser mes querry dans ma procédure de calcul car il y a énormément d'appels.

PS: il m'est difficile de poster mes plans de requete car il y a énormément de querry impactés comme je l'ai dit plus haut.

Sinon, j'ai essayé de faire un alter index sur plusieurs index et apres j'analyse les stats et je n'ai pas vu d'amélioration. Est-ce normal? Faut-il attendre?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
--Reconstruction d'un index--
ALTER INDEX ALL
ON [tblEmballageModele]
REORGANIZE;
GO
 
--Défragmentation d'un index--
ALTER INDEX ALL
ON [tblEmballageModele]
REBUILD;
GO

Apres l'exécution j'ai toujours les résultats suivants:

INDEX NAME: PK_tblEmballageModele
INDEX ID: 1
FRAG EXTENE: 66.67
FRAG INTERNE: 63.84
FILL FACTOR: 90
PAGES: 3
TYPE: CLUSTERED INDEX
PADDED: 0
INDEX LEVEL: 2
PARTITION: 1

Merci pour votre aide.

[SQLpro]

D'abord comment observez vous les temps de réponse. Si c'est du chronométrage depuis votre poste client, c'est stupide. Ce que vous mesurerez sera essentiellement la qualité de votre réseau et non la consommation des ressources du serveur.

Pour mesurer ce qui se passe sur un serveur depuis un client, il faut utiliser SSMS et le paramétrage SET STATISTICS TIME ON par exemple et ne s'intéresser qu'au temps CPU.

A +

[Bronzato]

Bonjour SQL Pro, oui, je suis conscient pour les temps de réponse. C'est pour cela que je vais tenter la chose suivante:

- faire un backup de ma db depuis le serveur
- faire un restore toujours sur le serveur mais dans une nouvelle db
- comparer les temps de réponse.

J'imagine qu'il va reconstruire les index dans la nouvelle db au moment de la création, juste? De cette manière je saurai déjà si les index y sont pour quelques choses dans la lenteur des querry.

__________

Sinon dans mon post précédent, j'aivais fait une remarque sur l'instruction ALTER INDEX, j'ai tenté cela sur des index et je n'ai pas remarqué de changements dans les stats. Quelqu'un sait il m'expliquer?

Merci en tout cas pour votre aide.

[elsuket]

Bonjour,

En ce qui concerne les index, vous devez regarder la colonne avg_fragmentation_in_percent de la DMF sys.dm_db_index_physical_stats, avant et après la reconstruction de l'index.

Sachez que le REORGANIZE se fait en ligne, donc qu'il introduit peu de verrous, et plus rapidement qu'un REBUILD, mais qu'il ne convient pas à un taux élevé de fragmentation de l'index (plus de 25 - 30 %).

REBUILD ne se fait pas en ligne et équivaut à supprimer puis recréer l'index.
Enfin, il ne sert à rien d'effectuer une reconstruction pour un taux de fragmentation inférieur à 5%.

Je pense que les index sont conservés tel quels lors d'une opération BACKUP + RESTORE.

@++

[Bronzato]

En ce qui concerne les index, vous devez regarder la colonne avg_fragmentation_in_percent de la DMF sys.dm_db_index_physical_stats, avant et après la reconstruction de l'index.

Eh bien avant et après la reconstruction j'ai toujours les mêmes valeurs ?!!

Sachez que le REORGANIZE se fait en ligne, donc qu'il introduit peu de verrous, et plus rapidement qu'un REBUILD, mais qu'il ne convient pas à un taux élevé de fragmentation de l'index (plus de 25 - 30 %).

Je fais à chaque fois les 2 instructions les unes à la suite des autres de la manière suivante:

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
 
USE [TPB_BKP]
GO
 
--Reconstruction d'un index--
ALTER INDEX ALL
ON [tblRfidScanLog]
REORGANIZE;
GO
 
--Défragmentation d'un index--
ALTER INDEX ALL
ON [tblRfidScanLog]
REBUILD;
GO

Est-ce que je m'y prend mal? Parce que j'ai les valeurs de stat suivantes avant et après ALTER INDEX

tblRfidScanLog IDNUM 83,3333333333333 1
tblRfidScanLog IDTAG 50 1
tblRfidScanLog PARAM1 50 1

Merci pour votre aide.

[SQLpro]

- faire un backup de ma db depuis le serveur
- faire un restore toujours sur le serveur mais dans une nouvelle db
- comparer les temps de réponse.

Aucun intérêt. Ceci laiussera la base de données excatement dans l'état ou vous l'avez prise !

J'imagine qu'il va reconstruire les index dans la nouvelle db au moment de la création, juste?

Vous imaginez beaucoup de chose. Ce n'est heureusement pas vrai. Imaginez le temps de restauration qu'il faudrait dans ce cas, si vous aviez perdu la base ! La restauration étant une opération d'urgence, elle restitue le plus vite possible la base qui a été sauvegardée...

De cette manière je saurai déjà si les index y sont pour quelques choses dans la lenteur des querry.

Hé ben non !

Sans plus d'information sur votre problème votre agitation sert a rien. Commencez par faire ce que je vous ait dit. On élimine les causes les unes après les autres et non pas en donnant des coups de pied au hasard dans la fourmilière !

A +

[Bronzato]

Je compte bien suivre vos conseils mais je ne retourne chez ce client que la semaine prochaine. En attendant, je n'arrive pas à comprendre pourquoi l'exécution de la requête ALTER INDEX ne me donne pas d'amélioration du taux de fragmentation. Avez-vous une idée?

[SQLpro]

Sans les données de frag impossible de vous dire. Si vos index sont tout petits (moins de 8 pages par exemple) alors la fragmentation sera toujours là car certains paramètre statistiques de comptage de frag se font par extension (bloc de 8 pages).

Lisez l'article que j'ai écrit sur ce sujet : http://sqlpro.developpez.com/optimis...ntenanceIndex/

Mais je ne pense pas du tout que vos index soient fragmentés puisque vous dites les avoir déjà reconstruits.



A +

[Bronzato]

Bonjour SqlPro, j'ai lut votre pdf qui m'a beaucoup intéressé; voilà une synthèse assez complète de la problématique.

Pour mon problème, je met ci-dessous les données de frag. Vous verrez que j'ai des index qui comportent (pour certains) plus de 20 pages. Et malgré cela, la routine ALTER INDEX ne semble pas avoir d'effets dessus car les stats n'ont pas changé. Etrange?

[elsuket]

Bonjour,

Et malgré cela, la routine ALTER INDEX ne semble pas avoir d'effets dessus car les stats n'ont pas changé. Etrange ?

Absolument pas, une fragmentation sur un nombre très faible de pages, et 20 pages c'est très faible, est normale et tout à fait acceptable.

@++

[Bronzato]

Bonjour elskunet, merci pour votre réponse. J'ai quand même une table (tblItineraire) ou j'ai 83 pages (voir print screen ci-dessus)! C'est normal aussi?

Et pourquoi certains de mes index n'ont pas de nom? (il est mis NULL) ?

[SQLpro]

7% de frag sur 83 pages c'est du pipi de chat !
On commence à parler de frag à partir de 30% sur des index de plus de 100 pages.

Compte tenu du très faible volume de données de vos tables et index, ne cherchez pas les problèmes de perf de ce côté là... Les index ne montrent une réelle efficacité que sur de fort volume : entre 1000 et 1 millions de pages./

Encore une fois SUIVEZ CE QU'ON VOUS A DIT DE FAIRE pour mesurer les temps de réponse

C'est assez lassant et décourageant de voir des gens que l'on tente d'aider et qui en ont rien à foutre de ce que l'on dit ! On a vraiment envie de continuer à les aider !!!

A +

[Bronzato]

Ah ben voilà j'ai encore appris qqch sur les index:

On commence à parler de frag à partir de 30% sur des index de plus de 100 pages

Et aussi:

Les index ne montrent une réelle efficacité que sur de fort volume : entre 1000 et 1 millions de pages

Merci beaucoup ProSql

C'est assez lassant et décourageant de voir des gens que l'on tente d'aider et qui en ont rien à foutre de ce que l'on dit ! On a vraiment envie de continuer à les aider !!!

Je ne vois pas pourquoi vous dites ca? Je prend bonne note de vos conseils. Et ne vous découragez pas, je vous rassure, ces petits coups de main sont utiles. Maintenant que j'ai compris ceci, je vais pouvoir me concentrer sur l'optimisation de mes procédures de calcul.

Merci encore