mibuso.com
Home› SQL Performance
Options

Performance Issue on posintg sales and purchase invoice

denvitdenvit Member Posts: 12
edited 2007-03-04 in SQL Performance
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.

Comments

  • kinekine Member Posts: 12,562
    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).
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • denvitdenvit Member Posts: 12
    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..
  • NobodyNobody Member Posts: 93
    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.
  • denvitdenvit Member Posts: 12
    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
  • denvitdenvit Member Posts: 12
    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
  • ecclecticecclectic Member Posts: 176
    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_SingletonDavid_Singleton Member Posts: 5,479
    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).
    David Singleton
  • krikikriki Member, Moderator Posts: 9,121
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • krikikriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
Sign In or Register to comment.