Почему оптимизатор запросов не анализирует содержимое буферного пула
OTUS
В SQL Server используется стоимостной оптимизатор запросов (cost-based optimizer), который ищет оптимальный план в течение времени, выделенного для компиляции запроса. При оптимизации плана учитывается информация о таблицах, участвующих в запросе, существующих индексах, а также статистика распределения данных. Поиск оптимального плана также включает в себя минимизацию количества физических чтений.
Иногда возникает вопрос, почему оптимизатор не учитывает содержимое буферного пула, ведь это, безусловно, может помочь ускорить выполнение запроса. В этой статье разберемся почему.
Чтение содержимого буферного пула
Одна из причин, по которой оптимизатор игнорирует буферный пул, состоит в том, что его структура не позволяет простым способом выяснить его содержимое. Страницы файла данных в буферном пуле представлены небольшими структурами данных, называемых "буферами" (buffer), в которых хранится следующая информация (список неполный):
- идентификатор страницы Page ID (номер файла : номер страницы в файле);
- время последнего обращения к странице (используется lazy writer'ом для реализации алгоритма least-recently-used для вытеснения значений, которые дольше всего не запрашивались);
- расположение страницы в буферном пуле;
- признак того является страница грязной (dirty) или нет (грязная страница содержит изменения, которые еще не были записаны в долговременное хранилище);
- единица распределения (allocation unit), к которой принадлежит страница (подробнее здесь). Идентификатор единицы распределения (allocation unit ID) можно использовать для определения к какой таблице и индексу относится страница.
Для каждой базы данных со страницами в буферном пуле, также есть хэш-список страниц, упорядоченных по идентификаторам (page ID), используя который можно быстро определить, находится страница в памяти или нет, то есть понять, потребуется ли выполнить физическое чтение. Однако не так просто выяснить, какой процент страниц листового уровня индекса находится в памяти. Придется сканировать весь буферный пул базы данных в поисках буферов нужной единицы распределения. И чем больше страниц в памяти, тем больше времени это займет. Что будет очень дорого в рамках компиляции запроса.
О том как получить информацию о буферном пуле с помощью DMV sys.dm_os_buffer_descriptors можно посмотреть в этом посте.
Почему использование содержимого буферного пула может быть опасным
Давайте представим, что все-таки существует эффективный механизм анализа содержимого буферного пула, которым может воспользоваться оптимизатор при выборе индекса. Проверим следующую гипотезу: если оптимизатор знает, что в памяти уже находится менее эффективный большой индекс, по сравнению с более эффективным меньшего размера, но находящимся на диске, то он должен выбрать индекс в памяти, потому что это уменьшит количество физических операций, и запрос будет выполняться быстрее.
Рассмотрим пример таблицы BigTable с двумя некластеризованными индексами Index_A и Index_B. Для запроса, рассматриваемого далее, оба индекса будут покрывающими, а для получения результатов потребуется полное сканирование листового уровня индекса. Вставим в таблицу один миллион строк, тогда в индексе Index_A на листовом уровне будет 200 000 страниц, а в Index_B — 1 000 000, поэтому для полного сканирования Index_B потребуется обработать в пять раз больше страниц.
Эксперимент я проводил на ноутбуке с 8 ядрами, 32 ГБ памяти, SSD-дисками и SQL Server 2019.
CREATE TABLE BigTable ( c1 BIGINT IDENTITY, c2 AS (c1 * 2), c3 CHAR (1500) DEFAULT 'a', c4 CHAR (5000) DEFAULT 'b' ); GO INSERT INTO BigTable DEFAULT VALUES; GO 1000000 CREATE NONCLUSTERED INDEX Index_A ON BigTable (c2) INCLUDE (c3); -- 5 записей на странице = 200 000 страниц GO CREATE NONCLUSTERED INDEX Index_B ON BigTable (c2) INCLUDE (c4); -- 1 запись на страницу = 1 миллион страниц GO CHECKPOINT; GO
Измерим длительность запросов:
DBCC DROPCLEANBUFFERS; GO -- Index_A не в памяти SELECT SUM (c2) FROM BigTable WITH (INDEX (Index_A)); GO -- CPU time = 796 ms, elapsed time = 764 ms -- Index_A в памяти SELECT SUM (c2) FROM BigTable WITH (INDEX (Index_A)); GO -- CPU time = 312 ms, elapsed time = 52 ms DBCC DROPCLEANBUFFERS; GO -- Index_B не в памяти SELECT SUM (c2) FROM BigTable WITH (INDEX (Index_B)); GO -- CPU time = 2952 ms, elapsed time = 2761 ms -- Index_B в памяти SELECT SUM (c2) FROM BigTable WITH (INDEX (Index_B)); GO -- CPU time = 1219 ms, elapsed time = 149 ms
Когда в памяти нет ни одного индекса, то Index_A более эффективен — время выполнения запроса 764 мс против 2761 мс при использовании Index_B, и то же самое происходит, если оба индекса находятся в памяти. Однако если Index_B находится в памяти, а Index_A — нет, то при использовании Index_B запрос будет выполняться быстрее (149 мс), чем при Index_A (764 мс).
Теперь давайте позволим оптимизатору сделать выбор на основе содержимого памяти…
Если Index_A отсутствует в памяти, а Index_B присутствует, то эффективнее было бы использовать Index_B. Несмотря на то что размер индекса Index_B больше и потребуется больше циклов процессора для обработки запроса, но физических операций чтения будет меньше, а они намного медленнее, чем дополнительные циклы процессора. Более эффективный план запроса содержит меньшее количество физических чтений.
План "использовать Index_B" лучше плана "использовать Index_A", только если Index_B находится в памяти, а Index_A нет. Как только большая часть Index_A окажется в памяти, план "использовать Index_A" будет эффективнее плана "использовать Index_B".
Если мы скомпилируем и закэшируем план "использовать Index_B", то получится следующая картина:
- Если оба индекса, и Index_A и Index_B находятся в памяти, то скомпилированный план займет почти в три раза больше времени.
- Если индексов нет в памяти — скомпилированный план выполняется в 3,5 раза дольше.
- Если Index_A находится в памяти, а Index_B — нет, то для получения данных необходимо выполнить физические чтения с диска, и запрос будет выполняться в 53 раза дольше.
Резюме
Хотя в нашем мысленном эксперименте оптимизатор может использовать информацию о буферном пуле для поиска наиболее эффективного плана запроса, но это может быть опасным при использовании скомпилированного плана из-за постоянного изменения буферного пула, и это может влиять на эффективность кэшированного плана.
Помните, что задача оптимизатора быстро найти хороший план, но он не обязательно будет единственно лучшим для 100% случаев. На мой взгляд, оптимизатор SQL Server поступает правильно, игнорируя фактическое содержимое буферного пула и полагаясь на различные правила вычисления стоимости без попыток создать самый лучший план для всех ситуаций.