@DBA_Huangzj 谢谢版主~不胜感激~ 那我先把“自动收缩”关闭,再观察看看。另再请教一下,下面这日志2014/5/7 18:16:07 last target outstanding: 2, avgWriteLatency 1 2014/5/7 18:16:07 average throughput: 0.01 MB/sec, I/O saturation: 1630, context switches 5365 2014/5/7 18:16:07 FlushCache: cleaned up 137 bufs with 129 writes in 125428 ms (avoided 584 new dirty bufs) for db 5:0是脏页回写磁盘吗?自动收缩会引发脏页回写磁盘吗?谢谢~~
@DBA_Huangzj 老大,问题依然存在,网页很卡。看来还是脏页回写的问题。自动收缩关闭了,上午还是出现这样的日志:2014/5/9 10:55:52 spid7s SQL Server has encountered 659 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS.mdf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FB4. The offset of the latest long I/O is: 0x000003418440002014/5/9 10:52:00 spid18s last target outstanding: 6400, avgWriteLatency 16732014/5/9 10:52:00 spid18s average throughput: 0.07 MB/sec, I/O saturation: 12387, context switches 302742014/5/9 10:52:00 spid18s FlushCache: cleaned up 4352 bufs with 2797 writes in 503337 ms (avoided 650 new dirty bufs) for db 5:02014/5/9 10:50:34 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS_log.ldf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FD0. The offset of the latest long I/O is: 0x00000017f214002014/5/9 10:48:43 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS.mdf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FB4. The offset of the latest long I/O is: 0x0000043d2f40002014/5/9 10:48:12 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf] in database [tempdb] (2). The OS file handle is 0x0000000000001080. The offset of the latest long I/O is: 0x00000000e1f000 这时打开网页很卡,SQL Server IDE 也很繁忙,如果急于操作会出现假死(未响应),网页没流量,说明SQL Server 很繁忙。 我的猜想:出现卡的时候,主要是SQL数据库内存脏页写回磁盘出现I/O 延迟问题,页面请求得不到及时响应,造成超时。或许是写回容量大,或许是磁盘性能瓶颈。要如果解决呢?执行更频繁的检查点?以减少对 I/O 飙升,或增加 I/O 子系统的容量? 注:数据库没用分区表、凌晨自动完整备份和重建索引。
2014/5/9 10:50:34 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS_log.ldf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FD0. The offset of the latest long I/O is: 0x00000017f214002014/5/9 10:48:43 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS.mdf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FB4. The offset of the latest long I/O is: 0x0000043d2f40002014/5/9 10:48:12 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf] in database [tempdb] (2). The OS file handle is 0x0000000000001080. The offset of the latest long I/O is: 0x00000000e1f000百来个用户同时在下单应该不会对数据库造成威胁,手工作业再快,也就100来人临时库,数据文件,日志文件都存在请求超时的情况临时库和数据文件超时,应该跟查询有关,造成大量的IO日志文件的请求超时,可能数据库复制跟日志有关,或者你先停掉复制,看看日志文件超时会不会再出现我觉得这个得一个一个解决
嗯,已改为完整模式了,再观察看看。 其实我也考虑过改完整模式,因为生产环境数据库很大,怕更改会出现什么问题,再者就是对恢复模式了解不是很透彻。平时注重代码,没专业的DBA,对数据库管理掌握得不是很深~另外复制会不会造成性能降低或产生以上问题呢?这个数据库做了事务复制,充当发布和分发服务器,同步复制到局域网另一台服务器上。 并发量应该也不算大,百来个用户同时在下单,查询大表(就是那两三个千万级别的表)情况大点,但都有日期范围,难道是索引设计不合理? 这些问题不能一概而论,太多方面需要考虑,简单来说,只能通过监控才能回答,《SQL Server 2012实施与管理实战指南》值得一看,也是我其中一本案头书日常管理的监控主要有哪几种途径呢?SQL Server Profiler?
谢谢版主~不胜感激~
那我先把“自动收缩”关闭,再观察看看。另再请教一下,下面这日志2014/5/7 18:16:07 last target outstanding: 2, avgWriteLatency 1
2014/5/7 18:16:07 average throughput: 0.01 MB/sec, I/O saturation: 1630, context switches 5365
2014/5/7 18:16:07 FlushCache: cleaned up 137 bufs with 129 writes in 125428 ms (avoided 584 new dirty bufs) for db 5:0是脏页回写磁盘吗?自动收缩会引发脏页回写磁盘吗?谢谢~~
老大,问题依然存在,网页很卡。看来还是脏页回写的问题。自动收缩关闭了,上午还是出现这样的日志:2014/5/9 10:55:52 spid7s SQL Server has encountered 659 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS.mdf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FB4. The offset of the latest long I/O is: 0x000003418440002014/5/9 10:52:00 spid18s last target outstanding: 6400, avgWriteLatency 16732014/5/9 10:52:00 spid18s average throughput: 0.07 MB/sec, I/O saturation: 12387, context switches 302742014/5/9 10:52:00 spid18s FlushCache: cleaned up 4352 bufs with 2797 writes in 503337 ms (avoided 650 new dirty bufs) for db 5:02014/5/9 10:50:34 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS_log.ldf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FD0. The offset of the latest long I/O is: 0x00000017f214002014/5/9 10:48:43 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\data\QQMIS.mdf] in database [QQMIS] (5). The OS file handle is 0x0000000000000FB4. The offset of the latest long I/O is: 0x0000043d2f40002014/5/9 10:48:12 spid7s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf] in database [tempdb] (2). The OS file handle is 0x0000000000001080. The offset of the latest long I/O is: 0x00000000e1f000
这时打开网页很卡,SQL Server IDE 也很繁忙,如果急于操作会出现假死(未响应),网页没流量,说明SQL Server 很繁忙。
我的猜想:出现卡的时候,主要是SQL数据库内存脏页写回磁盘出现I/O 延迟问题,页面请求得不到及时响应,造成超时。或许是写回容量大,或许是磁盘性能瓶颈。要如果解决呢?执行更频繁的检查点?以减少对 I/O 飙升,或增加 I/O 子系统的容量?
注:数据库没用分区表、凌晨自动完整备份和重建索引。
其实我也考虑过改完整模式,因为生产环境数据库很大,怕更改会出现什么问题,再者就是对恢复模式了解不是很透彻。平时注重代码,没专业的DBA,对数据库管理掌握得不是很深~另外复制会不会造成性能降低或产生以上问题呢?这个数据库做了事务复制,充当发布和分发服务器,同步复制到局域网另一台服务器上。
并发量应该也不算大,百来个用户同时在下单,查询大表(就是那两三个千万级别的表)情况大点,但都有日期范围,难道是索引设计不合理?
嗯,谢谢x_wy46
我也觉得奇怪,怎么临时库也会超时。
数据库已改为完整模式了,再观察看。如果还会出现就把复制先停止了。
其实我也考虑过改完整模式,因为生产环境数据库很大,怕更改会出现什么问题,再者就是对恢复模式了解不是很透彻。平时注重代码,没专业的DBA,对数据库管理掌握得不是很深~另外复制会不会造成性能降低或产生以上问题呢?这个数据库做了事务复制,充当发布和分发服务器,同步复制到局域网另一台服务器上。
并发量应该也不算大,百来个用户同时在下单,查询大表(就是那两三个千万级别的表)情况大点,但都有日期范围,难道是索引设计不合理?
这些问题不能一概而论,太多方面需要考虑,简单来说,只能通过监控才能回答,《SQL Server 2012实施与管理实战指南》值得一看,也是我其中一本案头书日常管理的监控主要有哪几种途径呢?SQL Server Profiler?