Nav 2009R2 on sql 2005 performance issue

Business Central/NAV Technical Forum
1

The environment:

VMWare 5.5 on 2x IBMx3650M4, 2 CPU, 196 GB RAM, IBM Storewize v3700 SAS SAN.

SQL server is Windows 2008R2 with SQL 2005 standard. 12vCPU, 12 GB RAM. Dedicated SSDs 4x200GB SSD Raid10.

Client is RDS Windows 2008R2 on RAID10 Datastore on the SAN

The environment has been migrated from an old SBS2003 server to to a completely NEW install with dedicated vServers for everything (everything is new - the client wanted a clean install)

Users report that daily use (browsing in NAV etc) is faster than old server.

They report that batch invoicing is considerably slower than the old server. when batch invoicing runs in nav, all users experience slowdown in NAV…

performance monitors in vmware reports:

cpu load on both vHosts and VMs is never over 60%

Memory load is at 40-60%

disk load on all disks is almost none.

network load between server is less than 20 mbps

Historic data shows that the server uses more resources during nightly backup than during batch invoicing.

is it a DB issue?

I don’t know a lot about NAV - i’m pure infrastructure, but it falls back on me so I have to figure out where the problem is

2

an addition:

while on the old server, users reported that it was getting increasingly slower over two years…

3

Well, degraded performance could have zillion reasons, thus there are zillion potential solutions.

If you like you could check out this: http://dynamicsuser.net/blogs/stryk/archive/tags/NAV_2F00_SQL+Performance/default.aspx

Maybe you’ll get some inspirations.

It’s a guess - but an educated guess: probably you have plenty of Expensive Queries and Missing Indexes. You need to run SQL Profiler Traces and investigate the SQL Server’s procesure cache …

4

Thank you Jörg,

I knew there could be thousands of reasons (even Zillions :slight_smile: ) - But due to my lack of knowledge regarding NAV, I thought I would ask and see if there was someone who could say: “do this and that, and test”…

:slight_smile:

5

Yeah, unfortunately it’s not that easy. Again, I dare say, most of the problems originate from insufficient indexing. SQL Profiler and Procedure Cache coould help you to identify the problematic queries, then you might be able to add the required indexes or implement required code changes.

Secondly, there might be lot of blocking problems.

Here you could find some presentations, showing where problems could come from, how to detect and how to resolve:

Profiling/Indexing: http://www.mibuso.com/dlinfo.asp?FileID=1398
Blocks/Deadlocks: http://www.mibuso.com/dlinfo.asp?FileID=1488

Or watch online: http://www.youtube.com/user/navsqlperformance

Hope this could help you.