В 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 поступает правильно, игнорируя фактическое содержимое буферного пула и полагаясь на различные правила вычисления стоимости без попыток создать самый лучший план для всех ситуаций.

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