Performance Issue on posintg sales and purchase invoice

denvit

The problem we face is posting performance issue, once the problem occur all posting will very slow, one invoice with 7 lines might take more than 3 mins to post. We are now resolve the problem by restart DB server then everything back to OK but we face this situation nearly once a week.

I would like to know what root cause is and how to prevent this situation, please kindly assist.

My customer had more than 22,000 sales invoice line per day and currently DB size is over 100G with 9 months operation. They are run Navision 4.0 on SQL2000. We are running on SAN and I think hardware is not issue.

kine

100GB after 9 months??? It is huge increase! How many dimensions you are using? You need to thing about the increase and solution for it (moving historical data - e.g. posted headres and lines - into separate tables etc.). And of course - optimizing indexes and SIFT. Search the forum for info about optimilizing NAV on SQL and watch the webcast about that (see download section).

denvit

Thank you for your comment kine.

No Dimension !!!!

As i said more 22,000 sales invoice line per day. that's why DB size increase very fast.

I still would like to know why restart can fix the problem and how to prevent the error.

Please kindly comment..

Nobody

It is possible than part of the problem is SQL 2000. In SQL 2000 Standard Edition you have a max of 1.7 gigs of RAM regardless of how much RAM is on the server. This RAM is used for lock memory, plan caching, and data caching. With the volumes you are quoting this is not nearly enough. SQL 2000 Enterprise Edition can use more RAM with AWE but AWE can only be used for data caching and you are still limited to 1.7 gigs for plan caching and lock memory. Is the customer running Standard or Enterprise Edition of SQL? Also I would disable all SIFT indexes on Sales Header and Sales Line, the overhead to maintain SIFT indexes on these tables is much greater than the overhead for a simple SELECT SUM() statement on the tables them selve.

Note: SQL 2005 x64 Standard Edition with Windows Server 2003 x64 R2 Standard Edition will support upto 32 gigs of RAM and 4 processor sockets and a 2 node cluster. With x64 you nolonger have to deal with AWE or the 1.7 gig limit and all the memory can be used for locks, plan caching, and data caching and you can replce SIFT indexes with the new non-clustered indexes with included columns.

How are the disk on the SAN configured? This can make a huge performance difference.

denvit

Below is SQL server information. Oh one more thing we are all runing Navision on latest version of Citrix, 25 users.

CPU. 4x 2.41GHz AMD
HD Size = 198Gb for DB and 133Gb for Log. HDD is on a SAN.
3.83Gb RAM
Windows 2003 Enterprise
SQL2000 Enterprise

denvit

I would like to add more info, the problem also happen on posting of purchase invoice and another.

I mean when I have problem all DB writing will impact but no impact on read :shock: .

please kindly advice

ecclectic

one thing to keep in mind, windows isn't exactly known to be the best memory(resource) manager. could be that restarting the server makes more resources available and thus makes the updates easier. maybe a memory manager could help

David_Singleton

denvit wrote:

Below is SQL server information. Oh one more thing we are all runing Navision on latest version of Citrix, 25 users.

CPU. 4x 2.41GHz AMD
HD Size = 198Gb for DB and 133Gb for Log. HDD is on a SAN.
3.83Gb RAM
Windows 2003 Enterprise
SQL2000 Enterprise

That sounds light both RAM and CPUs for this size of system.

Second what is your hard drive configuration. SAN is just a term for the "bit of wire" between the computer and the hard drives, it says nothing about the drives. Are they RAID 1 or RAID 10. Is the log file dedicated to a separate RAID 1 array. How many spindles do you have allocated to the Database? Is there anything else being allocated to the spindles used for the log file.

Still the fact that it gets faster on reboot tends to indicate RAM being the issue. I assume the Navision SQL database is the only program running on the server.

What version of Navision are you on (go to Help About to get the full version).

kriki

[Topic moved from Navision forum to SQL Performance forum]

kriki

Nobody wrote:

Also I would disable all SIFT indexes on Sales Header and Sales Line, the overhead to maintain SIFT indexes on these tables is much greater than the overhead for a simple SELECT SUM() statement on the tables them selve.

Don't just start to disable all of them. Some of them are really needed to get a good performance.

Marije_Brummel

If this is the only process that is slow you can measure the process with the profiler and/or the client monitor and find out what causes the problem.

It might easily be some customisation with a while find loop or whatever.

This is a real performance killer for memory.

Off course the other stuff is important like SIFT and Indexes but if it is only one slow process I would start this way.