Entity Framework 4.2 exec sp_executesql не использует индексы (сниффинг параметров)

Я сталкиваюсь с некоторыми серьезными проблемами с производительностью простых запросов SQL, генерируемых Entity Framework (4.2), работающих на SQL Server 2008 R2. В некоторых ситуациях (но не во всех) EF использует следующий синтаксис:

exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE', @param1...

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

После небольшого исследования кажется, что проблема может быть вызвана "анализом параметров". Если я добавлю подсказку запроса OPTION(RECOMPILE) примерно так:

exec sp_executesql 'DYNAMIC-SQL-QUERY-HERE OPTION(RECOMPILE)', @param1...

Используются индексы на целевых таблицах, и запрос выполняется очень быстро. Я также попытался включить флаг трассировки, используемый для отключения отслеживания параметров (4136) в экземпляре базы данных ( http://support.microsoft.com/kb/980653), однако, похоже, это никак не повлияло.

Это оставляет меня с несколькими вопросами:

  1. Есть ли способ добавить подсказку запроса OPTION(RECOMPILE) к SQL, сгенерированному Entity Framework?
  2. Есть ли в любом случае запретить Entity Framework использовать exec sp_executesql и вместо этого просто запустить сырой SQL?
  3. Кто-нибудь еще сталкивается с этой проблемой? Любые другие советы / подсказки?

Дополнительная информация:

  1. Я перезапустил экземпляр базы данных через SSMS, однако попробую перезапустить службу из консоли управления службами.
  2. Параметризация установлена ​​на ПРОСТОЙ (is_parameterization_forced: 0)
  3. Оптимизировать для adhoc рабочих нагрузок имеет следующие настройки
    • значение: 0
    • минимум: 0
    • максимум: 1
    • value_in_use: 0
    • is_dynamic: 1
    • is_advanced: 1

Я должен также упомянуть, что если я перезапущу службу SQL Server через консоль управления службами, ПОСЛЕ включения флага трассировки 4136 с помощью приведенного ниже сценария, по-видимому, фактически очищает флаг трассировки... возможно, мне следует сделать это по-другому...

DBCC TRACEON(4136,-1)
Источник

2 ответа

Решение

На данный момент я бы порекомендовал:

Установите для параметра оптимизации для специальных рабочих нагрузок значение true.

EXEC sp_configure 'show advanced', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'optimize for ad hoc', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

Если через какое-то время эта настройка, кажется, не помогла, только тогда я бы попробовал дополнительную поддержку флага трассировки. Они обычно зарезервированы как последнее средство. Установите флаг трассировки с помощью командной строки через диспетчер конфигурации SQL Server, в отличие от окна запроса и с использованием глобального флага. См. http://msdn.microsoft.com/en-us/library/ms187329.aspx

Источник

ТЛ; др

update statistics

У нас был delete запрос с одним параметром (первичным ключом), который занимает ~7 секунд для завершения при вызове через EF и sp_executesql, Выполнение запроса вручную с параметром, встроенным в первый аргумент sp_executesql заставил запрос выполняться быстро (~0,2 секунды). Добавление option (recompile) тоже сработало. Конечно, эти два обходных пути нам недоступны, так как мы использовали EF.

Вероятно, из-за каскадных ограничений внешнего ключа план выполнения для долгосрочного запроса был, ну... огромным. Когда я посмотрел на план выполнения в SSMS, я заметил, что стрелки между различными шагами в некоторых случаях были шире, чем в других, что, возможно, указывает на то, что SQL Server испытывал проблемы с принятием правильных решений. Это заставило меня задуматься о статистике. Я посмотрел на шаги в плане выполнения, чтобы увидеть, какая таблица была задействована в подозрительных шагах. Потом я побежал update statistics Table для этого стола. Затем я повторно запустил плохой запрос. И я снова запустил его. И снова просто чтобы убедиться. Это сработало. Наш перфоманс вернулся к норме. (Все еще несколько хуже, чемsp_executesql производительность, но эй!)

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

Источник
Другие вопросы по тегам