4

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.

2

1 Answer 1

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.