NAV 2009 on SQL 2008 R2

dmauldin

We just upgraded from Nav 4.0 on SQL 2005 to Nav 2009 on SQL 2008 R2. The performance seems to have dimenished. After opening the contact card then pressing F5 to show the list, it takes a long time to display the data. Also as we move from record to record it several seconds to move. We have the sort on the list form set to 'Company No,Company Name,Type,Name'. There wasn't a problem before the upgrade. It almost seems like secondary keys aren't working like they should.

I also had a user running the aged A/P report and takes a lot more time than before the upgrade. The Vendor Ledger Entries in the report are sorted on a seconday key.

Has anyone else experienced this, and if so how did you fix it?

davmac1

Was this a full upgrade where you moved your customizations into NAV 2009?
Usually a list form is slow because of the performance of flowfields.
This could be caused by a poorly performing SQL Server system or flowfields that take long to execute.
I recently ran into a problem where a count flowfield was taking a long time to execute.
If you are happy with your SQL Server performance overall, then you could experiment with dropping flowfields until you find the one or ones causing the slowness.

Alex_Chow

The way SIFT is calculated in NAV4.0 and NAV2009 is very different. There are other differences of how SQL query is executed between the versions.

Most likely, you'll need to re-tune NAV to be within the NAV2009 SQL 2008 environment.

I hope you didn't just do the upgrade on the live environment.

dmauldin

It is a full upgrade. We merged all the objects. If I change the sort on the list to the primary key, it's not slow at all, it runs just as it should. But when I change the sort to the secondary key, it takes several seconds to load. The AP report could just be due to the amount of data, but I'm not certain of that.

We've been looking at SQL performance and looking at possible changes, but we're not sure what those changes should be. We tried creating a full text index, but it didn't seem to help.

dmauldin

Alex Chow wrote:

The way SIFT is calculated in NAV4.0 and NAV2009 is very different. There are other differences of how SQL query is executed between the versions.

Most likely, you'll need to re-tune NAV to be within the NAV2009 SQL 2008 environment.

I hope you didn't just do the upgrade on the live environment.

We did the live upgrade over the weekend. We didn't find this problem until after the live upgrade. For now, I've changed the sorting to the primary key to allow it to load faster, but our customer service department needs it sorted on the secondary key for various reasons.

What kind of re-tuning should be done?

We started the upgrade process in January. We did several test upgrades and had about 3 weeks of testing before we did the live upgrade. Besides live, we also have a development and test environment.

We did alot of testing, but didn't expect something as simple as a lookup to cause problems. As far as the AP report, this may not be an issue, but it does run slower than before the upgrade, but that could be due to the report changing from 4.0 to 2009.

Alex_Chow

Did you upgrade to RTC? By default, RTC will run slower than the classic client...

Again, the execution plan is different between 4.0 and 2009, so there's no straight answer to "what kind of re-tuning should be done".

Run the client monitor and the SQL Profiler to pinpoint exactly where the performance problem occurs.

dmauldin

We are not using the RTC right now. We will eventually go there after I get our forms transformed and formatted.

Are there any suggestions you could make to take a look at? Any commons areas that could cause the performance to decrease?

SPost29

We are having the same problem. We are in Test with a Dec2010 go live We just upgraded from 4.3 to 2009 SP1
from SQL2005 to SQL2008
Even an unmodified form like the Chart of Accounts when I use the drill down it takes minutes to open.
Go back to the drill down again and it is much much faster
Is SQL learning?
Steve

ara3n

no it probably means you sql server doesn't have enought RAM and thus on first run reads the data from harddrive, and on secon read it's cached.

Alex_Chow

SPost29 wrote:

We are having the same problem. We are in Test with a Dec2010 go live We just upgraded from 4.3 to 2009 SP1
from SQL2005 to SQL2008
Even an unmodified form like the Chart of Accounts when I use the drill down it takes minutes to open.
Go back to the drill down again and it is much much faster
Is SQL learning?
Steve

I think this is a different issue than what dmauldin is talking about.

Alex_Chow

dmauldin wrote:

Are there any suggestions you could make to take a look at? Any commons areas that could cause the performance to decrease?

Have you used the client monitor and/or the SQL profiler? The information given by these tools will be better than any advice I can give you.

davmac1

There are people on this forum who make their living by correcting NAV SQL performance problems.
We can give you general help, but you do not seem to know enough to present all the information needed for any good suggestions.
In the medical profession, doctors do not hesitate to call in a specialist. In consulting, we can learn from that and do the same.
Rather than have an unhappy customer, I recommend you strongly consider this option.

bghubr

Make sure you have statistics turned on. This is critical in sql performance,
even more important than index defragmentation

kriki

bghubr wrote:

Make sure you have statistics turned on. This is critical in sql performance,
even more important than index defragmentation

If you mean "autocreatestatistics" and "autoupdatestatistics", I don't agree. This means that SQL can delay a SELECT because it finds that the statistics of an index is out-of-date and decides to recalculate statistics first. This can create unexpected (and random) delays. I read on some sql blogs that this can become a big performance problem.

There is only one thing that beats a good "index rebuild" maintenance plan (and not index defrag), and that is a better "index rebuild" maintenance plan. Best is to do it each night if possible.

BTW: remember that you also need to have defragmented disks and also the transactionlog should NOT be fragmented internally.