时间戳

首页 > 解决方案 > 基于初始“Use dB”语句的复杂SQL查询性能差异较大

sql-server - 基于初始“Use dB”语句的复杂SQL查询性能差异较大

问题描述

为什么使用 Use 语句和隐式 dB 引用的复杂 SQL 查询比使用 Use master 语句和对用户 dB 的完整引用运行得更差？

我正在使用在 Windows Server 2012 R2 上运行的 SQL Server Std 64 位版本 13.0.4466.4。这是我的一个用户提出的“学术”问题，而不是生产的障碍。

“复杂”是指几个 WITH 子句和一个 CROSS APPLY，下面的简化查询结构。“更糟”是指 3 分钟。与 1 秒 239 行相比，可重复。用于快速查询的“普通”执行计划不会显示，但是，带有实时查询统计信息的执行计划会为两者运行，下面将进一步分析。提前Tanx 了解这方面的任何信息！

USE Master versus USE <userdb>;
DECLARE @chartID INTEGER = 65; 

WITH 

with1 AS 
(  SELECT stuff FROM <userdb>.schema1.userauxtable ),

with2 AS 
(  SELECT lotsastuff  FROM <userdb>.dbo.<views w/ JOINS> ), 

with3 AS
(  SELECT allstuff FROM with2 WHERE TheDate IN (SELECT MAX(TheDate) FROM with2 GROUP BY <field>, CAST(TheDate AS DATE)) ),

with4 AS 
(  SELECT morestuff  FROM with1 WHERE with1.ChartID = @chartID )

SELECT finalstuff FROM with3 
  CROSS APPLY ( SELECT littelstuff  FROM with4 WHERE 
  with3.TheDate BETWEEN with4.PreDate AND with4.AfterDate 
  AND with4.MainID = with3.MainID ) as AvgCross

用于慢速查询的带有实时查询统计信息的执行计划的成本约为 41%。（总共 83%）在两个操作中：

a) 深入第 5 步（共 15 步）哈希匹配（内部连接）哈希键构建... 41% 的索引扫描成本（非集群）...

b) 在第 4 步（共 15 个）嵌套循环（左半连接）下非常深 -- 每 (1) 次几乎相同的索引扫描的成本为 42%，除了添加 (... AND datediff(day,Date1,getdate( ) ) 到谓词。

虽然 Exec Plan w/ Live Query Stats for fast query 在第 9 步（共 12 个）Hash match (Inner Join) Hash Keys Build 下显示了相当深的 Columnstore Idx Scan（非集群）成本为 83%。

似乎不同之处在于列存储 Idx，但为什么使用主 stmt 会沿着这条路发送执行？

标签： sql-server

解决方案

这种行为可能有几个可能的原因；但是，为了识别所有这些，您需要像 Paul Randall 或 Kalen Delaney 这样的人来回答这个问题。

以我对 MS SQL Server 的有限知识和理解，我至少可以想到 2 个可能的原因。

1.（最合理的一个）查询实际上是不同的

如果，如您所说，查询文本足够长且复杂，则在添加数据库限定符时完全有可能错过单个对象（表、视图、用户定义的函数等）并使其没有 DB 前缀。现在，如果该名称的对象以某种方式最终出现在数据库master和您的UserDB数据库中，那么将根据当前数据库上下文选择不同的对象，数据可能不同，索引及其碎片，甚至数据类型......嗯，你明白了。

这样，查询会根据数据库上下文而有所不同，并且没有必要比较它们的性能。

2、用户数据库的兼容级别

回到 2005 版本的全盛时期，我有一个数据库，其兼容性级别设置为 80，因此旧客户端应用程序中一些过时的 ORM 生成的 ANSI SQL-89 外连接将继续工作。大多数美味的新东西也有效，但是有一个明显的例外：pivot关键字。

PIVOT在该数据库的上下文中执行带有 的查询时，会引发错误，指出无法识别关键字。但是，当我将上下文切换到master用户数据库名称并为所有内容添加前缀时，它运行得非常好。

当然，这不完全是你的情况，但它很好地证明了我在说什么。有许多肉眼看不见的内部 SQL Server 组件会影响执行计划、性能，有时甚至影响结果（或您检索它们的能力，如上例所示），这取决于数据库的兼容性级别等设置，跟踪标志和其他类似的东西。

作为一个可能的原因，我可以想到 SQL Server 2014 中引入的新基数估计器。您提到的 SQL Server 实例的版本对应于 2016 SP1 CU7，但是仍然有可能：

  • 您的用户数据库可能与 2012 版本兼容（例如，如果它是从 2012 备份中恢复的，之后没有人检查其设置），或者
  • 为会话或整个 SQL Server 实例设置跟踪标志 9481 ，或者
  • LEGACY_CARDINALITY_ESTIMATION为数据库等设置了数据库范围的配置选项。

（谢天谢地，SQL Server 不允许更改master数据库的兼容性级别，因此它始终是最新支持的级别。这可能很好，因为没有人可以自己搞砸数据库引擎 - 至少不是这样。）

我很确定我只是触及了主题的表面，所以在检查上述地方肯定不会受到伤害时，您需要做的是确定差异的实际原因（如果不是上面的＃1，那是）。这可以通过使用普通 SSMS 以外的工具查看查询的实际执行计划（忘记估计的，它们毫无价值）来完成。例如，SentryOne Plan Explorer 可能是一个很好的开始。即使没有这些，将计划保存在 .sqlplan 文件中并使用任何支持 XML 的查看器/编辑器打开它们也会向您展示更多信息，包括可能解释您观察到的差异的潜在线索。

推荐阅读