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,7) THEN 0 ELSE I.QtyCheckedOut END) AS MyQtycheckedout,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

-- 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]

解决方法

ItemTran Clustered Index Seek Operator

这个操作符实际上是两个操作合二为一.首先,索引查找操作找到与谓词product_code_v42 =’M10BOLT’匹配的所有行,然后每行具有残差谓词bit_field_v41&应用4 = 0.将bit_field_v41从其基类型(tinyint或smallint)隐式转换为整数.

发生转换是因为bitwise-AND operator(&)要求两个操作数属于同一类型.常量值’4’的隐式类型是整数,而data type precedence rules意味着转换低优先级的bit_field_v41字段值.

通过将谓词写为bit_field_v41&可以很容易地纠正问题(例如它). CONVERT(tinyint,4)= 0 – 意味着常量值具有较低的优先级并被转换(在常量折叠期间)而不是列值.如果bit_field_v41是tinyint,则根本不会发生转换.同样,如果bit_field_v41为smallint,则可以使用CONVERT(smallint,4).也就是说,转换不是性能问题,但在可能的情况下匹配类型并避免隐式转换仍然是一种很好的做法.

此寻求的估计成本的主要部分是基表的大小.虽然聚簇索引键本身相当窄,但每行的大小很大.没有给出表的定义,但只是视图中使用的列加起来有明显的行宽.由于聚簇索引包括所有列,因此聚簇索引键之间的距离是行的宽度,而不是索引键的宽度.在某些列上使用版本后缀表明实际表格中有更多列用于以前的版本.

查看搜索,残差谓词和输出列,可以通过构建等效查询来单独检查该运算符的性能(1<> 2是防止自动参数化的技巧,优化器消除了矛盾并且不会出现在查询计划中)：

SELECT
    it.booking_no_v32,it.QtyCheckedOut,it.QtyReturned,it.Trans_qty,it.trans_type_v41
FROM dbo.tblItemTran AS it
WHERE
    1 <> 2
    AND it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint,4) = 0;

具有冷数据高速缓存的此查询的性能是令人感兴趣的,因为预读将受到表(聚簇索引)碎片的影响.此表的群集密钥会引发碎片,因此定期维护(重新组织或重建)此索引非常重要,并使用适当的FILLFACTOR为索引维护窗口之间的新行留出空间.

我使用SQL Data Generator生成的样本数据执行了碎片对预读的影响的测试.使用相同的表行计数,如问题的查询计划中所示,高度分段的聚簇索引导致SELECT * FROM视图在DBCC后15秒DROPCLEANBUFFERS.在相同条件下的相同测试与ItemTrans表上新重建的聚簇索引在3秒内完成.

如果表数据通常完全在缓存中,则碎片问题非常不重要.但是,即使碎片较少,宽表行也可能意味着逻辑和物理读取的数量远远高于预期.您还可以尝试添加和删除显式CONVERT以验证我的期望隐含转换问题在此处不重要,除非是最佳实践违规.

更重要的是离开搜索运算符的估计行数.优化时间估计为165行,但在执行时生成4,226行.我稍后会回到这一点,但是出现差异的主要原因是残差谓词的选择性(涉及按位与AND)对于优化器来说很难预测 – 实际上它是用来猜测的.

过滤运算符

我在这里显示过滤谓词主要是为了说明两个NOT IN列表是如何组合,简化然后扩展的,以及为以下哈希匹配讨论提供参考.可以扩展来自搜索的测试查询以合并其效果并确定Filter运算符对性能的影响：

SELECT
    it.booking_no_v32,it.trans_type_v41,it.QtyCheckedOut
FROM dbo.tblItemTran AS it
WHERE
    it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint,4) = 0
    AND
    (
        (
            it.trans_type_v41 NOT IN (2,6,22)
            AND it.trans_type_v41 NOT IN (6,7)
        )
        OR
        (
            it.trans_type_v41 NOT IN (6,7)
        )
        OR 
        (
            it.trans_type_v41 IN (6,7)
            AND it.QtyCheckedOut = 0
        )
        OR 
        (
            it.trans_type_v41 IN (6,7)
            AND it.QtyCheckedOut > 0
            AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
        )
    );

