Оптимизация производительности в системе управления базами данных Microsoft SQL Server

Размещено на http://www.allbest.ru/

Оптимизация производительности в СУБД Microsoft SQL server

Технические науки

Умрихин Виктор Павлович, кандидат наук, доцент, декан

Королькова Любовь Алексеевна, доцент

Бушманова Валентина Никифоровна, старший преподаватель Сибирский государственный университет водного транспорта

Аннотация

Цель данной работы состоит в описании методов настройки сервера таким образом, чтобы добиться максимальной производительности СУБД MS SQL Server с сохранением уже имеющегося функционала.

Введение

Настройка SQL-кода с точки зрения оптимизации производительности на практике начинается уже после того, как основной функционал БД разработан, и большая часть логических ошибок устранена. Пользователей (в широком смысле этого слова) устраивает, что делает приложение. Они хотят, чтобы то же самое делалось быстрее.

Как правило, молодые специалисты достаточно хорошо знают стандарты языка SQL и теорию реляционных баз данных. Они умеют проектировать хорошо нормализованные базы данных и строить достаточно сложные многотабличные запросы, возвращающие правильные результаты. К сожалению, на практике эти запросы работают непозволительно долго для системы on-line на больших таблицах (десятки и сотни тысяч строк) и/или в условиях работы большого числа (десятков и сотен) пользователей, выполняющих однотипные операции.

Цель данной работы состоит в описании методов настройки сервера таким образом, чтобы добиться максимальной производительности СУБД MS SQL Server с сохранением уже имеющегося функционала.

«Самый важный момент, о котором необходимо помнить при настройке производительности, состоит в том, что вам никогда не удастся познать всю подноготную данного вопроса. Если вы являетесь среднестатистическим разработчиком SQL Server, тогда хорошо, если вам известно хотя бы 20% всей информации. К счастью, к этой теме, несомненно, применим принцип “20/80” (знание 20% теории позволяет решить 80% практических вопросов)» [2].

Данная работа может быть интересна преподавателям курсов изучения многопользовательских SQL-ориентированных СУБД, а также администраторам и разработчикам баз данных (особенно на этапе сопровождения продукта).

Задача оптимизации производительности в MS SQL Server

Теоретически разработчик принимается за настройку производительности SQL-кода:

1. При разработке функционала БД, который априорно определен как «жизненно важный» и при этом достаточно сложный (потенциально «тяжелым» для СУБД). К сожалению, после внедрения не так уж редко выясняется, что:

o «жизненно важный» код на самом деле не такой уж «жизненно важный», выполняется не так часто, как предполагалось, и пользователи вполне готовы подождать результата лишнюю секунду/минуту. Тем временем другие пользователи при выполнении совершенно других операций постоянно жалуются, что «все работает медленно или не работает вообще».

o запросы, выполняющиеся за миллисекунды на таблицах с десятками и сотнями записей, работают недопустимо долго (десятки минут), когда число записей достигают миллиона-другого...

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

o возможно, выбранные показатели на самом деле не являются адекватными показателями производительности системы;

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

3. Система уже внедрена и работает, с точки зрения конечного пользователя, недостаточно быстро.

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

Собственно настройку SQL можно условно разделить на следующие этапы:

1. Идентификация и анализ запросов, которые являются причинами недостаточно высокой производительности;

2. Составление оптимального плана выполнения для запросов, определенных на предыдущем этапе;

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

Мониторинг производительности в MS SQL Server

Перед тем, как приступать к настройке, необходимо выяснить, что именно в такой настройке нуждается. На что тратятся ресурсы сервера? Какие хранимые процедуры нужно оптимизировать в первую очередь? Врачи говорят, что гораздо проще лечить конкретный орган, чем человека в целом. Проводя аналогию, администратору приходится иметь дело с работой сервера как единым процессом.

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

SQL Server предоставляет набор разнообразных средств для предупреждения, обнаружения и оценки быстродействия медленно выполняющихся запросов. Возможности этих средств варьируются от пассивных (выполняется измерение реальной производительности, позволяющее следить, что и с какой скоростью выполняется) до более активных (позволяющих создать «регулятор» запросов, который будет автоматически уничтожать запросы, работающие дольше установленного вами времени). Мы рассмотрим некоторые из них.

Трассировка выполнения процессов

