Performance question, NAV2009R2

xenon

Hello everyone.

First apologies for asking what has probably been asked before but I can't find anything appropriate.

I have a NAV2009R2 implantation, running classic client with 70 users, on a SQL2012 DB running on Server 2008R2. The server is a fairly high spec (although obviously 2008R2 limits to 32Gb) but all the discs are on fast RAID mirrors and so on. The DB is about 70Gb

I'm having some performance issues but I can't make and sense of performance monitor because even when I run a report which takes 10 minutes, the server appears idle. For the purpose of the test I'm also running the client on the server.

I realise that the client exe is not multi-threaded and therefore on a 8-core server even flat out it will not consume more than 12% of CPU resources but I'm seeing virtually nothing. I suspected that disc I/O would be the likely culprit but again that seems almost idle.

If the code was badly written then you still would expect to see the server busy executing poor code (??) but why do things take so long when the server appears to be almost idle?

Likewise if the indexes were bad - surely you'd expect to see the server working hard to overcome it?

In summary, where should I start looking? I'm an end-user and not a NAV or SQL expert but our reseller is not helping.

I realise also that the answer may be "it depends...." but any pointers would be appreciated....

Thanks

jglathe

Hi xenon,

if it's really an issue (sounds like it), talk to the guys from SQL perform. They cost, but they are worth the money from my experience.
What you see on the performance monitor sounds like the standard behaviour of the SQL server with NAV to me. Keep in mind that NAV usually fires lots and lots and lots of SQL statements against the server, most of them pretty simple. You have a statement overhead, though Also on the client side. The other thing is what the NAV client is doing when the report runs. Have you tried running it on the SQL server directly?

with best regards

Jens

xenon

Hi Jens

Yes, as I said I'm running the client on the server too so I can eliminate network issues etc. Even though the report (aged accounts receivable) takes a long time there is virtually no server activity on CPU or discs.

Thanks for your comment.

Ian

Tvis

Could you elaborate on the kind of performance issues you are having?

Is it just the aged accounts receivable report (report 120) that is an issue or do you have similar issues when running other reports?

Is NAV slow for the users when opening NAV and are you using Classic client / RTC or both?

etc.

xenon

Hi there

All users are using classic client and opening Nav seems fine. The aged accounts report was an example but most things seem to take a long time to execute. I just don't understand why I see hardly any server activity during these processes.

Tvis

Ok - you probably need a specialist to take a look.

You have not mentioned have much RAM you actually have on the server?

What kind of maintenance plans do you run for the SQL server?

As I understand your reply navigating in NAV is okay --> opening cards, looking at lists (page up/down and filtering) etc.

So what is slow is executing reports or?

xenon

Sorry I wasn't clear - when I said I was limited to 32Gb due to server 2008R2 Standard I should have pointed out that's what I have, however the cache hit ratio is > 95% so most of the stuff SQL wants eventually ends up in memory anyway.

It's hard to define exactly what is slow but most reports seem to churn away for ages.

I have a maintenance plan that drops and rebuilds indexes each week.

bbrown

So you're saying the Windows Server has 32 GB of RAM. But what is the "Maximum Memory" setting in SQL? I'd suggest about 28 GB. Also SQL 2008 R2 Standard supports up to 64 GB.

On another note, you say you maintaining indexes but are you also updating statistics? Also you say "drops and rebuilds indexes". I that what you are really doing? If so, that's a lot of work for nothing. You only need to rebuild/reorganize those indexes which are fragmented enough to need it.

None of these are guaranteed fixes for your problem. Since we don't know the causes. Just covering some basics.

sh

As you don't see any high resource utilization, please check your server's "Power Management" Settings (in BIOS/usually listed under CPU). This should be set to "High Performance" or "Maximum Performance" and not to "Balanced" or similar. If it's a proper server you should be able to set this via a management tool/web site online without a server restart.