Почему я НЕ должен использовать параметр SQL Server «Оптимизировать для специальных рабочих нагрузок»?

Я читал некоторые замечательные статьи о кешировании SQL Server от Kimberly Tripp, таких как: http://www.sqlskills.com/blogs/kimberly/plan -cache-и оптимизирующий-для-ADHOC рабочих нагрузок /

Почему существует даже опция «оптимизировать для специальных рабочих нагрузок»? Разве это не всегда должно быть? Независимо от того, используют ли разработчики ad-hoc SQL или нет, почему бы вам не включить этот параметр для каждого экземпляра, поддерживающего его (SQL 2008+), тем самым уменьшив кеш-память?

46 голосов | спросил SomeGuy 26 FebruaryEurope/MoscowbTue, 26 Feb 2013 22:43:44 +0400000000pmTue, 26 Feb 2013 22:43:44 +040013 2013, 22:43:44

5 ответов


42

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

Да, оптимизация для рабочих нагрузок adhoc отлично подходит для уменьшения раздувания кеша плана - но всегда проверяйте его в первую очередь!

[Редактировать: Кален Делани рассказывает интересный анекдот о том, что она спросила одного из своих друзей-инженеров Microsoft, будут ли обстоятельства, когда было бы нецелесообразно включить это. Он возвращается через несколько дней, чтобы сказать - представьте приложение, в котором есть много разных запросов, и каждый запрос выполняется ровно дважды. Тогда это может быть неуместно. Достаточно сказать, что таких приложений не так много!]

[Изменить: если большинство ваших запросов выполняются более одного раза (не совсем дважды); это, вероятно, будет неуместным. Общее правило состоит в том, чтобы включить его, если в базе данных имеется много одноразовых запросов adhoc; тем не менее, все еще не так много приложений.]

ответил Peter Schofield 26 FebruaryEurope/MoscowbTue, 26 Feb 2013 23:28:19 +0400000000pmTue, 26 Feb 2013 23:28:19 +040013 2013, 23:28:19
18

Ниже приведен небольшой код, который поможет вам решить, выгодно ли использовать «переключение для специальных рабочих нагрузок ВКЛ /ВЫКЛ». Обычно мы проверяем это как часть нашей проверки работоспособности для внутренних и клиентских серверов.

Это самый безопасный вариант для включения и описан хорошо с помощью Brad здесь и Гленн Берри здесь .

  --- для 2008 и выше .. Оптимизация ad-hoc для рабочей нагрузки
ЕСЛИ СУЩЕСТВУЕТ (
        - это на 2008 год и выше
        ВЫБРАТЬ 1
        FROM sys.configurations
        WHERE NAME = 'оптимизация для специальных рабочих нагрузок'
        )
НАЧАТЬ
    DECLARE @AdHocSizeInMB DECIMAL (14, 2)
        , @ TotalSizeInMB DECIMAL (14, 2)
        , @ ObjType NVARCHAR (34)

    SELECT @AdHocSizeInMB = СУММ (CAST ((
                    ДЕЛО
                        КОГДА usecounts = 1
                            И НИЖНИЙ (objtype) = 'adhoc'
                            THEN size_in_bytes
                        ELSE 0
                        КОНЕЦ
                    ) AS DECIMAL (14, 2))) /1048576
        , @ TotalSizeInMB = СУММ (CAST (size_in_bytes AS DECIMAL (14, 2))) /1048576
    FROM sys.dm_exec_cached_plans

    SELECT 'Конфигурация SQL Server' AS GROUP_TYPE
        , 'Общий размер плана кеша (МБ):' + cast (@TotalSizeInMB AS VARCHAR (max)) + '. Текущая память, занятая adhoc-планами, используется только один раз (МБ): '+ cast (@AdHocSizeInMB AS VARCHAR (max)) +'. Процент общей схемы кэша, занятой adhoc-планами, используется только один раз: '+ cast (CAST ((@ AdHocSizeInMB /@TotalSizeInMB) * 100 AS DECIMAL (14, 2)) AS VARCHAR (max)) +'% '+' 'AS КОММЕНТАРИИ
        , '' + CASE
            WHEN @AdHocSizeInMB> 200
                OR ((@AdHocSizeInMB /@TotalSizeInMB) * 100)> 25 - 200 МБ или> 25%
                THEN «Включить оптимизацию для специальных рабочих нагрузок, поскольку это будет иметь существенное значение. Ссылка: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx»
            ELSE 'Настройка Оптимизация для специальных рабочих нагрузок будет иметь небольшое значение!'
            END + '' РЕКОМЕНДАЦИИ
КОНЕЦ
 
ответил Kin 26 MarpmTue, 26 Mar 2013 20:43:35 +04002013-03-26T20:43:35+04:0008 2013, 20:43:35
6

Подумайте о производственном сервере, который обслуживает только 5 разных запросов, но несколько тысяч из них в секунду. Вы являетесь разработчиком Microsoft SQL Server. Вы собираетесь играть с кэшированием плана. Вы включаете это поведение по умолчанию, когда знаете, что некоторые из ваших самых больших и наиболее важных клиентов (например, внутренняя реализация SAP SAP) работают в одном кампусе и используют тот же самый кафетерий, который вы делаете?

ответил Stu 26 FebruaryEurope/MoscowbTue, 26 Feb 2013 23:34:04 +0400000000pmTue, 26 Feb 2013 23:34:04 +040013 2013, 23:34:04
6

