i

Description

Les dégâts d'une crue

Source

Unidentified National Park Service photographer · page d’origine

Licence

PD Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Cas catastrophiques : le SQL est propre, l'assemblage est faux

Trois traitements dont aucune requête n'est mauvaise, et qui pourtant ne passent pas à l'échelle.

En bref

• A · Valorisation : E1 E2 E3 E4 E5 E13.
• B · Hiérarchie : E1 E4 E5 E13.
• C · Habilitations : E1 E2 E5 E11.
• Remède commun : préparer ce que la boucle recalculait, puis très peu de requêtes.

Sur cette page

1. Les trois cas
2. Ce que les trois ont en commun
3. Comment les mesures ont été prises

Trois traitements réalistes dont chaque requête, prise isolément, est propre : ensembliste à l'intérieur, indexée, typée, relue. Ils échouent tous de la même façon : une erreur d'assemblage, souvent plusieurs à la fois. Pour chacun : la version procédurale complète, la raison de la catastrophe (en renvoyant aux erreurs de conception), les mesures, et la refonte par la démarche ensembliste : une préparation, puis très peu de requêtes, sans itération.

Les trois cas#

Ce que les trois ont en commun#

1. La revue de code ne trouve rien. Chaque brique, regardée seule, est conforme aux bonnes pratiques.
2. Le test unitaire passe. Un client, un nœud, un utilisateur : quelques millisecondes.
3. La boucle est ailleurs que dans la requête : dans l'orchestrateur, dans la fonction appelée par ligne, dans la récursion lancée par nœud.
4. Le coût est proportionnel au nombre d'itérations, et parfois à leur carré, parce que chaque tour relit une structure qui grossit avec le nombre de tours.
5. La correction n'est pas une optimisation. On ne rend pas la boucle plus rapide : on la supprime, en préparant ce que la boucle recalculait.

Comment les mesures ont été prises#

Quatre indicateurs, lus après avoir vidé le cache de plans : les instructions exécutées (somme de execution_count dans sys.dm_exec_query_stats), les appels de fonction (sys.dm_exec_function_stats), les lectures logiques et le CPU. Les trois premiers sont reproductibles ; le CPU et la durée ne le sont qu'en ordre de grandeur.

-- Gabarit de mesure (instance de TEST uniquement : DBCC FREEPROCCACHE vide le cache de plans de toute l'instance)
DBCC FREEPROCCACHE;
GO
DECLARE @t0 datetime2(7) = SYSDATETIME();

-- ... le traitement à mesurer, dans ce même lot ...

SELECT DATEDIFF(MILLISECOND, @t0, SYSDATETIME())                                   AS duree_ms,
       (SELECT SUM(execution_count)          FROM sys.dm_exec_query_stats)          AS instructions,
       (SELECT ISNULL(SUM(execution_count),0) FROM sys.dm_exec_function_stats)      AS appels_fonctions,
       (SELECT SUM(total_logical_reads)      FROM sys.dm_exec_query_stats)          AS lectures_logiques,
       (SELECT SUM(total_worker_time) / 1000 FROM sys.dm_exec_query_stats)          AS cpu_ms;