SQL Server 2000/E1 8.10 & hanging applications

KJB

Member
Hi

Does anyone out there have any suggestions please!

A fortnight ago we migrated our Enterprise Server and database from iSeries with DB2/400 to a clustered (active/passive) Wintel solution running Windows 2003 with SQL Server 2000 at SP3a. The database sits on a SAN with fibre connections to the servers.

Since then we have seen many instances where applications are hanging and the users have to crash out of EnterpriseOne and start again. We are also, in some instances, seeing ODBC timeouts. The problem is most evident within the Customer Service applications.

We are on EnterpriseOne 8.10 with Tools 8.93.N1. User access is through two farms of (W2K) Terminal Servers and fat clients (W2K & XP). Both terminal server and fat client users are experiencing the problem.

I've checked all the obvious things I can think of......

Thanks in advance.
 
[ QUOTE ]
Since then we have seen many instances where applications are hanging and the users have to crash out of EnterpriseOne and start again. We are also, in some instances, seeing ODBC timeouts. The problem is most evident within the Customer Service applications.


[/ QUOTE ]

From your description, it sounds like your database server is not tuned correctly. Make sure you have lots of ram and have tuned SQL to make use of it. Make sure your databases are indexed correctly. Have your DBA keep an eye on the TEMP DB database. Tempdb is MS SQL's achillies heel. If you have user's doing large open ended queries against the database, especially if they are using access or some other application besides JDE, then TempDb could be the bottle neck.

Here's an example - a user using Access does an open ended query against the 911 table. SQL locks up the 911 table and temp db while it is doing the fetch. Other applications (like purchase orders) continue to work fine, but a user doing a post to the GL is stuck waiting until the lock is cleared.

The solution is two-pronged. 1) Make sure your database and database server are well tuned and have lots of resources. The more ram the better. That way SQL can keep lots of data in ram rather than doing a fetch to the disks. It doesn't matter how fast your SAN is, it is still substantially slower fetching data than RAM. 2) Do not allow your users to do direct queries against the Production Database. Make a copy of that database and keep it current. Have them use that database to do their queries against. Those two strategies have worked wonders for us.

Gregg Larkin
JDE System Administrator (CNC) / North America
Praxair, Inc.
 
Back
Top