稀疏列、cpu 时间和过滤索引

稀疏

在对稀疏列进行一些测试时，正如您所做的那样，我想知道直接原因是性能受挫。

DDL

我创建了两张相同的表，一张有 4 个稀疏列，一张没有稀疏列。

--Non Sparse columns table & NC index
CREATE TABLE dbo.nonsparse( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                      charval char(20) NULL,
                      varcharval varchar(20) NULL,
                      intval int NULL,
                      bigintval bigint NULL
                      );
CREATE INDEX IX_Nonsparse_intval_varcharval
ON dbo.nonsparse(intval,varcharval)
INCLUDE(bigintval,charval);

-- sparse columns table & NC index

CREATE TABLE dbo.sparse( ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                      charval char(20) SPARSE NULL ,
                      varcharval varchar(20) SPARSE NULL,
                      intval int SPARSE NULL,
                      bigintval bigint SPARSE NULL
                      );

CREATE INDEX IX_sparse_intval_varcharval
ON dbo.sparse(intval,varcharval)
INCLUDE(bigintval,charval);

DML

然后我在两者中插入了大约2540 个非空值。

INSERT INTO dbo.nonsparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT 'Val1','Val2',20,19
FROM MASTER..spt_values;

INSERT INTO dbo.sparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT 'Val1','Val2',20,19
FROM MASTER..spt_values;

之后，我在两个表中都插入了1M NULL值

INSERT INTO dbo.nonsparse WITH(TABLOCK)  (charval, varcharval,intval,bigintval)
SELECT TOP(1000000) NULL,NULL,NULL,NULL 
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.sparse WITH(TABLOCK) (charval, varcharval,intval,bigintval)
SELECT TOP(1000000) NULL,NULL,NULL,NULL 
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

查询

非稀疏表执行

在新创建的非稀疏表上运行此查询两次时：

SET STATISTICS IO, TIME ON;
SELECT  * FROM dbo.nonsparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

逻辑读取显示5257页

(1002540 rows affected)
Table 'nonsparse'. Scan count 1, logical reads 5257, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

cpu时间为343 ms

 SQL Server Execution Times:
   CPU time = 343 ms,  elapsed time = 3850 ms.

稀疏表执行

在稀疏表上运行相同的查询两次：

SELECT  * FROM dbo.sparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

读数较低，1763

(1002540 rows affected)
Table 'sparse'. Scan count 1, logical reads 1763, physical reads 3, read-ahead reads 1759, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

但是 cpu 时间更高，547 ms。

 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 2406 ms.

稀疏表执行计划

非稀疏表执行计划

问题

原始问题

由于NULL值不直接存储在稀疏列中，cpu 时间的增加可能是由于将NULL值作为结果集返回吗？还是仅仅是文档中所述的行为？

稀疏列减少了空值的空间需求，但代价是检索非空值的开销更大

还是仅与使用的读取和存储有关的开销？

即使使用执行后丢弃结果选项运行 ssms，与非稀疏选择（219 毫秒）相比，稀疏选择的 CPU 时间（407 毫秒）更高。

编辑

即使只有 2540 个存在，它也可能是非空值的开销，但我仍然不相信。

这似乎是大致相同的性能，但稀疏因素丢失了。

CREATE INDEX IX_Filtered
ON dbo.sparse(charval,varcharval,intval,bigintval)
WHERE charval IS NULL  
      AND varcharval IS NULL
      AND intval  IS NULL
      AND bigintval  IS NULL;

CREATE INDEX IX_Filtered
ON dbo.nonsparse(charval,varcharval,intval,bigintval)
WHERE charval IS NULL  
      AND varcharval IS NULL
      AND intval  IS NULL
      AND bigintval  IS NULL;


    SET STATISTICS IO, TIME ON;

SELECT  charval,varcharval,intval,bigintval FROM dbo.sparse WITH(INDEX(IX_Filtered))
WHERE charval IS NULL AND  varcharval IS NULL
                     AND intval  IS NULL
                     AND bigintval  IS NULL
                     OPTION(RECOMPILE,MAXDOP 1);


SELECT  charval,varcharval,intval,bigintval 
FROM dbo.nonsparse WITH(INDEX(IX_Filtered))
WHERE charval IS NULL AND 
                      varcharval IS NULL
                     AND intval  IS NULL
                     AND bigintval  IS NULL
                     OPTION(RECOMPILE,MAXDOP 1);

似乎有大约相同的执行时间：

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 292 ms.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 319 ms.

但是为什么现在逻辑读取量相同？除了包含的 ID 字段和其他一些非数据页面之外，稀疏列的过滤索引不应该不存储任何内容吗？

Table 'sparse'. Scan count 1, logical reads 5785,
Table 'nonsparse'. Scan count 1, logical reads 5785

以及两个指数的大小：

RowCounts   Used_MB Unused_MB   Total_MB
1000000     45.20   0.06        45.26

为什么这些大小一样？稀疏性消失了吗？

使用过滤索引时的两个查询计划

额外信息

select @@version

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) 2019 年 7 月 12 日 17:43:08 版权所有 (C) 2017 Microsoft Corporation Developer Edition (64-bit) o​​n Windows Server 2012 R2 Datacenter 6.3 (Build 9600:)（管理程序）

在运行查询并仅选择ID字段时，cpu 时间是可比的，稀疏表的逻辑读取较低。

桌子的大小

SchemaName  TableName   RowCounts   Used_MB Unused_MB   Total_MB
dbo         nonsparse   1002540     89.54   0.10        89.64
dbo         sparse      1002540     27.95   0.20        28.14

当强制使用聚集索引或非聚集索引时，cpu 时间差仍然存在。