SQL Server的一个问题，tuning方面的

北风

足迹卧虎藏龙，请帮我解惑吧
谢谢了

背景简介
每周日会有一批XML文件被一个.net的程序Load进入SQL server 2008 R2的数据库
这个.net程序是一个row一个row的进行select,检测，计算，和update的，每一个row的时间花费很少。每一个row都有一次select，和0-2次的update
这个总过程太长，达到25小时以上，想看看有没有改进的机会
用profile trace几分钟，没有需要tuning的，只是有sp_reset_connection在每个complete

两个疑点
1. 过程其中前一个小时的batch requests/sec可以达到3000/sec，但是之后长时间只有100-200/s，即使重新开始也之后这么多
   我在另一个几乎同样的测试环境（一样的CPU, RAM, 用一个SAN），做的测试时也可以达到3000/sec，没有测试到一小时很多
2. 在过程中查看resource waits，最多的是network I/O 大概是300ms/s，请问这个值是不是太高了？

大家请先看着，提问
我会尽量补充细节
谢谢了

teddymu

能不能batch-load 到stage table 中，然后set操作

jerryclark

Rebuild Index?

dalaohu

把xml先load进memory 内处理，不要直接进sql。

memory内处理时，把数据分割成若干segments， fire多线程处理每个segment
每个segment处理时在fire多线程，每个线程里用parallel  处理。

左后每个线程处理完后，结果再合并多主线程。 左后全部合并好之后如果需要的话再batch insert到sql。
内存处理是要很注意variable的内存占用，最有效的利用内存。

主机core 越多也好，内存越大越好，有条件的话设立多个处理机，每个segment分配多单个机子里执行。
做的好的话，最终的处理时间可以用分钟来计算。

以前我一个银行海量数据处理就是这么干的。

marklan001

为什么要用.net做update?计算很复杂？

kanjunhai

does it have transaction for each update of each row, if it does, try to remove the transaction.

seth2000

完全同意多线程这个思路，

此外可以尝试用store procedure 取代.net 的select and then update 操作，这样在数据库上也能利用多线程的方法，
而且在做select 操作时可尝试用query Hints， 在update时尝试用table Hints可能有意外的收获。

Fernando

1.先保证每一个select, update都用到正确的索引，效率最高。
2.看看index是不是需要rebuild，有些batch操作可能要先drop index，然后再建。
3. dalaohu说的并行处理最好，大点的batch最好都并行处理。还可以把大的表改成分区表，让并行处理效率更高。

典

一行一行弄是很慢，效率最差

把XML直接传给stored procedure，用sp来批量处理，估计快几十倍几百倍都有可能。

无视

If it's admin's job, powershell && scheduled job is one of solutions u should consider....

http://www.sqlservercentral.com/articles/powershell/65196/

北风

谢谢讨论,很有帮助

不过程序现在已经是多线程的了
在每次之前我都先drop掉了所有不相关的index,只留下相关的index,之后再放回去的
现在没办法XML直接或者间接load,然后再处理, 因为在SQL里重做那些逻辑需要很长时间

对我说的这两个疑点,大家有没有什么看法?
两个疑点
1. 过程其中前一个小时的batch requests/sec可以达到3000/sec，但是之后长时间只有100-200/s，即使重新开始也之后这么多
   我在另一个几乎同样的测试环境（一样的CPU, RAM, 用一个SAN），做的测试时也可以达到3000/sec，没有测试到一小时很多
2. 在过程中查看resource waits，最多的是network I/O 大概是300ms/s，请问这个值是不是太高了？

典

会不会是.net程序有问题，如果这个.net程序越来越慢，数据库再快也没用.
程序没有insert只有update?

宝鸡苹果

原帖由 典 于 2012-4-30 09:57 发表
会不会是.net程序有问题，如果这个.net程序越来越慢，数据库再快也没用.
程序没有insert只有update?

我同意这个说法。
监视一下这个程序的资源使用情况吗， 每隔2两小时看看

Fernando

network IO 是不是 跑.net程序的client和DB server之间的通信？
看来还得把这个.net程序好好分析一下

交易人生

.net 程序一行一行的query/update本身就是个问题，.你的程序和sql server 应当不在一台机器上，本身网络的resource 就有问题（如果数据量特大的话），net 处理xml的功能强大，可以

1）做个clr stored procedure （用c#），把xml直接传到sql server 上来用clr sp处理xml；即使用clr sp，也要batch processing而不是row by row。
2) 有时候delete row后再bulk insert比update 更快

如果不想做clr sp，update也不要row by row update，算法可能有问题，为什么不一批一批拿到.net的程序处理。

越来越慢也正常，因为你们可能建了index，随着数据的增多，还是row by row, rebuild index也是sql server 的工作。除了特殊情况，别在xml上建立Index。 index建的不好，能够起的适得其反的效果。

[ 本帖最后由 交易人生 于 2012-4-30 13:27 编辑 ]