计划中的Compute Scalar运算符定义了以下表达式(计算本身将延迟到稍后运算符需要结果)：

[Expr1016] = (trans_qty - (QtyCheckedOut - QtyReturned))

哈希匹配运算符

对字符数据类型执行连接不是此运算符的高估计成本的原因. SSMS工具提示仅显示Hash Keys Probe条目,但重要的详细信息位于SSMS Properties窗口中.

Hash Match运算符使用ItemTran表中的booking_no_v32列(Hash Keys Build)的值构建哈希表,然后使用Bookings表中的booking_no列(Hash Keys Probe)探测匹配. SSMS工具提示通常也会显示Probe Residual,但文本对于工具提示来说太长了,而且只是省略了.

探针残留类似于索引之前寻找的残差;在散列匹配的所有行上计算残差谓词,以确定是否应将行传递给父运算符.在均衡的哈希表中查找哈希匹配非常快,但是通过比较将复杂的残差谓词应用于匹配的每一行是相当慢的. Plan Explorer中的Hash Match工具提示显示详细信息,包括Probe Residual表达式：

剩余谓词很复杂,包括预订进度状态检查,现在该列可从预订表中获得.工具提示还显示在索引搜索中较早看到的估计行数和实际行数之间的差异.大部分过滤都执行了两次似乎很奇怪,但这只是优化者乐观的.它不期望过滤器的部分可以从探测器残差下推到计划中以消除任何行(过滤器之前和之后的行计数估计是相同的)但优化器知道它可能是错误的.提前过滤行的机会(降低散列连接的成本)值得额外过滤器的小成本.整个过滤器无法向下推,因为它包括对预订表中列的测试,但大多数都可以.在哈希残差测试中仍然需要完整的测试以确保正确性.

行计数低估是哈希匹配运算符的问题,因为为哈希表保留的内存量基于估计的行数.如果内存对于运行时所需的哈希表的大小而言太小(由于行数较多),则哈希表会递归地溢出到物理tempdb存储,这通常会导致性能非常差.在最坏的情况下,执行引擎停止递归地将散列桶和调度溢出到非常慢的救助算法.散列溢出(递归或救助)是问题中概述的性能问题的最可能原因(不是字符类型连接列或隐式转换).根本原因是服务器基于不正确的行计数(基数)估计为查询保留太少的内存.

遗憾的是,在sql Server 2012之前,执行计划中没有任何迹象表明散列操作超出了内存分配(在执行开始之前保留后不能动态增长,即使服务器有大量可用内存)并且不得不泄漏到tempdb中.可以使用Profiler监视Hash Warning Event Class,但是可能很难将警告与特定查询相关联.

纠正问题

这三个问题是碎片,散列匹配运算符中的复杂探测残差以及索引搜索中猜测导致的不正确的基数估计.

推荐解决方案

检查碎片并在必要时进行更正,安排维护以确保索引保持可接受的组织.纠正基数估算的常用方法是提供统计数据.在这种情况下,优化器需要组合的统计数据(product_code_v42,bitfield_v41& 4 = 0).我们无法直接在表达式上创建统计信息,因此我们必须首先为位字段表达式创建计算列,然后创建手动多列统计信息：

ALTER TABLE dbo.tblItemTran
ADD Bit3 AS bit_field_v41 & CONVERT(tinyint,4);

CREATE STATISTICS [stats dbo.ItemTran (product_code_v42,Bit3)]
ON dbo.tblItemTran (product_code_v42,Bit3);

计算列文本定义必须与视图定义中的文本完全匹配才能使用的统计信息,因此应该同时更正视图以消除隐式转换,并注意确保文本匹配.

多列统计信息应该会产生更好的估计值,从而大大降低哈希匹配运算符使用递归溢出或救助算法的可能性.添加计算列(这是一个仅元数据操作,并且表中没有空格,因为它没有标记为PERSISTED),并且多列统计数据是我对第一个解决方案的最佳猜测.

在解决查询性能问题时,重要的是要测量经过时间,cpu使用率,逻辑读取,物理读取,等待类型和持续时间……等等.如上所示,单独运行部分查询以验证可疑原因也很有用.

