Есть хранимая процедура, вызывается порядка 500 раз в минуту.
avg cpu time = 200-300 миллисекунд.
Когда SP начинает вызываться 2000-3000 раз в минуту периодически (но не всегда) avg cpu time становится 2000-3000 миллисекунд.
Т.е. процессор при каждом вызове SP начинает потребляться в 10 раз больше.
План выполнения SP не меняется.
Кол-во строк немного меняется.
Проблемный statement внутри этой SP: insert into @TempTable ... join штук 15 больших таблиц
План выполнения statement не меняется.
Кол-во строк в statement немного меняется.
На SQL Server 2012 такой проблемы не было.
Проблема проявилась после перехода на SQL Server 2016. Обновление до SQL Server 2016 SP2 проблему не решило.
В плане параллелизм не используется.
Пробовал ставить maxdop = 1 на сервере, всё равно периодически cpu time прыгает с 200-300 миллисекунд до 2000-3000 миллисекунд.
Когда SP выполняется долго иногда помогает перекомпиляция SP, иногда помогает изменение sp_configure 'max degree of parallelism', иногда ничего не помогает и проблема проходит сама когда вызовов SP становится немного.
Что можно попробовать сделать? Что проверить?

версия: Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )

> 1. Самое эффективное сделать avg cpu time = 20-30 миллисекунд.
Уменьшить уже не получится

> 2. Менее эффективное - сваять в процедуре цикл и исполнять постоянно.
В цикле сделать нельзя - SP запускается приложением

> 3. 300 мс * 50 раз в секунду = 1500 мс.
300 мс * 50 раз в секунду = 15000 мс. Это примерно загрузка 15 ядер на 100%
На сервере ядер намного больше.
Ядра сильно ничем больше не нагружены.

> У вас, банально, начинается конкуренция между параллельно исполняющимися экземплярами.
Больших wait-ов нет.
На 100% процы не грузятся.

> какие ожидания в этот момент для проблемного spid, в котором выполняется процедура?
Waits анализировал, с ними проблем нет.
Есть небольшое кол-во SOS_SCHEDULER_YIELD и других ожиданий, но их суммарное кол-во несопоставимо с тем насколько дольше стала работать хранимка.

> а что при этом с физическими чтениями?
Физических чтений совсем мало, диски не нагружены

> Или это ожидание ресурсов (ядра, диски, память), или изменения плана запросов.
Как уже писал выше проблем с wait-ами нет, высокой загрузки железа нет, план запросов не меняется.

> Вы что, видите скачок с 10% до 100% загрузки?
Про "скачок с 10% до 100% загрузки" я не писал. Писал что загрузка при большом кол-ве вызовов ХП при отсутствии проблем была бы эквивалента загрузке 15 процессоров, а процов намного больше.

> Как Вы узнали, что это именно "потребление процессора"?
:)
Вижу в Extended Events

> а железо тоже самое осталось?
Железо новое

> Т.е. ванга 80 левела уверяет нас, что сервер ничего не дает, но и процедуры не исполняет.
Ничего подобного не писал. Писал что проблема с периодическим увеличением потребления CPU примерно в 10 раз при каждом вызове ХП при том же плане и примерно том же количестве строк.

> пишет в журнал. А журнал - он один.
Проблем с transaction log (соответствующих wait, загрузки железа) нет

> Да и "15 больших таблиц" неизвестно насколько велики.
> Если в память не помещаются - оно еще и читает перманентно.
Как уже писал ранее физических чтений совсем мало.
Практически всё к чему идёт обращение в buffer cache, оперативки много :)

> ну у Вас задач в 10 раз больше, и "потребление", т.е. общее время выполнения (ожидание плюс рантайм) одной задачи в 10 раз больше. Что за паника?
Похоже на троллинг какой то...
На всякий случай отвечу серьёзно: несколько раз уже писал что: 1. Проблема в том что CPU time становится примерно в 10 раз больше при КАЖДОМ вызове ХП. 2. Такие проблемы ИНОГДА возникают когда вызовов ХП становится раз в 5 больше обычного.