主页 / dba / 问题 / 224148
Accepted
SQL Sean
Asked: 2018-12-05 13:01:30 +0800 CST

SELECT from Clustered Columnstore 导致 LOB 读取

  • 772

我正在从以下包含 52 亿条记录的聚集列存储索引中执行 SELECT。当我查看 STATISTICS IO 的输出时，我看到 LOB 逻辑读取。

我的问题是为什么当表中的数据类型都不是 LOB 值时查询执行 LOB 逻辑读取？

我在 SQL Server 2014 SP3 企业版环境中工作。

表定义：

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  [T].[Impressions]
FROM    [dbo].[ColumnstoreTableA] AS [T]

统计 IO 输出：

表“ColumnstoreTableA”。扫描计数 5，逻辑读取 127，物理读取 0，预读读取 0，lob 逻辑读取 3719，lob 物理读取 0，lob 预读读取 0。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    多个来源似乎表明 Columnstore Indexes 将其数据存储为 LOB 数据，这在本例中发生了。我的理解和收获是，在使用 Columnstore 索引时，应该始终期望发生 LOB 读取。

    Columnstore 索引的结构是什么？

    如何在 SQL Server 中使用 Columnstore 索引

    SQL Server 2012 Columnstore 索引内部

    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
                                );

    结果：

    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
    • 7

相关问题