hexesЕсть такая загадка: ХП, запускаю с интервалом 1неделя (отчёт о продажах), отрабатывает за пару сек., запускаю его же с интервалом мес. уходит в думки на 10+мин., делаю ход конём неделя, добавляю неделю генерю, ещё неделю генерю, в итоге выхожу на требуемый месяц, генерится сек. за 15.


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


hexesЯ так понимаю дело в кэшах? он потихоньку их прогревает и быстро формирует месяц?


Далеко не факт, что это кэш данных. Может быть, и он, а может быть и нет.
Я ж говорю, надо по одному с проблемами разбираться. Сначала разобраться, чтобы переключение задач по
cache misses было бы порядка 3-5%, маленькое. Потом, если ещё есть проблемы -- ловить уже запросы и тюнить их.

hexesПочему такая большая разница с непрогретым кэшем? Сейчас сделаю sysmon и план запроса ХП с неделей и месяцем.



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

cherrex_DenМесяц

авторThe sort for Worktable1 is done in Serial
The sort for Worktable2 is done in Serial
Table: Payment scan count 3, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=10 apf=0 total=10), apf IOs used=0
Table: Payment scan count 3, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Client scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Table: Orders scan count 3, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=9 apf=0 total=9), apf IOs used=0
Table: TiOpRealization scan count 1, logical reads: (regular=110816 apf=0 total=110816), physical reads: (regular=1 apf=0 total=1), apf IOs used=0
Table: WorkSession scan count 13531, logical reads: (regular=16956 apf=0 total=16956), physical reads: (regular=168 apf=8 total=176), apf IOs used=0
Table: Users scan count 1, logical reads: (regular=17 apf=0 total=17), physical reads: (regular=7 apf=0 total=7), apf IOs used=0
Table: Currency scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TickStockUse scan count 17, logical reads: (regular=27 apf=0 total=27), physical reads: (regular=7 apf=0 total=7), apf IOs used=0
Table: TickStock scan count 17, logical reads: (regular=31 apf=0 total=31), physical reads: (regular=3 apf=0 total=3), apf IOs used=0
Table: Performance scan count 166643, logical reads: (regular=304357 apf=0 total=304357), physical reads: (regular=50 apf=6 total=56), apf IOs used=6
Table: Hall scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Building scan count 1, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #SpecBlancks_00001660015612255 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: AlienBoxoffices scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
Table: TiOpReturn scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 166643, logical reads: (regular=333334 apf=0 total=333334), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 13531, logical reads: (regular=27086 apf=0 total=27086), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
The sort for Worktable3 is done in Serial
Table: #tm__________01001660015612255 scan count 0, logical reads: (regular=17 apf=0 total=17), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Payment scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Payment scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Client scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Orders scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TiOpRealization scan count 43585, logical reads: (regular=534860900 apf=0 total=534860900), physical reads: (regular=1252893 apf=0 total=1252893), apf IOs used=0
Table: TiOpReturn scan count 1, logical reads: (regular=12914 apf=0 total=12914), physical reads: (regular=1315 apf=0 total=1315), apf IOs used=0
Table: WorkSession scan count 43585, logical reads: (regular=65257 apf=0 total=65257), physical reads: (regular=2799 apf=47 total=2846), apf IOs used=10
Table: TickStockUse scan count 29, logical reads: (regular=50 apf=0 total=50), physical reads: (regular=8 apf=0 total=8), apf IOs used=0
Table: TickStock scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Currency scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Users scan count 29, logical reads: (regular=43 apf=0 total=43), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Performance scan count 1569, logical reads: (regular=2600 apf=0 total=2600), physical reads: (regular=55 apf=3 total=58), apf IOs used=4
Table: Building scan count 1, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Hall scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=1 apf=0 total=1), apf IOs used=0
Table: #SpecBlancks_00001660015612255 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: AlienBoxoffices scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3 scan count 1569, logical reads: (regular=3186 apf=0 total=3186), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 326

Execution Time 42307.
SQL Server cpu time: 4230700 ms. SQL Server elapsed time: 4205530 ms.
Total writes for this command: 0

неделя

авторThe sort for Worktable1 is done in Serial
The sort for Worktable2 is done in Serial
Table: Payment scan count 6, logical reads: (regular=24 apf=0 total=24), physical reads: (regular=12 apf=0 total=12), apf IOs used=0
Table: Payment scan count 6, logical reads: (regular=21 apf=0 total=21), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Client scan count 3, logical reads: (regular=12 apf=0 total=12), physical reads: (regular=4 apf=0 total=4), apf IOs used=0
Table: Orders scan count 7, logical reads: (regular=30 apf=0 total=30), physical reads: (regular=13 apf=0 total=13), apf IOs used=0
Table: TiOpRealization scan count 1, logical reads: (regular=10893 apf=0 total=10893), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: WorkSession scan count 16327, logical reads: (regular=27029 apf=0 total=27029), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Users scan count 1, logical reads: (regular=17 apf=0 total=17), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Currency scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TickStockUse scan count 16327, logical reads: (regular=21459 apf=0 total=21459), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TickStock scan count 35, logical reads: (regular=43 apf=0 total=43), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Performance scan count 16327, logical reads: (regular=40394 apf=0 total=40394), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Hall scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Building scan count 3264, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #SpecBlancks_00001730015366011 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: AlienBoxoffices scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TiOpReturn scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 16327, logical reads: (regular=32702 apf=0 total=32702), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable2 scan count 3264, logical reads: (regular=6576 apf=0 total=6576), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
The sort for Worktable3 is done in Serial
The sort for Worktable4 is done in Serial
Table: #tm__________01001730015366011 scan count 0, logical reads: (regular=37 apf=0 total=37), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Payment scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Payment scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Client scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Orders scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TiOpRealization scan count 1, logical reads: (regular=10893 apf=0 total=10893), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TiOpReturn scan count 16352, logical reads: (regular=689 apf=0 total=689), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: WorkSession scan count 134, logical reads: (regular=279 apf=0 total=279), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TickStockUse scan count 16352, logical reads: (regular=21492 apf=0 total=21492), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: TickStock scan count 134, logical reads: (regular=152 apf=0 total=152), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Currency scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Users scan count 1, logical reads: (regular=17 apf=0 total=17), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Performance scan count 16352, logical reads: (regular=40443 apf=0 total=40443), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Building scan count 134, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Hall scan count 1, logical reads: (regular=4 apf=0 total=4), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: #SpecBlancks_00001730015366011 scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: AlienBoxoffices scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable3 scan count 134, logical reads: (regular=316 apf=0 total=316), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable4 scan count 4, logical reads: (regular=56 apf=0 total=56), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 49

Execution Time 6.
SQL Server cpu time: 600 ms. SQL Server elapsed time: 713 ms.

Вот такая вот разница.
Покажите этот запрос (STATEMENT 17 (at line 63)).

Да, это похоже на подкачку в кэш.