Логические чтения не повторяемы на колоночных индексах (эх...)

Автор: Brent Ozar, Logical Reads Aren’t Repeatable on Columnstore Indexes. (sigh)

Иногда я действительно ненавижу свою работу.

Вечно, ВЕЧНО, я мог утверждать: «Когда вы измеряете производительность хранилища во время настройки индексов и запросов, вы всегда должны использовать логические чтения, а не физические, потому что логические чтения повторяемы, а физические — нет. Физические чтения могут меняться в зависимости от того, что находится в кэше, какие другие запросы выполняются в данный момент, от редакции вашего SQL Server и от того, используются ли упреждающие чтения. Логические чтения просто отражают точное количество прочитанных страниц, независимо от того, откуда пришли данные (с диска или из кэша), так что пока это число уменьшается, вы делаете свою работу хорошо».

Чтобы проиллюстрировать это, мы возьмём большую версию базы данных Stack Overflow и подсчитаем количество строк в таблице Users.

SET STATISTICS IO ON;
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

Вывод статистики ввода-вывода показывает, что при первом выполнении страницы приходится читать с диска, потому что их ещё нет в кэше:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 4, 
page server reads 0, read-ahead reads 329114, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 317822, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Batch execution completed 3 times.

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

То же самое верно, если мы создадим и некластеризованный строчный индекс:

CREATE INDEX Age ON dbo.Users(Age);
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

Вывод статистики показывает физические чтения и упреждающие чтения при первом выполнении, но логические чтения остаются постоянными на всём протяжении:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 1, 
page server reads 0, read-ahead reads 38997, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Table 'Users'. Scan count 1, logical reads 39000, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 0, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Batch execution completed 3 times.

Но с колоночными индексами на SQL Server 2017 и новее…

На SQL Server 2017 или новее (не на 2016) создайте некластеризованный колоночный индекс:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Age ON dbo.Users(Age);
GO
DBCC DROPCLEANBUFFERS;
GO
SELECT COUNT(*) FROM dbo.Users;
GO 3

И посмотрите на lob logical reads, когда мы выполним запрос 3 раза:

Beginning execution loop

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 22432, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 89443, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 10947, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.

(1 row affected)
Table 'Users'. Scan count 2, logical reads 0, physical reads 0, 
page server reads 0, read-ahead reads 0, page server read-ahead reads 0, 
lob logical reads 10947, lob physical reads 0, lob page server reads 0, 
lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Segment reads 22, segment skipped 0.
Batch execution completed 3 times.

Lob logical reads показывает 22 342 для первого выполнения, а затем 10 947 для следующих двух проходов.

Это не так для SQL Server 2016, который выдаёт одинаковые числа логических чтений каждый раз, когда выполняется запрос к колоночному индексу, независимо от того, очищен пул буферов или нет. Только начиная с 2017 и новее.

<вздох> Вот почему нам нельзя никогда расслабляться.

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

Но вы, дорогой читатель, прочитали. По крайней мере, я помог вам, будем надеяться. И это делает всё это стоящим. (На самом деле нет. Я пойду налью себе офисной текилы, а на часах ещё нет 10 утра, пока я это пишу.)