[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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
0
amacfarl
Asked:
amacfarl
  • 6
  • 5
1 Solution
 
lluthienCommented:
are you doing update statements, or just selecting,
in the second query?
0
 
amacfarlAuthor Commented:
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
0
 
lluthienCommented:
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?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
amacfarlAuthor Commented:
in SQL Server EM under Management/Current Activity/ "Locks / Object" I see

XXProductionDB
XXProductionDB.dbo.T_Products
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.
0
 
lluthienCommented:
if the second update is using either of the locked tables, you'll be waiting.
if the db is locked in total, same story.

try using "with rowlock"
in your updates, this tries to minimize the infuence of your lock
use rowlocking in the FROM clause and if necessary in the JOINS.

see if that helps, if it does not, do the same to the second update.
if you still have a lock over the entire database,

it might be due to database settings and or database structure
that the lock is databasewide.
but i suspect step 1 and 2 will help.


0
 
amacfarlAuthor Commented:
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
0
 
lluthienCommented:
AFAIK,
you will have to set this in the update statement itself.

sorry about that.

0
 
amacfarlAuthor Commented:
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!!
0
 
lluthienCommented:
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?
0
 
amacfarlAuthor Commented:
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
0
 
lluthienCommented:
glad to be able to help out

cheers
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now