Symptons
在Solution的RC测试环境中,有两个站点,都有500W的Contact,但是其中站点10000357在打开联系人管理页面时一直都Timeout,站点10000358打开同个页面却很快。
Solution的生产环境中,站点10000001中的某个Email(发送量在4w)的送达详情报表和按域名统计报表都无法打开,Timeout。但是这个站点中,其他的发送量/退回量更大的Email的报表打开却很快。
第二天,同样是Solution的生产环境中,站点10001223的送达详情和按域名统计报表基本都打不开(发送量基本都在10w左右),都会timeout超时,但是同样的数据量,在站点10001224打开,就没有任何问题。
Troubleshooting
刚开始出现问题的是RC中,站点10000357出现打不开联系人管理页面的问题。但是这之前一直都是用的好好的。据测试人员Shelay说,是之前报了Timeout,然后出现一次黄页之后就出现了这个问题。
于是怀疑是不是数据库中该站点User表被锁住了,决定直接重启数据库。重启之后发现问题依然存在。
后来,又去造了另外一个站点10000358,也把数据量变成500w级别,但是这个站点打开页面非常顺利。
后来用SQL Server Profiler找到打开联系人管理页面所要执行的脚本如下:
select * from ( select ROW_NUMBER() over(order by Id desc) as Ridx,* from ( select * from t_User10000357 a where ([IfDeleted]=0 and [IfInRecycleBin]=0 and UserType = 3) ) returnV ) returnV where Ridx between 1 and 30
将这个脚本直接在数据库中执行,十几分钟过去还是无法得到结果。
但是如果将站点号换成10000358,执行却非常快。
于是使用SQL Server的Execution Plan查看一下查询的执行计划,分别如下(都为Estimated Execution Plan)
这个为站点10000357的执行计划。
发现使用了整表扫描,这当然会极大的拖慢查询速度。可是为什么会用整表扫描呢,该表上在ID列上有主键的。
这个为站点10000358的执行计划。这里使用了索引扫描和RID查找,所以速度会比较快。索引扫描使用的是User表中的ID主键。
所以,我们强制使用主键(下面语句中高亮处)进行查询尝试了一下。
select * from ( select ROW_NUMBER() over(order by Id desc) as Ridx,* from ( select id from t_User10000357 a with (index=PK_t_User10000357) where (UserType = 3 and [IfDeleted]=0 and [IfInRecycleBin]=0 ) ) returnV ) returnV where Ridx between 1 and 30
如果查询的是User表的所有字段,即Select *的情况下,还是很慢,若是Select Id,则可以出来,时间在24s左右。依然非常慢。
后来,未果。
同一天,生产环境站点10000001中的某个Email(发送量在4w)的送达详情报表和按域名统计报表都无法打开,Timeout。但是这个站点中,其他的发送量/退回量更大的Email的报表打开却很快。
这一天,还没有将这两个问题联系在一起。遇到这个按域名统计超时的问题,我们没有去深究问题的原因,仅仅是将数据库sqlcommand的超时时间设置为180s,虽然我知道真正的原因肯定不是因为这个超时时间的设置。
第二天,同样是Solution的生产环境中,站点10001223的送达详情和按域名统计报表基本都打不开(发送量基本都在10w左右),都会timeout超时,但是在数据量更大的站点,在站点10001224打开,就没有任何问题。
如何计算送达详情,开发人员用程序跑出来的脚本为
select count(*) FROM t_Newsletter_SentDetail10001223 AS s WHERE s.UserOrContactId not IN ( SELECT UserOrContactId FROM t_Newsletter_BouncedDetail bd WHERE SiteId=10001223 AND EmailId=2612 AND Type=0 ) AND s.EmailId=2612 AND s.Type=0
以上脚本,单独查询SentDetail和BouncedDetail都很快,用了not in之后就很慢。这个是正常的,基本上的时间应该是花在not in的计算中。
用SQL Server的Execution Plan查看一下查询两个站点的执行计划,分别如下
这个是站点10001224的执行计划,该站点查询很快。
从执行计划中可以看出只进行了对于BounceDetail的一次Index seek和RID Seek,另外有一次为针对SentDetail表的Table Scan。
这个是站点10001223的执行计划,该站点查询很慢。
从执行计划中可以看出进行了两次Index seek和RID Seek,一次对于BounceDetail,另外一次针对SentDetail表。
由此我们推断,应该不是脚本的问题,因为同样的脚本,在数据量大的站点反而运行起来比较快。鉴于两个站点所使用的执行计划不一样,我们推断问题应该出自数据库本身的数据上。
刚开始,我们怀疑是不是索引的问题,索引碎片化是不是太严重了,导致了碎片化大的站点查询变得特别慢。
使用以下DBCC命令查询索引情况,
dbcc showcontig('t_User10000357') dbcc showcontig('t_User10000358')
执行结果如下:
DBCC SHOWCONTIG scanning 't_User10000357' table...
Table: 't_User10000357' (1680061071); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 233143
- Extents Scanned..............................: 29151
- Extent Switches..............................: 29150
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [29143:29151]
- Extent Scan Fragmentation ...................: 49.23%
- Avg. Bytes Free per Page.....................: 309.7
- Avg. Page Density (full).....................: 96.17%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
或者你可以在SSMS中找到对应的索引,右键Properties,查看索引的具体信息,在Fragmentation中可以看到碎片的具体信息。
也可以使用如下的系统DMV查询索引碎片情况
select a.index_id,b.name,a.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('Comm100.Site20001'),object_id('t_User10000357'),null,null,null) a join sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id select a.index_id,b.name,a.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('Comm100.Site20001'),object_id('t_User10000358'),null,null,null) a join sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
但是结果发现,两个站点的索引碎片情况差不多。甚至运行比较快的站点碎片化会更严重一点。
但我们还是将碎片整理了一下,Rebuild了一下。
可以使用SSMS的图形界面方式,找到对应的索引,右键选择Rebuild。
或者使用如下SQL语句
alter index IX_t_User10000358_UserType_Email on t_User10000358 rebuild
对于何时Rebuild或者Reorganize索引,请参见 Reorganizing and Rebuilding Indexes 重建索引
<http://msdn.microsoft.com/en-us/library/ms189858(SQL.105).aspx>
Rebuild完了之后,进行查询,慢的站点还是慢,快的还是快。没有任何改变。
后来继续查询资料,终于找到一篇文章说 用于对运行慢的查询进行分析的清单,摘录如下:
查询和更新运行慢的常见原因有多种:
· 网络通讯速度慢。
· 服务器的内存不足,或者没有足够的内存供 SQL Server 使用。
· 缺少有用的统计信息。
· 缺少有用的索引。
· 缺少有用的索引视图。
· 缺少有用的数据条带化。
· 缺少有用的分区。
是否已经用有用的统计信息优化查询?
查询优化器使用统计信息来创建可提高查询性能的查询计划。对于大多数查询,查询优化器已为高质量查询计划生成必要的统计信息,在少数情况下,您需要创建附加的统计信息或修改查询设计以得到最佳结果。由于我们看到的几个站点,或者几条记录,数据量都差不多,查询语句也都一样,但是生产的执行计划却都不一样,我觉得问题应该在这里了。于是找到如何更新统计信息,
在某些情况下,启用 AUTO_UPDATE_STATISTICS 时,可以通过更频繁更新统计信息来优化查询计划,提高查询性能。可以使用UPDATE STATISTICS 语句或存储过程 sp_updatestats 来更新统计信息。
对于出现问题的相关的两个表,我都执行了如下语句,执行完之后,原先慢的表的查询也变得很快了。问题终于解决了。
update statistics t_Newsletter_BouncedDetail update statistics t_Newsletter_SentDetail10000357
关于update statistics的更多信息,请查看UPDATE STATISTICS
<http://msdn.microsoft.com/zh-cn/library/ms187348(v=sql.105).aspx>
Cause
出现问题的原因,既不是由于数据库表/记录等的锁,也不是索引的碎片问题,而是一个简单的统计信息。
几个站点,不知道什么原因,导致了各个表/索引的统计信息不同,然后造成了同一个语句的查询的执行计划也不同。
有个站点就可能得到了性能很差的执行计划,然后查询就超慢。
Resolution
解决方法很简单:
对出现问题的几个站点所设计到的表就执行一下手动更新统计信息,提高查询性能。
但是对于为什么会出现这样的统计信息延后的情况,还不清楚。另外,也没有去更新那些潜在的/未发现的已经出现问题的站点。