Когда вы включаете опцию Оптимизировать для специальных рабочих нагрузок ", вы будете вызывать временные запросы, которые выполняются во второй раз, так же медленно, как и первый, потому что вы будете Компиляция плана выполнения и вытаскивание тех же данных (без кэширования) в первые два раза.
Это не может быть большой проблемой, но вы заметите это при тестировании запросов.
Итак, что происходит сейчас, без эта опция включена, а кеш с 1-Off-Ad-Hoc-запросами?

Алгоритм управления кэшированием:

С введением этой функции оптимизации был также обновлен алгоритм управления кешированием.
Статья Кимберли Триппа также ссылается на сообщение Калена Делани об этом изменении алгоритма.
Она объясняет это лучше всего:

  

В результате изменения вычисляется размер кеша плана, на котором SQL Server   признает, что есть давление памяти, и оно начнет удалять   планы из кеша. Планы, которые нужно удалить, - это дешевые планы, которые   не были повторно использованы, и это ХОРОШАЯ ВЕЩЬ.

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

Итак, теперь вопрос:

   " Почему нам нужно« Оптимизировать для специальных рабочих нагрузок », когда SQL Server позаботится об удалении неиспользуемых планов при необходимости? »

Мой ответ на это: если вы регулярно используете s-тонну динамических sql-генерации непараметрированных запросов ad-hoc, тогда имеет смысл включить эту функцию.
Вы хотите избежать нагрузки на системные ресурсы, чтобы он принудительно удалил кеш-план /данные после того, как вы исчерпали максимальное пространство кэш-памяти.

Как узнать, когда мне нужно включить это?

Вот запрос, который я написал, чтобы показать вам, сколько Ad-Hoc-планов вы в настоящее время кэшировали и сколько дискового пространства они ели (результаты будут меняться в течение дня - так проверьте его во время большой нагрузки)

  - отличный запрос для использования аргумента «Оптимизация для специальных рабочих нагрузок»:
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST ((S.Total_Plan_1_Use * 1.0 /S.Total_Plan) как десятичный (18,2)) [Pct_Plan_1_Use],
       S.Total_MB_1_Use, S.Total_MB,
       CAST ((S.Total_MB_1_Use * 1.0 /S.Total_MB) как десятичный (18,2)) [Pct_MB_1_Use]
  ИЗ
  (
    SELECT CP.objtype [CacheType],
           COUNT (*) [Total_Plan],
           SUM (CASE WHEN CP.usecounts> 2 THEN 1 ELSE 0 END) [Total_Plan_3orMore_Use],
           SUM (CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END) [Total_Plan_2_Use],
           SUM (CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END) [Total_Plan_1_Use],
           CAST ((SUM (CP.size_in_bytes * 1.0) /1024/1024) как десятичный (12,2)) [Total_MB],
           CAST ((SUM (CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      /1024/1024) как десятичное (18,2)) [Всего_MB_1_Use],
           CAST (AVG (CP.usecounts * 1.0) как десятичный (12,2)) [Avg_Use],
           CAST (AVG (CASE WHEN CP.usecounts> 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) как десятичный (12,2)) [Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans как CP
     ГРУППА ПО CP.objtype
  ) AS S
 ORDER BY S.CacheType
 

Результаты: введите описание изображения здесь

Я не собираюсь говорить: « Когда у вас есть XM или " Если X% вашей Ad Hoc являются одноразовыми ", чтобы включить это.
Это не влияет на Sprocs, Triggers, Views или Parameterized /Подготовленный SQL - только Ad-Hoc-запросы.
Моя личная рекомендация заключается в том, чтобы просто включить в свою среду Prod, но подумайте о том, чтобы оставить ее в своей среде разработки. Я говорю только только для Dev, потому что, если вы оптимизируете запрос, который занимает минуту или больше для запуска, то вы не хотите запускать его 3 раза, прежде чем вы сможете увидеть, как быстро он будет используйте с ним кешированный - каждый один раз, когда вы его отредактируете, чтобы найти лучший оптимизационный проект.
Если ваша работа не включает в себя выполнение всего этого дня, то сходите с ума и попросите своего администратора базы данных включить его везде.

ответил MikeTeeVee 26 MarpmTue, 26 Mar 2013 20:26:34 +04002013-03-26T20:26:34+04:0008 2013, 20:26:34
0

«Почему я НЕ МОГУ использовать ...» В ходе некоторых проверок эффективности вытягивание планов из кэш плана почти в реальном времени, в то время как наблюдение за использованием ресурсов может быть очень полезным. «Оптимизировать для рабочих нагрузок adhoc» может нарушить это, поскольку планы adhoc stub не возвращают план при запросе кеша. В таком случае, если запрос и план не могут быть идентифицированы иным образом, настройка может быть отключена и включена снова для расследования. Обратите внимание, что изменение настроек запросов эффектов, скомпилированных с этой точки вперед. Кроме того, всякий раз, когда вы изменяете свойство «server», проверяйте экземпляр nonprod в той же версии, чтобы проверить, будет ли изменение сбросить кеш-план. Я лично ненавижу удивляться тому. (Например, изменение maxdop на уровне сервера обычно очищает кеш-план, а изменение доп-файла в Resource Governor - нет.)

«Скомпилированный плановый план не имеет плана выполнения, связанного с ним, и запрос обработчика плана не вернет XML Showplan». http://technet.microsoft.com/en-us/library/cc645587.aspx

ответил sql_handle 3 Maypm14 2014, 16:23:52

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132