Инструмент SQL Server Profiler по праву называют «спасательным средством» администратора и специалиста по сопровождению. Невозможно оценить исключительную важность этого инструментального средства при решении проблем быстродействия и не только. Именно SQL Server Profiler позволяет составить представление, что же «на самом деле» происходит на сервере. Однако настраивать трассировку следует так, чтобы она возвращала только необходимый минимум сведений. Нужно помнить о том, что SQL Server Profiler также необходимо пространство для аудита системы, и поэтому у системы могут появиться дополнительные накладные расходы, независимо от того, откуда был запущен SQL Server Profiler. Чем больше будет объем трассировочных сведений, тем больше увеличится нагрузка на систему.

В результате работы с SQL Server Profiler может быть составлен «черный список» запросов, выполняющихся недопустимо долго. Поскольку следует учитывать, что один и тот же параметризованный запрос может запускаться с разными параметрами, гораздо проще анализировать трассировки, представляющие собой вызовы хранимых процедур (и это нужно иметь в виду при разработке архитектуры БД!). Трассировку удобно сохранить в виде таблицы, которую затем можно обработать с помощью агрегатных запросов, с целью поставить в соответствие каждой процедуре количественное значение некоторого критерия, выбранного для сравнения процедур с точки зрения даваемой ими нагрузки на сервер. Такими критериями могут быть:

1. Максимальное зафиксированное время выполнения процедуры (самый «неудачный» с точки зрения производительности запуск).

2. Суммарное время выполнения всех вызовов процедуры.

3. Среднее время выполнения процедуры (суммарное время выполнения всех вызовов процедуры, отнесенное к числу вызовов).

Оперативный мониторинг процессов, занимающих ресурсы сервера

Нагрузка на сервер, как правило, является непостоянной в течение дня. Если с помощью SQL Server Profiler удалось обнаружить, что процедуры выполняются существенно дольше «среднестатистического» времени, или (что вероятнее) пользователи жалуются, что «два часа назад все работало нормально, а сейчас очень медленно», возникает необходимость оперативно выяснить, какие процессы занимают ресурсы сервера именно сейчас, а также какие именно ресурсы являются конфликтными.

· Процессорное время (в этом случае нагрузка сервера максимальна, остальные процедуры выполняются, но медленнее обычного, так как на их «долю» ресурсов остается недостаточно).

· Таблица/страница/строка, заблокированная в рамках транзакции (в этом случае процессы, обратившиеся конфликтным ресурсам, стоят в очереди и ожидают освобождения; загрузка сервера при этом может быть обычной или даже меньше обычного).

SQL Server имеет встроенное средство для мониторинга процессов. На рис. 1 показан монитор активности от SQL Server 2005 [5].

Рисунок 1. Мониторинг процессов.

К сожалению, администратору этот инструмент зачастую недоступен: в «час пик», если в базе работают сотни пользователей, на сбор данных с его помощью требуются десятки минут, что, разумеется, недопустимо. Тем же недостатком в различной степени страдают разнообразные системные хранимые процедуры служебной базы данных master.

В этом случае, как правило, используются самостоятельно разработанные запросы к системным таблицам master.dbo.sysprocess и master.dbo.syslocks, названия которых говорят сами за себя. Ниже (рис.2) приведен пример запроса, используемого одним из авторов при сопровождении БД именно в таких целях.

Наконец, можно узнать последнюю SQL-команду, которую отдал серверу интересующий нас процесс по его уникальному номеру spid с помощью специальной процедуры DBCC INPUTBUFFER (Spid).

Рисунок 2. Запрос для просмотра текущих процессов, ожидающих заблокированный ресурс.

Оптимизация SQL-кода

Так или иначе, разработчик получает список хранимых процедур, нуждающихся в оптимизации, и примеры их вызовов, выполняющиеся недостаточно быстро. Также он знает (или хотя бы предполагает, что знает) причину «медленной» работы:

· Ожидание ресурса, заблокированного в рамках транзакции, запущенной другим процессом;

· Неоптимальный план выполнения хранимой процедуры.

Работа по устранению каждой из этих причин имеет свою специфику.

Минимизация блокировок в системе

Задача минимизации блокировок в системе сложна в первую очередь потому, что приходится рассматривать различные сочетания вызовов процедур, выполняемых параллельно.

В реальной базе данных блокировки неизбежны. Рассмотрим базу данных торговой компании, реализующую учет товара на складе. Если несколько менеджеров параллельно запускают транзакции, которые в том числе бронируют для клиента товар А, блокировок и ожидания ресурсов не избежать: несколько процессов действительно должны последовательно изменить одну и ту же строку.

