Outils permettant d'analyser les performances de SQL Server

Version imprimable

20/09/2016, 16h35
Providence0

Outils permettant d'analyser les performances de SQL Server

Bonjour,
On m'a remonté qu'il y a des lenteurs sur nos bases SQL Server 2008 R2, du coup je voulais savoir s'il n'y aurait pas un outils (ou un module ou une méthode) qui me permettrait d'analyser la raison des lenteurs sur nos bases SQL Server?

Car les seuls choses qui viennent dans mon esprit de développeur aujourd'hui c'est :
- le moniteur de tâche pour regarder l'activité de la base (mais il faudrait que je puisse me connecter à l'instant où il y a des lenteurs).
- ou le SQL profiler qui me permettrait d'auditer les requêtes qui ont été lancé (pas spécialement utile sauf si je ne sais pas l'utiliser :p)
- ou les logs qu'on peut trouver sur Management > SQL Server Logs > Current (mais ça me montre plutôt qui s'est connecté qu'autre chose XD).
20/09/2016, 18h19
SQLpro
Il existe de nombreux outils, mais aucune magie.... De l'analyse, de la réflexion, du savoir, de l'interprétation... Bref un boulot d'expertise appelé audit de performance.

Il faut commencer par le commencement :
- Hardware : matériel, configuration
- Stockage : architecture du sous système disque, organisation du stockage
- Modèle de données : conformité aux formes normales, choix des types de données, mise en place des contraintes...
Ensuite investiguer en utilisant les outils perfmon, profiler et DMV, ce qui permet de débusquer les blocages, attentes, deadlock, requêtes couteuses, index inutiles, index manquants, index anormaux...

Lisez les articles que j'ai écrit sur le sujet :
http://sqlpro.developpez.com/optimisation/

A +
21/09/2016, 09h40
Providence0

Merci, je vais creuser ça :)

(Je vais finir par devenir DBA à force XD).
03/12/2016, 13h07
jacquesv

Citation:

