ADO.NET: безопасно указывать -1 для SqlParameter.Size для всех параметров VarChar?

У нас есть существующий код С#, который вызывает во многих местах параметризованные запросы SQL Server. Мы никогда не указываем SqlParameter.Size, и он задокументировал, что в этом случае класс SqlParameter запрашивает размер от значения параметра. Недавно мы узнали о проблемах, связанных с загрязнением кеша SQL Server, которые возникают, когда отдельный план кэшируется для каждой отдельной комбинации размеров параметров.

К счастью, всякий раз, когда мы создаем SqlParameter, мы делаем это с помощью единого метода утилиты, поэтому у нас есть возможность добавить несколько строк к этому методу и устранить эту проблему. Мы думаем о добавлении следующего:

if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar))
    m_sqlParam.Size = -1;

Другими словами, каждый раз, когда мы передаем параметр varchar, передаем его как varchar (max). Основываясь на некоторых быстрых тестах, это прекрасно работает, и мы можем видеть (через SQL Profiler и sys.dm_exec_cached_plans), что в каждом кэше для каждого ad-hoc-запроса есть один план, а тип строкового параметра (-ов) теперь varchar (max).

Это похоже на такое легкое решение, что должен быть скрытый, разрушающий производительность. Кто-нибудь знает об этом?

(Обратите внимание, что нам нужно только поддерживать SQL Server 2008 и более поздние версии.)

Обновление (16 января)

Да, есть скрытый, снижающий производительность недостаток!

Большое спасибо Мартину Смиту, чей ответ (см. ниже) показал мне правильный способ проанализировать это. Я тестировал нашу таблицу пользователей приложения, в которой есть столбец электронной почты, определенный как nvarchar (100), и имеет некластеризованный индекс (IX_Users_Email) в столбце Электронная почта. Я изменил пример запроса Мартина следующим образом:

declare @a nvarchar(max) = cast('a' as nvarchar(max))
--declare @a nvarchar(100) = cast('a' as nvarchar(100))
--declare @a nvarchar(4000) = cast('a' as nvarchar(4000))

select Email from Users where Email = @a

В зависимости от того, какие из выражений "declare" я не комментирую, я получаю ОЧЕНЬ другой план запросов. Варианты nvarchar (100) и nvarchar (4000) дают мне индекс искать на IX_Users_Email - на самом деле любая длина, указанная мной, дает мне тот же план. С другой стороны, версия nvarchar (max) дает мне индекс scan на IX_Users_Email, за которым следует оператор Filter для применения предиката.

Это достаточно для меня - если есть возможность получить сканирование, а не поиск, то это "лечение" хуже, чем болезнь.

Новое предложение

Я заметил, что каждый раз, когда SQL Server параметризует запрос с параметром varchar, в кэшированном плане просто используется varchar (8000) (или nvarchar (4000)) для параметра. Я считаю, что это достаточно хорошо для SQL Server, это достаточно хорошо для меня! Заменив код С# в моем исходном вопросе (см. Выше), выполните следующие действия:

if(sqlDbType == SqlDbType.VarChar)
    m_sqlParam.Size = 8000;
else if(sqlDbType == SqlDbType.NVarChar)
    m_sqlParam.Size = 4000;

Это, похоже, решает проблему загрязнения кэша плана без того же влияния на планы запросов с использованием размера -1. Тем не менее, я не проводил много испытаний с этим, и мне очень интересно услышать, как кто-либо вводит этот пересмотренный подход.

Обновление (24 сентября)

Нам пришлось изменить предыдущую версию (Новое предложение, выше), чтобы обработать случай, когда значение параметра больше максимального. В этот момент у вас нет выбора, кроме как сделать его varchar (max):

if((sqlDbType == SqlDbType.VarChar) || (sqlDbType == SqlDbType.NVarChar))
{
    m_sqlParam.Size = (sqlDbType == SqlDbType.VarChar) ? 8000 : 4000;

    if((value != null) && !(value is DBNull) && (value.ToString().Length > m_sqlParam.Size))
        m_sqlParam.Size = -1;
}

Мы используем эту версию около шести месяцев без проблем.

Ответы

Ответ 1

Он не идеален в том, что лучше всего указать параметр, соответствующий типу данных столбца (ов).

Вам нужно будет проверить ваши планы запросов, чтобы убедиться, что они все еще выглядят разумными.

Пробуждение следующего теста

CREATE TABLE #T
(
X VARCHAR(10) PRIMARY KEY
)


DECLARE @A VARCHAR(MAX) = CAST('A' AS VARCHAR(MAX))

SELECT *
FROM #T 
WHERE X = @A

Дает такой план, как

Plan

SQL Server добавляет вычислительный скаляр в план, который вызывает внутреннюю функцию GetRangeWithMismatchedTypes и по-прежнему удается выполнить поиск индекса (более подробная информация о неявных преобразованиях здесь).

Пример счетчика, в котором он имеет значение, показан в статье Почему не работает ли отказ в разделении?. Поведение, описанное в этой статье, также применяется для параметра varchar(max) для таблицы, разбитой на столбец varchar(n).