Pourquoi SQL diffère entre application et SSMS

Une requête fonctionne dans SSMS, mais échoue ou ralentit dans l'application. Copier son texte ne reproduit pas toute la demande. La connexion possède des options, le pilote transmet des types de paramètres et une procédure peut conserver des réglages enregistrés à sa création.

Comparer l'interprétation avant la performance

L'exemple interprète la même date textuelle avec deux DATEFORMAT. Il sauvegarde puis restaure la valeur initiale de la session.

DECLARE @OriginalFormat nvarchar(3);
SELECT @OriginalFormat = date_format
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(date, '03/04/2023') AS InterpretedAsDMY;

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(date, '03/04/2023') AS InterpretedAsMDY;

SET DATEFORMAT @OriginalFormat;

SELECT
    @@SPID AS SessionId,
    @@LANGUAGE AS SessionLanguage,
    @@DATEFIRST AS FirstDayOfWeek,
    @@LOCK_TIMEOUT AS LockTimeoutMs,
    SESSIONPROPERTY('ANSI_WARNINGS') AS AnsiWarnings,
    SESSIONPROPERTY('ARITHABORT') AS ArithAbort,
    SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QuotedIdentifier;

DBCC USEROPTIONS;

Le premier résultat est le 3 avril 2023, le second le 4 mars 2023. Les deux conversions réussissent. Un test limité à l'absence d'erreur ne détecte donc rien. Une date valide mais incorrecte peut être plus dangereuse qu'un échec explicite.

Préférez les paramètres de date typés dans l'application. Si un échange textuel est nécessaire, définissez un format non ambigu et un style de conversion explicite approprié. Ne dépendez pas de la langue du développeur. DATEFIRST peut également modifier les calculs de jours de semaine.

Exécutez la partie diagnostic dans la connexion applicative concernée si possible. Une session administrative indépendante ne décrit pas la session défaillante. Capturez aussi base, compatibilité, isolation et types des paramètres. varchar et nvarchar contenant le même texte visible ne constituent pas les mêmes métadonnées.

Séparer état courant et métadonnées stockées

Certaines options SET influencent l'exécution, d'autres l'analyse syntaxique ou la création des modules. QUOTED_IDENTIFIER définit l'interprétation des doubles guillemets. Pour une procédure, sys.sql_modules expose notamment uses_quoted_identifier et uses_ansi_nulls.

Changer un réglage dans une fenêtre de diagnostic ne réécrit pas automatiquement les métadonnées d'une procédure. Si une migration l'a créée avec de mauvaises options, corrigez intentionnellement son script CREATE ou ALTER puis vérifiez les valeurs stockées. Utilisez des apostrophes simples pour les chaînes et des conventions cohérentes pour les identifiants.

Les anciennes règles NULL ne constituent pas un contournement moderne. SQL Server 2017 et les versions suivantes utilisent toujours ANSI_NULLS ON. Employez IS NULL ou IS NOT NULL. Un ancien SET ANSI_NULLS OFF ne restaure pas de façon fiable les hypothèses historiques d'une application.

Le pooling demande aussi un contrat explicite. Initialisation et réinitialisation relèvent du fournisseur client. Le chemin de connexion pris en charge doit établir les options nécessaires, sans dépendre d'une requête précédente qui aurait préparé la session.

Expliquer les plans sans réglage magique

Certaines options participent au contexte du cache de plans. Des contextes distincts peuvent produire des plans distincts pour un SQL apparemment identique. Cela ne prouve pas que changer ARITHABORT constitue la bonne correction. Paramètres de compilation, estimations et chemins d'accès peuvent expliquer la différence réelle.

Capturez les plans des deux contextes et comparez types, valeurs compilées, estimations et lectures mesurées. Reproduisez des valeurs représentatives de l'application dans le même contexte. Un petit client choisi dans SSMS n'explique pas une demande de production portant sur un très gros client.

Certaines fonctions indexées imposent également un ensemble d'options. Les index filtrés et ceux sur colonnes calculées ont des exigences comprenant notamment ANSI_WARNINGS, QUOTED_IDENTIFIER, ARITHABORT et NUMERIC_ROUNDABORT OFF. Vérifiez l'ensemble documenté pour la fonction, au lieu de changer une option au hasard après chaque erreur.

Les réglages transactionnels comptent aussi. IMPLICIT_TRANSACTIONS peut laisser une transaction ouverte plus longtemps que prévu, tandis que LOCK_TIMEOUT ne limite que l'attente de verrous. Le comportement peut donc changer sans changement de plan.

Enfin, placez les options requises dans les chemins d'initialisation et de déploiement, puis testez application et administration. Gardez les valeurs observées avec l'incident. Un contrat reproductible transforme 'cela marche dans SSMS' en comparaison utile plutôt qu'en conclusion sans explication.

Références techniques: Microsoft Learn: SET statements · Microsoft Learn: SET DATEFORMAT · Microsoft Learn: SET QUOTED_IDENTIFIER.