hardware upgrade -> lower "insert" performance

Hey all,
I was about to move my SQL Server from a box that's about four years
old to a current box. The new box should be faster for everything ...
more RAM, faster CPU, faster disks... but the performance of repeated
"insert" statements is measurably worse. The following script took
2:16 on my old hardware (which is running my production system at the
same time), and 3:23 on the new hardware. "select" and "update"
statements do seem to be faster on the new hardware.
Anybody got any clue where I should be looking?
select getdate()
set nocount on
go
if exists
( select *
from sysobjects
where type = 'U'
and name = 'cccInsertTest')
begin
drop table cccInsertTest
end
go
create table cccInsertTest
( a int not null)
go
declare @.i int
select @.i = 0
while @.i < 50000
begin
insert into cccInsertTest (a) values (@.i)
select @.i = @.i + 1
end
go
select getdate()
gohmmm, is the raid different?
"Chris Curvey" wrote:

> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||Chris,
A couple of ideas:
Was the test database and log created with enough room so the database and
log didn't have to allocate more space (grow) while the process was running?
Instead of using getdate(), set the statistics time and statistics io
options on (QA Tools/Options, Connection Properties tab). Compare those
metrics instead. Also, run the tests on the servers using Remote Desktop to
eliminate any network transport (should be neglegible, but I'd do it
anyway).
-- Bill
"Chris Curvey" <ccurvey@.gmail.com> wrote in message
news:1170084503.675585.149280@.q2g2000cwa.googlegroups.com...
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
>|||On Jan 29, 10:28 am, "Chris Curvey" <ccur...@.gmail.com> wrote:
> Hey all,
> I was about to move my SQL Server from a box that's about four years
> old to a current box. The new box should be faster for everything ...
> more RAM, faster CPU, faster disks... but the performance of repeated
> "insert" statements is measurably worse. The following script took
> 2:16 on my old hardware (which is running my production system at the
> same time), and 3:23 on the new hardware. "select" and "update"
> statements do seem to be faster on the new hardware.
> Anybody got any clue where I should be looking?
> select getdate()
> set nocount on
> go
> if exists
> ( select *
> from sysobjects
> where type = 'U'
> and name = 'cccInsertTest')
> begin
> drop table cccInsertTest
> end
> go
>

> create table cccInsertTest
> ( a int not null)
> go
> declare @.i int
> select @.i = 0
> while @.i < 50000
> begin
> insert into cccInsertTest (a) values (@.i)
> select @.i = @.i + 1
> end
> go
> select getdate()
> go
Turns out that there were two issues. We needed to turn on write
caching within our RAID controller. But the bigger problem was the
client program that we were using to drive the script. "isql" was
running at 4 xacts/sec. "osql" ran at 9 xacts/sec. Writing a Python
script (that ran from another machine, across a slow network link)
gave us 30 xacts/sec.
Go figure!