Voici mon idée de départ

Par défaut SQL Server (j'insiste que c'est le mode par défaut et qu'il peut être changé) est en "Read Commited". Les modifications de données posent des verrous qui empêcheront les lectures (exclusive lock pour les modifications et shared lock pour les lectures).
Donc, plus le SGBDR subi des modifications, plus les lectures seront bloquées (mises en attente).

Le moteur Oracle lui, ne propose qu'un seul mode d'isolation : les lectures ne posent pas de verrou et si les données sont modifiées par une autre transaction durant la lecture, la lecture se fait sur l'ancienne valeur.
Dans ce cas, un système qui subi beaucoup de modification n'aura pas d'impact sur les lectures.

Mise en place du générateur de charge

Pour pouvoir faire varier à volonté le nombre de modification et de lectures subies par le système, j'ai écris un programme Java. Le code permet d'attaquer les 2 SGBDR via JDBC et génère des threads de travail.

Chacun des threads va exécuter la même quantité de travail excepté pour 1 thread qui est marqué comme "monitoring_thread". Ce thread sera notre référence : il va toujours exécuter les mêmes requêtes de lecture (uniquement des SELECT).
Ce thread va aussi collecter ses propres attentes qu'il va rencontrer :

  • Sur SQL Server, j'ai utilisé un capture via Extended Events : sqlos.wait_info
  • Sur Oracle, l'activité est suivie avec un TIMED_STATISTICS=TRUE

Les autres threads exécutent un certain nombre de modifications (UPDATE uniquement) et en faisant varier ce nombre de modifications, nous allons voir si le monitoring_thread est impacté.

Par la suite, j'utilise le terme de "select_ratio" qui correspond à : ( nombre de select ) / ( nombre de requêtes ). Plus ce ratio est élevé, plus la charge est composée de SELECT par rapport aux UPDATE.

Table de travail sur les SGBDR

Pour ces tests, une table de faible volumétrie est suffisante : je souhaite vous monter les effets de locks sur les lignes ; si toute la table est en mémoire, les effets seront visibles aussi. J'ai donc utilisé une simple table small_table qui n'a ni index, ni primary_key. Une table basique.

SQL Server

create table dbo.small_table
(
[date] [datetime2](7) NOT NULL,
[text] [nvarchar](80) NOT NULL,
[number] [decimal](16, 2) NULL
);

Vous notez que sans PK, la table sera une HEAP table !

Oracle

create table small_table
(
col_date date NOT NULL,
text varchar2(80) NOT NULL,
col_number number NULL
);

Aucune création d'index ni rien d'autre ; on reste simple.

Paramétrage du benchmark

Une fois le programme en place et les SGBDR installés avec leur table ; j'ai pu démarré les mesures.
Très franchement, j'ai eu beaucoup de difficultés à trouver le paramétrage adéquat et j'ai remis en cause à de nombreuse reprises ma méthode expérimentale. La charge n'est certainement pas idéale et je vais peut-être faire des conclusions rapides mais je suis arrivé à des résultats qui me semblent cohérents avec la théorie.
Par exemple, j'ai perdu beaucoup de temps car j'utilisais un mauvais paramétrage et je n'arrivais pas à comprendre les résultats. Les mesures étaient prises alors que les SGBDR étaient surchargés ; ils répondaient avec des attentes qui ne s'expliquaient pas avec leur fonctionnement interne.

J'ai réalisé les mesures ci-dessous avec le paramétrage suivant :

  • 10 Threads en parallèle (10 Workers)
  • Le thread n°5 sera le monitoring_thread
  • Chaque thread exécute 1 000 fois les requêtes suivantes :
    • 2 SELECT ; les 2 SELECT sont différents
    • Sauf si le thread est le monitoring_thread : exécution d'un certain nombre d'UPDATE via une procédure
    • 2 SELECT; les 2 SELECT sont différents et différents des 2 précédents
    • Sauf si le thread est le monitoring_thread : exécution d'un certain nombre d'UPDATE via une procédure
    • Encore un nouveau SELECT différents de tous les autres
    • 45 fois le même SELECT
    • Sauf si le thread est le monitoring_thread : exécution d'un certain nombre d'UPDATE via une procédure

Ensuite, je fais varier le nombre d'UPDATE réalisé par les 9 threads afin de changer le SELECT_RATIO du benchmark.
Vous voyez que le monitoring_thread va donc toujours réaliser les 50 SELECT et ne va jamais faire d'UPDATE. Sa charge ne varie pas alors que le SELECT_RATIO change.
J'ai récupéré les attentes de ce monitoring_thread et voici les chiffres.

Les résultats

SQL Server

wait_events_SQLSRV.png

Qu'en lire ?

Plus le select_ratio diminue, plus le temps total d'exécution (monitored_worker duration) augmente. Cela peut s'expliquer par la charge sur la DB qui augmente : avec un select_ratio de 100%, il n'y a aucun UPDATE sur la base ; avec un select_ratio de 94.3%, la base subie 3 x 1 000 x 9 = 27 000 UPDATES en plus des 500 000 SELECTS.
Maintenant, voyons les 2 événements : LCK_M_S + PAGELATCH_SH

  • PAGELATCH_SH = attente pour faire une lecture sur une page en mémoire alors qu'elle est en cours d'écriture par un autre process
  • LCK_M_S = attente pour faire une lecture sur une ligne / page alors qu'elle est en cours de modification par une autre session
  • les autres attentes sont insignifiantes


Ces 2 attentes sont très proches et sont toutes les deux en lien avec une lecture simultanée d'une écriture. La seule différence entre ces 2 attentes (cette explication va faire plaisir à une personne que je connais) est qu'un Latch est un verrou niveau système du SGBDR là où le Lock (LCK) est un verrou utilisateur. Par exemple, 2 sessions (dans 2 processus distincts) doivent modifier 2 lignes différentes d'une table mais stockée dans la même page. Ces 2 écritures peuvent se faire en même temps d'un point de vue SQL : les 2 sessions ne modifient pas les mêmes données. SQL Server va poser un Latch sur la page avant d'écrire et donc, le premier process va écrire et le second se mettre en attente sur le LATCH ; puis lorsque le 1er process a fini, il libère le LATCH et le 2nd process peut écrire.
Les locks sont eux posés lorsque 2 actions sont incompatibles d'un point de vue SQL : 2 sessions qui tentent de modifier la même ligne ; ou comme ici, 2 sessions en mode "READ_COMMITTED", l'une modifie la donnée, la seconde sera en attente pour lire la donnée jusqu'à-ce que la 1ère session valide sa modification.

Analyse

On voit donc que plus le select_ratio diminue, plus le monitoring_thread est en ralenti par des attentes sur des locks & latch créés lors d'UPDATE. Les SELECTS sont bloqués par les UPDATES.

Oracle

wait_events_Oracle.png

Les attentes

Sur Oracle, le temps total d'exécution du moniroting_thread augmente alors que le select_ratio diminue comme sur SQL Server. Par contre, les événements d'attentes sont différents :

  • library cache: mutex X = attente pour lecture d'un block dans la library cache. Je pense cet event apparait car beaucoup de sessions accèdent aux mêmes objects (notre small_table et la procédure de mise à jour)
  • latch: cache buffer chains = attente avant de pouvoir lire dans le buffer cache
  • buffer busy waits = attente pour lire un block alors qu'une autre session est en train de le lire/écrire
  • resmgr: cpu quantum = la session attend un quantum de CPU


Analyse

Mis à par l'évenement "resmgr: cpu quantum" les attentes ne changent pas vraiment avec la diminution du select_ratio. Ces 3 attentes sont liées à des lectures/écritures sur les mêmes données et on voit que changer la charge sur la base (de plus en plus d'UPDATE) ne fait pas augmenter ces attentes.
Par contre, le resmgr: cpu quantum augmente fortement avec la diminution du select_ratio. Cette attente (et toutes les attentes resmgr) sont créées par le moteur pour une régulation de la charge (Resource Manager) : ici, la session attend d'avoir un CPU disponible avant de s'exécuter. Cette attente apparait car la DB subie une augmentation de la charge : lorsque le select_ration diminue, le benchmark augmente le nombre d'UPDATE sans changer le nombre de SELECT ; la charge totale augmente et vu la configuration du benchmark, cela a suffit à mettre ma machine de LAB Oracle en manque de CPU.

Conclusion

Avec ce benchmark plutôt difficile à mettre en place, je pense avoir pu mettre en lumière l'impact de l'isolation "Read Committed " (=sans Snapshot).
Par défaut, SQL Server pose des locks sur les SELECT ; on peut voir que si le select_ratio est défavorable, cela aura un impact sur les performances. Sur Oracle, les SELECT ne posent pas de locks, changer le select_ratio n'impacte pas les performances.

Selon moi, ce choix a été fait stratégiquement par Microsoft afin d'avoir un système le plus efficace possible sur les environnements avec un select_ratio élevé comme c'est le cas dans le monde Web (beaucoup plus de lectures que de modifications). Tout comme le fait de créer les tables en mode CLUSTERED_INDEX par défaut dès qu'elles ont une PK ; idem ce choix est pertinent sur les environnements avec un select_ratio élevé.
Je continue mes tests sur ce benchmark prochainement et vous présenterai qu'il y a plus que le niveau d'isolation qui change entre les 2 systèmes.