Есть две базы, продовская и тестовая.
Продовская крутится на железе намного мощнее чем тестовая (больше памяти в 10 раз, быстрые серверные SSD, намного мощнее проц, гигабитная сеть и т.п) Тестовая база содержит состояние продовской базы недельной давности
При этом тестовая база работает на порядок быстрее чем продуктив (в основном дольше выполняются запросы на модификацию даных). При этом эта разница наблюдается даже в моменты наката изменений, когда пользователи отключены от продуктива, т.е нельзя объяснить разницу лишь бОльшей нагрузкой на прод. Например сегодня один и тот же скрипт нагонялся 30 секунд на тесте и 3 часа на продуктиве без внешних пользователей.
Проверяли актуальность статистики на проде, отсуствие блокировок - ничего подозрительного
Посоветуйте, куда еще можно копать. Возможно, собрать какие то счетчики производительности?

Версия Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Enterprise Edition

Апну старую тему, да прод на виртуалке
Сейчас скрипт который апдейтит 60к строк в таблице на 40млн запросом вида


проапдейтил на 22 часа 10к строк. На тестовой базе с бекапом поднятым с продуктива на вчера эта операция заняла 10 минут

События ожидания запросом из рекомендаций
На проде:
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_SHelp/Info URLCXPACKET449316211.9446061579.63254632.37639587123379.740.07030.06970.0005https://www.sqlskills.com/help/waits/CXPACKETLATCH_EX42695889.941536672.971159216.9420458562037.580.02090.02030.0006https://www.sqlskills.com/help/waits/LATCH_EXASYNC_IO_COMPLETION12482448.7212482116.37332.35864052.22144.4644144.46060.0038https://www.sqlskills.com/help/waits/ASYNC_IO_COMPLETIONBACKUPBUFFER11875817.5611752871.28122946.2824212190192.110.00490.00490.0001https://www.sqlskills.com/help/waits/BACKUPBUFFERBACKUPIO9162966.949145668.0117298.939625467181.630.00950.00950https://www.sqlskills.com/help/waits/BACKUPIOSOS_SCHEDULER_YIELD5959359.26175.895953183.3163839152951.060.000900.0009https://www.sqlskills.com/help/waits/SOS_SCHEDULER_YIELDPREEMPTIVE_OS_PIPEOPS5921067.515921067.51071471.05828.4689828.46890https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_PIPEOPS

На тесте
WaitTypeWait_SResource_SSignal_SWaitCountPercentageAvgWait_SAvgRes_SAvgSig_SHelp/Info URLPAGEIOLATCH_SH4782.034778.253.7829809229.880.0160.0160https://www.sqlskills.com/help/waits/PAGEIOLATCH_SHIO_COMPLETION3326.973325.061.987484820.790.00380.00380https://www.sqlskills.com/help/waits/IO_COMPLETIONLCK_M_S1941.71941.630.067612.1325.548625.54780.0008https://www.sqlskills.com/help/waits/LCK_M_SLCK_M_SCH_S1538.141538.14089.61192.2679192.26790https://www.sqlskills.com/help/waits/LCK_M_SCH_SLCK_M_X1215.641215.390.2520817.60.58420.5840.0001https://www.sqlskills.com/help/waits/LCK_M_XPAGEIOLATCH_EX935.61934.131.491170165.850.0080.0080https://www.sqlskills.com/help/waits/PAGEIOLATCH_EXCXPACKET753.09723.1729.92787544.710.00960.00920.0004https://www.sqlskills.com/help/waits/CXPACKETTRACEWRITE483.28483.280.012413.022.00532.00530https://www.sqlskills.com/help/waits/TRACEWRITEWRITELOG217.44202.44152362371.360.00090.00090.0001https://www.sqlskills.com/help/waits/WRITELOGPREEMPTIVE_OS_WRITEFILE129.57129.57060070.810.02160.02160https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_WRITEFILE

В мониторе сейчас 0 загрузка проца, очередь на диске нету. Да в плане аптейта фулл скан кластеризированного индекса потому что на SomeField нету индекса, но блин, за это время я бы и сам его успел просканировать сто раз...

Это нормально вообще такие средние цифры в ASYNC_IO_COMPLETION и PREEMPTIVE_OS_PIPEOPS?

Планы одинаковые, а какие именно настройки сравнивать, их то немерянно)
Кстати на обеих базах стоит MAXDOP = 0, наверное не оч. хорошо? Дока говорит не делать так..

Я сам в шоке
Запрос все еще идет, за полдня он проапдетил еще около 7к строк
Более того, я решил по этой доке
https://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/
узнать чего ждет именно эта сессия, включил мониторинг на 10 минут, и я просто не смог дождаться даже вставки во временную таблицу из файла в скрипте (несмотря на то что там всего то 28к записей в логе). Скопировал файл трассировки себе на локальный тестовый сервер - и этот же запрос обработал файл за 3 секунды.
Из логов за 10 минут 7.2 секунды ожидание PAGEIOLATCH_EX, 0.6 сек ожидание SOS_SCHEDULER_YIELD, что ж блин он делал остальные то 10 минут

>у вас случаем не линуксовая версия сервера?
Нет, виндовая, виртуалка Windows Hyper-V Server 2016

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

Что я смотрел:
- блокировки. Их нет, запрос вообще показывает пусто
- Кумулятивную статистику ожиданий, привел тут ее
- план выполнения, он довольно простой и там основное время - сканирование кластерного индекса, фильтрация строк, обновление кластерного индекса. Все просто как двери. Да, можно добавить индекс и это будет быстрее, но речь идет об обновлении нескольких десятков тысяч записей в таблице на 40 млн, за 30+ часов то эту таблицу уже можно было сто раз пересканировать и обновить
- Очереди ожидания записи на диск, там ничего
- Попробовал снять статистику событий ожидания проблемной сессии за 10 минут работы и там ожидания защелок 7 секунд, ну это нормально как по мне, я не могу понять что он делает все остальное время и как это определить

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

>А CXPACKET исчезло? LATCH_EX? Остались PAGEIOLATCH_EX и SOS_SCHEDULER_YIELD?

CXPACKET было в кумулятивной статистике, в статистике по конкретно этой сессии только PAGEIOLATCH_EX и SOS_SCHEDULER_YIELD и это время, оно как по мне адекватное (секунды за 10 минут)

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

>Еще можно обратить внимания на ожидания BACKUP*
Да, с бекапом не все так просто, DBA использует рекомендуемую вендором утилиту HPE StoreOnce Plugin for MS SQL Server для резервного копирования + какой-то самописный скрипт для бекапа транзакционных логов. Но деактивация этих всех вещей не помогла, хотя я не до конца уверен что эта бекапная утилита не делает что-то втихоря

Поставили MAXDOP = 1, ну вроде как это помогло, скрипты все равно гонятся медленнее чем на тесте но не в сотни раз а хотя бы в несколько раз, посмотрим как это отразится на работе в целом.

>"намного мощнее проц" - а не много ли у него ядер? с намного меньшей частотой?
"больше памяти в 10 раз" - а виртуалке сколько отдали?

Памяти у виртуалки 256 Гб, на сервере исключительно MSSQL и больше ничего. Если я не ошибаюсь, CPU на 128 ядер


Вобщем, пока предположение что MAXDOP корень зла, он на обоих серваках был в 0, просто на тестовом сервере меньше ядер и соответственно меньше степень параллелизма