разница в планах выполнения на сервере UAT и PROD

Я хочу понять, почему будет такая огромная разница в выполнении одного и того же запроса на UAT (выполняется за 3 секунды) и PROD (за 23 секунды).

Оба UAT и PROD имеют точно данные и индексы.

QUERY

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) ) 

ON UAT:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

введите описание изображения здесь

В PROD:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

введите описание изображения здесь>> </p>

<p> Обратите внимание, что в PROD запрос предлагает отсутствующий индекс, и это полезно, поскольку я тестировал его, но это не является предметом обсуждения. </p>

<p> <strong> Я просто хочу понять, что:
ON UAT - почему сервер sql создает рабочую таблицу и на PROD этоне ? Он создает настольную катушку на UAT, а не PROD. Кроме того, почему время выполнения отличается от UAT и PROD? </Strong> </p>

<p> Примечание: </p>

<p> Я запускаю RTM сервера R2 R2 RTM на обоих серверах (довольно скоро собираюсь запланировать с последним SP). </p>

<p> UAT: максимальная память 8 ГБ. MaxDop, аффинность процессора и максимальные рабочие потоки равны 0. </p>

<pre><code>Логическая карта физического процессора:
* ------- Физический процессор 0
- * ------ Физический процессор 1
- * ----- Физический процессор 2
--- * ---- Физический процессор 3
------ --- Физический процессор 4
----- * - Физический процессор 5
------ * - Физический процессор 6
------- * Физический процессор 7

Логический процессор на карту сокета:
**** ---- Сокет 0
---- **** Сокет 1

Логический процессор для NUMA Node Map:
******** NUMA Node 0
</code></pre>

<p> PROD: максимальная память 60 ГБ. MaxDop, аффинность процессора и максимальные рабочие потоки равны 0. </p>

<pre><code>Логическая карта физического процессора:
** -------------- Физический процессор 0 (Hyperthreaded)
- ** ------------ Физический процессор 1 (Hyperthreaded)
------ ---------- Физический процессор 2 (Hyperthreaded)
------ ** -------- Физический процессор 3 (Hyperthreaded)
-------- ** ------ Физический процессор 4 (Hyperthreaded)
---------- ** ---- Физический процессор 5 (Hyperthreaded)
------------ ** - Физический процессор 6 (Hyperthreaded)
-------------- ** Физический процессор 7 (Hyperthreaded)

Логический процессор на карту сокета:
******** -------- Розетка 0
-------- ******** Розетка 1

Логический процессор для NUMA Node Map:
******** -------- NUMA Node 0
-------- ******** NUMA Node 1
</code></pre>

<hr>
<p> ОБНОВЛЕНИЕ: </p>

<p> <strong> План выполнения UAT XML: </strong> </p>

<p> <a href= http://pastebin.com/z0PWvw8m

План выполнения PROD XML:

http://pastebin.com/GWTY16YY

План выполнения UAT XML - с помощью плана, созданного для PROD:

http://pastebin.com/74u3Ntr0

Конфигурация сервера:

PROD: PowerEdge R720xd - Intel (R) Xeon (R) CPU E5-2637 v2 @ 3,50 ГГц.

UAT: PowerEdge 2950 - Intel (R) Xeon (R) CPU X5460 @ 3,16 ГГц

Я разместил в answer.sqlperformance.com


ОБНОВЛЕНИЕ:

Благодаря @swasheck для предложения

Изменение максимальной памяти на PROD с 60 до 7680 МБ, я могу создать тот же план в PROD. Запрос завершается в то же время, что и UAT.

Теперь мне нужно понять - ПОЧЕМУ? Кроме того, я не смогу оправдать этот монстр-сервер для замены старого сервера!

36 голосов | спросил Kin 21 42013vEurope/Moscow11bEurope/MoscowThu, 21 Nov 2013 21:18:08 +0400 2013, 21:18:08

3 ответа


39

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

Память рабочего пространства

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

В SQL Server 2012 (все версии) это число сообщается в корневом узле плана запроса в разделе Optimizer Hardware Dependencies, показанным как Estimated Available Memory Grant. Версии до 2012 года не сообщают этот номер в плане показа.

