Постепенно был проведен апгрейд сервера. Отписываюсь по результатам. Возможно кому-то будет интересна практика наращивания мощности сервера с реальными результатами.

1. Сначала была добавлена память. Во первых были задействованы 12 Гб из 16-ти имеющихся вместо четырех, которые (к моему стыду) были задействованы ранее.
2. Переведены на гигабитную сеть все пользователи, которые работают с тяжелыми или требующей большой производительности операциями типа учета документов, формирование сложных документов, построение сложных отчетов.
3. Всем этим пользователям (предположим их 20 из 70-ти) были обновлены компьютеры со среднестатистического "по больнице" Celeron 2.2 512 Гб RAM до I3 2Гб RAM.
4. После этого возникло некоторое облегчение. Т.к. процесс происходил далеко не за один день - изменения мало были заметны, но если сравнить с тем что было изначально - стало чуть стабильнее (меньше блокировок, меньше подвисаний - да и вообще приятнее работать пользователям), хотя скорость работы впринципе визуально не изменилась.
5. Были добавлены еще 16 Гб памяти. Итого сейчас используется 29 Гб, т.е. количество буферов как и рекоммендовали выше доведено до 3 млн.
6. После этого стало еще стабильнее.
7. Приобретена дисковая полка и теперь дисковая система в отличии от того, что было имеет следующую структуру:
Первый контроллер
C: - RAID 1 - 2x72 Гб SAS 15K - система
E: - RAID 1 - 2x200 Гб SSD MLC - самый ненагруженный (судя по статистике) кусок БД
F: - RAID10 - 4x72 Гб SAS 15K - Лог-файл
Второй контроллер
G: - RAID10 - 4x72 Гб SAS 15K - tempdb
Третий контроллер (полка)
K: - RAID10 - 14x146 Гб SAS 15K - файлы БД кроме того, что лежит на E:
Само собой кэширование, память и батарейки везде. 50% чтение, 50% запись.

8. После этого работа стала еще стабильнее, и даже якобы выросла скорость работы процентов на 10-15.

А вот теперь самое интересное.
Вот счетчики состояния среднего по больнице.



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



Количество обращений к розовому диску (диск К - где лежит БД) впринципе бывает и зашкаливает за 600, но для 14-ти дисков в RAID10 я думаю это вполне уместно с учетом того, что они справляются судя по следующему скрину.



Как я и писал, что судя по среднему времени отклика - диски справляются, т.к. "пила" идет обычно только по розовому диску и не поднимается выше 10 мс.



99% - это самая обычная картина использования памяти. Т.е. почти все что нужно умещается в оперативке.



Ну и проц впринципе особенно не напрягается.


Вот вроде бы все хорошо и замечательно - сервер пустой, машины пользователей не напрягаются.
А где же рост? Ну хоть процентов на 50? (Я уж не говорю "в разы").
Куда дальше рыть?
Как сделать чтобы скорость выполнения запросов значительно увеличилась?
Учетную систему прошу не ругать, она самая обычная. Просто странно что при значительно проапгрейженном сервере и пользовательских машинах я получил только стабильность и рост скорости около 10%.

iv_an_ru,

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

Предположим имеем таблицу без ключей и индексов. Лежит она на диске. Мы делаем курсором пробежку по этой таблице за 500 мс.

Если я сделаю не 1 диск, а предположим 3 диска в RAID0, то (предположим что шины, контроллеры, память и т.п. - все пропускают), то скорость дисковой системы увеличится минимум раза в полтора (в идеале в три, но предположим что в полтора).
Будет ли рост в полтора раза скорости выполнения запроса? Вот не будет.

Вообще в MS SQL заложена функция гибкого торможения. Ее спонсируют производители железа. :)

miksoftKaktus_iv_an_ru,

Это понятно, но ведь даже если запрос крайне не оптимален, то он должен после такого апгрейда выполняться значительно быстрее, а не только на 10 процентов.С чего бы? Если упор происходит в CPU, то ничего не изменится.

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

P.S. В Paint-е выставьте размер картинки по-умолчанию поменьше, например, 100*100 пикселей, чтобы не было дурацких белых полей у скриншотов.

Да, с картинками закосячил.

Ну вот вы же видите мою систему - узких мест нет. Проц не загружен, винт не загружен, сеть (не вывел скрины, но всеже) не загружена. Машины пользователей также не шибко загружены.
Че он (сервер) делает? Поидее если нет узких мест система должна работать максимально быстро пока не появится узкое место, на нем она должна и тормозить. Но если у системы нет узких мест? :)

iv_an_ruKaktus_,

Грейс Хоппер неправильно использовала свои медные наносекунды. Она их студентам показывала, а надо было их этими наносекундами пороть. Чтоб хоть так дошло, что такое латентность, откуда берётся, и почему пиковые пропускные способности не имеют ничего общего с производительностью случайного доступа.

Ваш комп сейчас толще того, который готовил все расписания и транспаранты всех спортивных событий в Олимпиаду-2012. То есть если вы в кадре видели титры с именем спортсмена --- они были вынуты с того компа с кучей дополнительной всячины. Это примерно тысяча запросов от приложений-"читателей" в секунду + 30 "писателей" в секунду + репликация, разумеется. И это была четверть от максимальной постоянной нагрузки, которую он обеспечил на нагрузочных тестах.

