sql server 2005并发压力问题，请帮忙看看

Question

您好，我用一个数据库大约有11G，其中有约700张表（某些表做了分表处理），大部分表的记录不超过50万。日志用的是简单日志。

目前batch requests在1500~3500之间，一般在2500的样子。我抓过十分钟的数据分析，平均每秒约500更新(460)及插入(40)，200查询.

数据库服务器配置为2 X 4核至强，16G内存，4X300SAS硬盘做raid10.

运行的时候cpu并不高，不超过30%,内存从任务管理器看基本用完，15.9G。IO比较高，%read time平均178%，最大1529%。%write time平均3235%,最高6869%。

数据库应用是web，PHP+sql server 2005 sp3，运行在windows 2003 sp2 （X64）上。因为freetds一般连接sql server会经常发生连接不上的问题，所以后来改用的待久连接。持久连接的并发用户为1600。

不知道从上面的情况看是不是压力过大？应该怎么优化好？

另外现在还出现一个现象，基本上是一两天一次，会出来batch requests非常平稳的现象，波动很小。而这个时候数据库速度变慢，PHP进程跑满，然后就是网站连接不上。重启sql server问题解决。不知道这个问题原因大致在哪？

Answer 1

怡红公子也在？是不是以前dev-club上的那个怡红公子。我是以前linux版版主。呵呵。

Answer 2

dev-club现在萧条了啊。

看来是服务器压力比较大。把SQL的最大内存改到14G，以免页面交换。

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 14000;

GO

RECONFIGURE;

GO
看看select * from sys.dm_os_wait_stats order by wait_time_ms desc的结果是什么。如果是Pageiolatch_XX的话，说明磁盘性能跟不上。
查一下执行得比较频繁的语句，看logical_reads高不高，还有看看missing_index的统计，适当的建些索引。

---

想不想时已是想，不如不想都不想。

Answer 3

呵呵，果然是您呀。是呀，dev-club确实不行了。


select * from sys.dm_os_wait_stats order by wait_time_ms desc的结果：

WRITELOG    41067659    606117468    3718    3003093
LAZYWRITER_SLEEP    7337190    148572421    62984    330906
BROKER_TASK_STOP    14917    74582671    10000    0
SQLTRACE_BUFFER_FLUSH    18637    74544078    4031    62
LCK_M_X    160155    73825406    3531    61218
PAGEIOLATCH_EX    76450    22848265    3062    7078
PAGEIOLATCH_SH    205168    15539625    4109    4734
SLEEP_BPOOL_FLUSH    5128609    7067203    78    423359
LOGBUFFER    10459    2141203    1468    3468
SOS_SCHEDULER_YIELD    321107    1198140    218    1197609
PAGEIOLATCH_UP    4581    881843    2015    125

我们由于用了memcached等缓存，所以读还好，主要是写入太高，平均每秒约500更新(460)及插入(40)。missing_index也看了，基本上没什么大问题。

基本上我们也确认是磁盘IO跟不上，准备分库了。上面我提到的会出来batch requests非常平稳的现象，然后突然变慢。重启数据库才好，不知道是不是也和磁盘有关系？我主要是想确认如果我磁盘IO能跟上的话，sql server2005是否能够支持更大（比如两倍于现在的）的并发压力？

谢谢！！

Answer 4

You may need check blocking other than disk io. Better to put data files, log files and tempdb on separate disks.

Answer 5

writelog是主要等待因素，说明是写日志的问题。日志文件是不是放在单独的磁盘上？
观察一下disk writes/sec，如果和transaction/s差不多，说明事务并发不好。
看看索引的统计，没什么用的索引就删除。这个配置每秒2000inserts没什么问题。

在生产数据库中执行SELECT     TOP (100) QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, ius.database_id))

                      + N'.' + QUOTENAME(OBJECT_NAME(i.object_id, ius.database_id)) AS ObjectName, i.name AS IndexName, ius.user_seeks, ius.user_scans, ius.user_lookups,

                      ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update

FROM         sys.dm_db_index_usage_stats AS ius INNER JOIN

                      sys.indexes AS i ON ius.object_id = i.object_id AND ius.index_id = i.index_id

WHERE     (ius.database_id = DB_ID())

ORDER BY ius.user_seeks + ius.user_scans, ius.user_updates DESC

---

想不想时已是想，不如不想都不想。

Answer 6

另外，你这个主要是写入的话，考虑批量写入。

---

