Vvmebel.com

Новости с мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Оптимизация запросов sql server

Оптимизация запросов sql server

Методы оптимизации запросов к SQL Server — Советы для написания эффективных и быстрых запросов

Инновационный центр — Группа организаций Baba Farid

Автор перевода: Прищепа В.В.

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

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

II. ОБЗОР ВЫПОЛНЕНИЯ ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ СТАТИСТИКИ ЧТЕНИЯ/ЗАПИСИ

Важным параметром является количество логических операций чтения производящихся по запросу. Возможность просматривать этот параметр предусмотрена в SQL Server Management Studio. Для определения числа логических операций чтения, вы можете включить или выключить отображение параметра STATISTICS IO с помощью такого запроса:

SET STATISTICS IO ON

Рассмотрим следующий запрос:

SELECT * FROM tablename

В окне результата SQL Server Management Studio вернулось следующее сообщение: «Table ‘tablename’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0«.

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

III. ОБЩИЕ РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ

Используйте конкретные имена столбцов вместо * в запросе SELECT

Запрос SQL, становится быстрее, если использовать имена столбцов в SELECT вместо ‘*’. Так что нам нужно ограничить запросы с выборкой всех столбцов, выбрав только определенные столбцы из таблицы. Это приводит к выигрышу общей производительности, уменьшению сетевого трафика.

SELECT col_1, col_2, col_3, col_4, subject FROM table_name;

SELECT * FROM table_name.

Используйте альтернативные методы для возврата общего количества строк таблицы вместо COUNT (*)

SELECT COUNT (*) делает полное сканирование таблицы, это может занять много времени для больших таблиц. Если нам нужно узнать количество строк таблицы, мы можем использовать альтернативный способ – системную таблицу sysindexes. В ней присутствует столбец ROWS, содержащий общее количество строк для каждой таблицы в системе. Таким образом, мы можем использовать следующий оператор выбора:

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID (‘table_name’) AND indid 10;

SELECT id, col1, col2 FROM table
WHERE col2 != 10.

Для сравнения строк:

SELECT id, col1, col2 FROM table
WHERE col1 LIKE ‘Nav%’;

SELECT id, col1, col2 FROM table
WHERE SUBSTR(col1,1,3) = ‘Nav’.

Для сравнения чисел в диапазоне:

SELECT Col1, Col2 FROM table
WHERE Col3 BETWEEN MAX (Col3) and MIN (Col3);

SELECT Col1, Col2 FROM table
WHERE Col3 >= MAX (Col3) and Col3 <= MIN (Col3).

Применяйте модифицированное значение колонки в инструкции WHERE только при необходимости.

SELECT id, Col1, Col2 FROM table
WHERE Col2 < 25000;

SELECT id, Col1, Col2 FROM table
WHERE Col2 + 10000 < 35000.

IV. ЕЩЕ НЕСКОЛЬКО СОВЕТОВ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ/ТАБЛИЦ/ХРАНИМЫХ ПРОЦЕДУР

  • Таблица должна иметь минимум один кластеризованный индекс и соответствующее число не кластеризованных индексов;
  • Избегайте использования триггеров, если это возможно. Включите логику триггера в хранимую процедуру;
  • Таблица должна иметь ключевое поле;
  • Старайтесь использовать переменные таблицы вместо временных. Переменные занимают меньше системных ресурсов и ресурсов логов;
  • Избегайте использования VIEW, постарайтесь заменить их таблицами;
  • Избегайте инструкции DISTINCT, используйте ее только если это действительно необходимо;
  • Используйте TOP в иснтрукции SELECT, если необходимо выбрать некоторое количество строк в начале таблицы;
  • Оформите повторяющийся код в пользовательскую процедуру. Это поможет улучшить производительность, ускорить вашу работу, уменьшить сетевой трафик;
  • Использование TRUNCATE вместо DELETE позволит ускорить удаление строк из таблицы, потому что удаление происходит без записи информации в лог-файл;
  • Избегайте использования курсоров, если это возможно, они сильно замедляют производительность;
  • Когда разрабатывается запрос с подзапросами:
    • Используйте коррелированный подзапрос только тогда, когда возвращаемый результат будет относительно небольшим и/или другие критерии быстродействия подзапроса будут эффективными;
    • Используйте не коррелированные подзапросы при работе с большими таблицами, из которых ожидается большой результат и/или подзапрос имеет низкие показатели эффективности;
    • Убедитесь в том, что несколько подзапросов расположены в наиболее эффективном порядке;
    • Переписывание подзапроса с JOIN иногда может повысить эффективность;
  • Для хранения символьных и строковых данных используйте char/varchar вместо nchar/nvarchar, если нет необходимости в использовании UNICODE. В первом случае для хранения символов используется один байт, во втором – два;
  • Можно попытаться использовать инструкцию RETURN для возвращения целочисленного значения вместо того, чтобы это значение было частью результирующего набора данных;
  • Очистите систему от неиспользуемых индексов, они занимают место на диске и замедляют операции DML;
  • Создавайте индексы для целочисленных полей, это способствует меньшему объему индекса на диске, меньшему количеству операций чтения при использовании индекса;
  • Если часто используется объединение одних и тех же таблиц, то стоит создать индекс для объединяемых столбцов.

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

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