(mais il faudrait que je puisse me connecter à l'instant où il y a des lenteurs).

Bonjour,

Dans la suite de la question initiale...

L'on me pose souvent les questions suivantes :
- cette nuit, tel job a été beaucoup plus lent que d'habitude, pourquoi ?
- tout à l'heure, un utilisateur s'est plaint d'une lenteur en cliquant sur tel bouton, pourquoi ?

En fait, la même question : pouvoir enquêter a posteriori, sans possibilité de reproduction.

Le but du jeu n'étant pas d'avoir une vue basse ("le CPU est monté à 100% entre 2:30 et 2:32" : on ne peut pas faire grand chose avec un tel point de départ) mais plutôt une vue haute : telle instruction de telle proc stock/job a été lente car elle a attendu la levée d'un verrou posé par une autre session qui exécutait telle instruction dans telle proc stock/job... et il y avait des millions de lignes à manipuler.

Afin d'analyser, outre des outils "temps réels", qu'a-t-on comme outils standards ?
- Data Collection/Moniteur d'activité --> j'ai compris que cela fonctionnait par échantillonnage (ai-je mal compris ?) A tous les coups mon utilisateur/job va tomber entre 2 photos...
- Trace/Profiler --> prends de la ressource = à ne pas mettre en production en permanence ==> plouf.
- Extended Events --> en cas de lenteur, prendre une photo des sessions en cours ?
- Alertes ? Je suis prévenu... et après ?

Je ne trouve pas d'articles/how to/pdf sur le sujet, même si Microsoft a bien ces pages :

Analyse des performances et surveillance de l'activité du serveur
https://msdn.microsoft.com/fr-fr/library/ms191511.aspx

Surveiller et régler les performances
https://technet.microsoft.com/fr-fr/...=sql.110).aspx

... mais je n'y ai rien trouvé de satisfaisant.

Comment faire ?
Quels outils avez-vous mis en place pour pouvoir répondre à cette question ?
Que faites-vous quand on vous la pose ?
Des outils tiers ?

Merci !
05/12/2016, 14h22
SQLpro
Citation:

Envoyé par jacquesv

Bonjour,

Dans la suite de la question initiale...

L'on me pose souvent les questions suivantes :
- cette nuit, tel job a été beaucoup plus lent que d'habitude, pourquoi ?
- tout à l'heure, un utilisateur s'est plaint d'une lenteur en cliquant sur tel bouton, pourquoi ?

En fait, la même question : pouvoir enquêter a posteriori, sans possibilité de reproduction.

Il est possible d'utiliser la session d’événement étendu système "health" tout comme la trace n°1 pour enquêter à postériori sur des problématiques passées. Mais la première des chises est d'avoir étalonnée sa base, car des affirmations comme "c'est lent" de la part d'utilisateurs ne veulent rien dire.
De plus la rémanence des informations que l'on peut obtenir par ces moyens n'est heureusement pas infinie.

Vous pouvez aussi voir des choses passées à travers un certain nombre de DMV comme :
- sys.dm_exec_procedure_stats;
- sys.dm_exec_query_stats;
- sys.dm_exec_trigger_stats;
- sys.dm_exec_function_stats;
Citation:

Le but du jeu n'étant pas d'avoir une vue basse ("le CPU est monté à 100% entre 2:30 et 2:32" : on ne peut pas faire grand chose avec un tel point de départ) mais plutôt une vue haute : telle instruction de telle proc stock/job a été lente car elle a attendu la levée d'un verrou posé par une autre session qui exécutait telle instruction dans telle proc stock/job... et il y avait des millions de lignes à manipuler.

Afin d'analyser, outre des outils "temps réels", qu'a-t-on comme outils standards ?
- Data Collection/Moniteur d'activité --> j'ai compris que cela fonctionnait par échantillonnage (ai-je mal compris ?) A tous les coups mon utilisateur/job va tomber entre 2 photos...

Oui, vous avez mal compris. Non, il capte tout cce qui pose des problèmes. Mais comme tous les outils de traçage il est limitable et limité dans le temps

Citation:

- Trace/Profiler --> prends de la ressource = à ne pas mettre en production en permanence ==> plouf.

Oui, il prend de la ressource. Un peu, très peu si vous le paramétrez bien. Beaucoup si vous sniffez tout et que vous l'utilisez en mode graphique !

Citation:

- Extended Events --> en cas de lenteur, prendre une photo des sessions en cours ?

Même topo, remarque et mode de fonctionnement que le profiler

De manière générale, tous les outils de diagnostics sont intrusif. Faut-il alors ne pas les utiliser ? C'est un peu comme si vous disiez, cette personne est malade, mais luis faire passer une radio ne va pas l'arranger à cause des rayons, donc, on ne fait aucun diagnostic, comme cela ça ira mieux !

Citation:

- Alertes ? Je suis prévenu... et après ?

Les alertes servent généralement à déclencher un traitement de "libération" en sus d'être prévenu...

Citation:

Je ne trouve pas d'articles/how to/pdf sur le sujet, même si Microsoft a bien ces pages :

Analyse des performances et surveillance de l'activité du serveur
https://msdn.microsoft.com/fr-fr/library/ms191511.aspx

Surveiller et régler les performances
https://technet.microsoft.com/fr-fr/...=sql.110).aspx

... mais je n'y ai rien trouvé de satisfaisant.

Comment faire ?
Quels outils avez-vous mis en place pour pouvoir répondre à cette question ?
Que faites-vous quand on vous la pose ?
Des outils tiers ?

Merci !

Il existe des outils tiers de monitoring. Ici tous les experts vous dirons qu'actuellement le meilleur est Sentry. Cependant c'est à réserver aux grosses config (plusieurs To, plusieurs centaines, voire milliers d'utilisateur.... avec le hardware qui va bien !)

A +