SQL-Ex blog

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

Одним из способов это сделать - построить оптимизатор запросов на базе стоимости. При построении планов выполнения он приписывает стоимость каждому оператору в плане выполнения. Вот как это делает SQL Server, и вы можете увидеть доказательство этому, если наведете вашу мышку на компоненты в плане выполнения:



В данном случае я навел мышку на Clustered Index Scan (сканирование кластеризованного индекса), и вы видите некоторые составляющие стоимости.

• Estimated I/O Cost 5.46609 (предполагаемая стоимость ввода/вывода) - означает, что SQL Server думает, что этот оператор будет стоить 5.46609 единиц. (Подробнее о единицах через секунду.) Чем больше таблица, тем выше должна быть эта стоимость. SQL Server даже делает предположения о том, как много данных будет находиться в кэше, а сколько придется прочитать из хранилища.


• Estimated CPU Cost 0.165757 (предполагаемая стоимость ЦП) - некоторые операторы требуют много работы ЦП, но не этот. Здесь мы просто сканируем таблицу, примерно как вы листаете телефонную книгу. Вам не требуется много думать - вы просто ищите конкретную фамилию.


• Estimated Operator Cost 5.63084 (предполагаемая стоимость оператора) - сложите два предыдущих числа, и вы получите это. Да, обычно когда у вас есть два числа, которые составляют итог, вы ставите сначала два меньших, а затем итог. Но не так делает SQL Server. Когда SQL Server показывает данные во всплывающей подсказке, он либо использует алфавитный порядок, либо загружает Data Cannon™ и выводит его на экран. Эта всплывающая подсказка была получена с помощью Data Cannon™.

Если вы сложите стоимости всех трех операторов в плане запроса, вы получите общую стоимость, которая показана здесь при наведении мышки на сам оператор SELECT:



В данном случае план выполнения стоит 12.8589. (И опять - мы вернемся к единицам измерения через секунду.)

SQL Server строит множество планов и использует стоимость, чтобы их сравнивать

После построения первого плана SQL Server не опускает руки и говорит: "Хорошо, еще разок". Он строит другой план выполнения, а затем сравнивает стоимость этого нового плана со стоимостью исходного. После нескольких подобных итераций он выбирает план с наименьшей стоимостью и выполняет его.

Вот сильно упрощенная схема:

• Иногда запрос тривиален, поэтому SQL Server строит только один план.


• Иногда SQL server строит один или два плана, а затем говорит: "Хватит, стоимость настолько мала, что не имеет смысла продолжать строить планы. Я должен просто следовать этому плану, поскольку он достаточно хорош."


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

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

Реализации оптимизатора на основе стоимости не обязательно идеальны

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

Стоимость не отражает характеристик вашего железа. Оптимизатор на основе стоимости был построен в 1990-х, и он не обновлялся, чтобы учитывать современные ЦП, память или скорость хранилищ. Мы все еще используем ту же стоимость на поиск ключа, которую использовали свыше 20 лет назад. Что касается меня, я не думаю, что Microsoft должен когда-либо попытаться привести стоимость в соответствие с текущим аппаратным обеспечением: кроме всего прочего, после установки SQL Server может использоваться десять лет и более на всех видах различного железа. Было бы бесполезно пытаться обеспечивать точность стоимости для аппаратного обеспечения, которое даже еще не построено.

Некоторая работа, выполняемая запросом не получает стоимости. Например, выделение памяти (memory grants) не принимается здесь в расчет: Вы можете увидеть идентичные запросы с грантами памяти в 1Мб и 100Гб, которые имеют одинаковую стоимость.

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

Чем больше вы знаете, тем быстрей движетесь

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

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

Это знак того, что нужно изучить то, как работает оптимизатор на основе стоимости. Начните с моей статьи Как думать подобно SQL server и двигайтесь дальше.