Дата поста: 01-10-2012

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

1. Оптимизация таблиц.

Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины — text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

Читать еще:  Установка видеокарты на пк

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

2. Перестройка данных в таблице.

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

3. Тип данных.

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

4. NOT NULL и поле по умолчанию.

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

5. Постоянное соединение с сервером БД.

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

6. Разделение данных.

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

Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть — не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример — счётчик посещений.

Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе — текстовое, а третье числовое — считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже — отсыл к ключевому полю id из первой таблицы.

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

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

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

7. Имена полей,

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

8. Требовать меньше данных.

При возможности избегать запросов типа:

Запрос не эффективен, так как скорее всего возвращает больше данных, чем необходимо для работы. Вариантом лучше будет конструкция:

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится «легче»; и производительнее.

Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.

Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.

Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.

Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

9. Ограничить использование DISTINCT.

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

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

10. Ограничить использование SELECT для постоянно изменяющихся таблиц.

11. Не забывайте про временные таблицы типа HEAP.

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

12. Поиск по шаблону.

Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%

13. Команда LOAD DATA INFILE

позволяет быстро загружать большой объём данных из текстового файла

14. Хранение изображений в БД нежелательно.

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

15. Максимально число запросов при генерации страницы,

как мне думается, должно быть не более 20 (+- 5 запросов). При этом оно не должно зависеть от переменных параметров.

Оптимизация запросов sql server

Какую огромную разницу в скорость исполнения запроса может внести индекс! Недавно автор статьи получил еще одно наглядное подтверждение этой истины. Он ввел дополнительный индекс, и время обработки запроса уменьшилось с 40 минут до 12 секунд. Но индексы — всего лишь один из инструментов, применяемых администраторами баз данных для повышения производительности систем. Улучшить производительность можно настройкой самых разных параметров — от конфигурации технических средств до использования утилит баз данных. Ниже приведены 9 самых эффективных средств увеличения производительности SQL Server 6.5.

1.Выделите серверу столько оперативной памяти, сколько он выдержит.

Чем большая часть базы данных сможет уместиться в кэше, тем быстрее будут обрабатываться запросы. Поэтому целесообразно увеличивать размер оперативной памяти пропорционально размеру базы данных. К примеру, если ваша база данных занимает 1 Гб, то оперативная память размером 1 Гб позволит разместить практически всю базу данных в памяти. Некоторую часть оперативной памяти следует оставить для Windows NT. Автор предпочитает оставлять для операционной системы от 64Мб до 128 Мб, а всю оставшуюся часть памяти отводить под SQL Server. И непременно надо сохранять объем доступной физической памяти NT не менее 4 Мб. Если он окажется ниже указанного предела, то NT немедленно начнет создавать страницы виртуальной памяти на диске.

2. Используйте массивы RAID уровня 0 или 5 для распараллеливания получения информации из базы данных.

