Перестроение плана выполнения запросов SQL Server

У меня есть этот запрос, который выполняется через Linq to Entities. При первом запуске запроса создается план выполнения, который занимает чуть менее 2 минут. После кэширования плана запрос занимает 1-2 секунды. У меня проблема в том, что план обновляется каждые несколько часов, и я не уверен, почему это происходит?

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

var data = from row in mgr.ServiceDesk_RequestEvent
    .Include("ServiceDesk_Event")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleOperator")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleConstraintField")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Rule.ServiceDesk_RuleConstraintValue")
    .Include("ServiceDesk_Event.ServiceDesk_SLAEventRule.ServiceDesk_RuleSet.ServiceDesk_Action")
    .Include("ServiceDesk_Request")
    .Include("ServiceDesk_Request.People_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestCategory")
    .Include("ServiceDesk_Request.ServiceDesk_RequestCategory.ServiceDesk_SLA")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups.Security_Role.Security_UserRoles")
    .Include("ServiceDesk_Request.ServiceDesk_RequestRole_Groups.Security_Role.Security_UserRoles.Security_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestPriority")
    .Include("ServiceDesk_Request.Offices_User")
    .Include("ServiceDesk_Request.ServiceDesk_RequestTechnicians")
    .Include("ServiceDesk_Request.ServiceDesk_RequestTechnicians.People")
    where row.Completed == false && row.Deleted == false
    select row;

Я не хочу вставлять сюда сгенерированный t-sql, так как он довольно большой. Если у кого-то есть идеи, пожалуйста, не стесняйтесь вносить свой вклад.

Благодарю вас.


tsql sql-server-2008 linq-to-entities
person Lukasz    schedule 23.11.2009    source источник
comment
Ты прав. Это выглядит безумно. На самом деле вам нужно каждое поле каждого объекта, который вы включаете, и вы собираетесь обновлять каждый отдельный экземпляр, и который вы профилировали и обнаружили, что это быстрее, чем загрузка по индивидуальному требованию? Это единственный разумный аргумент, который я могу придумать для этого вместо проецирования (мой первый выбор для случаев использования только для чтения) или разбиения на более мелкие запросы.   -  person Craig Stuntz    schedule 23.11.2009

Ответы (2)


arrow_upward
1
arrow_downward

Возможная причина заключается в том, что ваш сервер испытывает нехватку памяти, из-за чего кэш плана запроса перезапускается быстрее.

Сколько оперативной памяти на сервере?

Перечитывая ваш вопрос, эта строка немного беспокоит: «При первом запуске запроса создается план выполнения, который занимает чуть менее 2 минут». 2 минуты в первый раз для простого запроса - это долго. Запускаете ли вы какие-либо другие приложения на сервере SQL Server (надеюсь, нет)?

Я предлагаю вам отслеживать встроенные счетчики производительности: SQL Server, Plan Cache Counters .

Удаление планов выполнения из кэша процедур

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

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

При нехватке памяти компонент Database Engine реагирует удалением планов выполнения из кэша процедур. Чтобы определить, какие планы следует удалить, Компонент Database Engine многократно проверяет состояние каждого плана выполнения и удаляет планы, когда их текущая стоимость равна нулю. План выполнения с нулевой текущей стоимостью не удаляется автоматически при нехватке памяти; он удаляется только тогда, когда Компонент Database Engine проверяет план и текущая стоимость равна нулю. При проверке плана выполнения компонент Database Engine приближает текущую стоимость к нулю, уменьшая текущую стоимость, если запрос в данный момент не использует план.

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

Компонент Database Engine использует монитор ресурсов и пользовательские потоки для освобождения памяти из кэша процедур в ответ на нехватку памяти. Монитор ресурсов и пользовательские потоки могут проверять планы, выполняемые одновременно, чтобы снизить текущую стоимость каждого неиспользуемого плана выполнения. Монитор ресурсов удаляет планы выполнения из кэша процедур, когда существует глобальная нехватка памяти. Он освобождает память для применения политик для системной памяти, памяти процессов, памяти пула ресурсов и максимального размера для всех кэшей.

Максимальный размер всех кэшей зависит от размера пула буферов и не может превышать максимальный объем памяти сервера. Дополнительные сведения о настройке максимальной памяти сервера см. в описании параметра максимальной памяти сервера в процедуре sp_configure (Transact-SQL).

Если вы еще не видели его: Планирование кэширования в SQL Server 2008

person Mitch Wheat    schedule 23.11.2009
comment
На сервере 8 ГБ оперативной памяти, но он не находится под высокой нагрузкой. Но если есть способ подтвердить это, было бы здорово. - person Lukasz; 23.11.2009

arrow_upward
1
arrow_downward

Эта статья MSDN является хорошим справочником по кешу планов выполнения. Одним из способов оптимизации использования плана выполнения является использование параметризованного SQL вместо жестко заданного/динамического SQL.

e.g.

SELECT * FROM MyTable WHERE ID=@Id

лучше, чем

SELECT * FROM MyTable WHERE ID=1

поскольку один и тот же план можно кэшировать и использовать повторно, независимо от значения @Id. Чем больше повторно используется план выполнения, тем больше вероятность того, что он останется в кеше, поскольку считается полезным.

Я не знаю, какой тип оператора создает LINQ, но об этом стоит помнить. Также, как сказал Митч, чем больше у вас памяти, тем больше вы можете хранить в кеше.

Кроме того, обратите внимание, что здесь работает не только кэширование плана выполнения. У вас также есть кеш данных, производительность которого сильно отличается — после того, как вы выполнили запрос один раз, данные будут храниться в кеше данных, поэтому для последующих вызовов данные уже находятся в памяти — это источник большинства разница в производительности.

person AdaTheDev    schedule 23.11.2009
comment
LINQ to Entities генерирует параметризованный sql, поэтому я не думаю, что это проблема. Спасибо за статью! - person Lukasz; 23.11.2009
comment
Ах хорошо. Просто обратите внимание на мое последнее редактирование о кеше данных - это очень важно для производительности. - person AdaTheDev; 23.11.2009