Почему SQL работает иначе в приложении и SSMS

Запрос работает в SSMS, но ошибается или замедляется в приложении. Копирование текста не воспроизводит весь запрос. У соединения есть настройки, драйвер передает метаданные параметров, а процедура может хранить значения, зафиксированные при создании. Эти различия нужно включать в диагностику.

Сначала проверить интерпретацию

Пример преобразует одну строку даты при двух DATEFORMAT. Исходное значение сессии сохраняется и затем восстанавливается.

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;

Первый результат означает 3 апреля 2023 года, второй 4 марта 2023 года. Оба преобразования успешны. Тест только на отсутствие ошибок не обнаружит расхождение. Допустимая, но неверная дата иногда опаснее явного отказа преобразования.

Используйте типизированные параметры даты. Если нужен текстовый обмен, задайте однозначный формат и соответствующий явный стиль преобразования. Не полагайтесь на язык разработчика. DATEFIRST также влияет на вычисления дня недели, поэтому числовые обозначения дней требуют договоренности.

По возможности выполняйте диагностику через настоящее соединение приложения. Значения отдельной администраторской сессии описывают только ее состояние. Сохраните также базу, уровень совместимости, изоляцию и типы параметров. varchar и nvarchar с одинаковым видимым текстом имеют разные метаданные.

Разделить состояние сессии и модуля

Часть SET-настроек влияет на выполнение, другие участвуют в разборе или сохраняются при создании модуля. QUOTED_IDENTIFIER определяет интерпретацию двойных кавычек. В sys.sql_modules доступны uses_quoted_identifier и uses_ansi_nulls хранимой процедуры.

Изменение настройки в диагностическом окне не обязательно переписывает сохраненные параметры процедуры. Если миграция создала модуль с неверными настройками, исправьте CREATE или ALTER явно и проверьте итоговые метаданные. Используйте одинарные кавычки для строк и согласованные правила идентификаторов.

Старое поведение NULL не является современным обходным решением. SQL Server 2017 и новее всегда использует ANSI_NULLS ON. Пишите IS NULL или IS NOT NULL. Историческое SET ANSI_NULLS OFF не восстанавливает надежно устаревшие предположения приложения.

Пул соединений также требует определенности. Инициализация и сброс зависят от провайдера. Поддерживаемый путь приложения должен устанавливать необходимые параметры, не рассчитывая на случайную подготовку сессии предыдущим запросом.

Объяснить разницу планов

Некоторые SET-параметры входят в контекст кэша планов. Разные контексты могут давать разные планы для внешне одинакового SQL. Но это не доказывает, что переключение ARITHABORT является правильной оптимизацией. Причиной могут оказаться параметры компиляции, оценки и пути доступа.

Сохраните оба плана, сравните типы, скомпилированные значения, оценки строк и измеренные чтения. Воспроизведите характерные значения приложения в том же контексте. Интерактивный тест маленького клиента не объясняет рабочий запрос для огромного клиента.

Некоторые индексируемые возможности требуют определенного набора настроек. Фильтрованные индексы и индексы вычисляемых столбцов имеют требования, затрагивающие ANSI_WARNINGS, QUOTED_IDENTIFIER, ARITHABORT и NUMERIC_ROUNDABORT OFF. Проверяйте полный документированный набор для конкретной возможности, а не меняйте случайный параметр после ошибки.

Транзакционные настройки важны не меньше. IMPLICIT_TRANSACTIONS может оставлять работу открытой дольше ожидаемого. LOCK_TIMEOUT ограничивает ожидание блокировок, а не общую длительность команды. Поведение ошибок и блокировок меняется даже без нового плана.

В конце закрепите необходимые настройки в инициализации и развертывании, затем проверьте приложение и административный путь. Сохраните наблюдения в записи инцидента. Полезно включить в диагностический запрос версию драйвера и точное объявление параметров на стороне приложения: эти сведения часто теряются при ручном копировании текста. Цель состоит в воспроизводимом контракте запроса, при котором сравнение с SSMS дает проверяемую причину, а не только спор о том, где код работает.

Техническая документация: Microsoft Learn: SET statements · Microsoft Learn: SET DATEFORMAT · Microsoft Learn: SET QUOTED_IDENTIFIER.