Multi Threading with Single Processor
Folks,
I have a server with the following config:
Intel P4 Processor
2GB Memory
RAID 1+0
On it I have running a rather large MS SQL DB. For some reason I cannot execute two queries at the sametime. Which for me seems odd!
For example. I am using linked servers to update a table called T_Products. The bottle neck in this operation is the bandwidth between the two linked servers. Therefore CPU is not maxed out, plenty of memory, plenty of I/O. However if I wish to execute another write operation on another table..... it does not allow it - well... it times out.
Any ideas??
Cheers
Angus
I have a server with the following config:
Intel P4 Processor
2GB Memory
RAID 1+0
On it I have running a rather large MS SQL DB. For some reason I cannot execute two queries at the sametime. Which for me seems odd!
For example. I am using linked servers to update a table called T_Products. The bottle neck in this operation is the bandwidth between the two linked servers. Therefore CPU is not maxed out, plenty of memory, plenty of I/O. However if I wish to execute another write operation on another table..... it does not allow it - well... it times out.
Any ideas??
Cheers
Angus
ASKER
Yes,
I am trying to execute two updates at the sametime. I have one update that takes 10 mins and in that time I cannot execute any further updates - and I am not even trying to update the same table.
I have since checked the setting of the server and see that I have listed 2 processors as it has the P4 has Multi Threading.
Kind regards
Angus
I am trying to execute two updates at the sametime. I have one update that takes 10 mins and in that time I cannot execute any further updates - and I am not even trying to update the same table.
I have since checked the setting of the server and see that I have listed 2 processors as it has the P4 has Multi Threading.
Kind regards
Angus
even if both the updates are on different tables,
you might have locking issues..
can you make sure you don't lock the entire database when you do either update?
you might have locking issues..
can you make sure you don't lock the entire database when you do either update?
ASKER
in SQL Server EM under Management/Current Activity/ "Locks / Object" I see
XXProductionDB
XXProductionDB.dbo.T_Produ
master.dbo.spt_values
tempdb.dbo.##lockinfo52
so it seems that it locking the DB with the first object in the list.
How can I prevent this from happening? How can I limit the locks to Object level and not DB level?
Thanks for your help so far - much apprecaited.
XXProductionDB
XXProductionDB.dbo.T_Produ
master.dbo.spt_values
tempdb.dbo.##lockinfo52
so it seems that it locking the DB with the first object in the list.
How can I prevent this from happening? How can I limit the locks to Object level and not DB level?
Thanks for your help so far - much apprecaited.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
lluthien,
Thanks for your response. Quick question - are there any DB setting to prevent Global locking of the DB or to manage how locks are set.
The reason I ask is that we have 100's of update statements already coded and want to prevent the need to include 'with rowlock' in each one.
I have not tried the 'with rowlock' solution yet as I am currently on a business trip.
Thanks again for your help!!
Regards
Angus
Thanks for your response. Quick question - are there any DB setting to prevent Global locking of the DB or to manage how locks are set.
The reason I ask is that we have 100's of update statements already coded and want to prevent the need to include 'with rowlock' in each one.
I have not tried the 'with rowlock' solution yet as I am currently on a business trip.
Thanks again for your help!!
Regards
Angus
AFAIK,
you will have to set this in the update statement itself.
sorry about that.
you will have to set this in the update statement itself.
sorry about that.
ASKER
Thanks for your answer. No need to apologies it is not your fault.
New issue... you cannot use (WITH ROWLOCK) with LinkedServers....
Which opens a new world to me as I cannot seem to find out how to prevent the whole DB getting locked on a single UPDATE statement from a remote DB.
The alternatives do not look good... it means downloading the data... interfacing.... and then uploading with (WITH ROWLOCK).
Unless anyone else has any other ideas??
Help! ;-)
Many thanks!!
New issue... you cannot use (WITH ROWLOCK) with LinkedServers....
Which opens a new world to me as I cannot seem to find out how to prevent the whole DB getting locked on a single UPDATE statement from a remote DB.
The alternatives do not look good... it means downloading the data... interfacing.... and then uploading with (WITH ROWLOCK).
Unless anyone else has any other ideas??
Help! ;-)
Many thanks!!
in theory,
you could put your statements into stored procedures on your linked servers.
and put the (rowlock) on the stored procedures there.
then call the stored procedures from the remote servers.
but that means moving the updatestatements to your stored procedures on the linked servers.
is that an option?
you could put your statements into stored procedures on your linked servers.
and put the (rowlock) on the stored procedures there.
then call the stored procedures from the remote servers.
but that means moving the updatestatements to your stored procedures on the linked servers.
is that an option?
ASKER
lluthien,
After long hours and fighting with the code... I have got it working. Largely (if not entirely) thanks to all your support.
Many thanks for your help - much apprecaited. I have awarded you all the points.
All the best
Angus
After long hours and fighting with the code... I have got it working. Largely (if not entirely) thanks to all your support.
Many thanks for your help - much apprecaited. I have awarded you all the points.
All the best
Angus
glad to be able to help out
cheers
cheers
in the second query?