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

Я сталкиваюсь с некоторыми серьезными проблемами производительности с простыми 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...

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

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

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

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

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

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

DBCC TRACEON(4136,-1)

Ответ 1

В этот момент я бы рекомендовал:

Установите для параметра оптимизация для заданных рабочих нагрузок значение 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

Ответ 2

TL;DR

update statistics

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

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

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