SQL Server. Оптимизация SQL запросов, план запроса в SQL Server. Советы по оптимизация запросов SQL

Здесь приведем некоторые советы по оптимизации запросов SQL Server при анализе плана выполнения запроса. 

1. Обычные виновники предупреждений в планах запросов - это операторы, сбрасывающие данные в tempdb, неявные преобразования, которые SQL Server должен производить для выполнения сравнения (что потенциально препятствует использованию индекса), и SQL Server сообщает вам, что он превысил/недооценил количество памяти, которое необходимо использовать для выполнения вашего запроса.

2. Общая мудрость гласит, что поиск (seek) - это хорошо для производительности, поскольку он представляет собой прямой доступ SQL Server к требуемым строкам данных, в то время как сканирование (scan) - это плохо, поскольку он предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке.

3. RID Lookup (поиск идентификатора записи) легко пофиксить (в плане запроса) - если вы видите этот оператор, это означает, что у вас отсутствует кластеризованный индекс на таблице. По крайней мере, вы должны добавить кластеризованный индекс. И вы тут же получите некоторый рост производительности для большинства, если не для всех, ваших запросов.
SQL Server использует Key Lookup (поиск ключей в записи), когда он знает, что с большей эффективностью может использовать некластеризованный индекс, а затем перейти к кластерзованному индексу для поиска оставшихся значения строк, которые отсутствуют в некластеризованном индексе.

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

5. Index spools. Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb. SQL Server часто использует спул для обработки сложных запросов, преобразуя данные в рабочую таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск в tempdb.

6. Соединения в плане запросов. Merge join оптимально. Nested loops нормально для небольших данных. Hash match - присмотреться (возможно надо упорядочить сначала набор или индекс иметь на поля упорядочивания, по которым идет связь).

7. Если я вижу по плану запроса, что данные напрямую поступают из некластеризованных индексов, то доволен, что данные извлекаются эффективно, поскольку возвращается только ограниченное число столбцов, и надеюсь, что они вернутся в предпочтительном порядке (оттого, что я создаю свои индексы довольно узкими).
Если все, что я вижу, это Index scan (сканирование индекса), тогда все прекрасно, но следует убедиться, что:
a. Я не вижу table scan (сканирование таблицы) - на худой конец, это может быть сканирование кластерного индекса.
b. Я не использую без необходимости SELECT * в своем запросе - зачем считывать все эти лишние данные в память или мешать использованию более узкого индекса, если в этом нет необходимости.
c. SQL Server не сканирует весь индекс, чтобы вернуть только ограниченное подмножество строк.
Затем я ищу в плане любые другие операторы, обычно вызывающие проблемы: сортировки, спулы, хэш-соединения и т.д.
Наконец, я быстро просматриваю план в поисках желтых восклицательных знаков на любых операторах в плане. Эти символы отмечают действия, о которых SQL Server считает, что должен нас предупредить.

8. Стараемся не использовать поля allow null. Избегаем по возможности left join

9. Памятка по плану запроса - https://yadi.sk/i/spoZqlZSJ75eKQ

Источник: https://sql-ex.ru/blogs/?/Kak_Ja_ispolzuju_plany_vypolneniJa_v_SQL_Server_dlJa_resheniJa_problem.html