Performance problems in NAV 5.00 SP1 on SQL2005

Marco_Ferrari

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

David_Singleton

Marco Ferrari wrote:

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.

matttrax

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.

davmac1

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.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

Marco_Ferrari

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.

pdj

What is the version no. of your SQL2005? It should be atleast 09.00.3200 (SP2 with update package 4)

azerty74

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

Marco_Ferrari

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