Skip to main content

Optimisation pointue des requêtes TSQL

Comme les DBA savent, l'Assistant Paramétrage du moteur de base de données (DTA, database tuning advisor) pourra vous aider dans certains cas.

  • L'analyse des traces sera assez efficace en OLTP dont les requêtes sont répétitives ou facilement généralisées
  • L'analyse sera beaucoup moins efficace en OLAP/reporting et surtout sur le reporting paramétrée par utilisateur. Les recommandation des index avec des colonnes incluses dépassent rapidement la taille doublée de votre BDD. Soyez attentifs d'appliquer ces recommandations.
  • Enfin, l'analyse sera quasiment inutile en cas OLAP/Decisionnel. Chaque requête "adhoc" d'utilisateur sera produit la recommandation d'ajout d'un index avec colonnes incluses. En prenant en compte la taille des entrepôts et datamarts ainsi que les requêtes difficilement généralisées, l'optimisation de ce genre ne sera pas une bonne solution.

Dans ce cas l'optimisation pointue est nécessaire. Je vous propose suivre la procédure suivante, que vous pouvez adapter et améliorer selon vos besoins.

Obtenir l'information nécessaire

Personnellement, j'ai un outil qui ramasse les données statique et statistique concernant le serveur BDD. Vous pouvez le télécharger sur sourceforge.

Instructions à lancer:

  1. Décompressez le ZIP et placez l'outil dans un répertoire sur le serveur BDD
  2. Changez les paramètres de connexion dans le fichier de configuration appcfg.xml
  3. Lancez ssexpertcmd.exe directement ou à partir de l'invite de commande

L'outil créera le sous-répertoire correspondant au temps de démarrage et y stocke tous les information sous la forme des fichier CSV.

En même temps le fichier journal "ssexpertcmd.exe.log" sera créé et vous apportera l'information concernant les erreurs éventuelles. Par exemple, certaines BDD peuvent être hors ligne.

Ce n'est pas nécessaire placer l'outil sur le serveur même, vous pouvez se connecter depuis son poste de travail. Mais dans ce cas les informations sur le stockage sera correspondre aux disques des votre PC.

Choisir les données nécessaires

Dans le répertoire créé par ssexpertcmd.exe vous avez les différentes fichiers CSV que vous pouvez ouvrir sous en forme d'une table en Excel ou en Open/LibreOffice :

Vue d'ensemble
000_Storage.csv Les informations de stockage logique et physiques
010_ServerVersion.csv Les informations concernant les versions de logiciels (voir aussi les correspondances des versions SQL Server)
020_ServerInfo.csv Certains informations des paramètres au niveau serveur
Vue BDD
030_Databases.csv La liste des BDD et ces paramètres
040_DBFiles.csv Les informations sur le stockage des BDD (répartition par les fichiers et groupes)
050_TableSizes-<nom BDD>.csv La taille et nombres de lignes des tables ainsi que les index
060_Indexes-<nom BDD>.csv Les informations sur les index de la BDD
070_IndexesStat-<nom BDD>.csv Les informations statistique des index : densité, longueur moyenne de clé...
Anomalies
080_IndexesAnomalies-<nom BDD>.csv Les anomalies trouvées au niveau des index, i.e. manque des clés primaires (NO_PK) etc.
Statistique des requêtes
090_QStatTopAVGCPUTime-00-GLOBAL.csv Les 300 requêtes plus gourmand au niveau de le temps UC (vue globale). Calculée comme AvgCPUTime = sum(total_worker_time) / sum(execution_count) dont "total_worker_time" est le temps processeur total, indiqué en microsecondes (mais précis uniquement en millisecondes), utilisé par les exécutions de ce plan depuis sa compilation.
090_QStatTopAvgCPUTime-<nom BDD>.csv idem pour les BDD (faites attention, les requêtes adhoc sont dans la vue globale).
090_QStatTopFrequent-00-GLOBAL.csv Les 300 requêtes plus fréquentes (vue globale)
090_QStatTopFrequent-<nom BDD>.csv idem pour les BDD
090_QStatTopLogicalReads-00-GLOBAL.csv Les 300 requêtes plus gourmandes au niveau des lectures logiques (vue globale)
090_QStatTopLogicalReads-<nom BDD>.csv idem pour les BDD
090_QStatTopMaxWorkerTime-00-GLOBAL.csv Le top 300 requêtes par la valeur du "max_worker_time" (vue globale). MaxWorkerTime est le temps processeur maximum, indiqué en microsecondes (mais précis uniquement en millisecondes), jamais utilisé par ce plan en une seule exécution.
090_QStatTopMaxWorkerTime-<nom BDD>.csv idem pour les BDD

Voir aussi l'article MSDN concernant la vue sys.dm_exec_query_stats.

Analyser les requêtes gênantes

Dans notre cas vous pouvez commencer par les données 090_QStatTop* et analyser quelques requêtes de début de la liste.

Il vous faudra installer le plug-in de formatage SQL dans Management Studio. Puis on copie la requête dans SSMS et fait le formatage du code SQL.

Ajoutez au début du code SQL les instructions suivantes pour afficher les informations sur les temps et lectures

SET STATISTICS IO ON
SET STATISTICS TIME ON

Si nécessaire, rajoutez aussi les instructions de nettoyage du cache pour simuler le démarrage à "froid". Attention, cette option ne dois pas être appliquée sur le serveur de production !.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Activez l'option "Include actual execution plan" sur la barre d'outil SSMS ou via le menu "Query - Include actual execution plan". Sinon utiliser l'instruction SET

SET STATISTICS XML ON

Lancez le requête et observez les résultats concernant le temps UC/temps écoulé, lectures ainsi que le plan d’exécution réel.

Il existe plusieurs sources qui vous guident lors d'amélioration des requêtes TSQL. Par exemple, un bon point de départ est l'article "Liste de vérification pour l'analyse des requêtes longues à s'exécuter".

Bon courage !