Поэтому, выясняя, подлежит ли блокировка устранению, рекомендуется ответить на следующие вопросы:

1. Может ли объект блокировки (как правило, это таблица) быть конфликтным ресурсом? В каждой базе данных есть таблицы, по которым блокировки не должны возникать никогда.

2. Какая часть таблицы (таблица/страница/строка) является конфликтным ресурсом? За очень редким исключением справедливо утверждение: «право на существование» имеет только блокировка по значению первичного ключа.

3. Какие две параллельно выполняющиеся процедуры привели к возникновению блокировки? Имеют ли они общий конфликтный ресурс исходя из их функционального назначения?

4. Если блокировка подлежит устранению - кто «виноват» в ее возникновении: процесс, неправильно заблокировавший ресурс, или процесс, неправильно к ресурсу обратившийся?

При написании запросов на изменение и удаление строк в таблице (операторы UPDATE и DELETE) в рамках транзакции следует помнить: эти операции должны выполняться только по первичному ключу! В противном случае сервер заблокирует таблицу целиком.

При написании запросов на выборку следует учитывать назначение полей таблицы, к которым выполняется обращение, а также назначение самого запроса. Возможно, что логика работы приложения допускает построение этого запроса с опциями, разрешающими чтение неподтвержденных данных (NOLOCK) или только незаблокированных строк (READPAST). Например, директор торговой сети периодически формирует и просматривает отчет о выполнении менеджерами планов по валовой прибыли за текущий месяц. При этом менеджеры активно совершают сделки, на сервере постоянно открыто несколько транзакций, проводящих или удаляющих соответствующие документы. Директору вовсе не нужно дожидаться, пока ему отдадут все ресурсы в монопольный доступ. Незавершенные транзакции можно просто не учитывать - они не могут серьезно изменить картину в целом.

Если процессы, вызывающие блокировку, обращаются к одной и той же строке, но к разным полям, то имеет смысл разбить таблицу на несколько, связанных друг с другом по схеме «один к одному».

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

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

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

Выбор индекса

Индексы являются очень мощным средством увеличения производительности сервера.

Разработчики склонны к крайностям при работе с индексами: либо не использовать их вовсе, либо создавать «на все случаи жизни».

Индексы должны создаваться для столбцов, которые будут часто использоваться в параметре WHERE либо, в меньшей степени, в параметре ORDER BY.

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

Таким образом, если база данных организована так, что большая часть времени посвящена выполнению операторов SELECT, а изменения вносятся сравнительно редко, то чем больше индексов, тем лучше. В противном случае нужно ограничиться индексами только для наиболее часто используемых столбцов. Большую помощь в их определении может оказать мастер настройки индексов - специальный инструмент MS SQL Server, предназначенный для исследования рабочей нагрузки и поиска оптимального варианта внесения изменений в индексы.

Стратегическая денормализация

Иногда следование стандартным и, казалось бы, универсальным правилам, может навредить. На вопрос, в чем мы выигрываем, нормализуя базу данных, 90% вчерашних студентов бойко отвечают: «Обеспечивается целостность, устраняется избыточность и повышается быстродействие». При этом следующий вопрос о том, в чем же мы при этом проигрываем, приводит большинство в тупик. Далеко не все отдают себе отчет, что в хорошо нормализованной базе данных производительность повышается только при выполнении простых операций изменения данных, тогда как производительность запросов на выборку (а также в некоторых сложных случаях использования операторов DELETE, UPDATE и INSERT... SELECT) в лучшем случае не хуже, чем в слабо нормализованной базе данных. Часто добавление в таблицу всего лишь одного столбца может устранить необходимость в сложных объединениях, либо даже позволяет отказаться от объединений, включающих несколько таблиц, за счет чего время выполнения запроса уменьшается с нескольких минут до нескольких секунд.

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

Правильная организация хранимых процедур. Реализация множественных решений

база производительность трассировка код

Примером может послужить поисковый запрос, допускающий множество параметров, но не требующий наличия всех. Вполне можно написать собственную хранимую процедуру, чтобы она просто запускала единственный запрос, независимо от того, сколько в действительности было задано параметров - достаточно универсальный подход. С точки зрения разработки - это громадная экономия времени, однако, если взглянуть на вещи с точки зрения оптимального быстродействия, такой подход убийственен. Более чем вероятно, что каждый раз при запуске вашей хранимой процедуры, сервер будет обращаться к нескольким абсолютно не нужным таблицам!

