Вероятность того, что вы будете сталкиваться с различными проблемами производительности при написании запросов и работе с SQL, велика, если вы используете его на регулярной основе. Вы можете иметь дело с медленными или плохо написанными запросами, неэффективными подзапросами, индексами с тоннами записей в них, но с небольшим количеством операций чтения или вообще без них, или другими проблемами ЦП и памяти. В этой статье объясняется, как найти и решить множество проблем производительности SQL.
Определение банка
Давайте посмотрим на некоторые определения, прежде чем мы начнем:
- Состояния ожидания или состояния очереди. Период ожидания, который наступает после выполнения запросов или загрузки ресурсов, связанных с конкретными задачами. В то время как SQL выполняет один или несколько запросов или извлекает ресурсы, необходимо потратить определенное количество времени как на сканирование хранилища и данных, так и на выполнение вычисления или задачи под рукой. Два распространенных типа ожидания — это типы ожидания защелки, такие как PAGEIOLATCH_EX, который относится к ожиданию, которое происходит, когда задача ожидает в защелке буфера типа запроса ввода-вывода, и типам ожидания CXPACKET, типичной проблеме, связанной с сервером высокого уровня. Загрузка процессора из-за плохо написанных параллельных запросов (запросов, предназначенных для одновременного выполнения). Третьим распространенным типом ожидания является ожидание WRITELOG, которое связано с сеансом SQL, записывающим содержимое кэша журнала на диск, где хранится журнал.
- Блокировка: в SQL есть ресурсы блокировки и режимы блокировки. Ресурсы блокировок относятся к местам, в которых SQL может устанавливать блокировки, а режимы блокировок относятся к блокировкам, которые могут быть наложены на ресурсы, чтобы к ним можно было получить доступ при одновременных задачах и транзакциях. Существует несколько ресурсов, в которые можно поместить блокировки, например строку в таблице или блокировку каждой строки в индексе. Существует также несколько типов режима блокировки, таких как общие блокировки и эксклюзивные блокировки. Некоторые блокировки вполне подходят, но другие могут отрицательно сказаться на производительности.
- Дисковый и сетевой ввод / вывод: данные и транзакции SQL направляются в и из диска, кэша или по сети. Чем больше, тем хуже может быть производительность. Однако точная настройка запросов и индексации может значительно сократить ввод и вывод на физических и логических дисках и в сети.
- Конфликт: обычно термин, связанный с конфликтом в блокировке. Блокировка в SQL помогает обеспечить согласованность при выполнении задач чтения или записи в базе данных, но может возникнуть конфликт при блокировке. Конфликт может возникнуть, например, когда процессы пытаются выполнять обновления одновременно на одной странице.
- Высокая загрузка ЦП: высокая загрузка ЦП сервера, связанная с SQL, напрямую связана с выполняемыми процессами SQL, плохим выполнением запросов, системными задачами и чрезмерной компиляцией и перекомпиляцией запросов. Процессор также может быть нагружен, если на нем имеются плохие индексы.
1. Плохо написанный SQL
Поскольку SQL декларативен, вы можете написать один и тот же запрос разными способами, чтобы получить одинаковые результаты. Эти различия в написании запросов могут отрицательно повлиять на производительность. Существует два способа переписать запросы для повышения производительности:
- Перепишите SQL, чтобы минимизировать объем запросов и ускорить их выполнение.
- Перепишите SQL, чтобы убрать необходимость в подзапросах.
Переписывание запросов
Давайте рассмотрим пример переписывания запроса для повышения производительности. Скажем, вы хотите найти все перекрытия для данного диапазона. Вы можете сделать это несколькими различными способами, но давайте рассмотрим эти два:
SELECT *
FROM range
WHERE end_time >= @start
AND start_time <= @end
However, this query would obtain the same results, only faster:
SELECT *
FROM range
WHERE (start_time > @start AND start_time <= @end)
OR (@start BETWEEN start_time AND end_time)Последний быстрее, потому что он более конкретен в точных диапазонах, которые должны быть найдены.
Удаление подзапросов
Подзапрос — это запрос, который нуждается в другом запросе для правильной работы (или вообще). Тем не менее, существуют проблемы с производительностью подзапросов; они могут быть медленнее и приводить к возвращению значений NULL. Тем не менее, подзапросы могут быть переписаны как Joins, чтобы избежать таких проблем. Ниже приведен пример подзапроса, переписанного как Join:
SELECT * FROM employeeTable WHERE id NOT IN (SELECT id FROM employeeTable2);
SELECT * FROM employeeTable WHERE NOT EXISTS (SELECT id FROM employeeTable2 WHERE employeeTable.id=employeeTable2.id);
Can be turned into:
SELECT employeeTable.*
FROM employeeTable LEFT JOIN employeeTable2 ON employeeTable.id=employeeTable2.id
WHERE employeeTable2.id IS NULL;Последний достигает тех же результатов без использования подзапроса, улучшая производительность и избегая любых других проблем.
2. Плохие показатели
Если число функций записи в таблицу и ее индексы значительно превышает число операций чтения в нее, существует высокая вероятность того, что базовые индексы снижают общую производительность. Каждый раз, когда выполняется запись в столбец SQL, имеющий индекс, соответствующая модификация также должна быть активирована для индексов столбца. Если большая часть действия связана с записью, возможно, стоит подумать об удалении или изменении соответствующих индексов. Это, вероятно, повысит производительность за счет снижения общего объема операций записи. Найдите неверные индексы с помощью динамических представлений управления для анализа статистики выполнения запросов. После нахождения индексов, у которых много записей, но нет или мало операций чтения, рассмотрите возможность удаления этих индексов для повышения производительности.
3. Конфликт блокировки
Конфликт блокировки может возникать, когда существуют процессы, которые пытаются выполнить обновления блокировки одновременно на одних и тех же ресурсах блокировки. Кроме того, когда две или более задач блокируют друг друга, поскольку каждая задача имеет блокировку для ресурса, в котором другие задачи пытаются установить блокировку, может возникнуть тупик, поскольку ни одна из них не может быть решена.
Для серверных целей это можно объяснить несколькими запросами от нескольких пользователей. Это повышает вероятность возникновения конфликтов с течением времени, поскольку многочисленные процессы могут одновременно запрашивать доступ к одним и тем же ресурсам базы данных.
Вот несколько советов по обнаружению конфликтов и блокировок:
- Используйте информационный инструмент базы данных для обнаружения. По словам Microsoft, «… компонент Database Engine предоставляет инструменты мониторинга… флаги трассировки и событие графика тупиков в SQL Server Profiler».
- Убедитесь, что все ресурсы базы данных доступны в том же порядке каждый раз.
- Обновите SQL до начала задачи или транзакции.
- Не допускайте использования данных во время задач и транзакций.
- Ограничьте или избегайте использования курсоров при кодировании.
- Держите свои транзакции маленькими.
4. Проблемы с памятью
Как правило, SQL-серверы — это проблемы с памятью, даже если на сервере установлено достаточно памяти. Для повышения производительности и уменьшения использования памяти SQL Server автоматически сохраняет данные в кэшах памяти. Как только данные будут считаны с диска, они не изменятся, если только SQL не потребуется обновить данные. Если ваш сервер SQL имеет достаточно памяти для кэширования всей базы данных, SQL Server будет кэшировать все это. SQL запрограммирован, чтобы компенсировать такие вещи, как избыточный код, значения NULL, слишком много записей и недостаточно операций чтения в запросах, что приводит к увеличению используемой памяти и снижению производительности.
Вот что вы можете сделать, чтобы проверить и управлять использованием памяти:
- Откройте SQL Server Management Studio и подключитесь к нужному серверу.
- Выберите «Аутентификация Windows» в меню «Аутентификация».
- Выберите View, затем Object Explorer, затем щелкните правой кнопкой мыши на имени сервера и выберите Properties. Нажмите «Память» слева, и вы увидите различные опции, в том числе «Использование AWE для выделения памяти», «Минимальная память сервера» и «Максимальная память сервера».
Установите флажок AWE для выделения памяти при использовании 32-разрядного сервера с объемом памяти менее 4 ГБ, но оставьте его без проверки, если используется больше памяти или 64-разрядный сервер. Убедитесь, что минимальная память сервера установлена на ноль. Не меняйте это число — если оно было изменено, проведите некоторое исследование, чтобы выяснить, почему, потому что не рекомендуется менять его или устанавливать минимальное и максимальное значения на одну и ту же сумму. Если вам нужно уменьшить максимальную память сервера, уменьшите и минимальную. Если вам нужно увеличить максимум, оставьте значение минимума таким же.
Последняя опция, Максимальная память сервера, будет иметь значение по умолчанию. Если вы пытаетесь уменьшить объем памяти, который может использовать SQL Server, вы можете установить для него все, что захотите. Имейте в виду, что если вы установите его ниже, производительность также снизится. Иногда администраторы устанавливают меньшее значение, например 6 ГБ, чтобы освободить память для бесперебойной работы ОС. Вы можете протестировать производительность на основе изменения минимумов и максимумов, чтобы увидеть, что лучше всего подходит для вашей конкретной установки.
5. Высокая загрузка ЦП
Высокая загрузка ЦП может происходить по ряду причин, но обычно это вызывается следующими причинами:
- Плохо написанные запросы
- Высокие компиляции и перекомпиляции
- Высокое использование временной таблицы
- Системные потоки, вызывающие высокие пики в процессоре
Поскольку мы уже рассмотрели плохо написанные запросы и коснулись высоких компиляций и перекомпиляций, давайте рассмотрим другие возможные проблемы:
- Широкое использование временной таблицы. Поскольку ваши запросы становятся более сложными, временные таблицы, как правило, используются чаще. Попробуйте исключить использование временных таблиц для повышения производительности процессора.
- Системные потоки: Проверьте это, написав select * from sys.sysprocesses, где spid <51, и проверьте наличие проблем с системными потоками.
Монитор ресурсов также может использовать большое количество ресурсов процессора, когда виртуальная память заканчивается. Конкретную информацию по устранению неполадок можно найти на веб-сайте поддержки Microsoft.