SQL Server 性能受新功能影响
改进某些 SQL 性能问题的具体示例。
引言
SQL Server 是一个伟大的系统，但它也有一些可能导致极端性能问题的怪癖。
不充分了解该系统可能会直接导致您遇到这些怪癖。另一个方面是，SQL Server 是一个“活”的系统。我的意思是它会随着时间而变化。这可能令人头疼，因为您曾经认为正确的事情可能最终被证明是错误的。这同时也是一个绝佳的机会，因为它开辟了新的编码途径。在不了解其潜在影响的情况下直接进入新事物，可能会让您深陷这些怪癖之中。
这是由于这种误解导致极端性能问题的示例。
我可以解释为什么我的函数运行得更快，我可以大致解释为什么 TOP 1000 比查看整个数据集的视图运行得慢。我无法解释为什么“TOP 100”比“TOP 1000”慢 11 倍，“TOP 10”慢 10.5 倍，“TOP 1”慢 3.3 倍。我无法解释为什么在 SSMS 查询中运行两个 `select` 语句的整个过程不到 6 秒，但在查询窗口中开始看到结果却需要 19 秒。
背景
首先，让我们回到基本概念。性能问题通常是由大量 IO、CPU 或 IO 争用引起的。关于第一个问题，您能做的就是将数据传输限制在较小的块中。CPU 是由执行某些操作引起的。在 SQL 中，在 `where` 子句中执行产生常量值的函数通常是最大的罪魁祸首。在任何情况下，一次执行通常比多次执行要快。将函数产生的常量值定义在一个变量中，然后在 `where` 子句中使用它，可以降低函数执行的成本。如果字段已索引，它可以迅速找到所需的行，但如果 `where` 使用函数，则必须检查整个表，为每一行执行函数。在此案例中，`where` 子句不是问题，但多次执行是此案例中的一个次要问题。IO 争用涉及到一次访问许多资源，但磁盘一次只能在一个位置，所以您必须等待磁盘移动。我认为这是本例的主要问题。（这里发生的很多事情对我来说并不合理。）
在 SQL 2005 中，很多事情发生了变化，但其中两件事特别导致了这个问题。公用表表达式 (CTE) 是在 2005 年引入的。这是一种创建子查询的新方法，它具有递归特性，使得 CTE 能够完成子查询中无法完成的任务。但是，子查询应谨慎使用，因为它会消除表中可能有助于查询的索引。这里同时使用了几个 CTE。
天真如我，即将步入“狮子口”。我刚开始一份新工作，我在 SSMS 里……嗯，这个视图里有什么样的数据？使用下拉菜单获取前 1000 行。嗯，30 秒了，还没有数据，还在运行。那么，这个视图里有什么呢？在新窗口中打开一个“`create view`”脚本。天哪，这简直是一个怪物。许多（6 个）CTE 不断地与视图连接，而我看到的第一个 CTE 查询让我毛骨悚然。这个视图运行得怎么样了？还在运行。19 分钟后，我终于得到了结果。
在我运行的同时，我创建了两个查询，一个是让我毛骨悚然的那个，另一个是功能相同但我想成本更低的替代方案。我得到了一个成本估算。令人毛骨悚然的那个是 100%，我的版本是 0%。我准备了一封电子邮件，谈论这个视图，展示成本估算，说明我为什么不喜欢成本估算，因为它们可能不准确，而且这可能不是问题的根源。（它不是问题的根源，而且成本几乎是 50/50，因为它的测试条件在数据中不存在。）
Using the Code
下载文件包含一个 SQL 文件。它旨在创建一个新数据库，使用它并设置包含数据的表、视图和表值函数。换句话说，在您的服务器上建立一个测试环境。只有当您想设置测试环境来了解 SQL 的情况时才执行它。在此之前，您可以更改数据库名称，或者直接使用您已定义的数据库。我这样设置是为了方便您通过删除数据库来删除所有内容，如果您愿意的话。
这两个视图与我执行的视图类似。名称已被更改，试图隐藏任何可能专有的内容。条件也已更改，以便更容易设置测试环境。
如果您使用 SSMS 从视图 `vwVRec` 中选择 1000 行（如果您保持数据库名称和脚本不变），生成的脚本如下。它使用了 `vwA` 13 次。`vwA` 的执行速度比 `vwVRec` 快得多，但仍然很慢。如果您想看到 `vwVRec` 的加速版本，可以打开一个查询窗口，指向该数据库，然后执行“`select * from dbo.fnVRec()`”。
/****** Script for SelectTopNRows command from SSMS ******/
SELECT
TOP 1000 [ClassID]
,[Class]
,[ClassDesc]
,[SubClassID]
,[SubClass]
,[SubClassDesc]
,[GroupID]
,[GroupCode]
,[GroupDesc]
,[LIID]
,[LI]
,[LIDesc]
,[LIDID]
,[LID]
,[LIDDesc]
,[CEID]
,[CE]
,[CECode]
,[CEDesc]
FROM [testdbx].[dbo].[vwVRec]
在我的机器上，这大约需要一分钟。我第一次尝试时，忘记给其中一个表添加索引，结果运行了 2 个小时。这比原始版本确实简化了很多。我认为与我在工作中运行了 19 分钟的版本完全相同的版本，在我的机器上运行需要 8 分钟。测试环境表现不同并不罕见。视图中的数据是无意义的数据，但它保留了原始数据所具有的独特性。`fnVRec` 返回的数据并不完全像视图，因为它被设置为实现注释中说明的视图应该实现的功能。最初我匹配了视图的结果，而函数的性能在这两个版本中都没有受到影响。
我选择了函数，因为您可以在函数中实现比视图更复杂的逻辑，并利用它来提高性能。
在 `vwA` 中，它只使用了一个 CTE。（它不必被命名为 CTE，这似乎是一种常见的做法。如果您愿意，也可以在查询之前设置字段名。）这会生成一个包含 6 个 `hierarchyid` 类型字段的查询。在层级结构中，您通常是从上往下查看，脚本加载数据的方式也是如此，但这个查询是从下往上走的。因此，`CEHID` 是最低级别，它有 10 条记录定义在每个 `LIDHID` 下。每个其他级别都有 5 个子级别，包括最后一个级别 `ClassHID`。（总共 5 条记录。）
WITH CTE
as
(
select g.MembHierarchyId CEHID
,g.MembHierarchyId.GetAncestor(1) LIDHID
,g.MembHierarchyId.GetAncestor(2) LIHID
,g.MembHierarchyId.GetAncestor(3) GroupHID
,g.MembHierarchyId.GetAncestor(4) SubClassHID
,g.MembHierarchyId.GetAncestor(5) ClassHID
from dbo.tstAMemb g
join dbo.tstANode n on n.NodeId = g.NodeID
where n.NodeName = 'FinalA'
)
5 的 6 次方乘以 2 是 31250。这是 `CEHID`（`NodeName = 'FinalA'`）中的记录数。这是 `vwA` 中唯一的 `cte` 中进行的 5 次函数调用（`GetAncestor`）的次数。然后，这个 `cte` 与 `tstAMemb` 连接 6 次，试图同时查找一个表中的 6 个不同位置。连接到同一个表 6 次就是 IO 争用。（在我的机器上不算多，这个视图运行只需要 3-4 秒。`fnA` 运行只需要 2-3 秒。）
在 `fnA` 中，它定义了一个变量表 `@CE`，该表检索 `vwA` 中的 6 个连接之一所提取的所有数据。该函数写入 6 个变量表，总共调用 `GetAncestor` 35,155 次（表中记录的数量）。`fnA` 中的脚本比 `vwA` 更复杂、更长，但仍然稍快一些。这是执行 `GetAncestor` 31,250 次的第一个查询（将相同数量的行放入 @CE）。
DECLARE @CE TABLE(CEHID HierarchyId primary key, LIDHID HierarchyId
, CEid int, CE varchar(10), CEDesc varchar(200)
)
INSERT INTO @CE (CEHID, LIDHID, CEid, CE, CEDesc)
select g.MembHierarchyId CEHID
,g.MembHierarchyId.GetAncestor(1) LIDHID
,g.AMembId
,g.Code
,g.Description
FROM dbo.tstAMemb g
JOIN dbo.tstANode n ON n.NodeId = g.NodeID
WHERE n.NodeName = 'FinalA';
它定义了一个变量表 `@LID`，该表检索 `@CE` 中的不同子值。然后，这个包含 3,125 条记录的表使用 `vwA` 的另一个连接（这里也使用了）进行更新。它调用 `GetAncestor` 3,125 次。这个过程重复四次，使用四个变量表，因此 `tstAMemb` 被访问六次以获取六组信息，但一次只获取一组。（没有 IO 争用。）在 `vwA` 中，所有六次连接都必须匹配 31K 次。在 `fnA` 中，最后一个连接匹配五条记录。
DECLARE @LID TABLE(LIDHID HierarchyId primary key, LIHID HierarchyId
, LIDid int, LID varchar(10), LIDDesc varchar(200)
)
INSERT INTO @LID (LIDHID)
SELECT DISTINCT LIDHID FROM @CE;
update a set LIHID = a.LIDHID.GetAncestor(1), LIDid=g.AMembId
,LID=g.Code, LIDDesc=g.Description
FROM @LID a
JOIN dbo.tstAMemb g ON g.MembHierarchyId=LIDHID;
`vwVRec` 有六个类似的 CTE：
WITH CLASSCODE (ClassDesc, Class, Pair)
as
(
SELECT distinct
f1.ClassDesc
,RTRIM(f1.Class)
,f1.ClassDesc + ' (' + RTRIM(f1.Class) + ')'
FROM dbo.vwA f1
join dbo.vwA f2 on f2.ClassDesc = f1.ClassDesc
AND f2.Class <> f1.Class
)
`vwA` 有六个对同一个表的连接，并且执行超过 150K 次函数。因此，上面的 CTE（这个 CTE 被称为 `CLASSCODE` 并在稍后的 `join` 中使用）是这个的两倍。这就是让我皱眉的查询。它在查找重复的描述，而类名不同。我皱眉的原因是：如果有一**千**条记录使用相同的描述，这将**连接一百万**条记录，如果所有一千条都使用不同的类名，那么“distinct”必须将一百万条记录减少到一千条。请注意，`RTRIM` 函数对每条记录执行两次（在 distinct 减少记录之前）。
在下面的代码中，移除 `datetime` 逻辑并执行估算的执行成本，第一个查询的成本是 99%，第二个是 1%。这在估算工具中通常是正常的，当一个查询不是最优的时候。我首先使用了上述字段进行了比较，结果为 0。并将两个 select 执行结果保存在注释行中。（查询中结果为 0 和 2）。wallclock 时间来自 SSMS 查询窗口。我没有看结果窗口，所以又运行了第三次。在出现任何结果之前，等待了 19 秒。两次都更快。分别快了 7/100 秒和 1/10 秒。我不知道为什么结果显示有延迟，这不正常。注释掉前两个 `select` 语句，所有三次（第三个 `select` 中的 2 个 `datetime` 差异，以及 SSMS 查询窗口）都记录并花费了 0 秒。
DECLARE @d1 DATETIME=GETDATE(), @d2 DATETIME, @d3 DATETIME
SELECT distinct
f1.LIDDesc
,RTRIM(f1.LID)
,f1.LIDDesc + ' (' + RTRIM(f1.LID) + ')'
FROM dbo.vwA f1
join dbo.vwA f2 on f2.LIDDesc = f1.LIDDesc
AND f2.LID <> f1.LID
SET @d2=GETDATE()
SELECT DISTINCT
LIDDesc
,RTRIM(LID)
,LIDDesc + ' (' + RTRIM(LID) + ')'
FROM dbo.vwA
WHERE LIDDesc IN
(SELECT LIDDesc FROM dbo.vwA GROUP BY LIDDesc HAVING COUNT(DISTINCT LID) > 1)
SET @d3=GETDATE()
select DATEDIFF(millisecond,@d1,@d2)/1000. [Secs first], _
DATEDIFF(millisecond,@d2,@d3)/1000. [Secs second]
-- ClassDesc: [Secs first]=3.910000, [Secs second]=1.476000 wallclock=20 seconds
-- LIDDesc: [Secs first]=3.390000, [Secs second]=1.943000 wallclock=20 seconds
现在，来看产生输出的 `view join`。
from dbo.vwA ce
left join CLASSCODE CL1 on CL1.Class = RTRIM(ce.Class)
left join SUBCLASSCODE SC2 on SC2.SubClass = RTRIM(ce.SubClass)
left join GROUPCODE GR3 on GR3.GroupCode = RTRIM(ce.GroupCode)
left join LICODE LI4 on LI4.LI = RTRIM(ce.LI)
left join LIDCODE LD5 on LD5.LID = RTRIM(ce.LID)
left join CECODE CE6 on CE6.CE = RTRIM(ce.CE)
没有任何索引，所以抱怨 `where` 子句中的函数没有意义。据我计算，这是 13 次 `vwA` 的连接，78 次同步连接到同一个表，以及（不算 `RTRIM` 函数执行次数）2,031,250 次（31,250*5*13）函数调用来创建所有记录。我不知道在 TOP 处理的底层发生了什么。
对于 `fnVRec` 中的重复描述处理，我创建了一个变量表，该表查找在多个 Code 记录值中使用的所有重复描述，然后删除每个级别只有一个描述的那些描述（在原始设置中测试过，在此设置中未测试）。这在一个表中一次性处理了所有 6 个级别。
DECLARE @Code TABLE (Code varchar(10), level smallint, _
Descrpt varchar(200), Pair varchar(200))
INSERT INTO @Code
SELECT DISTINCT Code, MembHierarchyId.GetLevel(), _
Description, Description + ' (' + RTRIM(Code) + ')'
FROM dbo.tstAMemb
WHERE Description IN
(SELECT Description from dbo.tstAMemb
GROUP BY Description
HAVING COUNT(DISTINCT Code) > 1)
DELETE a --Remove rows where descriptions match only on different levels
FROM @Code a
JOIN (SELECT level, Descrpt
FROM @Code group by level, Descrpt having count(*) = 1) b
ON b.level=a.level AND b.Descrpt=a.Descrpt
在原始数据库中，这将找到零条记录，在此数据库中（除非您更改表中的数据），它将找到 2 条记录。要匹配不同的级别，您可以手动更新一条记录，如果您愿意的话。视图在 CTE 中创建了一个“`pair”字段，但从未被使用。我使用了它（因为我更改了 `join` 逻辑，使其匹配注释）。
在视图/函数的 `select` 部分，我确实在 `ISNULL` 逻辑中使用了 pair 值，而视图使用了一个 `case` 语句。
", ISNULL(CL1.Pair,ce.ClassDesc)"
" , ce.ClassDesc + CASE WHEN CL1.ClassDesc IS NOT NULL
THEN + ' (' + RTRIM(CL1.Class) + ')'
ELSE ''
END"
我使用了 `ISNULL`，因为我读到内联函数比 `CASE` 语句更快。我认为它也更容易阅读。
这个语句：`ce.ClassDesc + ISNULL(' (' + CL1.Class + ')','')` 实现了与视图的 `case` 语句相同的功能。（因为在视图的 CTE 中，`RTRIM` 已经在这个字段上执行过了。）如果您想标记视图在类数据中标记的每个唯一描述，也可以在函数中执行此操作。有一个“`fifth1”代码和一个“`fifth2”代码（`LID` 字段）使用了重复的描述。函数在 20 条记录中包含带括号的类名（`fifth1`/`fifth2`），而视图在 6K 条记录中包含它。这是因为我在生成数据时没有使用不同的代码值命名约定。
关注点
如果您不使用“`TOP”任何数字，视图 `vwVRec` 运行得更快。这告诉我 SQL 不会截断数据流，而是会在满足“`top”标准时中断它。您首先看到的“`top”结果与选择所有数据时的第一个结果不同。这表明“`TOP”会影响数据处理顺序。
“好的，`top 1000` 要 8 分钟，那么所有数据是 31 倍大，列出所有数据大约需要 4 个小时。呃，我真的要这么做吗？好吧，我最好去做。1 分钟后，就完成了！！”
这些是我（现在仍然是）在我机器上的原始观察结果，当时模式更接近于原始设置，结果需要 19 分钟。使用此处提供的新版本，`top 1000` 大约需要一分钟，不使用 `TOP` 大约需要 40 秒。我没有在原始设置上测试过，但在该版本上，`TOP 100` 需要 11 分钟。`TOP 10` - 10:30，`top 5` - 5:20，`top 1` - 3:20。我对此没有任何解释。
流计算完成后没有停止，这并不意外，我事先就知道。影响处理顺序，并且使用 `TOP` 导致性能下降，这让我感到意外。
`fnVRec` 的性能在需要 8 分钟的模式下与此模式（下载中的）几乎相同，此模式需要 1 分钟才能获得视图中的 `TOP 1000` 行。当我忘记包含索引时，该函数执行成本增加了大约 2 秒。
对于 `vwA`，视图或函数之间没有显著差异。未缓存时，它们都完成于 4-5 秒，并根据输出在零到 2 秒内产生结果。（`TOP` 使视图比函数快。预期差异。）缓存时，它们大约在 2 秒内完成。`ORDER BY` 对函数的速度没有影响，对 `vwA` 的影响也很小。`vwVRec` 会**延迟数小时**！
`TOP` 可以显著加速 `vwA`（使用 `TOP` 的预期行为）。
在调查原因时，我打开了 SQL Profiler，并让它告诉我所有信息（关于 `vwA`）。我得到了 155K 条记录，其中只显示了命令的开始时间，但没有其他信息。没有命令、经过时间、成本等。整张表有 33K 条记录。什么鬼？哦，天哪。查询中有 31K 条记录是相关的，并且它执行了 5 个函数。5 * 31K = 155K。对于 `fnA`，它执行一个函数 35,155 次（5 + 25 + 125 + 625 + 3,125 + 31,250）。前 31K 用于相关记录，然后是其父值的 1/10，然后每次是 1/5，一直到基础的 5 条记录。
如果您想了解更多关于 `hierarchyid` 的信息，请查看下载脚本如何生成数据，以及视图和函数如何处理数据。然后，您可能需要查找帮助以获取关于这个相对较新的元素如何工作的更详细的解释。
如果您想了解 CTE 的工作原理，请查看 `vwVRec`。不要在函数中查找，函数中没有使用 CTE。请查找帮助以获取更多信息，因为它们比视图中的 CTE 功能强大得多。它们是 SQL 工具箱中一个有价值的工具，我只是觉得它们的使用频率可能比实际需要的要高一些。我必须承认，它们比使用子查询更易读。
历史
我没有记录完成文章的每一个步骤。基本上是在写文章的同时重读、重写和运行新的测试。最后更新日期 10/6。