Массивы RAID уровней 0 и 5 распределяют запросы на чтение по нескольким физическим дискам. Вы, наверняка, знаете, что творится на подступах к мостам в час пик, когда тысячи машин одновременно стремятся проехать через пространство ограниченной ширины. Такое же узкое место возникает и для запросов на чтение файлов с устройств вашей базы данных. Если вам удастся направить данные по нескольким каналам, то сервер сможет параллельно считывать блоки данных с каждого жесткого диска. При этом наблюдается почти линейное улучшение производительности. Такое увеличение пропускной способности для операций чтения обязано своим возникновением массивам RAID уровней 0 и 5. В качестве примера приведем цифры из книги Рона Саукапа ‘Внутри SQL Server 6.5’, вышедшей в издательстве Microsoft Press в 1997 году. Он пишет, что один жесткий диск емкостью 4 Гб в состоянии обработать 80 — 90 операций ввода/вывода в секунду. В то же время массив RAID уровня 0 из 8 жестких дисков по 500 Мб каждый (то есть, обладающий такой же суммарной емкостью) пропускает 400 операций ввода/вывода в секунду. Конечно, при этом вопрос увеличения затрат остается открытым. Но в общем случае, чем больше жестких дисков в массиве, тем больше пропускная способность базы данных для операций чтения.

Читать еще:  Греется видеокарта на ноутбуке

3. Позвольте функции Max Async I/O воспользоваться всеми преимуществами вашего компьютера.

Возможно, ваша дисковая подсистема в состоянии обрабатывать свыше восьми асинхронных операций ввода/вывода в секунду, то есть больше величины, принятой в качестве значения по умолчанию более трех лет назад при выходе в свет версии SQL Server 6.5. Для оптимизации этого параметра следует увеличивать его небольшими шагами, наблюдая при этом за значением счетчика средней длины очереди к дискам, AvgDiskQueueLength, в мониторе производительности NT (NT Performance Monitor). До тех пор, пока средняя очередь к дисковой подсистеме не превышает удвоенного количества дисков в ней, можно считать, что вы ее не перегружаете.

4. Установите пороги расширения блокировок на всю таблицу.

Три параметра расширения блокировок на всю таблицу (LE — Lock Escalation): Максимальный порог (LE Threshold Maximum), Минимальный порог (LE Threshold Minimum) и Пороговый процент (LE Threshold Percent), определяют, сколько страниц должен заблокировать SQL Server, прежде чем будет заблокирована вся таблица целиком. По умолчанию для этих параметров приняты значения соответственно 200, 20 и 0. Для очень больших таблиц блокировка всей таблицы позволяет избежать накладных расходов, связанных с тысячами блокировок. Если в базе данных содержатся сотни таблиц, то устранение таких накладных расходов может оказать существенное влияние на производительность.

5. Создайте кластеризованные индексы для запросов, которые считывают диапазоны значений.

В силу того, что кластеризованные индексы упорядочивают данные физически, располагая их в порядке следования значений в индексе, такие индексы представляют собой прекрасную основу для запросов, которые ищут диапазоны значений. Например, если у таблицы, содержащей отдельные записи (строчки) счетов, имеется кластеризованный индекс по столбцу с идентификатором счета, то все строчки, относящиеся к счету 0001, будут находиться в самом начале таблицы, а строчки счета 9999 расположатся в самом ее конце. Такой порядок означает, что если эта таблица будет соответствовать части ‘многие’ отношения ‘один — ко — многим’ при соединении с другой таблицей, то SQL Server сможет легко найти первую строку с нужным значением идентификатора счета, а затем последовательно двигаться по таблице до тех пор, пока значение идентификатора не изменится.

6. Сформируйте не кластеризованные индексы для запросов на поиск уникальных значений.

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

7. Создайте составные индексы для поддержания множества запросов.

В тех случаях, когда с вашими таблицами в основном выполняются операции UPDATE и INSERT, а также производится чтение данных, уменьшение количества индексов позволит снизить накладные расходы на сопровождение индексов. Операция INSERT заставляет SQL Server добавлять новые записи в индекс, а операция UPDATE может привести к перемещению строки на новое место в индексе, или даже на новую страницу в таблице. Более того, часто SQL Server выполняет операцию удаления как последовательность двух операций: сначала удаляется старая строка, а затем вставляется новая. С точки зрения накладных расходов управления индексами, это наихудший вариант. Выход из этой ситуации — создание составных индексов, которые SQL Server сможет применять для разнообразных запросов.

8. Индексируйте соединенные столбцы.

