When SQL Server behaves differently in the application

A query works in SSMS but fails or slows down in the application. Copying the text into a new window does not reproduce the whole request. The connection has settings, the driver sends parameter metadata, and a stored module can carry options captured when it was created. Those differences belong in the evidence.

Reproduce interpretation before tuning

The example shows the same date string interpreted under two DATEFORMAT settings. It saves and restores the session's original date format.

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;

The first result is 3 April 2023 and the second is 4 March 2023. Both conversions succeed, so a test that checks only for errors will miss the disagreement. A wrong but valid date can be more damaging than an obvious conversion failure.

Prefer typed date parameters for application input. When a text interchange format is unavoidable, define an unambiguous representation and an explicit conversion style appropriate to it. Do not depend on a developer's current language setting. DATEFIRST can similarly affect weekday calculations; a report that assumes a particular numeric weekday needs an explicit contract.

Run the diagnostic portion through the actual application connection where possible. Values obtained in an unrelated administrator session describe that session, not the failing one. Capture database name, compatibility level, transaction isolation, and parameter types as well. A varchar parameter and an nvarchar parameter with the same visible text are not the same request metadata.

Separate runtime state from module metadata

Some SET options influence execution, while others affect parsing or are saved with module creation. QUOTED_IDENTIFIER determines how double-quoted text is interpreted. For stored procedures, relevant captured settings can be inspected through sys.sql_modules, including uses_quoted_identifier and uses_ansi_nulls.

Changing an option in a troubleshooting window does not necessarily rewrite a stored procedure's captured metadata. If a migration created a module under unintended settings, fix the creation or alteration script deliberately and verify the stored values afterward. Keep string literals single-quoted and use consistent identifier conventions rather than relying on ambiguous quotation.

Do not teach old NULL behavior as a modern workaround. SQL Server 2017 and later always use ANSI_NULLS ON behavior. Comparisons with NULL should use IS NULL or IS NOT NULL. A legacy script containing SET ANSI_NULLS OFF is not a reliable way to restore an obsolete application assumption.

Connection pooling adds another reason to be explicit. Initialization and reset behavior belong to the provider, and an application's supported connection path should establish the settings it requires. Avoid a design in which one unrelated request silently prepares session state for another.

Diagnose plan differences without a magic switch

Certain SET options participate in plan-cache context. Different contexts can result in different cached plans for apparently identical SQL. That observation does not prove that toggling ARITHABORT is the correct performance fix. Different compilation parameters, estimates, and data access choices may explain the actual runtime difference.

Capture the plans from both contexts and compare parameter metadata, compiled values, row estimates, and measured reads. Reproduce representative application values in the same context before drawing conclusions. An interactive test with a selective customer does not explain a production request for a very large customer.

Some indexed features also require a specific set of options. For example, filtered indexes and indexes on computed columns have requirements involving options such as ANSI_WARNINGS, QUOTED_IDENTIFIER, and ARITHABORT, with NUMERIC_ROUNDABORT OFF. Verify the complete documented set for the feature rather than fixing the first error by changing a random option.

Be equally careful with transaction-related state. IMPLICIT_TRANSACTIONS can cause work to remain open longer than a developer expects, while LOCK_TIMEOUT controls lock waiting rather than total command duration. A difference in these settings can change failure and blocking behavior even without a different plan.

Finish by placing the required settings in the supported initialization and deployment paths, then test both application and administrative execution. Save the observed settings with the incident record. The goal is a reproducible request contract, so 'works in my query window' becomes a useful comparison instead of the end of the investigation.

Technical references: Microsoft Learn: SET statements · Microsoft Learn: SET DATEFORMAT · Microsoft Learn: SET QUOTED_IDENTIFIER.