想不想时已是想，不如不想都不想。

Answer 7

数据库11G.。。。内存16G，干嘛要分配那么多内存给SQL SERVER呢？

分配8G RAM足够。。。留下的给WEB用。。。

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 8

disk writes/sec
disk transaction/s

这两个数字基本差不多，平均700的样子,每一分钟左右会跳到1800的水平保持十秒的样子。

disk writes byte/sec

平均1948525,也是每一分钟的样子会跳高一次，最高16709464。

日志文件不是放在单独的磁盘上，因为机器是租用的，目前并没有条件再放硬盘单独跑日志了。

我看了下，数据文件和日志文件的写入分别为25%和75%。为什么使用简单日志后，还有这么大的写入量呢？

多余的索引比较少，对整体性能没什么影响。都有试过。

因为主要是update,又是web应用，批量写入比较难处理。

我现在是一周做一次索引重建，重建后备份数据库，大小为9.5G，到第二天再备份，大小就变成了11.4G了。往后就基本是这么大了，增长比较小。

有个现象有点怪，我拿那些sql在服务器上跑的时候，速度都是非常快的。基本上很少有超过0.1秒的。在出现batch requests非常平稳的问题的时候也是这样。这种情况怎么会造成PHP进程跑满呢？而且重启数据库就好了。

Answer 9

blocking主要还是在disk io,详细的信息见下面的帖子。目前很难分到不同的硬盘上。

Answer 10

内存是不指望它再多出来用做其它的了，只要能保证我数据库正常，我就很高兴了。呵呵。

 只是我不知道对于sql server 2005,怎么评估一个数据库的内存使用呢？有类似mysql的内存使用的公式吗?

Answer 11

recoverymode=simple不表示不写日志，只是日志自动截断。disk writes/sec和transaction/s差不多，说明你的事务并发度低，没有串行化。不过，你是不是看的disk transfers/sec？
一分钟跳高一次的disk io是数据库的checkpoint。

dmv里面有内存的详细使用情况，评估的话，你可以看performance monitor里面的sqlserver buffer manager:cache hit ratio，大于99%就说明内存足够用。但是一定要留出足够的空闲内存，新开连接之类的内存都不在sql的缓存管理里面的。

如果sql和web在一起，一定要调小sql的最大内存，如果是单独的sql服务器，把最大内存调到14G就可以了。

---

想不想时已是想，不如不想都不想。

Answer 12

我看的是disk transfers/sec，事务并发低是因为磁盘IO造成的吗？没有串行化是什么意思？如何解决呢？

怎么确认数据库是不是在做checkpoint?sql server一般是多久做一次，可以配置的吗？

Answer 13

Disk i/o will affect number of concurrent transactions. Sql does checkpoint every minute by default, but you can run it any time manually.

Answer 14

checkpoint你可以不管它，事务并发低会造成写日志文件的次数增加，你的主要等待是writelog，不是pageiolatch。我说错了，不是没有串行化，是没有并行化。

不过，每秒500更新，怎么disk writes/sec有700呢。你检查一下transaction/s，另外，你内存配置改没有？怀疑有内存的页交换。看看pages/sec

---

想不想时已是想，不如不想都不想。

Answer 15

checkpoint你可以不管它，事务并发低会造成写日志文件的次数增加，你的主要等待是writelog，不是pageiolatch。我说错了，不是没有串行化，是没有并行化。

不过，每秒500更新，怎么disk writes/sec有700呢。你检查一下transaction/s，另外，你内存配置改没有？怀疑有内存的页交换。看看pages/sec

---

想不想时已是想，不如不想都不想。

是的，我的主要等待是writelog,不是pageiolatch。pages/sec是0，刚刚监控几分钟的数据看，disk writes/sec每秒560,sql server的transaction/s每秒490.disk writes/sec确实要比transaction/s大，这块我估计是windows系统本身及sql server好像内部也有跑一些进程造成的吧。

Answer 16

Where did you put database log files? What kind of raid? Should be on their own raid10 disk.

Answer 17

Where did you put database log files? What kind of raid? Should be on their own raid10 disk.

datatbase log方向和data file放在同一个4X 300GSAS做的raid10上。目前给他一个单独的raid跑有困难。因为是机器自带的raid.盘位有限，只有两个空的硬盘盘位了。后面考虑再买两个硬盘做个raid1或raid0去跑日志，不知是否可行？

Answer 18

Should add them asap to solve your disk bottleneck.