Performance problems in NAV 5.00 SP1 on SQL2005

Marco_FerrariMarco_Ferrari Member Posts: 53
edited 2008-10-02 in SQL Performance
Hi all, we are experiencing a great performance problem in NAV 5.00 SP1 running on a SQL 2005 database. The problem arises when a user applies a filter on any standard form. Sometimes it has to wait more that 100 seconds in order to see the next record list when it press PagUp or PagDown or simply using the mouse trackball. Those filters were applied also before the porting, on a 4.03 database on SQL2000 and did not have problems.
Does anyone experienced the same problems?
Marco
Marco Ferrari
Microsoft Certified Trainer
Cronus.it

Comments

  • David_SingletonDavid_Singleton Member Posts: 5,479
    Hi all, we are experiencing a great performance problem in NAV 5.00 SP1 running on a SQL 2005 database. The problem arises when a user applies a filter on any standard form. Sometimes it has to wait more that 100 seconds in order to see the next record list when it press PagUp or PagDown or simply using the mouse trackball. Those filters were applied also before the porting, on a 4.03 database on SQL2000 and did not have problems.
    Does anyone experienced the same problems?
    Marco

    It could be a hundred different things, and without more information, any anser will just be a random guess.

    But....

    If I had to make a random guess, I would start by checking if you have any flowfields on the list form. That is a good place to start.
    David Singleton
  • matttraxmatttrax Member Posts: 2,309
    Yep, flowfields or variables that are calculated and displayed on the form. Anytime it has to calculate something it can take a while to move to the next record.

    Past that, try running a SQL trace / running Client Monitor to see where it is spending all of it's time.
  • davmac1davmac1 Member Posts: 1,283
    There is a SQL 2005 patch that allows a new trace flag specifically for NAV. This makes the "like" behavior the same as SQL 2000. If your filter is doing a "LIKE" type operation, you could try this fix.
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Marco_FerrariMarco_Ferrari Member Posts: 53
    Hi, no I talked about standard forms, so there are not any FlowField on the list form and many of those forms on which we have a problem are without code inside. There are also forms with code inside (as the form 232 of Customer Applying Entries) that have the same problem, but are in any case completely standard, without customization.
    We installed the SQL Server patch (Trace Flag) but nothing changed.
    Marco Ferrari
    Microsoft Certified Trainer
    Cronus.it
  • pdjpdj Member Posts: 643
    What is the version no. of your SQL2005? It should be atleast 09.00.3200 (SP2 with update package 4)
    Regards
    Peter
  • azerty74azerty74 Member Posts: 82
    Just a quick question: How did you apply the trace flag ?

    Because, adding it to the startup parameter in the service requires a restart for it to work :)
    Debugging is twice as hard as writing code. Therefore if you write the code as cleverly as possible you are by definition not smart enough to debug it.
  • Marco_FerrariMarco_Ferrari Member Posts: 53
    We have done it during the weekend. I will see the exact version of SQL and I will reply as soon as I can.

    Thanks
    Marco Ferrari
    Microsoft Certified Trainer
    Cronus.it
Sign In or Register to comment.