27,579
社区成员
发帖
与我相关
我的任务
分享sql server 大数据量并发插入问题
开发了一个设备采集监控系统。其中设备数据项有2000多个，保存在sql server2008中。存储方案是针对每个设备创建一个单独的数据库，里面建立多个数据表，把数据项分表存储。在主数据库中提供插入数据存储过程，将传入的数据插入到对应数据库中。
测试中发现，使用ado连接数据库，当有60个线程同时插入60个设备的数据，同时开30个线程进行查询。每个线程都采用独立的连接，查询的时候将数据表设置为nolock。但是还是会出现查询超时已过期问题。想问一下这是由于sql server本身处理不过来导致的，还是ado的问题。
同时单独运行一个线程插入时速度还可以，但60个线程同时插入的时候，单个线程的插入速度就降到原来的1/20。所有线程调用的是同一个存储过程，但是插入数据库不同。
测试中发现，使用ado连接数据库，当有60个线程同时插入60个设备的数据，同时开30个线程进行查询。每个线程都采用独立的连接，查询的时候将数据表设置为nolock。但是还是会出现查询超时已过期问题。想问一下这是由于sql server本身处理不过来导致的，还是ado的问题。
同时单独运行一个线程插入时速度还可以，但60个线程同时插入的时候，单个线程的插入速度就降到原来的1/20。所有线程调用的是同一个存储过程，但是插入数据库不同。
...全文
请发表友善的回复…
发表回复
人鱼传说 2013-12-15
- 打赏
- 举报
楼主的方案有很大问题，60个设备+一个主数据库+60个设备数据库，你服务器再牛，存储过程也只能一个个的将60个设备数据依先后次序写入相应的设备数据库，主数据库是写入的性能瓶颈，现在60个设备以内写入可能感觉不到慢，是因为设备数据库数据量还不大，一但大起来，不用到60个设备就能感觉到慢了，所以我说你的方案设计是有问题的，如何解决：架构改为N设备对N数据库，如体做法是：
修改你的数据采集程序，由程序线程自动通过设备号来判断应该连接哪个数据库(ado连接字符串可以确定)，跳过数据写入瓶颈，调用的存储过程自然就是相应的设备数据库的存储过程，这样就能真正的做到并发写入，磁盘阵列IO性能得到发挥.
阳泉酒家小当家 2013-11-29
- 打赏
- 举报
这个是latch，也就是闩锁等待：ACCESS_METHODS_DATASET_PARENT 用于同步在并行操作期间对父数据集进行的子数据集访问。
也就是你的select语句，采用了并行的执行计划，在执行时，需要同步这些并行操作，所以就出现了等待。
比如，一共处理n条数据，给2个线程处理，每个n/2条，但是有一个线程处理的快，一个慢，所以快的那个就得等着慢的那个，所以就出现了这种等待。
hgwyl 2013-11-29
- 打赏
- 举报
怎么觉得数据库的设计显得怪怪的……
设备数据项有2000多个
如果达到这个级别，硬件设备才是最大的瓶颈吧？
如果就是一台服务器，拖那么多库，还是并发，怎么都快不起来吧……
也许你可以看看这个大神问的。也是搞监控的。
猛戳这个传送门：海量数据，每天5T,数据至少保存1年，使用啥数据库来解决呢？
imlmy 2013-11-29
- 打赏
- 举报
多线程并发查询，select语句被自身给阻塞了，这个不太好理解。查询sysprocesses，发现spid和blocked相同，cmd为select，waitresource为ACCESS_METHODS_DATASET_PARENT。waittime达到5763ms。select的表有增加nolock标志，不过这个是针对插入数据的，应该没有关系。
imlmy 2013-11-29
- 打赏
- 举报
存储过程中使用游标导致的阻塞主要是由于对tempdb的并发读写，waitresource为2:1:1或2:1:3。
http://support.microsoft.com/kb/328551/zh-cn
阳泉酒家小当家 2013-11-28
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
declare @i int;
declare @count int;
set @i = 1
set @count = (select count(*) from 表);
while @i <= @count
begin
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from 表
)
select 你的变量1 = (select 字段 from 表 where rownum = @i),
你的变量2 = ...
--用变量构造动态语句
set @i = @i + 1
end
select @max = count(*) from #temptable
set @i = 1
while @i < @max
begin
select ....... from #temptable where rownum=@i
set @i = @i+1
endimlmy 2013-11-28
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
declare @i int;
declare @count int;
set @i = 1
set @count = (select count(*) from 表);
while @i <= @count
begin
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from 表
)
select 你的变量1 = (select 字段 from 表 where rownum = @i),
你的变量2 = ...
--用变量构造动态语句
set @i = @i + 1
end
select @max = count(*) from #temptable
set @i = 1
while @i < @max
begin
select ....... from #temptable where rownum=@i
set @i = @i+1
end
shoppo0505 2013-11-26
- 打赏
- 举报
1. 去除游标的使用。
2. 新装个SSD,把数据库文件安到SSD上，再挂在server上，不需要更改其他任何设置。
阳泉酒家小当家 2013-11-26
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
declare @i int;
declare @count int;
set @i = 1
set @count = (select count(*) from 表);
while @i <= @count
begin
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from 表
)
select 你的变量1 = (select 字段 from 表 where rownum = @i),
你的变量2 = ...
--用变量构造动态语句
set @i = @i + 1
end
imlmy 2013-11-26
- 打赏
- 举报
会不会是设备数据库文件大小增长问题，不过我目前已经设置成1M的增长
imlmy 2013-11-26
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
declare @i int;
declare @count int;
set @i = 1
set @count = (select count(*) from 表);
while @i <= @count
begin
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from 表
)
select 你的变量1 = (select 字段 from 表 where rownum = @i),
你的变量2 = ...
--用变量构造动态语句
set @i = @i + 1
end
阳泉酒家小当家 2013-11-22
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
declare @i int;
declare @count int;
set @i = 1
set @count = (select count(*) from 表);
while @i <= @count
begin
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from 表
)
select 你的变量1 = (select 字段 from 表 where rownum = @i),
你的变量2 = ...
--用变量构造动态语句
set @i = @i + 1
end
阳泉酒家小当家 2013-11-22
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0imlmy 2013-11-22
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0
火拼阿三 2013-11-22
- 打赏
- 举报
楼上的都牛X的一塌糊涂。。。学习下。。。
阳泉酒家小当家 2013-11-22
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。[/quote]
运行下面的语句，看看有没有阻塞的问题：
select *
from sys.sysprocesses
where blocked <> 0imlmy 2013-11-22
- 打赏
- 举报
我觉得单个存储过程被多个连接并发调用，只要使用的资源不存在排他锁问题，应该可以。不过cpu却是占用很高，几乎满了。我主要是在想是由于cpu处理不过来导致的，还是由于并发插入存在锁的问题。因为我并发插入的数据是在不同的数据库中，只是调用的存储过程是同一个。
imlmy 2013-11-22
- 打赏
- 举报
目前的数据库设计是这样的，有一个主数据库，里面有设备类型描述表，提供一个创建设备的存储过程，在主数据库的设备表中增加一条记录，同时根据设备类型描述创建一个新的设备数据库，里面创建设备数据表。设备数据库就只有数据表，没有存储过程。同时主数据库提供一个存储过程用于插入数据，这个存储过程用于构建sql语句，use到对应的设备数据库，执行插入语句。主数据库和设备数据库都在一个实例里面。
之前这样设计是考虑兼容扩展多种不同的设备，如果添加新的设备，只需要在主数据库的设备类型描述表中增加对应设备数据的描述就行。而且应用程序调用也比较简单，不用考虑数据库结构，只需要调用存储过程接口就行。
之前一直提示查询超时的问题找到了，是由于查询数据的范围包含正在插入的数据，所以导致等待锁超时。
开着拖拉机泡妞 2013-11-22
- 打赏
- 举报
都分数据库了，为什么还要使用同一个存储过程来往不同数据库里写数据？
插入数据的话会在page的级别产生IX锁，这个是不影响查询的。
如果表有聚集索引，会在key级别产生X锁，所谓X锁就是排他锁。
也就是说如果事务T在某个页面或者表上加了X锁，那么别的事务是不能够访问这个页面或者表的。
---涛声依旧--- 2013-11-22
- 打赏
- 举报
为什么要用同一个存储过程呢？
既然是不同的连接就调用该连接的数据库中的那个存储过程了
同一台机器上哪怕用存储过程了但跨数据库了效率还是有影响的
加载更多回复（11）