При соединении двух таблиц SQL Server ищет во внутренней таблице все строки, значения которых удовлетворяют условию, вычисляемому на основании текущего значения из внешней таблицы. И такой поиск SQL Server повторяет для каждой строки из внешней таблицы. Если имеется индекс, то SQL Server сможет сначала отобрать только те строки, которые отвечают условию соединения. Когда размер внутренней таблицы в несколько раз больше размера внешней, выигрыш во времени выполнения соединения может составить несколько порядков. (Более подробно о соединении таблиц написано в статье Ицыка Бен-Гана и Кэйлен Дилани ‘Усовершенствованная техника соединения таблиц’ (‘Advanced JOIN Techniques’), опубликованной в декабрьском номере журнала за 1999 год.) Какой индекс выбрать — кластеризованный или не кластеризованный — в основном, зависит от того, присутствуют ли в списке SELECT другие столбцы. Если в список входят только те столбцы, по которым производится соединение, лучше всего применять не кластеризованный индекс.

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

По определению покрывающим индексом называется такой индекс, который содержит все столбцы, упомянутые в операторах SELECT, UPDATE или DELETE. Запрос при этом называется покрываемым запросом. Поскольку не кластеризованный индекс содержит на уровне листьев запись для каждой строки в таблице, то вся информация для выполнения запроса находится в индексе. В силу этого процессор запросов может сканировать не огромную таблицу, а только небольшой индекс. В общем случае, если вам удастся построить покрывающий индекс, то вы сразу почувствуете значительное улучшение производительности обработки запросов. Это объясняется тем, что индекс содержит не всю строку таблицы, а только ее подмножество. Однако оборотная сторона медали состоит в том, что введение в индекс дополнительных столбцов приводит к тому, что на странице индекса умещается меньше записей. Это, в свою очередь, вызывает увеличение места, занимаемого индексом, и возрастание числа операций ввода/вывода, необходимых для считывания индекса в кэш. Построение покрывающих индексов оправдано до тех пор, пока суммарная длина всех входящих в индекс столбцов остается значительно меньше длины строки таблицы.

Читать еще:  Как узнать модель установленной видеокарты

Морис Льюис является президентом компании Holitech, специализирующейся на консалтинге и обучении технологиям Internet и разработкам корпорации Microsoft в области баз данных.

Оптимизация запросов SQL Server. Часть II

Метод оптимизации для неизвестного используется в следующих случаях:
1. При работе с локальными переменными
В отличие от значений параметров, которые можно прослушивать, значения переменных обычно прослушать нельзя. Исключение будет описано немного позже. Причина проста: начальная единица оптимизации — весь пакет, а не только инструкция запроса. Объявление и задание значений переменным выполняются в оптимизируемом пакете. Точка, в которой запрос оптимизируется, предшествует заданию любой переменной, поэтому значения переменных нельзя прослушивать. В результате оптимизатору приходится использовать метод оптимизации для неизвестного.

Чтобы сравнить метод оптимизации для неизвестного с естественным методом оптимизации для известного, рассмотрим следующий запрос, имеющий предикат фильтра с оператором >= и известную константу в качестве входных данных:

План выполнения для этого запроса показан на рисунке выше.

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

Выполнив этот программный код после предшествующего запроса, я получил имя статистики _ WA_Sys_00000004_44 СА3770. Запомните полученное вами имя. Затем используйте следующий код для просмотра гистограммы после замены имени статистики на полученное вами:

Последние несколько шагов в полученной гистограмме показаны в таблице выше.

Мы ясно видим, что CE, показанная на рисунке выше, основана на последних трех шагах гистограммы. Оценка довольно точная: 4,00639 при действительном значении 4.

В отличие от приведенного выше примера, в следующем запросе используется локальная переменная, что вынуждает оптимизатор применить метод оп т и м и за ц и и для неизвестного:

План для этого запроса показан на рисунке выше.

Как было предсказано, это оценка 30% количества элементов ввода. Примечательно, что из-за неточности оценки оптимизатор выбрал неоптимальную стратегию статистической обработки. Здесь использован алгоритм статистической обработки Hash Match вместо сортировки и алгоритма Stream Aggregate. Это лишь одно из многих возможных последствий неточных оценок.

