Решение проблем с запросами в Microsoft SQL Server
Материал из 1GbWiki.
Решение проблем с запросами в Microsoft SQL Server
Содержание |
Для кого статья?
Для технических специалистов, программистов и веб-мастеров.
Первоначально рекомендуется ознакомится со статьёй базы знаний хостинга проблема медленных SQL запросов – в статье основной упор делается на mySQL, но даётся ряд базовых знаний, верных и для Microsoft SQL Server. Далее по тексту предполагается, что вы с указанной статьёй знакомы.
Верхний предел
1Gb.ru предлагает услугу Microsoft SQL Server в рамках виртуального (shared, разделяемого) хостинга. Это означает, что на одном сервере находятся ресурсы множества клиентов и они не должны мешать друг другу. Специфика сайтов и сопутствующих служб запускаемых на виртуальном хостинге в том, что они не должны рассчитывать на монопольное использование ресурсами сервера хостинга. Если ваш проект оперирует большим объёмом данных и требует для этого больших ресурсов (к примеру, он постоянно читает и пишет большие блоки данных на диск и на это время фактически захватывая его под свои нужды вытесняя другие задачи) – он не предназначен для виртуального хостинга. Если для стабильной работы сайта требуется большой объём памяти для удержания в ней (кэширования) какой-то информации – сайт не предназначен для виртуального хостинга. Если есть любые другие попытки длительного монопольного владения всеми или ключевыми ресурсами сервера и нет расчёта на то, что кроме вашей задачи на сервере исполняются сотни и тысячи других равноправных задач – проект не предназначен для виртуального хостинга. В этом случае, попытка размещения проекта на виртуальном хостинге является ошибкой ответственных за проект лиц. Если, вдруг, это ваша ситуация – вам не повезло, решить эту проблему на виртуальном хостинге нельзя и дальше можно не читать: вам нужны услуги выделенного сервера либо мощной VDS, где ваш сайт сможет использовать все выделенные доступные ресурсы и работать эффективно.
Цифры
Для каждого запроса к БД вычисляются параметры исполнения, если они малы и не могут являться источником излишней нагрузки на сервер баз данных то они игнорируются. В противном случае запросы сохраняются и могут быть проанализированы. На основании анализа может быть вынесено решение об предупреждении либо остановки аккаунта хостинга, базы данных либо её частей.
Кто меряет
Microsoft SQL Server имеет встроенную систему трассировки исполняемых запросов, используя которую можно получать данные об исполняемых запросах и процессах внутри SQL Server. Создавать новые трассировки можно как программно, используя соответствующие хранимые процедуры (начать стоит с sp_trace_create) так и при помощи инструментов идущих в комплекте с SQL Server – SQL Server Profiler. Profiler используя систему трассировок позволяет отслеживать и записывать в лог запросы удовлетворяющие некоторым параметрам а также характеристики их исполнения.
Система мониторинга Microsoft SQL Server на 1Gb.ru использует систему трассировок программно, минуя профайлер – это позволяет более эффективно отслеживать запросы и не зависеть от внешних процессов. Если вы являетесь программистом и хотите отлаживать или отслеживать запросы на локальном сервере – следует использовать Profiler. Использовать систему трассировки на серверах хостинга не получится – для этого у клиентов хостинга нет достаточных прав.
Что меряется
С целью контроля нагрузки от исполнения запросов для каждого запроса записываются следующие параметры исполнения:
- Reads - число логических операций чтения.
- Writes - число логических операций записи.
- CPU – количество миллисекунд ЦПУ (1000ms – секунда).
- RowCounts* – количество рядов (строк) возвращаемой таблицы.
- StartTime – время начала исполнения запроса.
- EndTime – время конца исполнения запроса.
Также для лучшего понимания сути происходящего для запроса собираются следующие данные:
- DatabaseName – имя базы данных, в контексте которой исполняется запрос.
- LoginName – имя логина MSSQL, из под которого исполняется запрос.
- TextData – текст запроса, может быть обрезан.
- TextDataLength – полный размер предыдущего поля.
- Error* – исполнился ли запрос или возникла ошибка.
- ApplicationName – имя приложения, подавшего команду на исполнение.
- HostName – имя хоста, на котором исполняется приложение.
* - только для версии 2005 и выше
А что же цифры?
Ключевыми параметрами являются Reads и CPU: Reads свидетельствует о потреблении дисковой подсистемы (количество операций чтения с диска) и потреблении памяти (количество операций чтения не с диска), CPU свидетельствует о потреблении ЦПУ сервера. Чем больше характеристики, тем хуже. При достижении некоторой критической точки становится совсем плохо и работа базы начинает влиять на общую производительность сервера свыше тех лимитов, которые ей предоставлены тарифным планом и регламентом хостинга.
Считается, что хорошими показателями исполнения запросов являются следующие числа:
- Reads - не более 500 - 1000 на формирование страницы сайта.
- 2000 – 5000 для регулярных запросов – проблема.
- 50 000 проблема или показатель серьезной неоптимальности БД в случае единичных запросов.
- Более 100 000 – критическая ситуация, в случае регулярных запросов БД может быть остановлена.
- Writes – не более 50 – 100 для регулярных запросов.
- Более 10 000 – критическая нагрузка.
- CPU – до 100 на исполнение рядового запроса.
- Более 1000 – 3000 – критическая нагрузка.
В договоре нет этих цифр!
Да, верно. Цифры из предыдущего раздела являются разумным ориентиром. Контроль нагрузки запросов основывается на ряде статей регламента хостинга – 1.4.d, 2.3.a, 2.3.b, 2.3.d, 2.3.e
Сценарии решения проблемных запросов
В зависимости от того, являетесь ли вы разработчиком скриптов и БД или нет – существует несколько вариантов решения проблем.
Можно оставить как есть?
Нет, нельзя. Мы не можем продолжать обслуживание БД, которая является источником проблем производительности сервера. Если оставить как есть - база данных будет остановлена и сайт работать не будет.
Представьте себя на другой стороне - вы клиент хостинга, используете БД MSSQL, все ваши запросы отлажены и великолепно работают. Однако ваш сайт "тормозит" из-за того, что соседний клиент развернул проект не предназначенный для виртуального хостинга или содержащий грубые ошибки программирования. Что будет вашим первым, вторим или третьим желанием?
Наша конечная цель в том, что бы таких желаний не возникало и у всех всё работало прекрасно. Поэтому мы вынуждены вмешиваться в работе проектов с проблемами вплоть до отказа в услугах хостинга.
CMS
Если вы используете CMS для которой требуется поддержка СУБД Microsoft SQL Server и у вас нет возможности исправлять код CMS.
Рекомендуем вам выполнить следующие пункты:
- Обновите CMS до последней версии, если в интервале между вашей версией и актуальной были произведены изменения положительно влияющие на производительность.
- Поищите в интернете схожие проблемы и пути их решения – вероятно этой проблемой уже кто-то занимался и успешно её решил.
- Обратитесь к разработчикам CMS или не тематические форумы с описанием проблемы – вероятно, вам смогут дать адекватный совет по решению проблемы.
- Попробуйте найти или понять, какая именно часть сайта является источником запроса – отключите её, если есть такая возможность или измените её параметры, если они могут положительно повлиять на производительность.
Собственная разработка
Если БД и скрипты являются плодом вашей собственной разработки, то вам следует оптимизировать работу скриптов и БД.
Для проведения работ по оптимизации работы базы данных – сделайте бэкап БД, скачайте его на локальный компьютер и разверните локально. Все работы и тесты производите с локальной БД, так как они тоже могут вызывать излишнюю нагрузку и являться основанием для блокировки аккаунта по перегрузке. Используйте Profiler в сочитании со средством выполнения запросов и просмотра Execution Plan. Выпишите все типы запросов, которые могут встретится на сайте и примените пункты (ниже) для каждого типа запросов.
Посмотрите статьи по оптимизации производительности MSSQL, например, тут - http://www.sql.ru/articles/articles.aspx?g=MSSQLServer
В общем виде, следуйте следующим лёгким правилам:
- Обязательно должен быть ключевой уникальный индекс.
- Стройте индексы исходя из запросов обрабатываемых БД.
- Не забывайте про возможность строить индексы по нескольким полям.
- Не забывайте, что очередность полей в индексе имеет значение.
- Избегайте использования временных таблиц.
- Избегайте использования курсоров (sp_cursoropen).
- Можно пренебречь нормализацией в пользу производительности.
- По возможности не используйте массовое изменение (UPDATE) или удаление (DELETE) данных.
- Используйте полнотекстовые индексы для поиска по большим текстовым полям.
- Возвращайте только необходимо число строк из БД.
- Выбирайте только необходимые столбцы.
- По возможности избегайте использования BLOB-данных.