Сейчас померял профайлером количество запросов в секунду (снял фильтр по Duration). Получилось около 2 тысяч запросов (чтение и запись не делил).

Интересная информация даже для меня. У меня в профайлере по Duration всегда было наложено 100 мс - это чтобы контроллировать ситуацию по торможению.

miksoftKaktus_Проц не загруженНу как же? одно ядро загружено почти полностью. Если в этот момент выполняется только один запрос, то как раз в это ядро и будет упор.

У меня обычно статистика по процу изредка касается пиками 100%, но никогда достигнув 100% не остается на ней более 1 секунды и то очень очень редко. Просто гипотетически если я воткну более мощные процы и статистика по использованию процессора уменьшится ровно в два раза - я не думаю что получу даже 10%-ый выигрыш общей производительности системы.

alexeyvgKaktus_Ну вот вы же видите мою систему - узких мест нет. Проц не загружен, винт не загружен, сеть (не вывел скрины, но всеже) не загружена. Машины пользователей также не шибко загружены.
Че он (сервер) делает? Поидее если нет узких мест система должна работать максимально быстро пока не появится узкое место, на нем она должна и тормозить. Но если у системы нет узких мест? :)Ну так найдите узкие места.

Смотрите конкретные проблемы, а не "система должна работать максимально быстро".
То есть смотрите выполнение конкретного запроса, или целиком цепочку, включая приложение (может, проблема в клиенте, который долго отрисовывает?)

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

Да как найти узкие места? Уже все обычные узкие места отработаны и судя по счетчикам не являются узкими.
Если укрупненно, то
- Очередей нет
- Проц не загружен
- Памяти достаточно
- Сеть не загружена
- Компы пользователей мощны
- Среднее время чтения и записи на диски намного менее 20 мс

Что еще смотреть? У меня нет длинных запросов - у меня каждый запрос выполняется адекватное количество времени, да и выполнялся раньше адекватное количество времени, но я хочу еще ускорить. Как это сделать? Возможности учетной системы считаем исчерпанными. Нечего там больше оптимизировать. А если что-то и оптимизируем, то это не даст нужно роста. Уже много лет оптимизируем. Нечего там уже переписывать. Только жать базу. А этого я не хочу.

[quot Khod]Kaktus_ Вырезал...

1. Обновление статистики.
2. Фрагментация таблиц.
3. Топология сети.
4. Оптимальность запросов к базе.
и т.д.

Обновление статистики по основным таблицам (порядка 30-ти) происходит ежедневно принудительно. Плюс стоит Auto Update Statistics. Да и после обновления статистики прироста не видно никакого.

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

Сеть достаточно разветвленная, на площади порядка 70 тыс кв. метров. Около 120 юзеров из которых учетной системой одновременно пользуются около 70-ти. У 20-ти из них (с самой большой нагрузкой) гигабит. Реальный до сервера через гигабитные естественно свичи. Но это все не узкое место. Дело в том, что скорость выполнения запросов на сервере примерно такая же как и у пользователей. Т.е. нельзя сказать что на сервере выполняется даже в полтора раза быстрее.

Ну а оптимизация запросов - это бесконечная история. Длится она уже несколько лет и ну нечего там уже оптимизировать.

Пожалуйста если есть еще какие-то мысли что может быть гипотетическим узким местом - напишите мне. Просто уже все идеи закончились.

miksoftKaktus_Пожалуйста если есть еще какие-то мысли что может быть гипотетическим узким местом - напишите мне. Просто уже все идеи закончились.Мои мысли про CPU вы принципиально не воспринимаете?

Сколько в среднем сессий у вас находится в активном состоянии (т.е. выполняют какой-нибудь запрос) ?

Почему же. Я пока наблюдаю, но действительно не вижу чтобы хотя бы по одному ядру у меня зашкаливала сотня. Обычно даже если и доходит куда-то высоко, то касается сотни и падает, но это редкость. Т.е. ни одно из ядер на мой взгляд не перегружено.
Сессий в активном состоянии чтобы одновременно выполняли запросы... Вот последний раз я снял - у меня 70 тыс запросов в минуту получилось. Выкинем отсюда 20 тысяч запросов - какой-то пользователь запустил видимо какой-то хитрый отчет и получится что за минуту 30 пользователей (по трассе) выполнили 50 тысяч запросов. Получается в секунду было около 900 запросов от не более чем 30 сессий.

miksoftKaktus_Пожалуйста если есть еще какие-то мысли что может быть гипотетическим узким местом - напишите мне. Просто уже все идеи закончились.Мои мысли про CPU вы принципиально не воспринимаете?

Сколько в среднем сессий у вас находится в активном состоянии (т.е. выполняют какой-нибудь запрос) ?

Еще один нюанс забыл написать в предыдущем сообщении очень важный. Дело в том, что скорость выполнения на скажем так отключенном от сети сервере примерно такая же как и у пользователей (ну максимум на 20-30 процентов быстрее) но не в разы. Об этом я писал кому-то из пользователей выше и поэтому я сейчас вообще в полной растерянности куда рыть.