Существует исключение, при котором оптимизатор может прослушивать переменные: событие перекомпиляции происходит на уровне инструкций. Дело в том, что по определению перекомпиляция на уровне инструкций происходит после того, как выполнено задание всех переменных. Автоматическая перекомпиляция всегда происходит на уровне инструкций. Такбыло все время после появления SQL Server 2005 и до написания данной статьи. Я тестирую программный код на SQL Server 2016. Для ручной перекомпиляции на уровне инструкций нужно добавить указание запроса RECOMPILE с использованием оператора OPTION:

Этот запрос формирует такой же план, как показанный на рисунке «Последние несколько шагов гистограммы», где оценка является точной. Обратите внимание, что если указать параметр WITH RECOMPILE на уровне процедуры, то прослушивание не будет включено — это достигается только указанием в запросе OPTION (RECOMPILE). Перейдем к следующему случаю использования метода оптимизации для неизвестного.

Проектирование индексов для оптимизации запросов в Microsoft SQL Server

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

Ранее в материале «Основы индексов в Microsoft SQL Server» мы с Вами узнали, что же такое индексы и какие они бывают, а сейчас мы с Вами научимся правильно проектировать эти индексы, чтобы достичь максимальной производительности SQL запросов.

Общие рекомендации по проектированию индексов в Microsoft SQL Server

  • Одним из самых эффективных индексов является индекс для целочисленных столбцов, которые имеют уникальные значения, поэтому по возможности создавайте индексы для таких столбцов;
  • Если таблица очень интенсивно обновляется, то не рекомендуется создавать большое количество индексов, так как это снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE. Потому что после изменений данных в таблице SQL сервер автоматически вносит соответствующие изменения во все индексы;
  • Если таблица с большим объемом данных обновляется редко, при этом она активно используется в инструкциях SELECT, т.е. на выборку данных, то большое количество индексов может улучшить производительность, так как у оптимизатора запросов будет больший выбор индексов при определении наиболее эффективного и быстрого способа доступа к данным;
  • Если создавать некластеризованный индекс в файловой группе, которая расположена не на том диске, на котором расположены файловые группы таблицы, то это может повысить производительность для больших таблиц и индексов, так как это позволяет одновременно обращаться к нескольким дискам;
  • Для таблиц с небольшим объемом данных создание индексов в частности некластеризованных индексов с целью повышения производительности может оказаться абсолютно бесполезно, да еще и затратами на их поддержание. Так как оптимизатору может потребоваться больше времени на поиск данных в индексе, чем просмотр данных в самой таблице. Поэтому не создавайте индексы для таблиц, в которых очень мало данных;
  • Кластеризованный индекс необходимо создавать для столбца, который является уникальным и не принимает значения NULL, также длина ключа должна быть небольшой, другими словами ключ индекса не нужно составлять из нескольких столбцов;
  • Если представление содержит агрегаты и объединения таблиц, то индексы для таких представлений могут дать неплохое улучшение производительности.

Рекомендации по проектированию индексов с целью оптимизации запросов

  • Некластеризованные индексы необходимо создавать для всех столбцов, которые часто используются в условиях (WHERE) и в объединениях (JOIN);
  • По возможности не стоит создавать индексы, в которых очень много ключевых столбцов, так как это влияет на размер индекса и на ресурсы его поддержания;
  • Эффективно использовать покрывающие индексы, т.е. индексы которые включают все столбцы, используемые в запросе (это называется «Покрытием запроса»). Благодаря этому оптимизатор запросов может найти все значения столбцов в индексе, при этом не обращаясь к данным таблиц, что приводит к меньшему числу дисковых операций ввода-вывода. Это можно достичь с помощью включения в индекс неключевых столбцов (включенные столбцы), но также следует принять во внимание, что это влечет за собой увеличение размера индекса;
  • Если есть возможность, то рекомендовано заменять неуникальный индекс уникальным для той же комбинации столбцов, это обеспечивает оптимизатору запросов дополнительные сведения, что может сделать индекс более эффективным;
  • При создании индекса учитывайте порядок ключевых столбцов, это повышает производительность индекса. Например, столбцы, которые используются в предложении WHERE в условиях поиска равно (=), больше (>), меньше (

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую пройти наши онлайн-курсы по T-SQL, на которых используется последовательная методика обучения специально для начинающих.

На этом у меня все, надеюсь, материал был Вам полезен и интересен, пока!

Ссылка на основную публикацию
Adblock
detector