SQL Server lent : comment trouver la vraie cause avant d'acheter du matériel

D'abord : de quelle lenteur parle-t-on ?

« La base est lente » peut recouvrir des situations très différentes, qui n'ont pas les mêmes causes. Avant de lancer la moindre requête de diagnostic, il vaut la peine de qualifier le symptôme :

• tout est lent, tout le temps : on pense à la configuration de l'instance, à la mémoire ou au stockage ;
• c'est lent à certaines heures : charge de pointe, traitement planifié, sauvegarde ou maintenance d'index qui tombe au mauvais moment ;
• un écran ou un traitement précis est lent : presque toujours une requête, un index ou un plan d'exécution ;
• c'est lent depuis une date précise : mise à jour de l'application, migration, changement de niveau de compatibilité, ou table qui a franchi un seuil de volumétrie.

Notez aussi un exemple concret et daté : « l'écran des commandes a mis 14 secondes mardi à 10 h 20 ». Un exemple précis vaut mieux que dix impressions générales, et il permettra de vérifier le gain à la fin.

Étape 1 : regarder ce que SQL Server attend

Une requête passe son temps soit à travailler (CPU), soit à attendre quelque chose : une page à lire sur le disque, un verrou tenu par une autre session, une écriture dans le journal, de la mémoire. SQL Server comptabilise ces attentes, et c'est le point de départ le plus fiable d'un diagnostic.

/* Les attentes les plus lourdes depuis le dernier redémarrage */
SELECT TOP (15)
    wait_type,
    waiting_tasks_count,
    CAST(wait_time_ms / 1000.0 AS decimal(12, 1))                AS attente_totale_s,
    wait_time_ms / NULLIF(waiting_tasks_count, 0)                AS attente_moyenne_ms,
    CAST(100.0 * signal_wait_time_ms / NULLIF(wait_time_ms, 0)
         AS decimal(5, 1))                                       AS pct_attente_cpu
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT LIKE 'XE%'
  AND wait_type NOT LIKE 'BROKER%'
  AND wait_type NOT LIKE 'QDS%'
  AND wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
                        'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'DIRTY_PAGE_POLL', 'WAITFOR',
                        'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
                        'ONDEMAND_TASK_QUEUE', 'SOS_WORK_DISPATCHER')
ORDER BY wait_time_ms DESC;

Ces compteurs sont cumulés depuis le dernier redémarrage. Pour analyser une période précise, prenez deux photos à une heure d'intervalle pendant le ralentissement et comparez-les. Et regardez deux choses : l'attente totale, qui dit où part le temps du serveur, et l'attente moyenne, qui révèle les problèmes rares mais graves que le total masque.

Étape 2 : trouver les requêtes qui coûtent le plus

Dans la très grande majorité des cas, une poignée de requêtes explique l'essentiel de la charge. Si le Query Store est activé sur vos bases (disponible depuis SQL Server 2016, actif par défaut sur les nouvelles bases depuis 2022), ses rapports « requêtes les plus consommatrices » sont le meilleur outil. Sinon, le cache des plans donne une première vue :

/* Les 20 requêtes qui consomment le plus de CPU (cache des plans) */
SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000                          AS cpu_total_ms,
    qs.total_logical_reads                               AS lectures_totales,
    qs.total_elapsed_time / qs.execution_count / 1000    AS duree_moyenne_ms,
    DB_NAME(st.dbid)                                     AS base,
    SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
         - qs.statement_start_offset) / 2 + 1)           AS requete
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;   /* ou total_logical_reads DESC */

Triez successivement par CPU total, par lectures totales puis par durée moyenne : les mêmes requêtes reviennent souvent en tête. Gardez en tête que ce cache se vide à chaque redémarrage et sous la pression mémoire, ce qui explique pourquoi une requête problématique peut ne pas y apparaître. C'est l'une des raisons d'activer le Query Store.

Étape 3 : les blocages, en direct

Pendant un ralentissement, cette requête montre les sessions bloquées et celles qui les bloquent :

