时不待我 天道酬勤

近期公文反映会比较慢，和珊姐做了一下监控，返回的监视结果如下。

数据库服务器的CPU消耗时间如下图(2011-08-10 至 2011-08-18号)：

从数据库服务器的CPU消耗时间来看，08-10至08-18号的CPU时间占用始终没有超过20%，服务器的性能很好。但是这段时间频繁的产生性能问题，很诡异。

继续分析珊姐提供的SQL SERVER PROFILE文件。

从Sql Server Profile的监视结果来看，CPU耗时不高，逻辑读也很低，就是总时间消耗得很厉害。这些名词的作用解释如下：

CPU时间是等待锁等资源释放、解析、编译查询所需的处理器时间。
Duration(占用时间)可以说是除了CPU时间之外，还有存储器中的数据存取,磁盘IO,总线上消耗的时间总和。

这里面就可以解释了我们的数据库服务器的CPU时间为什么不高，而总是有性能问题了，因为我们的消耗全在数据的存取和磁盘的IO之上。

找到了影响性能的关键语句，如下面的截图：

让珊姐执行了这条语句，每一次都会返回21万条记录。这就是数据的存储和IO的耗时所在了。

继续看看哪些功能会执行上面的SQL，从项目中分析得到以下方法会执行这条语句。

继续看看被谁引用了

这个代码片断是用于做 节点总量控制 和 签批路径树 的。这两个功能是我们的核心功能，会被频繁的调用。而调用时每次都会查询数据库并返回20多万条数据。

后面我们将优化这个功能，并且清理tbWF_NodeInstance表的冗余数据。相信近期困扰我们的性能问题会被解决。

原来的语句执行时间

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SET STATISTICS TIME ON

SELECT cnvcLeadEname

    FROM tbApproval WHERE cndSpeedTime < cndReplyTime and cnvcReturnFlag = '0' AND cniCommonPartId in

       ( SELECT cniid FROM tbCommonPart WHERE   cnvcDeleteFlag='0' AND cnvcEndFlag<>'0' and

           cnvcfiletype Not In ( 0170,0115,0166,0160,0120,0121,0122,0123,0125,0126,0127, 0881,0883,0884,0885,0886,0887,0887,0888,0889,0893,0891)

              AND datediff(d,cndEndTime,'2010-01-01' )<=0 AND datediff(d,cndEndTime,'2010-11-08' )>=0 )

              AND(cnvcLeadEname != '999999999' AND cnvcLeadEname!='999999998')

              AND(cnvcLeadOrganId in (SELECT cniNodeId FROM DBHNAOA3.dbo.tbCompany WHERE (cnvcOrganID LIKE '0-1-%') and cnvcflag=1 ) )                                     

                  group by cnvcLeadEname having count(cnvcLeadEname)>=4

SET STATISTICS TIME OFF

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

(871 行受影响)

SQL Server 执行时间:

   CPU 时间= 9531 毫秒，占用时间= 65547 毫秒。

优化之后的语句：

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT cniid INTO #tempCommonPart FROM tbCommonPart WHERE  

    cnvcDeleteFlag='0' AND cnvcEndFlag <> '0' AND cnvcfiletype NOT IN ( 0170,0115,0166,0160,0120,0121,0122,0123,

       0125,0126,0127, 0881,0883,0884,0885,0886,0887,0887,0888,0889,0893,0891)

              AND cndEndTime BETWEEN '2010-01-01 00:00:00' AND '2010-11-08 23:59:59';

CREATE INDEX IX_Temp_vcTempTable3_CommonPartId ON #tempCommonPart(cniId);

SET STATISTICS TIME ON

SELECT A.cnvcLeadEname FROM #tempCommonPart C JOIN tbApproval A ON A.cniCommonPartId = C.cniId JOIN DBHNAOA3.dbo.tbCompany Company ON A.cnvcLeadOrganId = Company.cniNodeId

    WHERE cndSpeedTime < cndReplyTime and cnvcReturnFlag = '0' AND (A.cnvcLeadEname != '999999999' AND A.cnvcLeadEname != '999999998')

        AND Company.cnvcOrganID LIKE '0-1-%' AND Company.cnvcflag = 1 GROUP BY A.cnvcLeadEname HAVING COUNT(A.cnvcLeadEname) >= 4;

SET STATISTICS TIME OFF    

DROP TABLE #tempCommonPart;

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。

(394871 行受影响)

(871 行受影响)

SQL Server 执行时间:

   CPU 时间= 1516 毫秒，占用时间= 4901 毫秒。

有一个项目的选型，采用了微软提供的ORM框架Entity FrameWork 4.1。经过这些天和同事们的验证和搭建，昨天出来了一个初步的版本。针对该版本我们做了一次并发测试，测试的策略是这样的：

1：LoadRunner模拟一个用户打开页面，该页面中执行一个联表查询，由Entity FrameWork提供出来的数据库操作方法来执行，该方法查询出对象集后，Entity FrameWork将对该对象集跟踪，可以做延迟加载的操作。查询的SQL语句如下：

public List<DemoUserBM> GetUserList()