В такой ситуации можно поступить следующим образом: добавить в код несколько выражений IF . . . ELSE для проверки условий. Это уже может дать заметный эффект. Однако есть еще одна тонкость. Запуская такую процедуру с некоторым фиксированным набором параметров и исключая из недостижимых при данном вызове блоков IF . . . ELSE запросы на выборку, можно обнаружить, что время выполнения увеличивается иногда на порядок! Казалось бы, в чем причина? Ведь исключенные запросы все равно не запускались! К сожалению, SQL Server при составлении плана выполнения хранимой процедуры «не знает», что наши условия взаимоисключающие. Он исходит из худшего случая - что все операторы SELECT (а также INSERT, UPDATE и DELETE) будут выполняться последовательно. Алгоритмические конструкции (IF … ELSE, WHILE, GOTO) игнорируются. Поэтому с точки зрения быстродействия желательно, чтобы внутри блоков IF . . . ELSE помещались не запросы, а вызовы хранимых процедур, в каждой из которых содержится запрос для работы с каждым определенным набором параметров (вызов вложенной хранимой процедуры также игнорируется при составлении плана).

Данная проблема возникает во множестве написанных сценариев. Разработчики - тоже люди, и, как правило, они привыкли иметь дело с объектно-ориентированными языками и развитыми технологиями повторного использования кода. Можно представить, как они отнесутся (и относятся!) к идее написать кучу практически идентичных запросов, чтобы в зависимости от заданных параметров учесть все возможные нюансы применения. Однако этот случай, когда результат, достигаемый таким «возмутительным» образом, говорит сам за себя, и его невозможно достичь никаким другим способом. «По этому поводу я хочу сказать, что в любой работе случаются рутинные моменты, а иначе все давно уже стали бы разработчиками программного обеспечения. Иногда вам стоит, скрипнув зубами, все же проделать эту работу ради получения конечного результата» [2].

Минимальное использование курсоров и циклов

Программисты, работавшие на языках программирования высокого уровня, иногда широко используют курсоры. К сожалению, в среде SQL Server справедливо утверждение: все, что работает с использованием курсора, работает крайне медленно.

На самом деле многое, что, казалось бы, требует применения курсоров, может быть выполнено при помощи набора операций. Иногда искомое решение гораздо менее очевидно, чем цикл, но, как правило, оно вполне осуществимо. Ключевой момент здесь состоит в том, что, избавляясь от курсоров, где это только возможно, вы обеспечиваете грандиозный прирост производительности и одновременно упрощаете программный код [2].

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

На практике нужно помнить - операторы INSERT, UPDATE и DELETE - циклы сами по себе, и именно для выполнения таких и только таких циклов разрабатывался SQL Server. Один запрос на UPDATE (даже многотабличный, с вложенными JOIN), модифицирующий сразу 1000 строк выполняется в разы быстрее 1000 запросов, модифицирующих по одной строке.

Для выполнения рекурсивных запросов совсем отказаться от циклов невозможно. В этом случае рекомендуется использовать CTE (common table expression - общие табличные приложения), доступные, начиная с SQL Server 2005.

Заключение

В данной работе были рассмотрены цели и этапы настройки производительности СУБД MS SQL. Представлен обзор основных встроенных в СУБД инструментов мониторинга процессов. Собраны и систематизированы наиболее универсальные приемы оптимизации SQL-кода таким образом, чтобы можно было достичь максимальной производительности в условиях коммерческой базы данных.

Список литературы

1. Артемов Д.В. Microsoft SQL Server 2000. Новейшие технологии. - М.: Издательско-торговый дом «Русская редакция», 2001. - 576 с., ил.

2. Вьейра Р. SQL Server 2000. Программирование. В 2 ч./ Р. Вьейра: Часть II; Пер. с англ.; Под ред. С.М. Молявко. - М.: БИНОМ. Лаборатория знаний, 2004. 807 c., ил.

3. Мамаев Е., Шкарина Л. Microsoft SQL Server 2000 для профессионалов. - СПб: Питер, 2001. - 1088 с.

4. Тоу Д. Настройка SQL. Для профессионалов. - СПб.: Питер, 2004. - 333 с., ил.

5. Электронный ресурс: https://habrahabr.ru/post/136481

Размещено на Allbest.ru