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.