Performance problems on Navision 3.60 SQL

ahnn

We are having serious performance problems in our Navision Attain 3.60 SQL implementation. This is how our server is configured:
- Compaq Proliant DL380 Dual Processor P4-3.2-Hyperthread
- 4 Gb memory
- QLogic 2300 series 2 Gb HBA connected to EMC Clariion CX400 SAN with 1 Storage controller set to RAID-1 high performance Fiber Channel disk
- 2 GBit LAN links to highspeed Cisco switch platform; no Adaptive load balancing; 2 links configured as fail over with 10Gb+ backplane bandwith
- Alle the latest revisions/firmware applied to the hardware above
- MS Win2003 server - latest service packs applied
- MS SQL2000 server - latest service packs applied
- Server is dedicated used for SQL/Navision and has no other software or connectivity
- Serverhardware tested with Sisoft Sandra Professional and tuned for maximum performance (disk I/O and LAN)
- SAN environement maximum configured for disk i/o related to a seperate storage controller to serve SQL/Navision.

Regarding to the WAN enviroment: dark fiber 1 Gbit full duplex link (single mode) connecting 2x 1Gbit server to remote hosts on Cisco high speed 1 Gbit link.

There are about 35 concurrent Navision 3.60 clients connected by TCP/IP to the SQL Navision server.

The SQL database is located on a seperate LUN, DB size is about 4 Gb and lots of free diskspace availble. Disk I/O tests are extremely good; Sisoft reports disk i/o speeds that are commonly known as very high.

Regarding the hardware/LAN: no bottlenecks are measured; FTP'ing large files to the Navision server doesn't give any problem; LAN/WAN and disk I/O are performing like a Ferrari. 8)

So far, so good; here is our problem:

At a certain moment*) it looks like the system is hanging. At that specific moment, the server utilization (disk I/O, processor, memory) are in no stress situation, everything looks normal (about 1 to 5% util.) The WAN and LAN links are also looking normal (LAN about 0.02% util and WAN about 1 to 1.5% util). The SQL Server doesn't report extreme utilization and doesn't seem to have any performance issues. But the clients are all freezing. After about 5 minutes, the system is back to normal and no specific performance issues are occuring. Then, after about an hour, the problem is back again. After about five minutes all clients are "unlocked" and can work like there is no problem. It seems like a certain client is locking the whole system for other users, and after some time (5 min.) that client releases the system for availble to the other users.

*) It seems that a certain client is submitting a function / report that locks the entire system until this particulair report is finished. In this stress situation some client have lots of local disk I/O, propably swapping memory.

:?: We have the following questions regarding to our related problem as described above:

A. Does anyone of you recognize this kind of problems?
B. Could this problem be related to the use of SQL and would it be solved when using C/Side?
C. Is migrating to 4.0 a solution?
D. Is anyone having experience with the use of HyperThread processors in this situation; would turning off the HP function have positive influence on our performance problem?
E. It looks like a certain client is triggering a lock statement of something similar. Does anyone have a clue where to look for this kind of locks, or is this a typical sympthom for a so called deadlock?
F. Does the local client PC performance have any influence on this problem?

Thank you for your reaction; regards .

kine

What do you see in Session table in the time of lock? Something abou locked by ID, locked by Object?

ahnn

kine wrote:

What do you see in Session table in the time of lock? Something abou locked by ID, locked by Object?

Both items occur sometime in slow performance situation, but 'killing' them has the same effect as ignoring them. There isn't any pattern in slow performance and locking by ID or Object.

kine

No, it is not sollution, you need find user, who blocked others and is not locked. This is the Blocking user. You need to know that, to find, what is blocking the tables...

Sometime it may be some CONFIRM in the middle of write transaction (confirm in post codeunit etc.) - User run some code, go from PC - CONFIRM - lock - user return in few minutes ...


2nd question: Have you looked at Disk write Queue performance? Look at this parameter in the time of slow response...

Tommy_Schou

In certain reports you will find a locktable statement that locks Table 81. This is a very critical table in navision and most if not all post-routines will use it. If this table is locked a lot of users will experience lockups. If this is the case with your installation is difficult to say but do you per chance have a report or codeunit that imports data into a Journal once every hour.. and that takes approximately 5 minutes to run? :-)

I know this is far fetched but this could be your problem. Ie. report "Suggest Vendor payments" will lock table 81 until it has finished running. Perbabs something similar is occuring in your database? The specific lock here is located on "PostDataItem" (Vendor).

Switching to C/Side DB will not solve your issue but might aliviate it a bit since the Native version is quite a lot quicker with some types of operations than SQL and the thus the lockup will be "over" sooner.

davmac1

Have you tried running sp_lock in SQL Server to see who is locking and what is being locked?

Dave

csdpdrs

Use the sp_who2 command in the SQL Query Analizer... it will show you who is creating the bottle neck (check the Blocked by column) and analyze whay is really doing (may be the process is not optimize)

Additional topics to be checked using SQL...

a.- memory in the server is Ok (do not add more becasue SQL can not use more than 4 Gb without migrating to Windows 2000 Advance Server.

b.- Processor is too limited (under my opinion) but it seems that is not the bottleneck. In servers with SQL DB try to use Xeon processors...

c.- Navision Clients should have good machines... I can ensure you that slow clients can slow the system because if you run a long process with clients like this other clients will be affected. Check if all your clients have at least 256Mb of RAM (for client cache tasks). Check also the network performance of the slow clients.

d.- Check the user that is looking the rest of the users as other people has recommended.

e.- How is the Transaction Log in sql server? Backup it periodiclly to avoid a long one.


Good luck!

Polleke

The transaction log only causes a problem when it is full (then you are really f...d).
What we have seen is that analyzing the initator of the lock, you will probably find that this person is posting a couple of invoices (batch posting) or posting a bankjournal in combination with an inserted payment history (specific for the Dutch localization: Bankboek met ingevoerde betaalrun).

facade

Get rid of transaction log - ie use recovery model simple.
Maybe that'll help?

Polleke

I don't think that that is a good idea. How do you want to recover your database accurately in case of an emergency?

cdennis

check this guys site out. Some pretty useful SQL stuff you might find helpful.

http://vyaskn.tripod.com

My experience is with SQL Server - not Navision but hearing your description of the problem and the hardware my first instinct would be to see what the application architecture is doing to cause this 'bottle-neck'. You need to look into the actual processes going on at the time.

As for performance, your database size and hardware seem very well matched from a sql perspective. I've got a 40+ gig Navision system on similar but dual quad/sans etc hardware and over 100+ concurrent users. No bottle-necks other than those caused by Jet Reports sucking up bandwidth. To address that we are migrating most if not all reporting and analysis to Crystal Enterprise and Stored Procedures. MUCH More efficient than doing this anaylsis via a Fat client.

Hearing mention of this table 81 / report locking issue makes me think that this is a more likely direction to look. Not changing your Logs/backup procedures. My experience so far is showing that Navision truely is a fat client. No busiess logic up on the database all of it is inside the client app.

Good Luck