我有一个非常重要、非常缓慢的观点，其中在 where 子句中包含了一些非常丑陋的条件。我也知道连接是粗连接和慢连接varchar(13)而不是整数标识字段，但想改进下面使用此视图的简单查询：
CREATE VIEW [dbo].[vwReallySlowView] AS
AS
SELECT
I.booking_no_v32 AS bkno,
I.trans_type_v41 AS trantype,
B.Assigned_to_v61 AS Assignbk,
B.order_date AS dateo, B.HourBooked AS HBooked,
B.MinBooked AS MBooked, B.SecBooked AS SBooked,
I.prep_on AS Pon, I.From_locn AS Flocn,
I.Trans_to_locn AS TTlocn,
(CASE I.prep_on WHEN 'Y' THEN I.PDate ELSE I.FirstDate END) AS PrDate, I.PTimeH AS PrTimeH, I.PTimeM AS PrTimeM,
(CASE WHEN I.RetnDate < I.FirstDate THEN I.FirstDate ELSE I.RetnDate END) AS RDatev, I.bit_field_v41 AS bitField, I.FirstDate AS FDatev, I.BookDate AS DBooked,
I.TimeBookedH AS TBookH, I.TimeBookedM AS TBookM, I.TimeBookedS AS TBookS, I.del_time_hour AS dth, I.del_time_min AS dtm, I.return_to_locn AS rtlocn,
I.return_time_hour AS rth, I.return_time_min AS rtm, (CASE WHEN I.Trans_type_v41 IN (6, 7) AND (I.Trans_qty < I.QtyCheckedOut)
THEN 0 WHEN I.Trans_type_v41 IN (6, 7) AND (I.Trans_qty >= I.QtyCheckedOut) THEN I.Trans_Qty - I.QtyCheckedOut ELSE I.trans_qty END) AS trqty,
(CASE WHEN I.Trans_type_v41 IN (6, 7) THEN 0 ELSE I.QtyCheckedOut END) AS MyQtycheckedout, (CASE WHEN I.Trans_type_v41 IN (6, 7)
THEN 0 ELSE I.QtyReturned END) AS retqty, I.ID, B.BookingProgressStatus AS bkProg, I.product_code_v42, I.return_to_locn, I.AssignTo, I.AssignType,
I.QtyReserved, B.DeprepOn,
(CASE B.DeprepOn
WHEN 1 THEN B.DeprepDateTime
ELSE I.RetnDate
END) AS DeprepDateTime, I.InRack
FROM dbo.tblItemtran AS I
INNER JOIN -- booking_no = varchar(13)
dbo.tblbookings AS B ON B.booking_no = I.booking_no_v32 -- string inner-join
INNER JOIN -- product_code = varchar(13)
dbo.tblInvmas AS M ON I.product_code_v42 = M.product_code -- string inner-join
WHERE (I.trans_type_v41 NOT IN (2, 3, 7, 18, 19, 20, 21, 12, 13, 22)) AND (I.trans_type_v41 NOT IN (6, 7)) AND (I.bit_field_v41 & 4 = 0) OR
(I.trans_type_v41 NOT IN (6, 7)) AND (I.bit_field_v41 & 4 = 0) AND (B.BookingProgressStatus = 1) OR
(I.trans_type_v41 IN (6, 7)) AND (I.bit_field_v41 & 4 = 0) AND (I.QtyCheckedOut = 0) OR
(I.trans_type_v41 IN (6, 7)) AND (I.bit_field_v41 & 4 = 0) AND (I.QtyCheckedOut > 0) AND (I.trans_qty - (I.QtyCheckedOut - I.QtyReturned) > 0)
这个视图通常是这样使用的：
select * from vwReallySlowView
where product_code_v42 = 'LIGHTBULB100W' -- find "100 watt lightbulb" rows
当我运行它时，我得到这个执行计划项的成本占批处理总成本的 20% 到 80%，谓词CONVERT_IMPLICIT( .... &(4))表明它在执行这些操作时似乎很慢，bitwise boolean tests例如(I.ibitfield & 4 = 0).
我不是 MS SQL 或 DBA 类型工作的专家，因为我大部分时间都是非 SQL 软件开发人员。但我怀疑这种按位组合是一个坏主意，最好有离散的布尔字段。
我能否以某种方式改进我拥有的这个索引，以便在不更改架构（已经在数千个位置生产）的情况下更好地处理这个视图，或者我必须更改将几个布尔值打包成一个整数的基础表bit_field_v41，以解决这个问题?
tblItemtran这是我在此执行计划中正在扫描的聚集索引：
-- goal: speed up select * from vwReallySlowView where productcode = 'X'
CREATE CLUSTERED INDEX [idxtblItemTranProductCodeAndTransType] ON [dbo].[tblItemtran]
(
[product_code_v42] ASC, -- varchar(13)
[trans_type_v41] ASC -- int
)WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
CONVERT_IMPLICIT这是导致此谓词成本为 27% 的其他产品之一的执行计划。更新请注意，在这种情况下，我的最差节点现在是 an 上的“哈希匹配” inner join，这将花费 34% 我相信这是我无法避免的成本，除非我可以避免对我目前无法执行的字符串进行连接摆脱。上面视图中的两个INNER JOIN操作都在varchar(13)字段上。
放大右下角：
整个执行计划作为 .sqlplan 在 skydrive 上可用。此图像只是一个视觉概述。单击此处查看图像本身。
更新发布的整个执行计划。我似乎找不到什么product_code价值是病态的，但一种方法是不做select count(*) from view单一产品。但是，仅在基础表中 5% 或更少的记录中使用的产品似乎在CONVERT_IMPLICIT 操作中显示出低得多的成本。如果我要在这里修复 SQL，我想我会WHERE在视图中使用总条款，并计算并将那个巨大的 where-clause-condition 的结果作为“IncludeMeInTheView”位字段存储在基础表中. Presto，问题解决了，对吧？
您不应该过分依赖执行计划中的成本百分比。这些始终是估计成本，即使在具有“实际”数字（例如行数）的执行后计划中也是如此。估计的成本基于一个模型，该模型恰好可以很好地达到其预期目的：使优化器能够为同一查询在不同的候选执行计划之间进行选择。成本信息很有趣，也是一个需要考虑的因素，但它很少应该是查询调优的主要指标。解释执行计划信息需要对呈现的数据有更广泛的了解。
ItemTran 聚集索引搜索算子
这个操作符真的是两个操作合二为一。首先，索引查找操作查找与谓词匹配的所有行
product_code_v42 = 'M10BOLT'，然后每行都bit_field_v41 & 4 = 0应用了残差谓词。存在bit_field_v41从其基类型（tinyint或smallint）到的隐式转换integer。发生转换是因为按位与运算符(&) 要求两个操作数的类型相同。常量值“4”的隐式类型是整数，数据类型优先级规则意味着
bit_field_v41转换较低优先级的字段值。bit_field_v41 & CONVERT(tinyint, 4) = 0通过将谓词编写为- 这意味着常量值具有较低的优先级并且被转换（在常量折叠期间）而不是列值，可以轻松纠正问题（例如） 。如果bit_field_v41没有tinyint发生任何转换。同样，CONVERT(smallint, 4)可以使用 ifbit_field_v41issmallint。也就是说，在这种情况下，转换不是性能问题，但匹配类型并尽可能避免隐式转换仍然是一种好习惯。此查找的估计成本的主要部分取决于基表的大小。虽然聚集索引键本身相当窄，但每行的大小很大。没有给出表的定义，但只是视图中使用的列加起来有很大的行宽。由于聚集索引包括所有列，聚集索引键之间的距离是行的宽度，而不是索引键的宽度。在某些列上使用版本后缀表明实际表具有更多用于先前版本的列。
查看 seek、residual predicate 和 output 列，可以通过构建等效查询来单独检查此运算符的性能（这
1 <> 2是防止自动参数化的技巧，优化器消除了矛盾并且不会出现在查询计划）：使用冷数据缓存的此查询的性能令人感兴趣，因为预读会受到表（聚集索引）碎片的影响。此表的集群键会导致碎片，因此定期维护（重组或重建）此索引可能很重要，并使用适当
FILLFACTOR的空间来在索引维护窗口之间为新行留出空间。我使用使用SQL 数据生成器生成的示例数据对碎片对预读的影响进行了测试。使用问题的查询计划中显示的相同表行数，高度碎片化的聚集索引导致
SELECT * FROM view在DBCC DROPCLEANBUFFERS. 在相同条件下使用 ItemTrans 表上新重建的聚集索引的相同测试在 3 秒内完成。如果表数据通常完全在缓存中，那么碎片问题就不那么重要了。但是，即使碎片较少，宽表行也可能意味着逻辑和物理读取的数量远高于预期。您还可以尝试添加和删除显式
CONVERT来验证我的预期，即隐式转换问题在这里并不重要，除非违反最佳实践。更重要的是离开搜索运算符的估计行数。优化时间估计为 165 行，但在执行时生成了 4,226 行。稍后我将回到这一点，但差异的主要原因是残差谓词的选择性（涉及按位与）对于优化器来说非常难以预测——实际上它诉诸猜测。
过滤运算符
我在这里展示过滤谓词主要是为了说明两个
NOT IN列表是如何组合、简化和扩展的，同时也为下面的哈希匹配讨论提供参考。可以扩展来自 seek 的测试查询以合并其效果并确定 Filter 运算符对性能的影响：计划中的 Compute Scalar 运算符定义了以下表达式（计算本身被推迟到后面的运算符需要结果时）：
哈希匹配运算符
对字符数据类型执行连接并不是该运算符估计成本高的原因。SSMS 工具提示仅显示 Hash Keys Probe 条目，但重要的详细信息位于 SSMS 属性窗口中。
哈希匹配运算符使用
booking_no_v32ItemTran 表中的列 (Hash Keys Build) 的值构建哈希表，然后使用booking_noBookings 表中的列 (Hash Keys Probe) 探测匹配项。SSMS 工具提示通常也会显示 Probe Residual，但文本对于工具提示来说太长了，因此被简单地省略了。Probe Residual 类似于之前索引搜索后看到的 Residual；对所有哈希匹配的行评估残差谓词，以确定是否应将该行传递给父运算符。在平衡良好的哈希表中查找哈希匹配非常快，但相比之下，对匹配的每一行应用复杂的残差谓词则非常慢。Plan Explorer 中的 Hash Match 工具提示显示了详细信息，包括 Probe Residual 表达式：
残差谓词很复杂，现在包括预订进度状态检查，因为该列可从预订表中获得。该工具提示还显示了之前在索引查找中看到的估计行数和实际行数之间的相同差异。大部分过滤执行两次可能看起来很奇怪，但这只是优化器乐观。它不期望过滤器的部分可以从探测残差中下推到计划中以消除任何行（过滤器前后的行计数估计值相同），但优化器知道这可能是错误的。尽早过滤行的机会（减少散列连接的成本）值得额外过滤器的小成本。整个过滤器无法下推，因为它包括对预订表中的列的测试，但大部分都可以。
行数低估是哈希匹配运算符的一个问题，因为为哈希表保留的内存量是基于估计的行数。如果内存对于运行时所需的哈希表大小来说太小（由于行数较多），则哈希表会递归地溢出到物理tempdb存储，通常会导致性能非常差。在最坏的情况下，执行引擎会停止递归地溢出散列桶并诉诸非常慢的救助算法。哈希溢出（递归或救助）是问题中概述的性能问题（不是字符类型连接列或隐式转换）的最可能原因。根本原因是服务器基于不正确的行数（基数）估计为查询保留的内存太少。
遗憾的是，在 SQL Server 2012 之前，执行计划中没有迹象表明哈希操作超出了其内存分配（在执行开始之前被保留后无法动态增长，即使服务器有大量可用内存）并且不得不溢出到临时数据库。可以使用 Profiler 监视Hash 警告事件类，但很难将警告与特定查询相关联。
纠正问题
这三个问题是碎片、散列匹配算子中的复杂探测残差以及索引搜索时猜测导致的基数估计不正确。
推荐解决方案
检查碎片并在必要时进行更正，安排维护以确保索引保持可接受的组织状态。修正基数估计的常用方法是提供统计数据。在这种情况下，优化器需要组合 (
product_code_v42,bitfield_v41 & 4 = 0) 的统计信息。我们无法直接对表达式创建统计信息，因此必须先为位域表达式创建计算列，然后手动创建多列统计信息：计算的列文本定义必须与视图定义中的文本几乎完全匹配才能使用统计信息，因此应同时更正视图以消除隐式转换，并注意确保文本匹配。
多列统计应该产生更好的估计，大大减少哈希匹配运算符使用递归溢出或救助算法的机会。添加计算列（这是仅元数据操作，并且由于未标记，因此在表中不占用空间
PERSISTED）和多列统计信息是我对第一个解决方案的最佳猜测。在解决查询性能问题时，重要的是测量经过的时间、CPU 使用率、逻辑读取、物理读取、等待类型和持续时间……等等。单独运行部分查询以验证可疑原因也很有用，如上所示。
在某些环境中，数据的最新视图并不重要，运行后台进程以每隔一段时间将整个视图具体化到快照表中会很有用。该表只是一个普通的基表，可以为读取查询建立索引，而不必担心影响更新性能。
查看索引
不要试图直接索引原始视图。读取性能将非常快（在视图索引上单次查找），但（在这种情况下）现有查询计划中的所有性能问题都将转移到修改视图中引用的任何表列的查询。更改基表行的查询确实会受到非常严重的影响。
具有部分索引视图的高级解决方案
对于这个特定的查询，有一个部分索引视图解决方案，可以纠正基数估计并删除过滤器和探测残差，但它是基于对数据的一些假设（主要是我对模式的猜测）并且需要专家实施，特别是关于合适的索引以支持索引视图维护计划。出于兴趣，我分享下面的代码，我不建议你在没有非常仔细的分析和测试的情况下实现它。
现有视图调整为使用上面的索引视图：
示例查询和执行计划：
In the new plan, the hash match has no residual predicate, there is no complex filter, no residual predicate on the indexed view seek, and the cardinality estimates are exactly correct.
As an example of how insert/update/delete plans would be affected, this is the plan for an insert to the ItemTrans table:
The highlighted section is new and required for indexed view maintenance. The table spool replays inserted base table rows for indexed view maintenance. Each row is joined to the bookings table using a clustered index seek, then a filter applies the complex
WHEREclause predicates to see if the row needs to be added to the view. If so, an insert is performed to the view's clustered index.之前执行的相同
SELECT * FROM view测试在 150 毫秒内完成，索引视图就位。最后一件事：我注意到您的 2008 R2 服务器仍在 RTM。它不会解决您的性能问题，但2008 R2 的 Service Pack 2自 2012 年 7 月以来就已可用，并且有很多充分的理由让 Service Pack 尽可能保持最新状态。