{

string strSql = "SELECT TOP 100 u.cniId as Id,u.cnvcUserName as UserName,u.cnvcPassWork as PassWork,getDate() as CreateTime,0 as Version FROM tbDemo_User u JOIN tbDemo_Address a ON u.cniID = a.tbDemo_UserId  ORDER BY u.cniID DESC";

return this.SqlQuery(strSql);

}

该查询中采用了ORDER BY u.cniID DESC语句，尽量减少SQL SERVER查询的缓存命中率，提高并发测试的准确率。

2：LoadRunner接着做一个Insert操作，该操作是一个联表的插入，同时将user和Address的数据插入表中。由Entity FrameWork维护事务。

Random ro = new Random(99999999);

DemoUserBLL bll = DemoUserBLL.Instance;

//测试保存数据

DemoUserBM user = new DemoUserBM();

user.UserName = "jadesun";

user.PassWork = "123456";

user.CreateTime = DateTime.Now;

user.AddressList.Add(new DemoAddressBM() { Phone = ro.Next().ToString(), Address = "测试地址" });

bll.Save(user);

3，LoadRunner 继续做一个 Update 操作，该操作也是一个联表的更新。更新了User表，并再新增 Address 的一条记录。同样由 Entity FrameWork维护事务。

//测试更新事务数据

user.UserName = Guid.NewGuid().ToString();

user.AddressList.Add(new DemoAddressBM() { ColumnUser_Id = user.Id, Phone = ro.Next().ToString(), Address = "测试地址2" });

bll.Update(user);

bll.Dispose();

并发测试的策略就是一个用户做了查询、新增、更改的操作，由200个用户并发交替的执行。和陟昕讨论了一下，这样的测试对框架的验证可以提供参考性。

LoadRunner使用200个并发进行压力测试一个半小时的截图。

测试的数据量

LoadRunner在15分钟前，反映了平均响应时间有6秒左右，每秒执行的事务只有50多个。我检查了数据库，原来没有给tbDemo_Address表的tbDemo_UserId字段加入索引。添加索引之后，Loadrunner的平均响应大幅度下降（在一秒左右），执行事务的个数增加（300个左右）。

在并发测试至一个小时以后，数据的量达到百万级，tbDemo_User表有100多万，tbDemo_Address有200多万。这时侯的平均的响应时间增加（2秒左右），执行的事务个数下降（120个左右）。

针对大型解决方案的架构设计有了一些进展，继续来做一下总结。

根据项目组中同事提供北京格瑞趋势的资料，我和他们进行了联系，根据他们提供的解决方案，发现能完全满足我们的业务要求。这个产品的简介如下：

1、Moebius采用无共享磁盘架构的设计，结构灵活简单，方便用户将多个中小型服务器组成集群替代大型服务器，实现更高的综合性能；采用和SQL Server高度集成的设计方式，将Moebius 集群的管理工具集成到SQL Server Management Studio中，方便用户操作，轻松部署、维护、管理集群。

2、传统的数据库集群都是保证业务持续可用的，有一个主节点，一个备用节点，如MSCS或者第三方的HA集群，这类集群的共同特点是始终只有一个节点在运行，在性能上得不到提升，系统也就不具备扩展的能力，当现有的机器不能满足应用的负载时只能更换更高配置的机器。这样的系统既不利于扩展，同时硬件资源浪费严重。

在Moebius for SQL Server 数据库负载均衡集群中，打破了以往主节点和备用节点的概念，集群中的每个节点都具有同等地位，Moebius可以在多个节点之间实现动态均衡连接请求，实现各节点压力的均衡，进而显著提升数据库系统的性能。

3、Moebius集群为用户提供了多种选择模式，可以根据对可用性要求程度的不同，采取合适的设置，采用多种故障监控机制实时监测系统的软硬件健康状况，在Moebius for SQL Server负载均衡集群中若某节点发生故障，故障节点的虚拟IP会立即飘移到其余健康的节点来响应连接请求，保证业务不中断，同时可以在不影响业务的情况下完成故障节点的修复、重新上线。

4、Moebius for SQL Server 负载均衡集群，在需要更高数据库处理速度时，只要简单地增加数据库服务器就可以了。这样的扩展方式称之为向外扩展，可以大大降低硬件投资的风险，而且大大提高现有服务的质量。

5、Moebius for SQL Server负载均衡集群采用无共享磁盘架构，这样各个机器可以不连接一个共享的设备，数据可以存储在每个机器自己的存储介质中。集群中各节点在任何时刻具有实时一致的数据，实现了真正的数据冗余，这样冗余的硬件架构不但可以避免单点故障而且提供了杰出的故障恢复能力；不会因为系统故障导致数据的丢失，大大提高了整个系统的可靠性与安全性。