/* Qui bloque qui, en ce moment */
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000        AS attente_s,
    DB_NAME(r.database_id)    AS base,
    t.text                    AS requete
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Remontez la chaîne jusqu'à la session en tête : celle qui bloque les autres sans être elle-même bloquée. C'est souvent une transaction laissée ouverte par l'application, ou un traitement lourd lancé en pleine journée. Pour les deadlocks, la session d'événements étendus system_health, active par défaut, conserve les graphes des derniers interblocages : inutile d'attendre le prochain pour commencer l'analyse.

Quand des lectures sont bloquées par des écritures, l'isolation par versions de lignes (option READ_COMMITTED_SNAPSHOT) supprime une grande partie de ces blocages : les lecteurs ne bloquent plus les rédacteurs, et inversement. Elle se teste avant d'être activée, car elle sollicite davantage tempdb et peut changer le comportement de certaines applications.

Étape 4 : vérifier la mémoire et le stockage

Un serveur qui manque de mémoire relit sans cesse les mêmes pages sur le disque, et l'attente affichée ressemble à un problème de stockage. Vérifiez d'abord que la mémoire maximale de SQL Server est réglée (elle ne l'est pas par défaut, voir les réglages par défaut à corriger), puis mesurez la latence réelle de chaque fichier :

/* Latence moyenne par fichier de base de données */
SELECT
    DB_NAME(vfs.database_id)                               AS base,
    mf.type_desc                                           AS type_fichier,
    mf.physical_name,
    vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)    AS latence_lecture_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)   AS latence_ecriture_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY latence_lecture_ms DESC;

Sur un stockage SSD ou NVMe correct, les lectures se comptent en quelques millisecondes et les écritures du journal restent autour d'une ou deux. Des latences de lecture durablement supérieures à 20 ms, ou des écritures de journal au-delà de 5 à 10 ms, justifient une enquête côté stockage ou virtualisation.

Les causes que nous retrouvons le plus souvent

• Des index absents, inadaptés ou en trop. Les suggestions d'index manquants de SQL Server sont un indice utile, pas une liste à appliquer telle quelle : elles ignorent les index existants et le coût ajouté à chaque écriture.
• Des statistiques obsolètes sur les grosses tables, qui conduisent l'optimiseur à de mauvaises estimations et donc à de mauvais plans.
• La sensibilité aux paramètres. Un plan parfait pour un client qui a dix commandes devient catastrophique pour celui qui en a deux millions. Ajouter OPTION (RECOMPILE) partout n'est pas la solution : le Query Store permet de forcer un bon plan, et SQL Server 2022 sait gérer plusieurs plans pour une même requête.
• Des requêtes qui empêchent l'usage des index : fonction appliquée à une colonne dans le WHERE, conversion implicite de type, LIKE commençant par un joker.
• Une maintenance trop lourde : reconstruire tous les index chaque nuit ou chaque dimanche consomme beaucoup d'I/O et de journal pour un gain souvent faible, alors que la mise à jour des statistiques, elle, est parfois oubliée.
• Une instance mal réglée : mémoire maximale non plafonnée, parallélisme par défaut, tempdb sous-dimensionnée.

Et le matériel ?

Ajouter des cœurs est la solution la plus chère : les licences SQL Server se paient au cœur, et une requête qui lit cent fois trop de pages le fera aussi sur un serveur neuf. Le matériel devient la bonne réponse quand les mesures le montrent : CPU saturé après optimisation des requêtes les plus coûteuses, latence de stockage mauvaise malgré une mémoire correcte, volumétrie qui a réellement changé d'échelle.

En résumé

1. Qualifier la lenteur et noter un exemple précis et daté.
2. Lire les statistiques d'attente, en total et en moyenne.
3. Identifier la poignée de requêtes qui fait l'essentiel de la charge.
4. Contrôler les blocages, la mémoire et la latence de stockage.
5. Corriger une chose à la fois, et mesurer le gain sur l'exemple de départ.

C'est exactement la démarche de nos missions d'optimisation des performances SQL Server : mesurer, isoler, corriger, puis prouver le gain, sans changer de serveur quand ce n'est pas nécessaire.