dezhnevoЗдравствуйте.
На новом месте работы поставили задачу разобраться с производительностью сервера SQL. Сказали, что пользователи жалуются, «тормозит, плохо работает». Я сам в БД не работаю, запросы не создаю и т.д., все со слов.
Исходные данные на момент написания:

Сервер:

Win server 2008R2 x64 – 125 Gb ОЗУ – Xeon E5 4640 x2
MS SQL 2012 (количество баз – 30, размер всего 900 Гб)

Информация по БД основная, без секундных метрик и данных о нагрузке в единицу времени:

Data Base Pages – 11 134 865
Buffer Cache Hit Ratio – 100 %
Target Pages – 261 914 624
Cache objects – 96 346
Cache pages – 885 283

Для мониторинга обстановки основные показатели интегрированы в сервер мониторинга и выводы следующие:
Дисковая подсистема и процессорный пул не сильно нагружаются. То есть тех ресурсов что есть вполне хватает. А вот с оперативной памятью вопрос. Для SQL выделено 100 Gb, он «съел» их полностью. Причем было выделено 80, добавили +20 за неделю он их «прикончил».
В связи с этим вопрос, какой оптимальный объем ОЗУ (ориентировочно) мне нужен в данной ситуации (я в курсе, что это очень грубо, без анализа количества запросов и других параметров работы пользователей с БД) и что посмотреть в первую очередь для некой оптимизации БД, без глобальных изменений.
Буду рад ответить на уточняющие вопросы. Спасибо большое заранее.
Давайте я напишу несколько очевидных вещей, которые гарантированно помогают (кроме шуток), увеличить производительность MSSQLSERVER. Правда, речь идет мере такого увеличения :-).
1. Разнесите базы, логи и tempdb по разным дискам. Заметьте, что крайне желательно, чтобы это были физически разные тома. Например, если у вас много дисков (10 :-), зеркало - под систему, и 8 болтаются на рэйд-контроллере), то лучшей конфигурацией будет зеркало пол логи, зеркало под темпдб, рэйд10 под данные.
2. Если у вас СХД - озаботьтесь приоритетами при работе с томами. Для логов критически важна задержка (латентность), для данных - общая пропускная способность.
3. ОБЯЗАТЕЛЬНО уберите tempdb с системного диска.
В качестве бюджетного "ускорителя быстродействия SQL" я бы посоветовал купить SSD для PCI-EX с большим ресурсом, и вынести tempdb на него. Тут правда есть момент, что в случае его отказа SQLSERVER аварийно остановится, но разрушения баз (теоретически) не произойдет, и вы сможете запуститься, после некоторых телодвижений ( https://infostart.ru/public/236716/) в течение нескольких минут после перезагрузки.
Ну, разумеется, если у вас не система 24х7 и вы не банковскими транзакциями рулите.
4. Если у вас не энтерпрайз версия SQLSQRVER, то больше 64 (2012, 2014) или 128 (2016SP1+) отдавать ему бессмысленно. Оно не умеет.
5. Если у вас сильно больше памяти, например - 256, и активно используется tempdb при работе (посмотрите хотя бы системным монитором) - подумайте, может сделать на "лишней" памяти RAM-диск, и разместить там tempdb.
6. Ограничьте, с учетом вышеизложенного, память, доступную для SQLSERVER. Лучше оставить системе 10%, но не менее 4ГБ.
7. Обязательно установите Instant File Initialization https://www.brentozar.com/blitz/instant-file-initialization/
8. Если у вас система с 8 ядрами - сделайте 8 файлов tempdb, если их больше - то еще +4 файла на каждые 8 ядер, но не более, чем 16 файлов. Обязательно установите для файлов одинаковый начальный размер и одинаковое приращение. Где-то 128-512Мб.
9. Установите такое же приращение для всех файлов БД, которые вы используете. Причем для файлов данных 256-512, для логов 32-64Мб (логи заполняются нулями при приращении, поэтому большое приращение может внезапно подвесить какой нибудь запрос на обновление).
10. Убедитесь, что опция AUTO CLOSE для БД - ВЫКЛЮЧЕНА. За опцию AUTO SHRINK - вообще нужно расстреливать сразу и без суда.
11. Убедитесь, что опция автоапдейта статистики ВКЛЮЧЕНА.
12. Не смотря на п.11. создайте задачу обслуживания по пересчету статистики рабочей базы, и сделайте ее автозапуск в периоды минимальной загрузки.
13. На самой деле задача на ребилд/реорганизацию индексов НЕ НУЖНА (при любом разумном сценарии использования SQL) :-)
14. Убедитесь, что опция БД Page Verify стоит в CHECKSUM. Если это не так, немедленно установите эту опцию и запустите DBCC.
15. Убедитесь, что вы регулярно делаете полный бэкап БД, и бэкап лога БД, для всех баз с FULL моделью восстановления.
16. Имейте ввиду, что SIMPLE модель восстановления НЕ имеет преимуществ перед FULL по быстродействию :-)
17. Перенесите базу на SSD.
18. Если вы не можете этого сделать, а у вас 2014+ SQL - хотя бы включите BPE https://olontsev.ru/tag/buffer-pool-extension/
Но, имейте ввиду, что SSD для этого должен сидеть на 6G SATA как минимум.
19. За практику шринка БД - нужно, по хорошему, расстреливать. Делать это нужно только в том случае, если вы отчетливо осознаете, что и зачем хотите сделать, и какие последствия этого будут.
20. Уберите флаг системного индексирования для быстрого поиска на томах, где расположены базы.
21. Добавьте файлы с базами в исключения антивируса.
22. Отключите на томах с базами генерацию 8.3 имен и времени последнего доступа.
23. Можно довольно сильно увеличить быстродействие БД, правильно настроив индексы. Но для этого вы должны понимать, что и зачем вы делаете :-)

