insert update problem

I have a couple Windows 2000 servers SP4, that cannot insert/updated to a
SQL 2000 SP3a server on a Windows 2003 server. Windows 2003 server was
installed fresh. Upgraded to 2000 to 2003 Windows servers with SQL 2000
SP3a work fine. I have ran MDAC 2.8 on the Windows 2000 SP4 server to see
if that would help, but it did not. Any ideas? Thanks, Marc
What query are you executing? What error or unexpected behavior do you get?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Insert Trigger on Linked Server during a FULL DB Backup

I have two servers, Server A and B. Server B is linked to Server A as a linked Server and there is one table TableA on ServerA and ServerB.
TableA receives contineous Inserts and there is an Insert trigger written on TableA on ServerA to insert the same data to ServerB using the INSERTED table. This equation works fine for most of the day, but whenever I run a Full Database backup on ServerB, the whole Trigger Insert process slows. I wonder why? Any idea, any suggestion?During backup server is kind of busy ;) and if there is only one processor and not fast I/O device ...

insert temp with linked server

Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
m
I think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:

> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except one
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sure
> that the settings are all right, including tricks like network service, etc.
> I guess it might be some kind of bug but I just can't find anything about it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m

insert temp with linked server

Hi,
I have a problem with insert temp table with linked server. I have a sql
2000 with sp4 and I have a dozen linked servers. All of them work except one
which is running on windows 2003. I didn't get any error message and the
transaction just open and never stop. I have to kill it manually.
I have checked the DTC on windows 2003, which is quite different. I am sure
that the settings are all right, including tricks like network service, etc.
I guess it might be some kind of bug but I just can't find anything about it
after searching Google and MS KB.
Is anyone running same problem or has any clue,
Thanks,
mI think I've got more or less the same problem.
Since I've load 2000 SP4 the data return from a SP called to a Default
instance is not the same as in the case of a Named Instance. Instead it does
not return a error but return false results.
IOW :
Before SP4 --> No Problems
After SP4 -->
exec[missql01].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For any Default Instance : Return Correct Results */
exec [M24BLACKB01\BES_001].SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* For Any Named Instance : Return INCORRECT Results */
Same command on Local server M24BLACKB01\BES_001:
exec SERVER_CONTROL_DB.DBO.sp_Is_Job_Running
'Free_Disk_Space_Watcher',@.Result OUTPUT
/* Return CORRECT Results */
"dp" wrote:

> Hi,
> I have a problem with insert temp table with linked server. I have a sql
> 2000 with sp4 and I have a dozen linked servers. All of them work except o
ne
> which is running on windows 2003. I didn't get any error message and the
> transaction just open and never stop. I have to kill it manually.
> I have checked the DTC on windows 2003, which is quite different. I am sur
e
> that the settings are all right, including tricks like network service, et
c.
> I guess it might be some kind of bug but I just can't find anything about
it
> after searching Google and MS KB.
> Is anyone running same problem or has any clue,
> Thanks,
>
> --
> m

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/col...realsqlguy.com

Insert Statement Fails on Linked servers

