Как настроить запросы производительности для SQL Server
Станьте мастером SQL!
Оказывается, хранение данных по строкам и столбцам удобно во многих ситуациях, поэтому реляционные базы данных остаются краеугольным камнем управления данными на предприятиях по всему миру. Язык структурированных запросов (SQL) - это мощный язык запросов, который позволяет извлекать данные в реляционных базах данных и манипулировать ими. Основы запроса данных с помощью SQL довольно легко освоить, и я настоятельно рекомендую изучить их, если вы с ним не знакомы.
В этой статье я расскажу вам, как можно исследовать медленно выполняющиеся запросы. Даже если вы не занимаетесь программированием, SQL - это фантастический язык, который можно использовать в вашем наборе инструментов для ситуаций, в которых excel не справляется! Если вы новичок, ознакомьтесь с моим введением в SQL.
Следуй вместе!
Его можно загрузить и использовать бесплатно! Я использую Microsoft SQL Server и SQL Server Management Studios на работе и дома, так что это то, что я использую в примерах.
Почему мой запрос медленный?
Есть много причин, по которым запрос может выполняться медленно, и это не всегда очевидно. Я написал множество запросов, которые, как мне казалось, будут легко обработаны, но в итоге потребовалось абсурдное количество времени, пока я немного отрегулировал их. Если вы новичок в оптимизации запросов, ознакомьтесь с описанием механизма запросов SQL Server 101 ниже. Если вы уже знаете это, переходите к советам по оптимизации запросов!
Модуль запросов SQL Server 101
Хотя синтаксис запроса довольно прост, под капотом SQL Server нужно многое понять. Я не могу осветить все это в статье, но я дам вам заметки на скалах.
SQL Server Engine состоит из двух основных частей: Storage Engine и Query Processor (Relational Engine) . Обработчик запросов - это часть SQL Server, которая принимает все входящие запросы и разрабатывает для них План выполнения. Нет гарантии, что для запроса всегда будет выбран один и тот же план. Позже я подробнее расскажу о планах выполнения ...
Четыре основных шага Обработчика запросов:
Анализ - проверяет, что запрос использует допустимый синтаксис.
Привязка - проверяет, существуют ли объекты и отвечают за разрешение имен.
Оптимизировать - использует оптимизацию на основе затрат для создания оптимального плана выполнения.
Выполнить - выполняет план выполнения
Оптимизатор
Оптимизатор запросов приходит к оптимальному плану, создавая и оценивая как можно больше планов выполнения в заданном пространстве поиска. Пространство поиска - это все возможные планы выполнения запроса. Любой план в области поиска должен возвращать результаты запроса.
Конечно, оптимизатор не всегда может оценить ВСЕ возможные планы. Исчерпывающий поиск может занять до смешного много времени и повлиять на общую производительность. Например, сложный запрос может иметь миллионы возможных комбинаций планов. Оптимизатор находит баланс между качеством плана и временем поиска.
Планы выполнения состоят из физических лиц, называемых операторами. Операторы станут более понятными, когда мы посмотрим на планы. Чтобы произвести ориентировочную стоимость плана, оптимизатор учитывает:
- Затраты на физический оператор и такие вещи, как ввод-вывод и память
- Предполагаемое количество записей (оценка количества)
Чтобы помочь оптимизатору запросов с помощью оценок мощности, SQL Server использует сохраненную информацию о распределении значений и столбцов в таблице под названием Статистика . Оптимизатор запросов довольно быстро суммирует все эти затраты и определяет, какой план достаточно хорош для использования!
Планы выполнения
Вы можете увидеть план выполнения запроса, щелкнув правой кнопкой мыши и выбрав Показать план выполнения или Показать предполагаемый план выполнения. Используйте оценочный план выполнения, если вы хотите найти узкие места перед выполнением большого или сложного запроса.
Изучение плана
Наиболее распространенный способ просмотра плана выполнения - это древовидный формат, в котором для представления операторов используются изображения. Например:
В этом примере вы можете увидеть два оператора в плане выполнения: SELECT и Просмотр таблицы. Вы также видите стрелку , которая представляет поток данных. Чем толще стрелка, тем больше записей.
Первый оператор называется оператором Результаты и в основном используется для представления SELECT. Кроме того, здесь много операторов! Каждый выполняет одну функцию, такую как сканирование, фильтрация или агрегирование. Он может представлять собой логическую операцию и / или физическую операцию. Ищите их, когда вам нужно, вместо того, чтобы пытаться запомнить их все!
Советы по оптимизации запросов
Несмотря на попытки, план, выполняемый обработчиком запросов, не всегда будет лучшим планом. Например, неправильная оценка количества элементов может привести к неправильному оператору. Вот почему вам нужно научиться оптимизации запросов! Вот мои основные советы и методы устранения неполадок при запросах.
ВКЛЮЧИТЬ статистику ввода-вывода
Использование команды SET Statistics IO ON перед запросом предоставляет информацию, которая может помочь в устранении неполадок с запросом. СТАТИСТИКА IO показывает вам IO, понесенный для каждого объекта. Это полезно для понимания того, что произошло за кулисами и как были получены данные.
Когда запрос завершится, перейдите на вкладку Сообщения, чтобы увидеть результат.
Обратите внимание на логические чтения 19397 года.
Чем меньше число, тем лучше чтение (логическое и физическое). Логическое чтение - это когда данные читаются из пула буферов SQL Server. Механизм SQL Server использует пул буферов при передаче данных, например, при получении их с диска.
Чтобы значительно уменьшить количество логических чтений, попробуйте добавить в таблицу индекс.
Обратите внимание, что логическое чтение - 0. Производительность значительно выросла по сравнению с запросами к таблице без индекса. Вы можете увидеть разные типы чтения вместо стандартных логических или физических. Это потому, что я использую индекс ColumnStore. Индексы ColumnStore обычно используются для больших таблиц данных или хранилищ данных.
Использовать индексы
Для хорошей производительности необходимо, чтобы таблицы имели хорошую индексацию. Не вдаваясь в подробности, в принципе, есть два типа указателей:
Кластерный индекс - Кластерные индексы сортируют и сохраняют строки данных в таблице или представлении на основе значений ключей.
Некластеризованный индекс - некластеризованный индекс - - это индекс, в котором строки упорядочены по столбцам, составляющим индекс.
Таблица без кластерного индекса называется кучей. Большинство таблиц должны иметь кластерные индексы. Если таблица представляет собой кучу, по-прежнему можно добавлять некластеризованные индексы. Таблицы могут иметь только 1 кластеризованный индекс, но много некластеризованных индексов.
Если вы не уверены, что включить в индекс, сгенерируйте оценочный план выполнения, а функция Отсутствующие индексы предоставит информацию об отсутствующих индексах, которые могут повысить производительность запроса.
Соблюдайте осторожность
Будьте осторожны при создании некластеризованных индексов, поскольку они занимают место, а чрезмерное индексирование - это плохо. Проблема с слепым созданием этого индекса в примере заключается в том, что SQL Server решил, что он полезен для определенного запроса (или нескольких запросов), но игнорирует остальную рабочую нагрузку. Индекс может не подходить, поэтому знайте, что вы делаете.
Избегайте союзов
Когда я запрашиваю большие таблицы, я стараюсь избегать UNION. Когда я вижу запросы, использующие его, я надеюсь, что собрал спальный мешок, потому что я могу быть там всю ночь!
Во-первых, важно знать разницу между UNION и UNION ALL. Оператор UNION используется для объединения набора результатов из двух или более операторов SELECT, но он исключает дубликаты. UNION ALL включает дубликаты; он по существу объединяет два набора данных . Поскольку они ведут себя по-разному, у них очень разные планы выполнения:
Давайте посмотрим на эту ситуацию:
--table testing has 1,000,000 rows --table testing2 has 5,000,000 rows --I want to return all 6 million rows. Which should I use? Is there a faster way? select * from testing union select * from testing2 select * from testing union all select * from testing2
Из двух вариантов UNION ALL гарантирует включение всех строк. Однако при сравнении планов выполнения UNION ALL работает медленнее!
Обратите внимание, что в этом примере операция UNION ALL занимает более 5 минут!
Вместо использования UNION ALL мне нравится использовать временную таблицу. Я выгружаю данные во временную таблицу, а затем выбираю все из таблицы.
select * into #tek from testing2 insert into #tek select * from testing select * from #tek --drop table #tek
Обратите внимание, что этот метод занял менее 3 минут, чтобы вернуть 6 миллионов строк, по сравнению с UNION ALL, который занял более 5! Поскольку данные существуют во временной таблице, они находятся в полупостоянном месте, что позволяет вам делать с ними больше, если это необходимо.
Избегайте сортировки
Сделайте все возможное, чтобы оператор сортировки не отображался в плане выполнения. Сортировка выполняется медленно и может отнимать много ресурсов, что приводит к утечкам, которые съедают tempDB! Если вы видите предупреждающий знак в своем плане выполнения, наведите на него курсор, чтобы узнать, что он говорит.
Не используйте в запросе предложение ORDER BY, если оно вам не нужно. В идеале, если вам нужно часто выполнять сортировку по определенному столбцу, вы можете добавить некластеризованный индекс для этого столбца, чтобы избежать использования операторов сортировки в плане.
Точечные ленивые столовые катушки
Я видел, как Table Spools заставляет запросы занимать часы, тогда как они должны занимать минуты или даже секунды. Оператор буфера таблиц в основном используется для создания временной таблицы в памяти или на диске, в которой хранятся результаты подзапросов, которые могут использоваться несколько раз при выполнении. план. Буфер таблиц создает временную таблицу, которая является ленивой, то есть она обращается к строкам только тогда, когда это необходимо. Существует около 5 различных типов операторов спула, но все они имеют одинаковое назначение.
Столовые катушки сложны, потому что иногда они могут показывать низкий процент затрат, но могут быть огромным узким местом в плане выполнения. Наведите указатель мыши на оператор, чтобы увидеть, как оцениваемые строки сравниваются с фактическими строками, и собрать дополнительную информацию о свойствах!
Чтобы избежать использования Table Spools, попробуйте использовать индекс, включающий все поля в запросе. Если это невозможно, вы можете попробовать Подсказки запроса или установить порядок запроса или указать операцию соединения. Например, попробуйте использовать INNER HASH JOIN вместо INNER JOIN. Принуждение к хэш-объединениям может значительно повысить скорость, но они могут привести к значительному перетеканию в TempDB, поэтому будьте очень осторожны при использовании подсказок в запросах!
Последние мысли
Понимание планов выполнения и оптимизация SQL-запросов может быть утомительным и требующим времени для изучения. Я использую SQL в течение многих лет и все время осваиваю новые методы! Если вы помните следующее, вы на пути к тому, чтобы стать мастером настройки SQL-запросов:
- Использовать индексы таблиц
- Включите статистический ввод-вывод
- Проверить план выполнения
Благодарю вас!
- Если вам понравилось, подписывайтесь на меня на Medium, чтобы узнать больше
- Получите ПОЛНЫЙ ДОСТУП и помогите поддержать мой контент, подписавшись
- Давайте подключимся к LinkedIn
- Анализировать данные с помощью Python? Загляните на мой сайт
