SQL-Ex blog

Мое мнение по этому поводу менялось на протяжении нескольких последних лет в связи с большим числом проблем с производительностью, над которыми приходилось работать. Я выступал на SQLSaturday 1000 (Oregon 2020) на прошлых выходных, и мой доклад был в основном о вещах, которые я узнал об оптимизации сборки мусора и аналогичных дополнительных процессах. Во время этой работы я столкнулся с рядом проблем с запросами, подобными следующему примеру для базы данных WideWorldImporters:

DELETE inv

FROM @OrdersGC gc

JOIN Sales.Invoices inv

    ON inv.OrderID = gc.OrderID;

Вопросы заказов

Логика здесь достаточно проста. Ранее в процессе мы обнаружили заказы, которые хотели удалить в соответствии с политикой хранения, и поместили значения OrderID в табличную переменную, оптимизированную для памяти (motv). Затем мы используем motv для удаления из всех связанных таблиц, и наконец из таблицы Orders.

Этот запрос не имеет предложения WHERE. Ясно, что мы хотим сделать, чтобы это работало. У нас имеется 100 строк в нашей motv, и мы хотим удалить связанные строки в Invoices. Однако я увидел проблемы, вызываемые планами выполнения, которые нарушают порядок:



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

Согласованность

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

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

DELETE inv

FROM @OrderList gc

INNER LOOP JOIN Sales.Invoices inv

    ON inv.OrderID = gc.OrderID;



DELETE inv

FROM @OrderList gc

JOIN Sales.Invoices inv

    ON inv.OrderID = gc.OrderID

WITH OPTION(FORCE ORDER);

Оба варианта навязывают порядок соединения. Хинт INNER LOOP JOIN имеет дополнительное преимущество, гарантируя, что план использует соединение вложенными циклами. Соединение hash match не дало бы эффекта при размере пакета в несколько сотен или тысяч строк. Merge join потребовало бы, вероятно, сортировки одного из входов, а это не то, что нужно.

Индексные хинты

Мне пришлось использовать индексный хинт в следующем примере:

DELETE TOP (@BatchSize) vt

FROM Warehouse.VehicleTemperatures vt

WHERE vt.RecordedWhen < DATEADD(DAY, -180, GETUTCDATE());

Это был пример процесса сборки мусора. План не выявляет проблемы, но следует с подозрением отнестись к сканированию здесь:



Сканирование таблицы читает только 100 строк, но это потому, что используется оператор ТОР. Первые 100 строк отвечают нашему фильтру, поэтому запрос заканчивается в этом месте. Если никакие строки не отвечают критерию (или их меньше 100), нам придется сканировать всю таблицу.

На столбце RecordedWhen имеется индекс; просто он не был использован. Это другой случай, когда применение хинта кажется очевидным. Возможно, обновление статистики также решило бы проблему, но это дает мне больше уверенности.

DELETE TOP (@BatchSize) vt

FROM Warehouse.VehicleTemperatures vt WITH (INDEX(IX_VehicleTemperatures_RecordedWhen))

WHERE

    vt.RecordedWhen < DATEADD(DAY, -180, GETUTCDATE());

Большая ответственность

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

1. Связи. Убедитесь, что вы представляете себе объем и связи между таблицами. Это позволит вам представить ожидаемое число строк, которое будет возвращено.


2. Индексы. Представление о возможных вариантах для каждой таблицы в запросе. Таблица может использовать один индекс на основе предложения WHERE, или другой на основе предложения ON. Порядок соединения и используемые индексы связаны. Индексный хинт может подтолкнуть SQL Server к конкретному порядку соединения; как и с хинтами соединения/порядка.


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


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


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

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

Один из моих коллег недавно решил проблему с производительностью, изменив порядок соединения с помощью хинта, или, по его словам, «выполнив Jared Poche». Это говорит о том, как часто я использовал подсказки, и как часто они срабатывали.