Введение в оценку стоимости плана
Пересказ статьи Benjamin Nevarez. An Introduction to Cost Estimation
Недавно, присутствуя на сессии саммита PASS, посвященного оптимизатору запросов, мне был задан вопрос о том, как рассчитываются оценки стоимости затрат процессора (Estimated CPU cost) и затрат на ввод-вывод (Estimated I/O cost), т.е. откуда берется конкретное значение, скажем, 1,13256. В тот момент я мог ответить только то, что Майкрософт не публикует, как рассчитывается стоимость.
Позже, работая над связанным проектом, я подумал, что могу вернуться к этому вопросу и показать один пример. Но, поскольку он включал множество операторов, я решил рассмотреть самый простой: оператор сканирования кластеризованного индекса (Clustered Index Scan). Итак, я привлек множество XML-планов, использовал XQuery для извлечения из них информации о стоимости, и после некоторого анализа смог получить основную формулу для этого конкретного оператора.
Но сначала краткое введение в оценку стоимости: стоимость каждого оператора зависит от его алгоритма, с каждым оператором связана стоимость процессора, и некоторые из них также имеют стоимость ввода-вывода. Общая стоимость оператора есть сумма этих двух значений стоимости.
Оператор, подобный сканированию кластеризованного индекса, имеет как одну стоимость, так и другую. Другие операторы, например, агрегация потока (Stream Aggregate) будет иметь только стоимость процессора. Интересно заметить, что эта стоимость оценивала время в секундах, за которое запрос или оператор выполнялся бы на некой эталонной машине. В последних версиях SQL Server .та стоимость больше не интерпретируется в секундах, миллисекундах или каких-либо других единицах.
Чтобы показать пример, давайте возьмем самую большую таблицу в AdventureWorks - Sales.SalesOrderDetail. Выполним следующий запрос и обратим внимание на оценки стоимости CPU и I/O для оператора Clustered Index Scan, показанные на следующем рисунке.
Для оператора Clustered Index Scan я наблюдал, что стоимость CPU составляла 0.0001581 для первой записи плюс 0.0000011 для каждой последующей. В данном случае мы имеем оценку числа строк 121317, что видно на рисунке выше, поэтому мы имеем 0.0001581 + 0.0000011 * (121317 -1) или 0.133606, т.е. значение совпадающее со оценкой стоимости CPU. Аналогичным образом я выяснил, что минимальная стоимость ввода-вывода составляет 0.003125 для первой страницы базы данных, а затем растет с приращением 0.00074074 для каждой следующей страницы. Поскольку сканирование кластеризованного индекса читает всю таблицу, я могу использовать следующий запрос, чтобы определить число строк базы данных, который возвращает 1234.
В этом случае мы имеем 0.003125 + 0.00074074 * (1234 – 1) или 0.916458, что представляет собой значение, показанное для предполагаемой стоимости операций ввода-вывода (estimated I/O Cost).
Наконец, сложив обе стоимости, 0.133606 + 0.916458, получаем 1.05006, что является общей стоимостью оператора. Аналогично, суммирование стоимости всех операторов даст нам общую стоимость плана. В нашем случае стоимость Clustered Index Scan, 1.05006, плюс стоимость первого оператора Compute Scalar, 0.01214, второго оператора Compute Scalar, 0.01213, и стоимость оператора Filter, 0.0582322, даст полную стоимость плана, 1.13256, что видно на следующем рисунке.
Примечание. Справочник по логическим и физическим операторам Showplan
Но сначала краткое введение в оценку стоимости: стоимость каждого оператора зависит от его алгоритма, с каждым оператором связана стоимость процессора, и некоторые из них также имеют стоимость ввода-вывода. Общая стоимость оператора есть сумма этих двух значений стоимости.
Оператор, подобный сканированию кластеризованного индекса, имеет как одну стоимость, так и другую. Другие операторы, например, агрегация потока (Stream Aggregate) будет иметь только стоимость процессора. Интересно заметить, что эта стоимость оценивала время в секундах, за которое запрос или оператор выполнялся бы на некой эталонной машине. В последних версиях SQL Server .та стоимость больше не интерпретируется в секундах, миллисекундах или каких-либо других единицах.
Чтобы показать пример, давайте возьмем самую большую таблицу в AdventureWorks - Sales.SalesOrderDetail. Выполним следующий запрос и обратим внимание на оценки стоимости CPU и I/O для оператора Clustered Index Scan, показанные на следующем рисунке.
SELECT * FROM Sales.SalesOrderDetail
WHERE LineTotal = 35;
Для оператора Clustered Index Scan я наблюдал, что стоимость CPU составляла 0.0001581 для первой записи плюс 0.0000011 для каждой последующей. В данном случае мы имеем оценку числа строк 121317, что видно на рисунке выше, поэтому мы имеем 0.0001581 + 0.0000011 * (121317 -1) или 0.133606, т.е. значение совпадающее со оценкой стоимости CPU. Аналогичным образом я выяснил, что минимальная стоимость ввода-вывода составляет 0.003125 для первой страницы базы данных, а затем растет с приращением 0.00074074 для каждой следующей страницы. Поскольку сканирование кластеризованного индекса читает всю таблицу, я могу использовать следующий запрос, чтобы определить число строк базы данных, который возвращает 1234.
SELECT in_row_data_page_count, row_count
FROM sys.dm_db_partition_stats
WHERE object_id = object_id(‘Sales.SalesOrderDetail’)
AND index_id = 1;
В этом случае мы имеем 0.003125 + 0.00074074 * (1234 – 1) или 0.916458, что представляет собой значение, показанное для предполагаемой стоимости операций ввода-вывода (estimated I/O Cost).
Наконец, сложив обе стоимости, 0.133606 + 0.916458, получаем 1.05006, что является общей стоимостью оператора. Аналогично, суммирование стоимости всех операторов даст нам общую стоимость плана. В нашем случае стоимость Clustered Index Scan, 1.05006, плюс стоимость первого оператора Compute Scalar, 0.01214, второго оператора Compute Scalar, 0.01213, и стоимость оператора Filter, 0.0582322, даст полную стоимость плана, 1.13256, что видно на следующем рисунке.
Примечание. Справочник по логическим и физическим операторам Showplan
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой