Lob Logical Read and lob read-ahead reads in NCCI

Question

I have a test table with following structure.

CREATE TABLE [dbo].[DW_test](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CourtCaseID] [int] NOT NULL,
[ActionID] [int] NOT NULL,
PRIMARY KEY CLUSTERED([ID] ASC)

Next I populated my table with about 470 million records with the following script.

insert into DW_test
--select count(*)
--from (
select top 1000000 abs(checksum(newid())) % 100000 + 1 a, abs(checksum(newid())) % 10 + 1 b
from sys.all_objects
cross join sys.all_objects a
cross join sys.all_objects b
cross join sys.all_objects c
cross join sys.all_objects d
cross join sys.all_objects e
cross join sys.all_objects f
cross join sys.all_objects g
--) t
GO

The script was executed about 470 times to generate 470 million records in the table and created NCCI on that table.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_test1] ON [dbo].[DW_test]
(
    [CourtCaseID],
    [ActionID]
)

Next I test simple query to count the records in the table.

DBCC DROPCLEANBUFFERS
GO
select COUNT_BIG(*)
from DW_test

If I turn SET STATISTICS IO ON I get the following results

With cold cache

Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 469697, lob physical reads 1, lob read-ahead reads 1877324.

Table 'DW_test'. Segment reads 453, segment skipped 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

With warm cache

Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 229248, lob physical reads 0, lob read-ahead reads 0.

Table 'DW_test'. Segment reads 453, segment skipped 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`

I know that 1 logical read is a single data page read from buffer pool during query execution and 1 physical read is a single data page read from disk. RedGate tells us that read-ahead read is:

This number tells us how many of the physical reads were satisfied by SQL Servers ‘Read-ahead’ mechanism. This is directly tied to physical reads, so if there are no physical reads, you will have 0 for Read-Ahead reads.

In my case I am dealing with lob logical, physical and read ahead reads. I want to understand what this number means in my particular case.

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?

Paul White · Answer 1 · 2019-09-02T11:16:21.663

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect.

How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?

Logical reads count the number of times a page in memory was touched. The reduction is from 469,697 (cold cache) to 229,248 (warm). I don't have a complete explanation for this, but it may be in part because columnstore data is cached contiguously in a separate columnstore object pool, rather than the general page-sized buffer pool.

Read-ahead reads pages from the upper levels of a b-tree to identify pages to read in advance of the scan, which may also result in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Columnstore indexes have a b-tree structure on persistent storage.

Another factor is parallelism (it seems your test was run at DOP 24) since overlapping requests from different threads can result in additional logical reads. You may find the excess logical reads reduce somewhat if you run with OPTION (MAXDOP 1).

Internal storage details for columnstore are not well-documented, nor fully supported by the usual DMVs. At this stage, I would say the best explanation for the reduction in reads between cold and warm cache cases is the difference between on-disk and in-memory (cached) storage.

Columnstore data (dictionaries and segments) are stored at LOBs on disk, but are not held in memory in the same format, for performance reasons. Because they are different things, you can't just add logical reads to physical reads to get a meaningful result.

score 2 · Answer 2 · edited Sep 01 '19 at 05:20

"How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?"

Because the other physical reads were served by read-ahead (the LOB type). Perhaps that RedGate article confused you, but if you serve an extent (say) as a read-ahead (RA), then you won't see any physical reads. The thread that collects the physical reads statistics found those pages in cache, so they weren't accumulated into the physical reads counter.

Another situation when we encounter this is the classic buffer cache hit ratio perfmon counter. RA reads do not contribute to this value, so you could have loads and loads of physical I/O but still close to 100 in BCHR.

"How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?"

My guess is that the first one kicked off an auto-create statistics.

Lob Logical Read and lob read-ahead reads in NCCI

2 Answers2