6、Moebius数据智能同步组件在相互同步数据的同时还要解析引起数据变化的SQL语句的类型及其特点，经智能分析后，以最经济的方式完成与其他节点的数据同步。中间件驻留在每个机器的数据库中，监测数据库内数据的变化，并将变化的数据同步到其他数据库中。数据同步完成后客户端才会得到响应，同步过程是并发完成的，所以同步到多个数据库和同步到一个数据库的时间基本相等；另外同步的过程是在事务的环境下完成的，保证了多份数据在任何时刻数据的一致性。 正因为中间件宿主在数据库中的创新，让中间件不但能知道数据的变化，而且知道引起数据变化的SQL语句类型，根据SQL语句的类型智能地采取不同的数据同步策略以保证数据同步成本最小化。

7、LB Director部署在应用程序服务器上（也可单独部署在单独的一台服务器上），LB Director建立在现有网络结构之上，它提供了一种廉价透明的方法，通过将大量的并发访问分担到多个数据库服务器上分别处理，保证集群中的多个数据库服务器均匀地承担来自外围的访问压力，有效地增加数据库的吞吐量、加强数据处理能力、减少用户等待响应的时间，提高了集群的灵活性。

 ------------

如果有类似需求的朋友可以和他们先联系，可以快速的解决问题。

北京格瑞趋势公司的网址是http://www.grqsh.com/index.htm

周四在新大厦办公，正好遇上老董反映公文查询失败的问题。和小红监视了该查询语句在高峰期间，查询耗时需要40多秒，所以有必要整一整它了。

公文查询功能由于数据的原因，我不能在本地调试，全得由珊姐执行测试的思路。珊姐在执行老董的相关查询之后，返回的系统信息如下：

(28 行受影响)

表'tbCommonPart'。扫描计数1，逻辑读取1690869 次，物理读取0 次，预读1 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数14，逻辑读取10570670 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tmp40072411'。扫描计数14，逻辑读取6773 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

SQL Server 执行时间:

   CPU 时间= 17406 毫秒，占用时间= 8751 毫秒。

娘的，tbCommonPart的逻辑读169万次，WorkTable表有1000多万次的逻辑读，执行时间累计26秒左右。

从存储过程中分析得到老董执行的存储过程核心语句有下面的四条。

1，SELECT distinct b.cnvcDeptid, cast(b.cnvcDeptid as varchar(300)) AS cnvcOrganID INTO tmp74790211 FROM tbUserRole a, tbRole b  WHERE a.cnvcDisable = '1' AND b.cniRoleId = a.cniRoleId AND b.cnvcDisable = '1'  AND cncJiYaoFlag !='0' AND a.cnvcUserId = '1000032237';

2，SELECT a.cnvcDeptid,b.cnvcOrganID INTO  tmp67731953 FROM tmp74790211 a, tbCompany b WHERE a.cnvcDeptid = b.cniNodeID;

3，SELECT distinct a.cniId as cniCommonPartId INTO tmp93053275 FROM DBHNAOA3_2011.dbo.tbCommonPart  a ,tmp74790211 b WHERE a.cnvcSendOrganId = b.cnvcDeptid;

4，SELECT distinct top 100 a.cniId, a.cniId as cniCommonPartId, a.cnvcFileType, a.cnvcFileName, a.cnvcTitle, a.cnvcSecName, a.cnvcSecretaryKind,a.cndSendTime, a.cnvcSendEname, a.cnvcSendCname, a.cndEndTime, a.cnvcSendOrganId, a.cnvcEndFlag  FROM DBHNAOA3_2011.dbo.tbCommonPart a, tmp93053275 b  WHERE a.cnvcEndFlag != '0' AND a.cniId = b.cniCommonPartId AND a.cnvcTitle like '%马国华%'  AND a.cndEndTime >= '2011-01-01' AND a.cndEndTime < '2011-07-15'  AND a.cnvcSecretaryKind not in ('0202','0203','0204')  ORDER BY a.cndEndTime DESC

在执行第四条语句时出现了Worktable表，说明访问了大容量的临时表，造成了SQL优化器生成一个worktable来缓存中间查询结果，因为临时表是默认没有索引的。在执行a.cniId = b.cniCommonPartId就会产生 tmp93053275 表的全表扫描。

tmp93053275表的数据量从系统信息可以看出(560469 行受影响) ,该表有56万行数据参于全表扫描。根据笛卡儿乘积，WorkTable表就产生了1000多万次逻辑读。

这个性能优化的方法也很简单，就是给tmp93053275表的cniCommonPartId字段创建索引项。修改sp_QueryDoc这个存储过程，加入下面的语句。

set @tempSql = 'CREATE INDEX IX_Temp_vcTempTable3_CommonPartId ON '+@vcTmpTable3+'(cniCommonPartId)';

exec(@tempSql);

重新让珊姐执行老董的查询语句，反馈的信息如下：

(28 行受影响)

表'tmp95890048'。扫描计数28，逻辑读取114 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'tbCommonPart'。扫描计数1，逻辑读取1690742 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

SQL Server 执行时间:

   CPU 时间= 2828 毫秒，占用时间= 2833 毫秒。

总结：存储过程经过优化之后，WorkTable表和1000多万次的逻辑读没有了，查询时间也从25秒降到了4-5秒左右。但是tbCommonPart表还有169万次的逻辑读，因为珊姐和我的环境里面观察到的执行计划不同。这个要留到后面有时间时再去优化了。