首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >稀疏列、cpu时间和过滤索引

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

Database Administration用户
提问于 2019-09-19 14:08:27
回答 1查看 496关注 0票数 11

解析

在对稀疏列进行一些测试时,就像您所做的那样,有一个性能下降的问题,我想知道直接原因。

DDL

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

代码语言:javascript
复制
--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个非空值插入到这两个值中。

代码语言:javascript
复制
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空值。

代码语言:javascript
复制
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;

查询

非稀疏表执行

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

代码语言:javascript
复制
SET STATISTICS IO, TIME ON;
SELECT  * FROM dbo.nonsparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

逻辑读取显示5257页。

代码语言:javascript
复制
(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。

代码语言:javascript
复制
 SQL Server Execution Times:
   CPU time = 343 ms,  elapsed time = 3850 ms.

稀疏表执行

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

代码语言:javascript
复制
SELECT  * FROM dbo.sparse
WHERE   1= (SELECT 1) -- force non trivial plan
OPTION(RECOMPILE,MAXDOP 1);

读数较低,1763年

代码语言:javascript
复制
(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。

代码语言:javascript
复制
 SQL Server Execution Times:
   CPU time = 547 ms,  elapsed time = 2406 ms.

稀疏表执行计划

非稀疏表执行计划

问题

原始问题

由于空值不是直接存储在稀疏列中的,cpu时间的增加是否是因为将空值作为结果集返回?或者仅仅是文献资料中提到的行为?

稀疏列减少了对空值的空间需求,而代价是检索非空值的开销更大。

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

即使在执行选项后使用丢弃结果运行ssms时,稀疏选择的cpu时间也比非稀疏(219 ms)高(407 ms)。

编辑

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

这似乎是相同的性能,但稀疏的因素失去了。

代码语言:javascript
复制
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);

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

代码语言:javascript
复制
 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字段和其他一些非数据页之外的任何内容吗?

代码语言:javascript
复制
Table 'sparse'. Scan count 1, logical reads 5785,
Table 'nonsparse'. Scan count 1, logical reads 5785

这两个指数的大小:

代码语言:javascript
复制
RowCounts   Used_MB Unused_MB   Total_MB
1000000     45.20   0.06        45.26

为什么这些尺寸一样?稀疏的感觉消失了吗?

使用筛选索引时的两个查询计划

额外信息

代码语言:javascript
复制
select @@version

Microsoft 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) 2019年7月12日17:43:08版权(C) 2017年微软公司开发版(64位)在Windows 2012 R2数据中心6.3 (Build 9600:) (Hypervisor)

当运行查询并且只选择ID字段时,cpu时间是可比的,稀疏表的逻辑读取较低。

表的大小

代码语言:javascript
复制
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时间差将保持不变。

EN

回答 1

Database Administration用户

回答已采纳

发布于 2019-09-19 17:35:29

还是仅仅是文档中提到的行为?

看来是这样。文档中提到的“开销”似乎是CPU开销。

对这两个查询进行分析,稀疏查询采样了367 ms的CPU,而非稀疏查询的CPU为284 ms .相差83毫秒。

其中大部分都在哪里?

这两个配置文件看起来非常相似,直到他们到达sqlmin!IndexDataSetSession::GetNextRowValuesInternal。此时,稀疏代码沿着运行sqlmin!IndexDataSetSession::GetDataLong的路径前进,该路径调用一些函数,这些函数看起来与稀疏列特性(HasSparseVector,StoreColumnValue)有关,加起来为(42 + 11 =) 53 ms。

为什么这些尺寸一样?稀疏的感觉消失了吗?

是的,当稀疏列用作索引键时,稀疏存储优化似乎不会转移到非聚集索引。因此,无论是否稀疏,非聚集索引键列都占据全部大小,但如果包含列稀疏且为NULL,则占用零空间。

查看具有空值稀疏列的聚集索引页的DBCC PAGE输出,我可以看到记录长度为11 (对于ID +7表示标准的每个记录开销):

代码语言:javascript
复制
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 11

对于过滤的索引,记录总是40,这是所有键列的大小之和(4字节ID + 20字节charval +4字节varcharval +4字节intval +8字节大intval = 40字节)。

出于某种原因,DBCC PAGE不包括索引记录的“记录大小”中的7字节开销:

代码语言:javascript
复制
Record Type = INDEX_RECORD          Record Attributes =  NULL_BITMAP    Record Size = 40

未经过滤的索引大小较小(4字节ID +4字节intval +4字节varcharval = 12字节),因为其中两个稀疏列包含列,这再次得到稀疏性优化:

代码语言:javascript
复制
Record Type = INDEX_RECORD          Record Attributes =  NULL_BITMAP    Record Size = 12

我猜这种行为上的差异与docs页面中列出的限制之一是一致的:

稀疏列不能是聚集索引或唯一主键索引的一部分。

允许它们作为非聚集索引中的键,但是它们不是,呃,稀疏地存储的。

票数 7
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/249182

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档