Book Online:
Параметр optimize for ad hoc workloads используется для повышения эффективности кэширования планов рабочих нагрузок, содержащих много отдельных нерегламентированных пакетов. Если этот параметр имеет значение 1, компонент Database Engine при первой компиляции пакета, сохраняет в кэше планов небольшую скомпилированную заглушку плана, а не полный откомпилированный план. Это несколько снижает требования к памяти, так как кэш планов не заполняется скомпилированными, не используемыми повторно планами.
Откомпилированная заглушка плана позволяет компоненту Database Engine понять, что данный нерегламентированный пакет компилировался ранее, но от него сохранилась только заглушка. При повторном вызове этого пакета для компиляции или выполнения компонент Database Engine откомпилирует пакет, удалит из кэша планов откомпилированную заглушку плана и добавит туда полный скомпилированный план.
Когда параметру optimize for ad hoc workloads присваивается значение 1, это влияет только на новые планы; те планы, которые уже находятся в кэше планов, остаются неизменными.
Скомпилированная заглушка плана принадлежит к объектам cacheobjtypes, которые можно просмотреть в представлении каталога sys.dm_exec_cached_plans. У каждой заглушки есть уникальный дескриптор SQL и дескриптор плана. Со скомпилированной заглушкой плана не связан план выполнения. Запрос по дескриптору плана не вернет XML-код Showplan.
Объяснение:
Каждая инструкция (T-SQL, SP и другие) сохраняет свой план выполнения в кэше, для дальнейшего использования. По этой причине, большое число планов хранится в системе и множество из них могут использоваться только 1 раз. Это в пустую тратит ресурсы сервера.
В загруженной системе легко может быть более миллиона разных инструкций, представьте сколько ресурсов вы тратите в пустую. Благодаря параметру optimize for ad hoc workloads, можно избежать хранения ненужных планов.
Как включить:
USE master EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'optimize for ad hoc workloads', 1 RECONFIGURE WITH OVERRIDE GO
Включение данной опции не сбрасывает текущие планы, поэтому необходимо будет выполнить команду, которая сбросить весь кэш:
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
Но будьте осторожны, данная операция может на время замедлить работу сервера, так как ему необходимо будет построить все планы запросов заново.
Чтобы не сбрасывать абсолютно весь кэш, можно сбросить кэш только конкретной базы данных:
DBCC FLUSHPROCINDB(db_id);