Прим:
:-) помечены теоретически спорные вопросы, которые, однако, в 90+% случаев работают именно так, как я сказал :-)

dezhnevouaggster,

Во первых, спасибо, что не поленились ответить. Из множества статей и видео, которые сейчас обрабатываю, в большинстве случаев с вами соглашусь.
У меня вопрос по физике только один, я разбиваю tempdb на части, сейчас один и на системном медленном винте (это уже по любому узкое место), по количеству и размеру приращения все понятно, но! от чего отталкиваться определяя его первоначальный размер. Пока логика не ясна.

Если перенесете tempdb на отдельный диск, то я советую вообще установить начальный размер файла = месту на диске / количество файлов (ну, за минусом места под лог ~1-2Гб, tempdb в общем случае большой лог не нужен), с приращением 0.
Однако, если не включите мгновенную инициализацию файлов - рискуете заполучить старт SQLSERVER в течение десятков минут :-)
dezhnevoНапример, сейчас один файл размером 30 Gb, делю его на 12, так как 8+4 в моем случае, процент роста ставлю 10% и без ограничения (хотя в рекомендациях говорится, что стоит ограничить рост по времени, хотя бы 2 минутами), внезапного аномального роста не наблюдается. Вопрос, какой изначальный размер каждого файла указывать, какая формула вычисляет это?

Процентный прирост файлов использовать КРАЙНЕ не рекомендуется.
Установите фиксированный прирост.
dezhnevoДля меня пока не ясно.
Вопрос по ребилду и переиндексу, после переноса на ssd, думаю не актуален, и даже противопоказан.
Еще раз спасибо.
Смысл ребилда и реиндекса - не в этом.
Упрощенно, дело в следующем:
При интенсивных вставках и обновлениях данных, в кластерном и некластерных индексах могут образовываться "дырки". Страницы, и данные на страницах, которые помечены как удаленные.
SQL же читает данные с дисками кусками по 8кб, и отображает их в память тоже этими же кусками, целиком, без изменения.
И вот представьте, что он вытянул с диска страницу, отобразил ее в памяти, а из 8 кб там полезных только 2 кб, остальное - помечено как удаленное.
Во первых он 8 кб с диска тащил, из которых только 2 - полезных (нагрузка на диск), а во-вторых в памяти то он 8 кб так и так занял, не смотря на то, что там всего 2 кб полезных.
Вот это и нужно исправить реорганизацией или перестроением.

Andy_OLAPuaggsterНапример, если у вас много дисков (10 :-), зеркало - под систему, и 8 болтаются на рэйд-контроллере), то лучшей конфигурацией будет зеркало пол логи, зеркало под темпдб, рэйд10 под данные.

Таки нет. Это очень плохое решение. А вот наоборот - зеркало под БД и raid10 под tempdb, это уже кошерно.
И готов пояснить, почему.

Когда вылетит один диск из 4-х (а он обязательно вылетит) - то ребилд тома под tempdb это не страшно. Отключите пользователей, запустите с БД внеочередной бэкап. Плюс скорость на запись и чтение для tempdb почти всегда важнее, чем для большой БД.
[/quot]
А вот это зависит от интенсивности использование tempdb.
Если у вас, например, версионирование вГлючено, то может и да.
А вот, например, если у вас база гигабайт 500, а памяти всего 16 - то таки нет, и запросы, скажем так, нелокальны. Скорее всего, уже определяющим будет время подъема данных с диска.
Andy_OLAPА вот если вылетит диск под томом с БД и начнется ребилд - Вы будете в страхе ожидать, что при выполнении бэкапа из-за повышенной нагрузки вылетит второй диск в том же зеркале из страйпа - и потеряете много свежих данных.
Почему? Логи то на другом диске.
Ну, забекаплю хвост лога. Или буду бэкапить его почаще.