主页 / dba / 问题 / 172434
Accepted
Joe Obbish
Asked: 2017-05-02 07:24:43 +0800 CST

为什么在空表上创建 CCI 需要大量内存授予？

  • 772

在内存非常低的 QA 服务器上，我在 SQL Server 2016 中的新表上创建聚集列存储索引时遇到错误 8545：

错误 8645：等待内存资源执行查询时发生超时。重新运行查询。

我可以轻松地在我的本地机器上重现大量请求的内存授权。对于以下代码：

DROP TABLE IF EXISTS dbo.MY_FIRST_FACT_TABLE;

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX MY_FIRST_CCI ON dbo.MY_FIRST_FACT_TABLE;

我收到大约 512 MB 内存的内存请求。实际执行计划中有excessive memory grant的警告：

内存授予

即使查询使用 0 KB 的内存，它仍然可能超时，具体取决于服务器上的其他活动。为什么 SQL Server 需要那么多内存？我该怎么办？

sql-server sql-server-2016

1 个回答

  1. Best Answer

    在CCI 查询性能的文档中有关于此的提示：

    规划足够的内存以并行创建列存储索引

    默认情况下，创建列存储索引是并行操作，除非内存受到限制。与串行创建索引相比，并行创建索引需要更多内存。当有足够的内存时，创建列存储索引所需的时间是在相同列上构建 B 树所需时间的 1.5 倍。

    创建列存储索引所需的内存取决于列数、字符串列数、并行度 (DOP) 和数据的特性。例如，如果您的表少于一百万行，SQL Server 将只使用一个线程来创建列存储索引。

    该引用表明表中的行数对 DOP 很重要，但对内存授予无关紧要。所需的内存还取决于列数，因此让我们使用列数较少的表进行测试，以便更轻松地查看内存授予的预期差异。我正在使用这个表和索引定义：

    DROP TABLE IF EXISTS dbo.MY_SECOND_FACT_TABLE;

CREATE TABLE dbo.MY_SECOND_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL
);

INSERT INTO dbo.MY_SECOND_FACT_TABLE WITH (TABLOCK) (ID, COL1)
SELECT TOP (0) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE CLUSTERED COLUMNSTORE INDEX MY_SECOND_CCI ON dbo.MY_SECOND_FACT_TABLE WITH (MAXDOP = 1);

    以下是一些测试的结果：

    ╔══════════════════╦════════════════╦═════════════════╗
║ REQUESTED_MAXDOP ║ NUMBER_OF_ROWS ║ MEMORY_GRANT_KB ║
╠══════════════════╬════════════════╬═════════════════╣
║                1 ║              0 ║           89928 ║
║                2 ║              0 ║           89928 ║
║                1 ║        2000000 ║           89928 ║
║                2 ║        2000000 ║          179896 ║
║                1 ║        5000000 ║           89928 ║
║                2 ║        5000000 ║          179896 ║
╚══════════════════╩════════════════╩═════════════════╝

    对于 0 行，DOP 降级为 1，但除此之外，行数似乎与内存授予无关。这无疑是一种严重的过度简化，但 SQL Server 可能会为每个 DOP 一次构建 CCI 一个行组，并在其进行时维护字符串字典。这在实践中听起来不错，意味着内存授予不会随表中的行数缩放，但如果不检查非常低的行数，SQL Server 可能会请求过多的内存授予。在大多数情况下，过多的内存授予无关紧要，因为CREATE INDEX速度太快了，但如果 SQL Server 等待内存授予，则可能会很重要。

    所以现在我知道为什么内存授予如此之大，但是可以做些什么呢？SQL Server 2014 引入了将索引定义定义为表定义的一部分的语法。可以编写一条同时创建表和索引的语句：

    INDEX index_name CLUSTERED COLUMNSTORE

    适用于：SQL Server 2014 到 SQL Server 2016 和 Azure SQL 数据库。

    指定使用聚集列存储索引以列格式存储整个表。这始终包括表中的所有列。数据未按字母顺序或数字顺序排序，因为对行进行组织以获得列存储压缩优势。

    对于原始表，语法如下所示：

    CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL,
    INDEX MY_FIRST_CCI CLUSTERED COLUMNSTORE
);

    CREATE TABLE语句不会生成实际计划，因此我无法立即判断这是否减少了内存授予。我在敲打 DMV 时尝试创建表格 1000 次，sys.dm_exec_query_memory_grants但从未得到任何结果。这表明记忆没有被授予，但不是证据。我还尝试使用 query_memory_grant_usage 扩展事件，但没有得到单个CREATE TABLE语句的任何结果。

    当然，最好的测试方法是在可用内存有限的情况下创建 CCI 原始工作流条件。我这样做了，没有遇到任何内存超时。

    我想可以说在CREATE TABLE新表的语句中定义 CCI 是最佳实践。但是，如果您的工作负载导致此错误，那么更改CREATE TABLE脚本可能不足以解决您将遇到的其他问题，例如开始将数据加载到 CCI 时的内存问题。

    • 3

相关问题