Solved

Cross-server queries

Posted on 2004-09-09
11
192 Views
Last Modified: 2008-01-09
Our production system is a quad-processor server with 2gb of ram dedicated to SQL. It runs SQL7 in 6.0 emulation mode due to the old software package we use.
Our web server is running SQL 2k with 3gb of ram and quad processors. Connections are TCP/IP over 10/100 Ethernet (soon to be gigabit).

A program on the web server, written in VB6 and ADO, provides detailed data from our production system to our intranet. Another similar one is written in VB.NET and ADO.NET.

Occasionally, Enterprise Manager on our production system is unable to provide process activity details, returning an error showing a timeout waiting for locks.

Our diagnostic program has shown processes running from the web server with Shared table locks on some of our critical tables. I am assured that the program is read only (except for temp tables I noticed), so why is it locking tables? I have assumed that the query comes to the production server without optimization, and because of the way it is written,
it isn't optimized there and so starts doing table locks while it retrieves data.

I have thought of having the web server program and its stored procedures changed to do SELECT .... WITH NO LOCK to try any ease this problem. DTS would take care of update transactions, but these are inquiry only.

I am looking for any ideas to get out of this hole.
0
Comment
Question by:geoffbtbc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
11 Comments
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 84 total points
ID: 12020264
A shared table lock only means that other processes cannot modify the table while the select query is running. It is by no means a blocking lock.

Anyway, try out With (NoLock) by all means. If that doesn't work, your queries might need work and/or you might need indexes.
0
 

Author Comment

by:geoffbtbc
ID: 12020300
If I have a user in production whose process wants to update that table (say an order header for example), will they just be forced to wait?

If I 'lose' the original request from the webserver, will the production server not keep the lock until all the dataset has been returned?

In one case, the production process had been active for over 20 minutes.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12020387
- With a shared table lock, they can do anything they want except modify the structure of the table.

- How long the process stays active all depends on how your web stuff is set up.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:geoffbtbc
ID: 12020438
I was under the impression from SQL 7 BOL that a shared lock stopped any other transaction from updating the data, which is why a table lock causes problems.

The web process should have timed-out in only a couple of minutes, but the production process wouldn't know that ?
0
 
LVL 15

Assisted Solution

by:SRigney
SRigney earned 83 total points
ID: 12021383
The web connection may not be a client side recordset.  If it's opening the connection and leaving it open to loop through rather than pulling the data and working with it in a disconnected fashion it may also affect locking.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12026069
1)  EM stinks for locking,   I cant believe how many times EM has been the cause of deadlocks. etc.

 better to use sp_who2 to get running processes state   (i have even tweaked this into a sp_who3 for better performance). and sp_lock2 for viewing locks.

2) ? the web server has SQL on it, you do understand that this can cause performance problems having SQL and IIS  on same box ?

3) yes i agree that a performance monitoring tool should probably make a lot of use of WITH(NOLOCK) hint.
0
 

Author Comment

by:geoffbtbc
ID: 12030394
Thanks for the ideas!

The web server does have SQL on it, but that is not where I am seeing the problems;however, that server is using a linked server connection to production to query and insert data there.

I am using SQL Diagnostic Monitor from Idera to monitor all SQL servers. I have just found Microsoft's article on sp_blocker_pss70 and a script to monitor blocking, and then info on how to compare this to Profiler run at the same time, so I am going to crank that up. I still get the feeling at times that the web server finishes its process before production does, leaving resources allocated in production that should and need to be reclaimed.
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 83 total points
ID: 12042612
sp_who2 will tell you who is blocking and sp_lock will tell what lock are maintained by the processes.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question