北风

谢谢人生的讨论
这个.net程序,我只是和作者粗略的review过code一次,没发现明显的问题
我自己也是建议不用这个.net程序而是用SQL处理XML, 但是短期内没法改变. 这确实是很大的问题, 而且根本的问题. 但是这里我们不得不暂时放一放.

周日是要好好看一下resource monitor

原帖由 交易人生 于 30/4/2012 13:25 发表
.net 程序一行一行的query/update本身就是个问题，.你的程序和sql server 应当不在一台机器上，本身网络的resource 就有问题（如果数据量特大的话），net 处理xml的功能强大，可以

1）做个clr stored procedure （用c#），把xml直接传到 ...

交易人生

把那部分.net的code，有可能可以直接转换成clr stored procedure，基本不用改变算法，也解决问题。sql server engine本身对sp有优化功能，但我不知道对clr sp有没有，但一旦你通过network，select/update，sql server也没辙。

还有一个就是最简单的，把xml 上的index (如果有的话）拿掉，建立index虽然是后台运行，但也占用资源。

我曾经干过一个项目，也是xml存储，当时update 很慢，索性delete /bulk insert更快，具体问题具体分析了，当时是update数据需要几分钟，后来搞成了10几秒。bulk insert和update牵扯到index和sql server 的存储，sql server把数据存储分割成一小块一小块，可能叫cluster（忘了名字了），index实际上是这些cluster的index， update会是分散寻址存储，并且重建index(需要的话），而bulk insert直接给一大块连续的存储空间，存储和indexing都是连续的。

典

Index Page split和重组，如果没有很多insert，只是update操作，不是很大的问题。 insert的情况下会引起系统对page的重组，效率变差

youngf

什么级别的数据量?

交易人生

提醒我了，叫page，不过印象中一个page就8k，不知道 2008 r2有没有变化，当update xml的时候，这种25个小时的数据折腾，数据有变化，8k per page抗不住update。不过还是具体问题具体分析，不做实验不清楚。

北风

有人用过SnareCore.exe吗?
我现在锁定这个东西
它在event viewer塞了无数个SQL login logs

北风

其实我觉得这已经超出DBA SQL tuning的范畴了
这分明就是Sys admin和Developer的工作
至少也应该是三方合力的
这周末把SnareCore拿下试试看
TMD有人装了这东西也不告诉我

典

一般一个8k的page不会是满的，所以update一条记录不太会引起page的split以及新的page的建立，以及页面重组。

如果是insert就不同了，反复加入新的记录会引起系统不断新建page, 重组page，曾经在课堂看老师演示过，在某种条件下insert会引起页面系统的一片混乱。

[ 本帖最后由 典 于 2012-4-30 18:06 编辑 ]

典

会不会是log的问题？

大家有什么好看法 都说一下吧

huaxianz

原帖由 典 于 2012-4-30 01:04 发表
一行一行弄是很慢，效率最差

把XML直接传给stored procedure，用sp来批量处理，估计快几十倍几百倍都有可能。

+1

"一行一行弄是很慢，效率最差"  -- This is killing the engine, absolutely the worst practices. SQL is set-based operation as opposed to other procedural-approach application languages. Plus, excessive establishing/Destroying task contexts is not trivial.

"把XML直接传给stored procedure，用sp来批量处理，估计快几十倍几百倍都有可能。" -- Pass in a whole document as XML data type -> build XML-type indexes on top of it -> use MERGE statement to insert/update/delete records within one query context in bulk operation mode. Data from XML documents is fetched through XQuery functions, which is XML-type index aware. Or if the XML elements can be decomposed/unmarshalled before being loaded into DBMS, that's better.

交易人生

北风他们是xml field，一个xml 文档，很多时候8k挡不住。

jerryclark

如果一定要在.net里面计算的话，
.Net里面一次读一个大大的dataset，比如全部的数据，或者说10万行之类的大量，然后用一个临时的dataset来存储需要insert的数据，
所有处理尽量在.net里面完成。
然后一次性用data adapter更新回去。

不知道你XML是怎么读的，如果是用dataset读的话，请在dataset上设定primary key column。我曾经碰到过这个问题，要2个DS对比数据，很慢很慢，加了primary key column，一下子很快

如果是xml reader读的话，我倒是没经验。

jerryclark

LZ还是贴个sample代码上来吧，这样才能有的放矢地给建议

典

原帖由 huaxianz 于 2012-4-30 19:58 发表



+1

"一行一行弄是很慢，效率最差"  -- This is killing the engine, absolutely the worst practices. SQL is set-based operation as opposed to other procedural-approach application languages. Plus, excess ...

Agree,如果说大家提出了10个方案，一行一行弄应该是最差的一个方案。

用.net将 XML直接传给SP, SP里做Merge, 简单高效，

不过 LZ说了不方便改程序,...所以...

[ 本帖最后由 典 于 2012-5-1 10:48 编辑 ]