Hi,
we have a local server with a database say A on it . we also have a
linked server which has a database B.
now we are trying to insert into a table in a using data from the
database B. both of the tables in both the database are the same in
structure .
now when i use a query like
insert into a.Table1
( No,
Name
)
select
no,
name
frrom
host_sever.B.dbo.table1
where <some condition >
the above query fails and the error says a nested distributed
transaction cannot be started
both the tables have a trigger attached to it .
we found that first inserting the data into a temp table and then
copying that data into the main table in local server in database A
works fine.
also i tested some scenario with no trigger and it works fine .
is this how it is when there are triggers attached and is there any way
we can succesfully run the query ableve without temp tables.
thanks
ravinderavravinder@.gmail.com wrote:
> Hi,
> we have a local server with a database say A on it . we also have a
> linked server which has a database B.
> now we are trying to insert into a table in a using data from the
> database B. both of the tables in both the database are the same in
> structure .
> now when i use a query like
> insert into a.Table1
> ( No,
> Name
> )
> select
> no,
> name
> frrom
> host_sever.B.dbo.table1
> where <some condition >
> the above query fails and the error says a nested distributed
> transaction cannot be started
> both the tables have a trigger attached to it .
> we found that first inserting the data into a temp table and then
> copying that data into the main table in local server in database A
> works fine.
> also i tested some scenario with no trigger and it works fine .
> is this how it is when there are triggers attached and is there any way
> we can succesfully run the query ableve without temp tables.
> thanks
> ravinder
>
Is the MSDTC service running on both machines? Is it enabled for
network access?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> avravinder@.gmail.com wrote:
> > Hi,
> > we have a local server with a database say A on it . we also have a
> > linked server which has a database B.
> > now we are trying to insert into a table in a using data from the
> > database B. both of the tables in both the database are the same in
> > structure .
> > now when i use a query like
> > insert into a.Table1
> > ( No,
> > Name
> > )
> > select
> > no,
> > name
> > frrom
> > host_sever.B.dbo.table1
> > where <some condition >
> >
> > the above query fails and the error says a nested distributed
> > transaction cannot be started
> > both the tables have a trigger attached to it .
> > we found that first inserting the data into a temp table and then
> > copying that data into the main table in local server in database A
> > works fine.
> > also i tested some scenario with no trigger and it works fine .
> >
> > is this how it is when there are triggers attached and is there any way
> > we can succesfully run the query ableve without temp tables.
> >
> > thanks
> > ravinder
> >
> Is the MSDTC service running on both machines? Is it enabled for
> network access?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
i looked at the services and DTC is runnign . there is another one is
control panel which says MSDTC which i think you are referreing too.
the network protocol says TCP/Ip and the salection default MS DTC
server is blank and disabled .
should this also not have created a issue when dum[ing into the temp
table and cause the same issue.
thanks
ravinder|||avravinder@.gmail.com wrote:
> Tracy McKibben wrote:
>> avravinder@.gmail.com wrote:
>> Hi,
>> we have a local server with a database say A on it . we also have a
>> linked server which has a database B.
>> now we are trying to insert into a table in a using data from the
>> database B. both of the tables in both the database are the same in
>> structure .
>> now when i use a query like
>> insert into a.Table1
>> ( No,
>> Name
>> )
>> select
>> no,
>> name
>> frrom
>> host_sever.B.dbo.table1
>> where <some condition >
>> the above query fails and the error says a nested distributed
>> transaction cannot be started
>> both the tables have a trigger attached to it .
>> we found that first inserting the data into a temp table and then
>> copying that data into the main table in local server in database A
>> works fine.
>> also i tested some scenario with no trigger and it works fine .
>> is this how it is when there are triggers attached and is there any way
>> we can succesfully run the query ableve without temp tables.
>> thanks
>> ravinder
>> Is the MSDTC service running on both machines? Is it enabled for
>> network access?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
> i looked at the services and DTC is runnign . there is another one is
> control panel which says MSDTC which i think you are referreing too.
> the network protocol says TCP/Ip and the salection default MS DTC
> server is blank and disabled .
> should this also not have created a issue when dum[ing into the temp
> table and cause the same issue.
> thanks
> ravinder
>
No, it wouldn't cause an issue that way. Everything that occurs within
a trigger is done inside a transaction, and for a transaction to cross a
linked server, DTC must be available.
I would suggest starting here:
http://www.sqlservercentral.com/columnists/ckempster/debuggingmsdtcissues.asp
Tracy McKibben
MCDBA
http://www.realsqlguy.com

INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>

Insert problem with linked server

Both servers running SQL 2000

I have set up on our local SQL server (using Enterprise Manager) a linked
server running on our ISP. Just did new linked server and added remote
password and login.

The following three queries work:

insert into LinkedServer.dbname.dbo.Table2
select *
from LinkedServer.dbname.dbo.Table1

select *
into LocalTable
from LinkedServer.dbname.dbo.Table1

insert into LocalTable
select *
from LinkedServer.dbname.dbo.Table1

This query, which is what we really want to do, does not work:

insert into LinkedServer.dbname.dbo.Table1
select *
from LocalTable

and returns the error: 'The cursor does not include the table being modified
or the table is not updatable through the cursor.'

I am new to all this and would welcome some help.

AdrianI believe I have now resolved this

In fact the example below would work

> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable

I was trying to insert into a table on the linked server that was not owned
by the dbo but by the remote username. It seems that providing the owner of
the table is dbo it will be OK.

Adrian.

"Adrian" <NoSpam@.hotmail.com> wrote in message
news:ANReb.6576$8_4.54623402@.news-text.cableinet.net...
> Both servers running SQL 2000
> I have set up on our local SQL server (using Enterprise Manager) a linked
> server running on our ISP. Just did new linked server and added remote
> password and login.
> The following three queries work:
> insert into LinkedServer.dbname.dbo.Table2
> select *
> from LinkedServer.dbname.dbo.Table1
> select *
> into LocalTable
> from LinkedServer.dbname.dbo.Table1
> insert into LocalTable
> select *
> from LinkedServer.dbname.dbo.Table1
>
> This query, which is what we really want to do, does not work:
> insert into LinkedServer.dbname.dbo.Table1
> select *
> from LocalTable
> and returns the error: 'The cursor does not include the table being
modified
> or the table is not updatable through the cursor.'
> I am new to all this and would welcome some help.
> Adrian

Insert performance different between two servers

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?"wriggs" <ian.w@.btinternet.com> wrote in message
news:1116923667.752900.260220@.f14g2000cwb.googlegr oups.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|||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|||"wriggs" <ian.w@.btinternet.com> wrote in message
news:1116938928.959078.246100@.z14g2000cwz.googlegr oups.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|||Thanks for the info Brian, think I'll put it down to this anyway.

Brian Cryer wrote:
> "wriggs" <ian.w@.btinternet.com> wrote in message
> news:1116938928.959078.246100@.z14g2000cwz.googlegr oups.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