Оценочный доступный доступ к памяти представляет собой вход в модель затрат, используемую оптимизатором запросов. В результате альтернатива плана, требующая большой операции сортировки или хеширования, скорее всего, будет выбрана на машине с большой настройкой пула буферов, чем на машине с более низкой настройкой. Для установок с большим объемом памяти very модель затрат может зайти слишком далеко с таким видом мышления - выбирая планы с очень большими разновидностями или хэшами, где предпочтительна альтернативная стратегия ( KB2413549 - Использование больших объемов памяти может привести к неэффективному плану в SQL Server - TF2335 ).

Поддержка памяти в рабочей области не является фактором в вашем случае, но это то, о чем стоит знать.

Доступ к данным

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

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

Опять же, только в SQL Server 2012, корневой итератор плана показывает количество Estimated Pages Cached в разделе Optimizer. Этот номер сообщает об одном из входов алгоритма калькуляции, который учитывает вероятность повторного доступа к странице из кеша.

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

В простых планах снижение затрат при повторном сканировании можно увидеть, сравнив Optimizer Hardware Dependencies с оценкой стоимости оператора, которая будет быть ниже. Вычисление более сложное в примерах планов из-за эффекта полусоединения и объединения.

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

Выбор плана

Модель затрат оптимизатора делает ряд предположений и содержит большое количество подробных вычислений. Не всегда (или даже обычно) возможно следовать всемдетали, потому что не все числа, которые нам нужны, будут раскрыты, и алгоритмы могут меняться между релизами. В частности, формула масштабирования, применяемая для учета вероятности столкновения с кэшированной страницей, не известна.

В этом конкретном случае варианты выбора оптимизатора основаны на неправильных номерах. Оценочное количество строк из поиска кластеризованного индекса составляет 28.7874, тогда как 256 строк встречаются во время выполнения - почти на порядок выше. Мы не можем напрямую видеть информацию, которую оптимизатор имеет о ожидаемом распределении значений в пределах этих строк 28.7874, но она также очень ужасно ошибочна.

Если оценки ошибочны, выбор плана и производительность исполнения не лучше, чем вероятность. План с индексной катушкой выполняется для выполнения лучше, чем повторение сканирования, но совершенно неправильно думать, что причиной увеличения аномалии стало увеличение размера пула буферов.

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

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

ответил Paul White 27 32013vEurope/Moscow11bEurope/MoscowWed, 27 Nov 2013 03:38:15 +0400 2013, 03:38:15
17

Пол Уайт объяснил превосходным ясным образом причину поведения сервера - sql при работе на серверах с большим объемом памяти.

Кроме того, огромное спасибо @swasheck для первого определения проблемы.

Открыт случай с microsoft и ниже - это то, что было предложено.

Проблема разрешена с использованием флага трассировки T2335 в качестве параметра запуска.

KB2413549 - Использование больших объемов памяти может привести к неэффективному плану в SQL Server .

  

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

ответил Kin 18 WedEurope/Moscow2013-12-18T20:34:10+04:00Europe/Moscow12bEurope/MoscowWed, 18 Dec 2013 20:34:10 +0400 2013, 20:34:10
12

Максимальные параметры памяти и гиперпоточность могут влиять на выбор плана.

Кроме того, я замечаю, что ваши настройки «set» различны в каждой среде:

StatementSetOptions на UAT:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="true" 
CONCAT_NULL_YIELDS_NULL="true" 
NUMERIC_ROUNDABORT="false" 
QUOTED_IDENTIFIER="true" 

StatementSetOptions on Prod:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="false" 
CONCAT_NULL_YIELDS_NULL="true"
NUMERIC_ROUNDABORT="false"
QUOTED_IDENTIFIER="true" 

SQL может генерировать различные планы на основе опций SET. Это часто происходит, если вы собираете план из разных сеансов SSMS или из разных исполнений из приложения.

Убедитесь, что разработчики используют согласованные строки подключения.

ответил rottengeek 21 42013vEurope/Moscow11bEurope/MoscowThu, 21 Nov 2013 22:42:38 +0400 2013, 22:42:38

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

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

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