Slow Sales Order entry

fredp1

Hi,
We are implementing Nav 5 sp1 with SQL 2005 sp3 (64 bit). The server has 10G of ram and has no load on. It has a couple of users at best and even with no users its still slow.
It has been a clean install with the platform 1 update KB956161 applied.

I'm creating a Sales order and when I type in the customer number, their is a delay of 3 seconds to populate the address delays and other fields on the Sales Order Header.
There are only 250 customers, a handfull of sales orders.
Even getting the sales order number generated is a couple of seconds delay.
The same slowness is apparent for a Purchase order. Their is a delay of a couple of seconds for a the PO number and about 3-4 second delay after the vendor number is entered. (there are only 1500 vendors).

SQL has Raid 10 volumne for the TransLog and tempdb and Raid 1 volumne for the data. The database is less than 1.5Gb
I want to get the sale order entry to be faster... 'almost instant' response.
How do I make Nav 'fly' on SQL?
Where do I start looking to find the bottle neck?
I know this is a $64 question, but if someone can point me to a guide to locate the problem?

Thanks

fredp1

I've should have mentioned that I have performed some maintenance plans on SQL server to make sure the rebuild/reorganise the indexes and to update the stats.

The database is definitely slower with windows authentication compared to database authentication.
I've been testing this by directly running Nav from SQL server.

Any ideas why database authentication would be faster than windows authentications and how to improve it!

kine

It can be because some functionality is checking for User name (e.g. because printing etc.).It means accessing the virtual tables which are reading data from Active Directory and this is very slow. Check that...

If your DB is slow, I recommend to look at HDD activity, Network activity, SQL settings, and to use SQL Profiler to check what is going on..

fredp1

Hi,

How do I check if AD is been accessed?

Has anyone found that there is a performance degradation when using windows login?
We have a Tectura Life sciences add on that I'm talking to Tectura about, but database authentication on our site is faster.

What events do I trace in Profiler? Can someone provide a guide on what to look for?

kine

You can use Client Monitor in NAV to check which tables are accessed (because the tables are virtual, you cannot catch them in the SQL profiler).

stryk

Hi!

I don't think the problems are coming from AD config or something. As reported, inserting records takes too long, fetching numbers from No. Series takes too long etc..
I guess the problems originate from wrong/missing indexes, insufficient setup/maintenance and maybe blocks or sub-optimal programming ...

You should use SQL Profiler to record all the poor performing transactions, if queries "consume" many Reads (e.g. >= 1000) this could indicate Index Scans, thus indexes need to be added or changed (or other things like RECOMPILE hints are necessary).

As this is a very small database which should not even show the slightest of all problems - hey, scanning the whole db should just take split seconds - I'm afraid that something is set up terrible wrong, so some more etails about the platform would be nice.

Which NAV "Security Model" are you running? If you change it, does this make a difference in performance?

DenSter

fredp1 wrote:

SQL has Raid 10 volumne for the TransLog and tempdb and Raid 1 volumne for the data. The database is less than 1.5Gb

That should really be the reverse. The log should be on a 2 spindle RAID1 (dedicated to the NAV database log only, no log files for any other database), and the data on the RAID10. I'm curious about the number of spindles in the RAID array, and the kind of disks that are used.

Your database should have exceptional performance with the numbers that you are providing. I know the vertical you are talking about too and that should not give you this kind of performance problem. Feel to me like someone went into your database and did some "tuning".

I'd also look at memory allocation in SQL Server setup (set it to static values, leaving about 2GB for the OS, AWE turned off), and make sure the server memory settings are correct (no /3GB or /PAE). Also look at degree of paralellism, which should be set to 1.

fredp1

Hi Stryk,
I'm going through your book at the moment to get a handle of the counters and what they mean.
The Platform is a Quad COre IBM Blade 3.2GHz Xenon, 10Gram and a SAN.
The OS is 2003 64Bit as is SQL 2005 9.0.4027
Nav is V5 sp1 Build 27191.
Security model is Standard. The Installation is fresh from the V5 CD with the platform 1 update applied.

Our Partner, has identified that their maybe extra querying in SQL for the windows login that is not been made when using database authentication. (I found it by comparing SQL profiler traces)
This should be a good start.

How do I force a RECOMPILE ? I assume you are talking about SQL queries/views not the Nav objects?

Thanks

fredp1

Hi DenSter,

I'll play with the volumes next week, but the degree of paralellism = 0. I'll change it again and see what the improvement is. AWE if off, however, do I need to specify the SQL server limit to be 8GB (to leave 2G for the O/S)?

Is there anything else I can check or should be looking for?

Thanks

P.S database authentication is about 50% faster across the board compared to our Windows login.
e.g a CTP calc will take 90secs for windows vs 60 secs for database authentication.

stryk

fredp1 wrote:

Hi Stryk,
I'm going through your book at the moment to get a handle of the counters and what they mean.
The Platform is a Quad COre IBM Blade 3.2GHz Xenon, 10Gram and a SAN.
The OS is 2003 64Bit as is SQL 2005 9.0.4027
Nav is V5 sp1 Build 27191.
Security model is Standard. The Installation is fresh from the V5 CD with the platform 1 update applied.

Our Partner, has identified that their maybe extra querying in SQL for the windows login that is not been made when using database authentication. (I found it by comparing SQL profiler traces)
This should be a good start.

How do I force a RECOMPILE ? I assume you are talking about SQL queries/views not the Nav objects?

Thanks

Hmmm ... with this platform etc. you shoud not experience the slightest hints of a problem ... strange ...

About RECOMPILE hints (briefly):

When queries are sent from NAV to SQL the first time, SQL Server generates an Execution Plan for this and caches it. If a similar query is sent afterwards, usually SQL Server will use the cached ExecPlan - not compiling a new one - to save CPU power.
But NAV has tendencies to provoke Index Scans too often (there are plenty reasons), so SQL Server decides to scan and "remebers" this ExecPlan any time, even though it might be possible to create a better plan (index seek).
An indicator for this issue is, if you detect poor performing queries in SQL Profiler, e.g. huge Reads; then if you copy to/assemble the "same" query in SSMS and - when executed - it is processed far better, e.g. Reads < 10, performing an index seek.
(P.S. to see the number of Reads in SSMS you need to enable the Statistic I/O, e.g. SET STATISTICS IO ON)
If that is the case - NAV "bad"/SQL "good" - then it could be feasible to force the recompilation of an Execution Plan, anytime the problematic query is fired.

With NAV it is possible to apply these RECOMPILE hints, or you could use "Plan Guides" on SQL Server site.
As you are using my little book - thanks for this! - you could see this described on pages 125 to 128.

So, if RECOMPILE is used the SQL Server does not use cached plan for this query and compiles a new one, thus getting a chance to perform "better".
But caution: excessive usage of RECOMPILE hints could cause high load on the CPU, causing a different perfromance problem!

Hope this helps a little.

Best regards,
Jörg

davmac1

With such a small database, it sounds like you either have a memory problem (key tasks are being paged) or a network problem. I have seen network performance crippled by driver problems - if this is the case, you should see slowdowns in other network related tasks- copying files across the network, etc.

fredp1

HI,

Our partner came back with a solution. There was some inefficient code when using windows login vs database login. (It was something to do with windows groups)
Because this partner also had an add-on that allows some very fine control of permissions, including field and Inventory type posting permissions, the problem was unique to their code unit.
It looks like the system is performing with a "snappy" response. i.e. the address details of a customer/vendor are populated without delay.

Thanks to all that replied.

Fred

DenSter

Thanks for the follow up Fred, and good to hear your system is performing properly.