在某些环境中,数据的最新视图并不重要,运行后台进程非常有用,后台进程每隔一段时间就将整个视图实现为快照表.此表只是一个普通的基表,可以为读取查询编制索引,而不必担心影响更新性能.

查看索引

不要试图直接索引原始视图.读取性能将非常快(对视图索引进行单一搜索)但(在这种情况下)现有查询计划中的所有性能问题都将转移到修改视图中引用的任何表列的查询.更改基表行的查询确实会受到严重影响.

具有部分索引视图的高级解决方案

对于此特定查询,存在部分索引视图解决方案,其校正基数估计并移除过滤器和探测残差,但它基于对数据的一些假设(主要是我对模式的猜测)并且需要专家实施,特别是关于合适的索引以支持索引视图维护计划.我有兴趣分享下面的代码,我不建议你在没有仔细分析和测试的情况下实现它.

-- Indexed view to optimize the main view
CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
SELECT
    it.ID,it.product_code_v42,it.booking_no_v32,it.QtyReserved,it.bit_field_v41,it.prep_on,it.From_locn,it.Trans_to_locn,it.PDate,it.FirstDate,it.PTimeH,it.PTimeM,it.RetnDate,it.BookDate,it.TimeBookedH,it.TimeBookedM,it.TimeBookedS,it.del_time_hour,it.del_time_min,it.return_to_locn,it.return_time_hour,it.return_time_min,it.AssignTo,it.AssignType,it.InRack
FROM dbo.tblItemTran AS it
JOIN dbo.tblBookings AS tb ON
    tb.booking_no = it.booking_no_v32
WHERE
    (
        it.trans_type_v41 NOT IN (2,22)
        AND it.trans_type_v41 NOT IN (6,7)
        AND it.bit_field_v41 & CONVERT(tinyint,4) = 0
    )
    OR
    (
        it.trans_type_v41 NOT IN (6,4) = 0
        AND tb.BookingProgressStatus = 1
    )
    OR 
    (
        it.trans_type_v41 IN (6,4) = 0
        AND it.QtyCheckedOut = 0
    )
    OR 
    (
        it.trans_type_v41 IN (6,4) = 0
        AND it.QtyCheckedOut > 0
        AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
    );
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V1 (product_code_v42,ID);
GO

调整现有视图以使用上面的索引视图：

CREATE VIEW [dbo].[vwReallySlowView2]
AS
SELECT
    I.booking_no_v32 AS bkno,CASE I.prep_on 
        WHEN 'Y' THEN I.PDate
        ELSE I.FirstDate
    END AS PrDate,CASE
        WHEN I.RetnDate < I.FirstDate 
        THEN I.FirstDate 
        ELSE I.RetnDate
    END AS RDatev,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,7)
        THEN 0
        ELSE I.QtyReturned
    END AS retqty,CASE B.DeprepOn
        WHEN 1 THEN B.DeprepDateTime
        ELSE I.RetnDate
    END AS DeprepDateTime,I.InRack
FROM dbo.V1 AS I WITH (NOEXPAND)
JOIN dbo.tblbookings AS B ON
    B.booking_no = I.booking_no_v32
JOIN dbo.tblInvmas AS M ON
    I.product_code_v42 = M.product_code;

示例查询和执行计划：

SELECT
    vrsv.*
FROM dbo.vwReallySlowView2 AS vrsv
WHERE vrsv.product_code_v42 = 'M10BOLT';

在新计划中,哈希匹配没有残差谓词,没有复杂的过滤器,索引视图搜索上没有残差谓词,基数估计完全正确.

作为插入/更新/删除计划如何受到影响的示例,这是插入ItemTrans表的计划：

突出显示的部分是新的,是索引视图维护所必需的.表假脱机重放插入的基表行以进行索引视图维护.使用聚簇索引查找将每一行连接到预订表,然后过滤器应用复杂的WHERE子句谓词以查看是否需要将行添加到视图中.如果是,则对视图的聚簇索引执行插入.

之前执行的相同SELECT * FROM视图测试在150ms内完成,索引视图就位.

最后一件事：我注意到你的2008 R2服务器仍处于RTM状态.它不会解决您的性能问题,但是自2012年7月开始提供Service Pack 2 for 2008 R2,并且有很多充分的理由要尽可能保持最新的服务包.