Analyse approfondie des traces systèmes, diagnostic et solution suite à une étude de consolidation databases

Secteur d'activité

Assurance
Contexte client

Rationnalisation des bases de données SQL Server
Enjeux client

Limiter l'impact des serveurs de bases de données sur le stockage SAN
Contexte technologique
- SGBD : SQL Server
- Stockage : SAN NetApp
Délais

2 jours
Résultat

En utilisant 2 index sur une table, réduction de 41To/jour de lecture IOs sur le SAN ! (80% de l'activité SAN)

Suite à l’analyse des traces consolidées de plusieurs serveurs de bases de données on cherche à expliquer certaines anomalies. Notamment l’impact du SGBD sur les performances du SAN.
Le tableau de bord suivant est construit :

Etude de consolidation de bases de données : on simule l'activité d'une instance en cumulant les mesures des sondes posées pendant plusieurs jours sur les différentes machines

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 des données de l’instance MSSQL « xxxTELx1 »

La première étape consiste à visualiser les traces des compteurs perfmon et les traces SQL:

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

Les traces SQL montrent comment sont lancées les requêtes sur la base de données

–> 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 :

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 avec son plan d’exécution:

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) :

Le plan d’exécution avant optimisation a un coût de 366 et intègre 2 scan full de l’index cluster de T_Calls_Display (ie : scan full de toutes les données).

Analyse de la table T_Calls_Display

L’analyse de la taille de cette table et de sa structure montre de nombreuses colonnes dont beaucoup sont de type char. Ce qui explique sa taille relativement importante par rapport à son nombre de lignes.

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 ce qui est le signe d’un faible impact sur les requêtes de mise à jour.