Sql-server – SELECT from Clustered Columnstore results in LOB Reads

columnstoresql serversql server 2014

I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads.

My question is why is the query performing LOB Logical Reads when none of the data types in the table are LOB values?

I am working within a SQL Server 2014 SP3 Enterprise Edition environment.

Table Definition:

CREATE TABLE [dbo].[ColumnstoreTableA](
    [RollupID] [bigint] IDENTITY(1,1) NOT NULL,
    [DT_DIMID] [int] NOT NULL,
    [TC_DIMID] [int] NOT NULL,
    [C_DIMID] [int] NOT NULL,
    [CD_DIMID] [int] NOT NULL,
    [AC_DIMID] [int] NOT NULL,
    [UA_DIMID] [int] NOT NULL,
    [UL_DIMID] [int] NOT NULL,
    [JP_DIMID] [int] NOT NULL,
    [RTT_DIMID] [int] NOT NULL,
    [RA] [int] NOT NULL,
    [Impressions] [int] NOT NULL,
    [Clicks] [int] NOT NULL,
    [Conversions] [int] NOT NULL,
    [Sends] [int] NOT NULL,
    [CreationDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [ModifyDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [Z_DIMID] [int] NOT NULL,
    [R_Actual] [money] NULL,
    [JP_DIMID2] [int] NULL
) ON [PRIMARY]

SELECT Statement:

SELECT  [T].[Impressions]
FROM    [dbo].[ColumnstoreTableA] AS [T]

STATISTICS IO Output:

Table 'ColumnstoreTableA'. Scan count 5, logical reads 127, physical
reads 0, read-ahead reads 0, lob logical reads 3719, lob physical
reads 0, lob read-ahead reads 0.

Best Answer

Multiple sources appear to indicate Columnstore Indexes store its data as LOB data, which is happening in this case. My understanding and take-away is that it should always be expected for LOB reads to occur when using Columnstore indexes.

What's the anatomy of a Columnstore index?

How to use Columnstore indexes in SQL Server

Inside the SQL Server 2012 Columnstore Indexes

SELECT      COUNT([partition_number]) AS [Number of Partitions],
            SUM([in_row_used_page_count]) AS [SUM_in_row_used_page_count],
            SUM([lob_used_page_count]) AS [SUM_lob_used_page_count],
            SUM([used_page_count]) AS [SUM_used_page_count],
            SUM([row_count]) AS [SUM_row_count]
FROM        [sys].[dm_db_partition_stats]
WHERE       [partition_id] IN   (
                                    SELECT  [p].[partition_id]
                                    FROM    [sys].[partitions] AS [p] 
                                    INNER JOIN  [sys].[objects] AS [o] ON [o].[object_id] = [p].[object_id]
                                    WHERE   [o].[object_id] = 971254615
                                );

Results:

Number of Partitions SUM_in_row_used_page_count SUM_lob_used_page_count SUM_used_page_count  SUM_row_count
-------------------- -------------------------- ----------------------- -------------------- --------------------
847                  0                          5630558                 5630558             5175958971
Related Question