fiche référence client

Des cas réels de problématiques traitées pendant les audits de performance ou les missions d'architecture et d'expertise

Contexte
Rationnalisation des bases de données SQL Server
Technologies
  • SGBD : MSSQL Server 2008R2 Standard et Enterprise Edition
  • ETL : Script et procédures stockées T-SQL
  • Reporting : NA

Objectif

Afin de connaitre la faisabilité d’une consolidation de plusieurs instances sql serveur sur une seule machine qui contient déjà des dizaines de databases, une mission d’audit des serveurs et instances MSSQL est menée.

Dans cette étude, nous appliquons notre méthodologie pour la consolidation des applications et des bases de données à savoir la pose de sondes et des traces, études des configurations hardware et logicielles, analyses des traces et des fonctionnalitées. Après plusieurs dizaines de jours de traces, les données sont consolidées dans une base de données pour être analysées. Voici les résultats de ces analyses :

 

  dashboard de consolidation des serveurs MSSQL

La première remarque qui saute aux yeux c’est le très fort impact disque du serveur xxxTELx1 (serveur hébergeant des données de téléphonie). Afin d’y voir plus clair, une analyse détaillée est alors menée sur ce serveur afin d’en apprendre davantage :

Analyse approfondie sur l’instance xxxTELx1

Analyse des compteurs perfmon

  Analyse système du serveur  xxxTELx1

Les différents diagrammes confirment une utilisation intensive des disques en lecture avec une signature IOs assez particulière.

 

Analyse d’une Trace MSSQL 

Gantt des procédures stockées et de requêtes responsables des IOs

--> 2 procédures stockées sont responsables de la très grande majorité des d'IOs.
Toutes les 4 minutes la procédure ps_wallboard_performance_dc est lancée via l'agent MSSQL.
Toutes les 2 minutes la procédures ps_wallboard_performance_do est lancée via l'agent MSSQL.

Pendant un temps les 2 procédures tournent au même moment : on a alors un pic IOs à 700Mo/s en lecture.
Pendant un temps seule 1 procédure tourne : on a alors un pic IOs à 350Mo/s en lecture.

Cela explique la signature IOs :

Signature de IOs de lecture sur le serveur xxxTELx1

 

Approfondissement sur une requête :

Il faut maintenant comprendre pourquoi ces procédures consomment autant d’IOs en lecture. Chaque requête qui compose ces procédures font des updates sur une table à partir d’une sous-requête (update/select). En voici une :

Requête SQL longue

Son plan d'exécution avant optimisation a un cout de 366 et intègre 2 scan full de l’index cluster de T_Calls_Display (ie : scan full de toutes les données) :

Plan d'exécution de la requête SQL longue
L’analyse de la taille de cette table et de sa structure montre de nombreuses colonnes dont beaucoup sont de type char.
La table ne contient qu'un seul index (clustered non unique)

Actions : 

 

Au vu des différentes requêtes il est décidé d'ajouter 2 index mono-colonne sur cette table.

 

La taille des index est très faible

 

Résultats des optimisations :

Le nouveau plan d'exécution de la requête initiale prise en exemple utilise les deux index créés et le cout est et 0,0297 (au lieu de 366,34) :

Toutes les requêtes des deux procédures stockées vont ainsi utiliser ces index et au final :

 

La procédure stockée ps_wallboard_performance_dc passe de 2min11s à 2s : +60 fois plus rapide, 50x moins d'IOs (maintenant réalisés en mémoire et non sur disque), 60x moins de CPU
L'impact des IOs de ce serveur sur la baie de stockage SAN est maintenant quasi nul, alors qu'il était le principal contributeur en IOs !!
Résultats 

L'étude préalable des consommations systèmes sur les différents serveurs à consolider aura permis de détecter des anomalies dans le comportement de certaines bases SQL Serveur. Une analyse approfondie a permis de trouver les racines du problème et d'apporter ainsi une solution efficace et pas chère ! L'impact était très important puisque à la suite de cela la carte flash du SAN a pu être utilisée à d'autre fin et améliorer les performances d'autres applications. A la suite de cette optimisation et aux regards du reste des résultats d'analyse il est donc possible de consolider sereinement les bases de données concernées sur une seule instance sur un serveur adapté pour SQL Serveur