 |
|
|
|
Ускоряем MS SQL Server
Ускоряем MS SQL Server Что может быть хуже сервера, который неизвестно из-за чего стал работать слишком медленно? Только сервер, который работает слишком медленно по хорошо известной и неустраняемой причине! Чтобы реже сталкиваться с такой ситуацией, рассмотрим методы борьбы с причинами замедления работы Microsoft SQL Server. В первой части статьи (см. «Почему MS SQL медленно работает? Ищем причины», №5, 2005 г.– http://www.samag.ru/cgi-bin/go.pl?q=articles;n=05.2005;a=12) мы рассмотрели способы локализации проблемы, кроме одного – как обнаруживать дедлоки? Для начала изучим оставшийся вопрос и после этого перейдем непосредственно к сегодняшней теме. Поиск дедлоков Дедлоки (Deadlocks) являются довольно специфичной проблемой. С одной стороны, они не затрагивают весь сервер, а происходят на некоторых конкретных процессах. С другой стороны, если жертвы (снятые процессы) дедлока очевидны просто по журналу ошибок, то вот с каким процессом и на каких объектах случилось пересечение – непонятно. К счастью, в SQL Profiles входит великолепная возможность отследить всю цепочку блокировок с помощью LocksLock:Deadlock Chain. Чтобы эффективно ею воспользоваться, можно построить следующий шаблон:
Первые два события нам нужны, чтобы отследить собственно блокировки. Остальные понадобятся для идентификации проблемного процесса. Дело в том, что событие «Deadlock Chain» ничего не пишет об участвующих процессах, кроме SPID. Поэтому нам потребуется регистрировать еще и входы и выходы из системы. По SPID и времени дедлока мы сможем точно найти записи о его подключении и отключении, а значит – найдём описание процесса. Событие «ExistingConnection» даст нам список тех процессов, на вход которых в систему мы уже опоздали. Первые два поля я выношу в список полей, определяющих порядок сортировки (называется он почему-то Groups). Итак, шаблон готов. Сохраняем, запускаем и... готовимся к долгому ожиданию. Редко сервер доходит до такого состояния, что дедлок случается каждую минуту. Даже в очень тяжелых случаях приходится подождать пару часов, а то и дней, прежде чем наберётся материал для анализа. Поэтому я советую настроить сохранение счётчиков в файл и забыть о них на сутки как минимум. Особенностью в данном случае является еще и то, что когда у вас упорядочение задаётся по нескольким полям в Groups, то работать с ними в онлайн-режиме очень неудобно: из-за ошибки в профайлере курсор всё время скачет. Приходится или останавливать процесс сбора информации, или работать с сохранённым файлом журнала. При анализе обратите внимание на то, что событие Deadlock Chain реализуется одним и тем же процессом с маленьким номером. Это системный процесс, который следит за ошибками, но сам в них не участвует. Процессы-участники дедлока указываются в TextData события Deadlock Chain. Не могу не упомянуть о методе, позволяющем сохранять подробную информацию о случившемся дедлоке без использования профайлера. Это включение trace-флагов: DBCC TRACEON(1204, -1) К сожалению, документирован только один из них – 1024, как раз и обозначающий сохранение информации о дедлоке. Но нам нужен еще и -1 для того, чтобы включить отладку для всех процессов, а не только для текущего. В некоторых случаях для того, чтобы полученная подробнейшая информация о совершившемся дедлоке сохранялась в журнал SQL сервера, потребуется установить еще и флаг 3605. Эти флаги отладки общеизвестны, но не документированы. Используйте их на свой страх и риск. Мы закончили рассмотрение методов обнаружения проблем и приступаем к обсуждению способов их устранения. Решаем проблемы производительности Итак, вы нашли, в какой точке возникает проблема производительности и какого она рода. Поздравляю! Вы сделали 3/4 работы. Осталось совсем немного, всего лишь исправить ситуацию. Оптимизация индексов Вопрос поиска идеального набора индексов неисчерпаем. На эту тему можно написать отдельную статью. Правда, польза от нее будет сомнительной, поскольку каждая база данных по-своему уникальна, и подбор наилучшей схемы доступа к данным до сих пор остаётся скорее искусством, нежели точной наукой. В данной статье я лишь упомяну о нескольких наиболее частых ошибках в выборе индексов. Не забывайте об индексах! Как показывает мой опыт, это происходит чаще, чем можно было бы ожидать. Проекты не укладываются в сроки, программа доделывается в большой спешке, и на оптимизацию по индексам не остаётся времени. В результате или система работает с индексами по умолчанию (которые редко близки к оптимальным), или с выбранными как попало, или без индексов вообще. Третий вариант, пожалуй, самый предпочтительный, так как в этом случае проблема будет выявлена очень быстро. Используйте составные индексы Microsoft SQL Server 7.0 не умел использовать в одном запросе два индекса на таблицу. Версия 2000 на это способна, но скорость выполнения запроса вас вряд ли обрадует. Поэтому, если какие-то два поля таблицы участвуют в большинстве запросов, – не делайте два индекса по каждому из столбцов. Сделайте один составной! Составной индекс имеет еще и то преимущество, что если он содержит все нужные для запроса поля, то обращение к таблице не понадобится. Значит, можно избежать операции Bookmark Lookup (поиска страницы в базе по известному индексу), что позволяет увеличить скорость работы раза в два. Мне приходилось добавлять в составной индекс поля, которые в индексе, в общем-то, и не нужны, но позволяют избежать поиска по родительской таблице. Разделяйте часто и редко используемые данные кластерным индексом Выбирая кластерный индекс, имейте в виду, что именно он определяет физический порядок записей таблицы на диске. Физический порядок определяет соседство записей на страницах, а те, в свою очередь, задают порядок загрузки и выгрузки страниц в память сервера. А это значит, что часто лучшим кластерным индексом будет такой, который разделяет часто и редко используемые записи. Давайте для примера рассмотрим таблицу заказов. Предположим, что у нас есть ClientID – идентификатор клиента и OrderDate – дата заказа. Пусть большая часть запросов содержит условие на ClientID как точное равенство и условие на OrderDate как диапазон. Рассмотрим ситуацию, в которой заказов в таблице очень много, но активно используется только заказы за последнюю неделю, и в запросе чаще всего указывается именно этот период. Классические рекомендации советуют выбрать индекс (ClientID, OrderDate). По первому полю будет происходить точное сравнение, по второму – сканирование диапазона. Так бы и стоило поступить, не будь этот индекс кластерным. Если вы сделаете такой кластерный индекс, то заказы за последнюю неделю будут разбросаны по всей базе данных! Загрузка их всех в память просто невозможна, и каждый запрос к данным нового клиента приведёт к физическому (а не логическому) чтению. Если же вы используете в качестве кластерного индекс (OrderDate), то, несмотря на ухудшение плана запроса, вы получите большой выигрыш на активно работающей базе данных. В самом деле при активной работе с заказами последней недели все страницы с данными большую часть времени будут находиться в памяти сервера. А сканирование диапазона дат в памяти, нужное для поиска данных по конкретному клиенту, как правило, быстрее, чем считывание с диска этих данных, даже по известному заранее адресу. Не увлекайтесь созданием большого числа индексов Обычно при тестировании индексированных запросов используют всевозможные выборки (т.е. запросы, выполняющие чтение данных). Это понятно – выборки не разрушают данные. Но при этом упускается очень важный момент – при вставке, удалении, а часто и при обновлении данных индексы работают противоположным образом. Нет, они, конечно, помогают найти строчки, нуждающиеся в изменении. Но потом тратится куча времени на реорганизацию изменённых данных, и в итоге порой получается гораздо медленнее, чем на базе данных совсем без индексов. Именно поэтому стратегия «добавим индексы везде, где что-то тормозит», глобально проигрывает при попытке хоть что-то изменить в этой базе данных. Более пяти индексов на большой таблице является почти приговором для процессов, которые как-то пытаются изменять индексированные поля. Самый главный совет – не следуйте советам В том числе и этому, да. Все советы даются для частных случаев, которые могут никогда не сложиться в вашей базе данных. Проверяйте. Меняйте индексы, делайте запросы и следите за производительностью. Анализ планов запросов – это мощнейший инструмент, но даже он порой даёт сбои и неправильно оценивает стоимость различных вариантов выполнения. Самый надёжный вариант – это в QA щелкнуть правой кнопкой в окне редактирования, выбрать Current Connection Properties, а там – галочки Set statistics time и Set statistics IO. И затем уже выполнить тестовый запрос. Уверяю вас, если время компиляции и выполнения может зависеть от нагрузки сервера, число физических чтений определяется состоянием кэша страниц, то число логических чтений даёт почти идеальный параметр для оценки затрат на выполнение именно этого запроса. Ищите новые варианты, пробуйте их. Оптимизация индексов – это всегда очень интересно. Управляем планами запросов Эта задача немного проще предыдущей. Если нужный индекс на базе данных уже есть, но MS SQL Server им почему-то не пользуется, то довольно просто уговорить его это сделать. Стоп, не тянитесь писать with (index=<ИмяИндекса?>)! Это тоже вариант, но он нужен на крайний случай. Предлагаю вам свой список мер: Выполните запрос dbcc freeproccache. Этот нехитрый запрос очищает кэш скомпилированных планов запроса и даёт серверу возможность перекомпилировать планы заново. Если сбой, нарушивший генерацию плана, был единичный, то новые алгоритмы окажутся правильными, и система оживёт. Но если вам приходится часто использовать dbcc freeproccache, то будьте внимательны – это может быть последствием какого-то неблагополучия сервера в целом. Cгенерируйте дополнительную статистику. Вот уж чего, как говорится, много не бывает. Её можно «навешивать» хоть на каждое поле. Главное – не забывать регулярно выполнять перегенерацию (а лучше создать задание для регулярного выполнения таких действий). Используйте подсказки для MS SQL Server, оставаясь в рамках стандарта ANSI-SQL. Предположим, что сервер выбрал использование индекса по полю, которое кажется вам наименее подходящим в такой ситуации. Всё просто – в секции запроса where пишите условие по этому полю не в виде прямого равенства или диапазона (например, Name = ‘Иван’), а с помощью функции COALESCE(<Имя поля>, <Имя поля>). Например, coalesce(Name, Name) = ‘Иван’. Это совершенно корректно математически, но при этом проблемный индекс по указанному полю не может быть использован. Другой пример – представьте, что у вас есть условие типа where @Number like Code + ‘%’. Если бы запрос был построен как Code like @Number + ‘%’, сервер сам догадался бы использовать индекс по Code. Но в нашем варианте Code используется в составе сложного выражения, и оптимизатор пасует. Человеку же очевидно, что @Number like Code + ‘%’ может быть верно только тогда, когда @Number >= Code, так как у них одинаковые первые символы, но @Number длиннее. Вот тут и имеет смысл добавить в условия @Number >= Code, так как оно не изменит логики и поможет серверу самостоятельно избрать нужный запрос. Упростите запросы. Например, мне известны ситуации, в которых планы запросов сильно «плавали» при внешних соединениях (join) с представлениями (view), построенными на внешних соединениях. И только если всё предыдущее не помогло, используйте подсказки MS SQL Server. Они порой дают великолепный непосредственный результат, но с ними необходимо проявлять осторожность – в другой ситуации, на других данных или с другой загрузкой вынужденный план запроса иногда сильно деградирует. Немного сложнее приходится, в ситуации если вас устраивают выбранные индексы, но не устраивают методы и порядок слияния. MS SQL Server позволяет настраивать и эти моменты, но тут выбор возможностей очень ограничен. Вы можете сделать порядок соединений жёстко зависящим от порядка их упоминания в запросе (force order), выбрать метод соединения (loop|merge|hash join) или указать, что несколько строк в результирующем запросе вам нужны раньше других (FAST число строк). Будьте осторожны, выбирая соединение хэшированием (hash join). Оно даёт порой потрясающие результаты по производительности, но всегда требует очень много памяти. И когда вашему серверу перестанет ее хватать, такие запросы не только заметно деградируют по скорости, но сильно замедлят работу в целом. И наоборот, если сервер работает в состоянии нехватки оперативной памяти, изменение метода слияния с hash на loop может увеличить число логических чтений, но при этом волшебным образом уменьшить время выполнения запроса и облегчить работу серверу целиком. Как лечить блокировки Лечение блокировок очень часто делается командой kill. Что может быть проще: уничтожил головной процесс – и всё заработало! Но у этого подхода есть и отрицательные моменты. Первый – распределённые блокировки склонны повторяться. Так что не исключено, что через некоторое время вы будете дежурить в офисе весь день и всю ночь и держать палец над кнопкой kill. Второй – убивая головной процесс, мы теряем всю информацию о распределённой блокировке. Впоследствии мы можем так никогда и не узнать, что же на самом деле произошло, и не сможем принять меры к тому, чтобы ситуация больше не повторялась. Третья причина – далеко не всегда уничтожение головного процесса приведёт просто к перезапуску клиентского приложения. Иногда ценой вопроса является несколько дней работы целого подразделения. Более правильным вариантом работы является продуманная стратегия блокировок. Но её нельзя делать без хорошего представления о конкретной системе и предметной области. Нужно знать, для каких запросов сгодятся «грязные данные» и какого рода неточности тут могут возникнуть. Некоторые проблемы можно лечить расстановкой подсказки nolock или переводом целых процессов на низший уровень изоляции транзакций. В других ситуациях может потребоваться исправление существенных ошибок проектирования. Например, представьте, что в одной таблице оказались данные справочного типа, запрашиваемые постоянно множеством процессов и активно изменяемые, и важные поля. Эта ситуация будет приводить к постоянным блокировкам – читающие процессы будут мешать изменяющим, и наоборот. Одно из решений – выполнять чтение справочных данных с флагом nolock. Но если это почему-то неприемлемо, то можно воспользоваться другим способом. А именно: разделить эту таблицы на две, с созданием на месте старой таблицы представления (view) с «instead of» триггерами. При этом процессы, запрашивающие справочную информацию, будут блокировать одну таблицу, а изменения с помощью триггеров будут выполняться на другой. Повысить или понизить уровень гранулярности блокировок на каком-либо индексе или таблице можно с помощью процедуры sp_indexoption. И всё-таки выработка стратегии блокировок – задача скорее разработчика, чем администратора. Для программиста огромным подспорьем будет уже картина блокировок, полученная вами на этапе поиска проблемы. Снимаем дедлоки Как уже говорилось, дедлоки, завязанные на несколько объектов, снимаются довольно легко. Ведь такой дедлок означает, что к одним и тем же объектам базы данных разные транзакции обращаются в разном порядке. Достаточно установить один какой-то порядок и проследить, чтобы он везде выполнялся. Если вы решили везде обращаться сначала к А, и только потом к Б, а необходимо сделать наоборот, то надо просто установить сначала блокировку на А, а потом можно работать с Б и снова с А в своё удовольствие. Например, это можно сделать так: declare @l tinyint select @l = 1 from A with (tablockx) where 1 = 2 Менее понятно, что делать с дедлоками в рамках одной таблицы. Часто они решаются четким указанием режима уровня блокировок (pagelock, например). Тогда сервер не будет повышать эту блокировку до табличного уровня без крайней необходимости. Возможно, что придётся поработать с индексами и планами запросов, приводящих к взаимной блокировке. Ну и наконец, самый надёжный способ борьбы с дедлоками на уровне таблицы – это подсказка with (tablock) или изменение режима блокировок с помощью sp_indexoption. Конечно, производительность в этой ситуации может пострадать (а может и нет, зависит от конкретной БД). Но зато дедлоков не будет наверняка. Проблемы на аппаратном уровне Как уже говорилось, решать эту проблему должен не администратор базы данных. Тут на его долю остаётся только контроль и забота о файлах базы данных – наблюдение за фрагментацией таблиц с помощью dbcc showcontig, регулярная проверка физической целостности и перестройка индексов. Но даже эти простые процедуры могут дать очень большой прирост производительности, особенно если вы давно этим не занимались. Заключение Описанные сценарии не претендуют на полноту или оптимальность, но они работают, и я использую их ежедневно. Надеюсь, что набор приёмов и рекомендаций пригодится вам при решении проблем производительности MS SQL Server. Литература и ссылки:
|
ЮЛИЯ ШАБУНИОКомментарии отсутствуют
| Добавить комментарий |
|
Комментарии могут оставлять только зарегистрированные пользователи |
|
