Insert performance different between two servers

[wriggs]

Hi,

Any suggestions on the following as I've kind of run out of ideas.

I have 2 servers which are the same spec ie box, processor etc. The
only difference I can tell is that the production box has raid setup
but the test box hasn't (I think).

I have created a stored procedure to insert 10k rows into a dummy table
with two columns.

I have logged onto the boxes directly so there are no networks issues
here. Also the boxes only have light traffic on them really, there
isn't much going on at the point of running.

The production box inserts the rows two times faster than the test box
i.e 30 secs rather than 1 min. Does anyone have any idea why this could
be, do you think it could be raid?

The prod box has 8 disks I think in hardware Raid 5 but as the test box
has 4 disks and it looks as if all the space is available i doubt raid
is being employed.

Thanks

Ian.

ps. does anyone know if there is a way to check the raid configuration
of a box from within windows? or do you have to re-boot and go through
the setup?

[Brian Cryer]

"wriggs" <ia***@btintern et.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Hi,

Any suggestions on the following as I've kind of run out of ideas.

I have 2 servers which are the same spec ie box, processor etc. The
only difference I can tell is that the production box has raid setup
but the test box hasn't (I think).

I have created a stored procedure to insert 10k rows into a dummy table
with two columns.

I have logged onto the boxes directly so there are no networks issues
here. Also the boxes only have light traffic on them really, there
isn't much going on at the point of running.

The production box inserts the rows two times faster than the test box
i.e 30 secs rather than 1 min. Does anyone have any idea why this could
be, do you think it could be raid?

The prod box has 8 disks I think in hardware Raid 5 but as the test box
has 4 disks and it looks as if all the space is available i doubt raid
is being employed.

Thanks

Ian.

ps. does anyone know if there is a way to check the raid configuration
of a box from within windows? or do you have to re-boot and go through
the setup?

Regarding performance, if the disk arrangement is the only difference
between the two servers, i.e. same CPU, same memory then that leaves only
the disk configuration. (Its unlikely to have any noticeable bearing, but
you could check that neither system is using heavily fragmented disks.)

Your production box may have raid 5 and 8 disks but are you saying that all
8 disks are used in the raid to produce a single logical disk?

If you consider a raid 5 across 3 disks compared with a single disk system.
Each time you write 2MB of data, each of the 3 disks in the raid would have
1MB of data written, but the single disk system would need to write all 2MB
to the disk. Thus on paper the raid would give the impression of being twice
as fast (i.e only half the time to write). But if you were to change your
single disk system to a dual disk arrangement and have the log file and data
files on separate disks then you would get similar-ish performance to the 3
disk raid. The actual performance you get in reality is tempered by other
factors such as how much data you can push through the bus and how many
buses the disks are on, the characteristics of the individual disks, whether
you are using software or hardware raid and in the case of hardware raid the
characteristics of the raid controller (such as how large a cache it has and
whether it does write behind caching).

One other thought, on one of my Windows 2003 servers my sql server database
was crawling along when doing bulk inserts. It turned out to be that
write-behind caching on the disk (single disk system) was turned off. That
meant that each time sqlserver updated a data file or wrote to the log, it
couldn't carry on until the disk write had been completed. Turning on
caching in windows and it was much much faster.

As for checking the raid configuration from windows. If you are using a
software raid then you should be able to see the raid configuration from
within computer management > storage > disk management. If it is a hardware
raid then it may have come with a utility for allowing you to configure (or
monitor) it from within windows. You will need to check with the
manufacturer of the raid card. Failing that, reboot and the raid card should
give you the option to manage/view the raid configuration. If you do that
just be careful that you don't change the raid configuration - do that and
you loose your raid and everything on it.

Hope this helps,

Brian.

www.cryer.co.uk/brian

[wriggs]

hmmm.. well i checked with the win2000 guys who originally setup the
box and it seems both boxes are setup with raid 5, its just that the
production box has got 8 drives in and the test box has 4 and obviously
half the space, so could this be the problem as less data is being
written to each individual drive on the production box??

One other point to note is that the test box seems to have a better
raid controller. the production one has a hp smart array 5300 where the
test box has a 6400 controller.

But obviously it isn't making that much difference

[Brian Cryer]

"wriggs" <ia***@btintern et.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

hmmm.. well i checked with the win2000 guys who originally setup the
box and it seems both boxes are setup with raid 5, its just that the
production box has got 8 drives in and the test box has 4 and obviously
half the space, so could this be the problem as less data is being
written to each individual drive on the production box??

One other point to note is that the test box seems to have a better
raid controller. the production one has a hp smart array 5300 where the
test box has a 6400 controller.

But obviously it isn't making that much difference

I think you've now answered your original question. If both are identical
systems, except the production one has an 8 disk raid 5 and the test one a 4
disk raid 5, then each disk write on the production system will be
distributed across 8 disks whereas on the test on it will be distributed
across 4 disks. The significant bit performance wise is that on your 8 disk
production system each disk only needs half as much data written to it as on
your 4 disk test box, and thus it should take about half the time - which is
what you are experiencing.

Personally I'm normally a bit sceptical about one controller being better
than another - if one has more ram on it then I can understand - but if you
are inserting a lot of records then once the cache becomes full then it
doesn't matter how large the cache is because you are still governed by how
fast the controller can stream the data to the disks. Also, if the
controllers are configured not to cache writes then it doesn't matter how
much ram they might have on them.

I know this isn't part of your question, but I assume that on both systems
when doing your data insert that the systems became disk bound - i.e.
disk/raid lights came on, stayed on and cpu usage fell away. In scenarios
like this it is the speed of your disks/raid which become the critical
factor. You could double the speed of your cpu and it wouldn't make any
difference.

Brian.

www.cryer.co.uk/brian

[wriggs]

Thanks for the info Brian, think I'll put it down to this anyway.

Brian Cryer wrote:

"wriggs" <ia***@btintern et.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

hmmm.. well i checked with the win2000 guys who originally setup the
box and it seems both boxes are setup with raid 5, its just that the
production box has got 8 drives in and the test box has 4 and obviously
half the space, so could this be the problem as less data is being
written to each individual drive on the production box??

One other point to note is that the test box seems to have a better
raid controller. the production one has a hp smart array 5300 where the
test box has a 6400 controller.

But obviously it isn't making that much difference

I think you've now answered your original question. If both are identical
systems, except the production one has an 8 disk raid 5 and the test one a 4
disk raid 5, then each disk write on the production system will be
distributed across 8 disks whereas on the test on it will be distributed
across 4 disks. The significant bit performance wise is that on your 8 disk
production system each disk only needs half as much data written to it as on
your 4 disk test box, and thus it should take about half the time - which is
what you are experiencing.

Personally I'm normally a bit sceptical about one controller being better
than another - if one has more ram on it then I can understand - but if you
are inserting a lot of records then once the cache becomes full then it
doesn't matter how large the cache is because you are still governed by how
fast the controller can stream the data to the disks. Also, if the
controllers are configured not to cache writes then it doesn't matter how
much ram they might have on them.

I know this isn't part of your question, but I assume that on both systems
when doing your data insert that the systems became disk bound - i.e.
disk/raid lights came on, stayed on and cpu usage fell away. In scenarios
like this it is the speed of your disks/raid which become the critical
factor. You could double the speed of your cpu and it wouldn't make any
difference.

Brian.

www.cryer.co.uk/brian