Оптимизация MS SQL Server

Оптимизация MS SQL Server


Задача оптимизации- задача достаточно специфичная. Зачастую она просто не ставится. Резервные копии, логшиппинг- то делают все, там особо придумывать нечего. Обслуживание базы- тоже почти все делают, почти. Здесь же- ну, медленно работает сервер, ну новый можно купить. Я когда на учебных курсах вопросы задавал- просто плечами пожимали, преподаватель отмечал, что такие вопросы очень мало кто задает.
Хотя это дорогие вопросы. Грубо говоря, покупать ли новый сервер или существующего без каких-либо изменений хватит еще на пару лет, до конца гарантии и сервиса. Ну или там какой брать, серверное оборудование стоит больших денег. На сколько ядер? Хватит Intel® Xeon® Processor E5  или нужен 7? А давайте возьмем Intel® Xeon® Processor E7-8894 v4! Intel® Xeon® Processor E7-8894 v4 Там целых 24 ядра! Давайте, но это его заявленная цена, только одного процессора, без сервера- $8,898.00. При этом, в целом ряде сценариев использования его мощность не удастся утилизировать, в отдельных случаях он будет ХУЖЕ других процессоров. Допустим, Intel® Xeon® Processor E5-2637 v4 Intel® Xeon® Processor E5-2637 v4 при 4 ядрах имеет бОльшую тактовую частоту и стоит $996.00. То есть, для задач связанных со скоростью вычислений при небольшом числе потоков он будет лучше. Далее я поясню почему 4 ядра.

Если обратиться к версии MS SQL Server у нас проявляется следующая картина: SQL Server pricing
При лицензировании по ядрам Enterprise редакция стоит $14,256 на ядро, Standard – per core $3,717. То есть, чтобы полностью покрыть лицензиями первый вариант нужно 342 тыс долларов, второй вариант 14 тыс долларов.
Разница между стандартной Enterprise и Standard в доступных ресурсах. В частности, ограничения Standard для 17 версии 24 ядра и 128 Gb оперативной памяти. Для более ранних версий 16 ядер и 64 Gb памяти. Enterprise ограничений не имеет. То есть, серер может иметь, допустим, 8 процессоров по (пусть) 16-24 ядер. 8 процессоров по 24 ядра это 192 ядра или 2,7 млн долларов.
Суть понятна? Если у вас получится запустить Вашу задачу на Standard редакции- Вы сэкономите очень большие деньги, разница на порядки. Минимальная конфигурация- 4 ядра. То есть, процессор- это невероятно ДОРОГОЙ ресурс, при чем, его ценность даже ни в стоимости оборудования а в стоимости лицензии.
Так вот, минимальная конфигурация при лицензировании по ядрам- 4 ядра, 14 тыс долларов для стандартной редакции. Но это не все бонусы- на лицензионных ядрах можно запускать до 50 серверов, можно  в виде инстансов но лучше на виртуальной машине. Памяти современные процессоры поддерживают от 1,5 до 3 Тб, на одном физическом сервере может быть размещены десятки MS SQL Server.
То есть, с одной стороны- миллионы долларов только на лицензию, с другой- 14 тыс. При чем, эти 14 тыс покрывают целую группу серверов. Теперь логичный вопрос- а потянут ли эти несколько ядер, 4 ядра, ну или 8 или пусть 16, серьезную рабочую нагрузку? При чем, значение имеет и сам сервер, модель на 2 сокета значительно дешевле нежели 8- сокетная.
Вот с этого места и начинаются вопросы оптимизации.
То есть, формально- ну работает ну и ладно. Начнет тупить- купим новое оборудование. На деле на имеющемся оборудовании можно перемолоть значительно бОльший объем полезной работы если просто изменить некие мелочи. Флажки выставить, индексы применить, запросы оптимизировать. Но это работа очень кропотливая- и малопонятная со стороны. Это уже ни говоря о ситуациях когда сервер останавливается по блокировкам. Где блокируется? Какие объекты? Как исправить? Мне эта работа крайне нравится, технический детектив. Иногда неделями отлавливаешь конкретную проблему 🙂
К слову, традиционная ситуация с перегруженным сервером- это не медленная работа, отнюдь. Транзакции выполняются медленней, они на бОльшее время блокируют объекты. Наступает такой момент, когда блокировки нарастают лавинообразно. Сервер не будет работать медленно- он остановится от взаимных блокировок. Рабочая нагрузка сервера имеет некоторое пороговое значение после которого работа физически невозможна. Потому, как правило, закладывают очень большой, избыточный запас мощности.
К примеру, буквально вчера решил одну проблему. Сервер медленно работал по блокировкам, джобы завершались ошибкой. Админы с программистами со взаимными претензиями обсуждали этот вопрос. Часа 4 кропотливо разбирался, решение было в том, что перестроил хитрым образом один кластерный индекс, это заняло меньше секунды. На следующий день перестроил еще 4 индекса и дал несколько запросов программистам- переписать. Проблема решена.
Практика показала, что проблемы на сервере создают 1-2 неудачных запроса, которые обязательно нужно переписать. Ну и десятка полтора во вторую очередь. Плюс необходимо создать несколко индексов, их нужно долго подбирать, но зачастую не так их и много. Основные проблемы с блокировками в базе создает 1-2 объекта. Это очень локальные и крайне несложные в исправлении задачи. Выявить эти объекты сложно, решить проблему куда проще. Такое решение финансово куда благодарней (на порядки) нежели приобретение под неэффективный и неоптимальный код нового оборудования.
Оптимизация MS SQL Server не просто способ успокоить пользователей, избавиться от их жалоб- это невероятно благодарное в финансовом плане дело. Это позволяет решать более сложные задачи на более простом оборудовании, экономить огромные деньги, сотни тысяч, иногда и миллионы долларов.

Дмитрий Горчилин, 20190313

Вместо послесловия

Несколько лет назад в одной очень большой организации внедряли зарплатный проект на 1С. А он, что называется, «не взлетел». Разработчики голову ломали, админы, сотрудники отдела-эксплуатанта. Перепробовали все что можно, на MS SQL Server шли дидлоки, процесс сбивался. Я там долгое время просил кое-что сделать, но когда все остальное перепробовали- разрешили. Проблему решили 4 индекса по таблицам на 80 мегабайт. Аж запрыгало.
Здесь еще нужно понимать, самовольное создание индексов в 1С является нарушением лицензионного соглашения. Начальство на это смотрит крайне подозрительно, потому любой удачный индекс после следует удалить и создать легальными средствами 1С. Зачастую процесс поиска таких индексов выносится на тестовую среду, в исключительных случаях (критическое падение производительности) могут разрешить что-то попробовать на продакшене.
В дальнейшем была проведена большая работа по анализу кода. Основной объект анализа- процедурный кэш, анализируется в момент наибольшей нагрузки, высоких блокировок и высокой дисковой активности.
Для понимания- по счетчику Page lookups/sec|SQLServer:Buffer Manager удалось установить, что в отдельные моменты запрос данных из памяти сервера достигает 2 Тб в минуту и более (!!). То есть, память используется не как средство ускорения доступа к информации на СХД а как некий математический механизм, эта нагрузка разительно отличается от рабочей нагрузки других серверов.
Выявлялись наиболее ресурсоемкие запросы, оптимизировались индексами либо передавались разработчикам на изменение кода. При системном подходе никаких ин-мемори таблиц и колумнсторе индексов не понадобилось, и так замечательно заработало.
Кроме того, хорошо поработали с флажками трассировки.
На сегодняшний момент проблемы оптимизации 1С возникают только при внедрении новых задач или существенных изменениях (меняются запросы).

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *