在我的计算机上,以下查询执行聚集索引扫描,并且需要大约6.8秒的CPU时间:
SELECT ID1, ID2
FROM two_col_key_test WITH (FORCESCAN)
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);
下面的查询执行聚集索引查找(唯一的区别是删除了
FORCESCAN
提示),但是大约需要18.2秒的CPU时间:SELECT ID1, ID2
FROM two_col_key_test
WHERE ID1 NOT IN
(
N'1', N'2',N'3', N'4', N'5',
N'6', N'7', N'8', N'9', N'10',
N'11', N'12',N'13', N'14', N'15',
N'16', N'17', N'18', N'19', N'20'
)
AND (ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
ORDER BY ID1, ID2 OFFSET 12000000 ROWS FETCH FIRST 1 ROW ONLY
OPTION (MAXDOP 1);
查询计划非常相似。对于这两个查询,从聚集索引中读取的行为120000001行:
我在SQL Server 2017 CU 10上。以下是用于创建和填充
two_col_key_test
表的代码: drop table if exists dbo.two_col_key_test;
CREATE TABLE dbo.two_col_key_test (
ID1 NVARCHAR(50) NOT NULL,
ID2 NVARCHAR(50) NOT NULL,
FILLER NVARCHAR(50),
PRIMARY KEY (ID1, ID2)
);
DROP TABLE IF EXISTS #t;
SELECT TOP (4000) 0 ID INTO #t
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);
INSERT INTO dbo.two_col_key_test WITH (TABLOCK)
SELECT N'FILLER TEXT' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) > 8000000 THEN N' 2' ELSE N'' END
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, NULL
FROM #t t1
CROSS JOIN #t t2;
我希望得到的答案不只是调用堆栈报告。例如,与快速查询相比,我发现在慢查询中
sqlmin!TCValSSInRowExprFilter<231,0,0>::GetDataX
占用的CPU周期要多得多:我不想停在那里,我想了解这是什么,为什么两个查询之间会有如此大的差异。
为什么这两个查询的CPU时间有很大的差异?
#1 楼
为什么这两个查询的CPU时间有很大差异?
扫描计划为每行评估以下推入的非可精化(残留)谓词:
[two_col_key_test].[ID1]<>N'1'
AND [two_col_key_test].[ID1]<>N'10'
AND [two_col_key_test].[ID1]<>N'11'
AND [two_col_key_test].[ID1]<>N'12'
AND [two_col_key_test].[ID1]<>N'13'
AND [two_col_key_test].[ID1]<>N'14'
AND [two_col_key_test].[ID1]<>N'15'
AND [two_col_key_test].[ID1]<>N'16'
AND [two_col_key_test].[ID1]<>N'17'
AND [two_col_key_test].[ID1]<>N'18'
AND [two_col_key_test].[ID1]<>N'19'
AND [two_col_key_test].[ID1]<>N'2'
AND [two_col_key_test].[ID1]<>N'20'
AND [two_col_key_test].[ID1]<>N'3'
AND [two_col_key_test].[ID1]<>N'4'
AND [two_col_key_test].[ID1]<>N'5'
AND [two_col_key_test].[ID1]<>N'6'
AND [two_col_key_test].[ID1]<>N'7'
AND [two_col_key_test].[ID1]<>N'8'
AND [two_col_key_test].[ID1]<>N'9'
AND
(
[two_col_key_test].[ID1]=N'FILLER TEXT'
AND [two_col_key_test].[ID2]>=N''
OR [two_col_key_test].[ID1]>N'FILLER TEXT'
)
搜索计划执行两项搜索操作:
Seek Keys[1]:
Prefix:
[two_col_key_test].ID1 = Scalar Operator(N'FILLER TEXT'),
Start: [two_col_key_test].ID2 >= Scalar Operator(N'')
Seek Keys[1]:
Start: [two_col_key_test].ID1 > Scalar Operator(N'FILLER TEXT')
...以匹配谓词的这一部分:
(ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))
剩余谓词应用于通过上述查找条件的行(示例中的所有行)。
但是,每个不等式都由两个单独的检验代替,检验结果小于
OR
大于:([two_col_key_test].[ID1]<N'1' OR [two_col_key_test].[ID1]>N'1')
AND ([two_col_key_test].[ID1]<N'10' OR [two_col_key_test].[ID1]>N'10')
AND ([two_col_key_test].[ID1]<N'11' OR [two_col_key_test].[ID1]>N'11')
AND ([two_col_key_test].[ID1]<N'12' OR [two_col_key_test].[ID1]>N'12')
AND ([two_col_key_test].[ID1]<N'13' OR [two_col_key_test].[ID1]>N'13')
AND ([two_col_key_test].[ID1]<N'14' OR [two_col_key_test].[ID1]>N'14')
AND ([two_col_key_test].[ID1]<N'15' OR [two_col_key_test].[ID1]>N'15')
AND ([two_col_key_test].[ID1]<N'16' OR [two_col_key_test].[ID1]>N'16')
AND ([two_col_key_test].[ID1]<N'17' OR [two_col_key_test].[ID1]>N'17')
AND ([two_col_key_test].[ID1]<N'18' OR [two_col_key_test].[ID1]>N'18')
AND ([two_col_key_test].[ID1]<N'19' OR [two_col_key_test].[ID1]>N'19')
AND ([two_col_key_test].[ID1]<N'2' OR [two_col_key_test].[ID1]>N'2')
AND ([two_col_key_test].[ID1]<N'20' OR [two_col_key_test].[ID1]>N'20')
AND ([two_col_key_test].[ID1]<N'3' OR [two_col_key_test].[ID1]>N'3')
AND ([two_col_key_test].[ID1]<N'4' OR [two_col_key_test].[ID1]>N'4')
AND ([two_col_key_test].[ID1]<N'5' OR [two_col_key_test].[ID1]>N'5')
AND ([two_col_key_test].[ID1]<N'6' OR [two_col_key_test].[ID1]>N'6')
AND ([two_col_key_test].[ID1]<N'7' OR [two_col_key_test].[ID1]>N'7')
AND ([two_col_key_test].[ID1]<N'8' OR [two_col_key_test].[ID1]>N'8')
AND ([two_col_key_test].[ID1]<N'9' OR [two_col_key_test].[ID1]>N'9')
重写每个不等式,例如:
[ID1] <> N'1' -> [ID1]<N'1' OR [ID1]>N'1'
...在这里适得其反。排序规则感知的字符串比较昂贵。将比较次数加倍可以解释您所看到的CPU时间的大部分差异。
通过禁止使用未记录的跟踪标志9130禁止不可精化谓词的推送,您可以更清楚地看到这一点。这将显示剩余的作为单独的过滤器,可以单独检查性能信息:
这也将突出显示搜索中的轻微基数估计错误,这说明了为什么优化程序首先选择搜索而不是扫描(它希望搜索部分消除一些行)。
不等式重写可能使索引匹配(可能已过滤)成为可能(为了充分利用b树索引的查找能力),如果两个半均以残差结尾,则最好随后还原此扩展。您可以在SQL Server反馈网站上建议这样做。
还要注意,默认情况下,原始(“旧版”)基数估计模型碰巧选择了一个扫描。