Multiple INSERTs into a single table scaling / performance problem

Hello all ...
This one has me a bit confused. I'd really appreciate any and all
suggestions. This may be pilot error on my part...
My requirement is to have multiple programatic database applications, each
on a separate database connection insert data into a single table as fast as
possible. My hope is that I'll see some scaling of rows-per-second inserted
by adding additional processes, connections and simultaneous inserts.
I'm not able to use BCP for this as the data is coming from an application
style feed.
I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on an
AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
tends to get up around 2.9GB at times ... it is the "only" service on the
box.
Here's my table:
CREATE TABLE MyTable
(myID numeric(10,0) not null,
myChar char(8000) null,
myRowversion rowversion)
ON A_TABLE_FILEGROUP
-- No indexes, just a simple heap. I know that each row will require 8K of
-- storage ... about a page each.
My database transaction log is on a different disk/file group on a separate
controller.
I used SQL Query Analyzer for my test. I opened up a single connection and
ran a simple batch along the lines of:
begin tran
while @.i <= 10000
begin
insert into MyTable ...
values ...
select @.i = @.i + 1
end
commit
OK, when I run the above and check the start and finish times I can insert
about 1200 rows per second. I checked the disks with perf mon and I have
very little Average Disk Write Queues on the table and transaction log disks
(on avg less than 1.)
Sooooo.....
When I open up two additional sessions in SQL Query Analyzer ... and kick
off three of the same loops "at the same time" I average ... a sum total of
about 1200 rows per second. No scaling at all. Note that I am running all
three windows from within the same SQL Query Analyzer session, each with a
different connection into the database.
Again, the disks and the whole box are pretty much snoozing.
Any suggestions I might try? I'm sure I'm missing something here.
Thanks so much!!!!
DBwell...
have you try varchar instead of char?
why a numeric and not an integer?
separated disks is not enough.
What is your disk config?
do you use RAID 5 or Raid 0+1?
how many disks on each controller? (do you use SCSI 15krpm disks?)
have you some write cache on your controllers?
does your files are an good initial size? when SQL server need to expand a
file, this takes some ressources, so if your files are larger then your
requirements you'll improve the performance.
have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
and 60MB/s (SQLIO results):
sqlio -kW -b64 -frandom -LS
with this result I achieve 3333 insert/sec using a script near like you (100
000 rows inserted in 30sec; no transactions)
I insert into a table like yours (int, char(8000), rowversion)
doing all the inserts into 1 transaction reduce the time of the process to
16sec.
and finally adding the set nocount on option, I reduce the process to 7sec
my log & database files are on the same disk
Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> Hello all ...
> This one has me a bit confused. I'd really appreciate any and all
> suggestions. This may be pilot error on my part...
> My requirement is to have multiple programatic database applications, each
> on a separate database connection insert data into a single table as fast
> as
> possible. My hope is that I'll see some scaling of rows-per-second
> inserted
> by adding additional processes, connections and simultaneous inserts.
> I'm not able to use BCP for this as the data is coming from an application
> style feed.
> I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> an
> AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> tends to get up around 2.9GB at times ... it is the "only" service on the
> box.
> Here's my table:
> CREATE TABLE MyTable
> (myID numeric(10,0) not null,
> myChar char(8000) null,
> myRowversion rowversion)
> ON A_TABLE_FILEGROUP
> -- No indexes, just a simple heap. I know that each row will require 8K
> of
> -- storage ... about a page each.
> My database transaction log is on a different disk/file group on a
> separate
> controller.
> I used SQL Query Analyzer for my test. I opened up a single connection
> and
> ran a simple batch along the lines of:
> begin tran
> while @.i <= 10000
> begin
> insert into MyTable ...
> values ...
> select @.i = @.i + 1
> end
> commit
> OK, when I run the above and check the start and finish times I can insert
> about 1200 rows per second. I checked the disks with perf mon and I have
> very little Average Disk Write Queues on the table and transaction log
> disks
> (on avg less than 1.)
> Sooooo.....
> When I open up two additional sessions in SQL Query Analyzer ... and kick
> off three of the same loops "at the same time" I average ... a sum total
> of
> about 1200 rows per second. No scaling at all. Note that I am running
> all
> three windows from within the same SQL Query Analyzer session, each with a
> different connection into the database.
> Again, the disks and the whole box are pretty much snoozing.
> Any suggestions I might try? I'm sure I'm missing something here.
> Thanks so much!!!!
> DB
>