SQL Server性能问题案例解析 （2）

语句执行时间长是SQL Server 性能问题的一种典型表现形式。当运行一条语句所需要的CPU时间较长或者所需的内存资源较多时，我们往往需要对目标语句本身进行调优。通常情况下，我们可以通过更新统计信息，修改索引，使用语句执行计划的强制选择(使用Hint), 以及对于语句本身的修改来使得语句占用更少的CPU时间或者内存。

在对问题语句调优之前，我们需要得到这条语句的执行计划。除了通过SQL Server Management Studio上的 "Include Actual Execution Plan"这个图标外，我们可以通过SQL Server Profiler中Performance - Showplan Statistics Profile这个事件的捕捉来获得语句的执行计划。

另外在问题语句执行时通过下面的方式可以收集到较为直观和详细的执行计划和相关统计信息。

 

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SET STATISTICS PROFILE ON;

GO

--问题语句执行

GO

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

SET STATISTICS PROFILE OFF;

 

下面开始我们的案例分析。 在这个案例中，客户最初碰到的问题是SQL Server 进程CPU使用率很高。通过我们的分析定位发现了占用CPU时间最多的语句。在这篇博文中，我们会着重分析这条问题语句占用较高CPU资源的原因。

问题语句本身是对一个用户定义视图运行select * 语句，相应的constraint也较为简单。但是这个视图的定义较为复杂，对应的定义我们简化如下：

 

CREATE VIEW [dbo].[demoView]

AS
WITH DemoInfo

              AS (Complex_Select_Query)  
SELECT                                           

       DISTINCT

            ColumnAlias31 ,

            ColumnAlias32 ,

            ColumnAlias33 ,

            ColumnAlias34 ,

            ColumnAlias35 ,

            ColumnAlias36 ,

            ... ,

            ColumnAlias325,

            ColumnAlias326 = CASE WHEN EXISTS ( SELECT TOP 1

    FROM DemoInfo b

                                    WHERE b.ColumnAlias328 = a.ColumnAlias328
AND a.ColumnAlias33 > b.ColumnAlias33 )

                      THEN NULL

                      ELSE a.ColumnAlias326

                 END ,

            ColumnAlias327 = CASE WHEN EXISTS (SELECT TOP 1

                                    FROM DemoInfo b

                                    WHERE b.ColumnAlias328 = a.ColumnAlias328
AND a.ColumnAlias33 > b.ColumnAlias33 )

                      THEN NULL

                      ELSE a.ColumnAlias327

                 END ,

            ColumnAlias328 = CASE WHEN EXISTS (SELECT TOP 1

                                               
                                        FROM DemoInfo b

                                        WHERE b.ColumnAlias328 =a.ColumnAlias328
AND a.ColumnAlias33 > b.ColumnAlias33 )

                           THEN NULL

                           ELSE
           a.ColumnAlias328

                      END ,

            ColumnAlias329 = CASE WHEN EXISTS (SELECT TOP 1

                               FROM DemoInfo b                                    
                                   WHERE b.ColumnAlias328 =a.ColumnAlias328
AND a.ColumnAlias33 > b.ColumnAlias33 )

                        THEN NULL

                        ELSE 1

                   END ,

            ColumnAlias30 ,

            ColumnAlias31 ,

            ColumnAlias32 ,

            ColumnAlias33 ,

            ColumnAlias34 ,

             ...

            ColumnAlias60

             FROM DemoInfo a        

 在客户环境中，每次运行语句时，动态视图中的信息都需要更新，所以等于每次视图都会被重建。接下来我们需要去看一下对应的执行计划。

首先我们发现在执行计划中并不存在并行。并行需要额外的CPU开销来支持多核之间的同步。那么也就说明客户环境中没有这部分额外的开销。

那么之后，我们应该去关注有没有Scan count（参考文档：https://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/14/scan-count-meaning-in-set-statistics-io-output.aspx）较多的表，来定位问题是否由某张表所导致的。然而在这个案例中我们有多张表的scan count 都比较高。

另外执行计划中还有一个参数叫做 "Total Subtree cost", 这个是语句优化器在执行语句前对于执行计划中对应分支所需CPU,I/O, 内存所需成本的一个计算结果。我们可以通过这个参数来去判定哪一部分使用了较多的成本。（注意：对于实例之间成本的比较，使用这个参数并不准确）。由于篇幅的关系，这里对执行计划做了一个简化：

有趣的是我们发现在复杂的执行计划中包含了四个完全相同的 "nest loops" Inner Join. 他们四个的 "Total Subtree cost"之和约有 1490， 而整个语句的cost为1969。尽管这个参数仅为SQL Server 语句优化器的计算值，而并非实际执行情况的精确反应，但足以说明这四个nest loops是整个语句执行成本的主要构成。

因为四个部分的高度一致，我们很容易找到对应的语句是之前高亮的四条使用“case when exists"的短句。当使用 “case when exist" 这个短句时，执行计划必须使用半连接（left semi join)，而半连接又只能使用Nest loop。在客户环境中，nest loop join 是一个结果集对自身做join， 且结果集较大，所以使用nest loop join 开销非常大。然而对于这种固有的设计我们无法通过简单的加索引或加hint去调优语句。唯一的方式就是客户结合自己的具体业务需求重写语句而避免使用 “case when exits"的短句。

 这就是今天的分享，更多SQL Server案例学习请持续关注本博客的更新。