hexes В какую сторону тогда копать если всё остальное ОК??? Что делать ума не приложу, БД стала сильно тормозить. Простые отчёты которые отрабатывали за секунды теперь до 1.5-2х минут генерятся.

Я б, начал бы с самих запросов. План, статистика, оптимизатор, итд...

Вот это еще смущает. Половина сбросов, если память не изменяет, то это бывший System Log Record (при запуске системной транзакции (например, выделение OAM-страницы) внутри пользовательской транзакции).

Transaction Management
----------------------

ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.1 0.0 74 0.5 %
by End Transaction 6.9 1.2 4130 28.5 %
by Change of Database 0.3 0.0 169 1.2 %
by Single Log Record 12.6 2.2 7547 52.1 %
by Unpin 3.3 0.6 1955 13.5 %
by Other 1.0 0.2 604 4.2 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 24.1 4.2 14479

авторЧто это значит?

Тут вообще загадка! В описании к с sp_sysmon нет такого счетчика, есть System Log Record . Может опечатка....
System Log Record - это системная запись в лог, например при аллоцировании новой OAM страницы.

авторЕсли вы готовы меня разик носом натыкать куда смотреть и как его читать хотя бы на одном примере, был бы крайне признателен!!!

Как говориться, "чем смогу..."
Но начать надо именно с них!

авторА про SSD и tempdb в оперативке что думаете? Про зеркалирование девайсов?

Забыл про это с версии 15.5, когда темповые базы стали "Durability оff".

hexes,

авторСвежий sysmon после ребута, нескольких настроек и "Fix allocation errors".

Лучше на "прогретом" sp_sysmon запускать, ну и конечно чтоб в интервал работы sp_sysmon попали ваши тормозящие запросы.

hexes,

Также интересно было бы увидеть:
1. раскладку по базам и девайсам (на каком девайсе какая база).
2. Раскладку по кэшам (какие базы/объекты к какому кэшу прибиндены).
3. конфигурайионный файл ASE.

[quot MasterZiv]cherrex_Denпропущено...


Тут вообще загадка! В описании к с sp_sysmon нет такого счетчика, есть System Log Record . Может опечатка....
System Log Record - это системная запись в лог, например при аллоцировании новой OAM страницы.


Здрасте, как это нет, когда я его даже помню?

В документации: System Log Record - есть , Single Log Record - нет , но sp_sysmon упорно пишет Single Log Record

авторCache: kinomirdat
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 10.0 %

Можно еще кэши на партиции побить...

MasterZivcherrex_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)).