Table Locks with NAS

cschrieber

Hello!

Currently my organization is experiencing crippling table locks when we attempt to use NAS to insert more than 4 sales orders per minute. Is this typical? Our vendor expressed that this is very low, but also hasn't provided any suggestions on improving this.

Does anyone have suggestions on what might be affecting this throughput? We have very good hardware in place, and I'm confident that the processing power on our SQL server is not a bottleneck.

Any suggestions on reducing the record locks would be greatly appreciated.

Thank you!

Sog

What version of NAV has your company implemented?

cschrieber

From Help:
Version NAVIGATOR 5.00.01,
NA Dynamics NAV 5.0 (5.0 SP1)

We are in the process of converting to 2009. We expect to be done in about 12 months. One office has been converted, though is not in production yet. We have 5 to go, with the largest ones being saved for last.

Chad S.

David_Singleton

The first place to start is the hardware. Unfortunately when people say that the server is not the bottle neck its then the first thing I look at. :whistle:

So how big is the db, how many lines per order, server specs, and importantly what is causing the locks. There must be other tasks running that are doing this.

ara3n

When I do integration. The data first comes into staging tables and then one Order is created the data from staging is removed to archive and the transaction is committed.

This minimizes lock time and users are not aware that orders are created.

David_Singleton

ara3n wrote:

When I do integration. The data first comes into staging tables and then one Order is created the data from staging is removed to archive and the transaction is committed.

This minimizes lock time and users are not aware that orders are created.

I would have thought this to be the only logical way to do it, but re-reading the original post, it is possible that they are directly creating the orders buy direct validation of 36/37 via NAS. That's quite scary.

cschrieber if you are not using staging tables, then Rashed's advise is probably the correct answer, and no amount of hardware is going to fix this bad design.

stryk

Hi,

maybe this helps you a little to identify the (b)locking issues:

http://dynamicsuser.net/blogs/stryk/archive/2010/05/19/decisions-spring-2010-nav-sql-performance-blocks-and-deadlocks.aspx
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

Regards,
Jörg

davmac1

I have created orders both ways with NAV.
One was thru an inherited web site with NAS updating the NAV database and I had to upgrade it to SQL Server. Once we resolved the error handling, it worked fine, but the initial troubleshooting was much more difficult. Among other things, you have to make sure the locking sequence is the same as the data entry forms.
The other was mass updates using staging tables and that handled high volumes without a problem. I used the job queue and batch sizes to control how much was created at any one time. This method is quite capable of handling 100K plus orders per day without impacting the local users.

cschrieber

Hello!

Sorry for the delay in responding. I wanted to make sure I got as much information about our environment in one post, to prevent a lot of back and forth.

Our hardware is decent. An upgrade is expected in the next few months. Here's what we have today:

Windows 2008 Enterprise Server R2 x64
SQL 2005 Enterprise x64
4 Xeon MP 3.00 GHz Processors
64.0 GB RAM

Separate data, log, and tempdb drives backed by RAID 10 EMC San LUNs.


EDIT: Our DB is about 90GB, and grows by about 10% per month. cs


On staging tables:
I know what you are talking about, but I'm not sure where you are talking. Should these staging tables be internal to NAV (residing inside the actual NAV db) or are these outside of the NAV realm? In asking the question, I think I know the answer, but I'l wait for some direction there.

My gut feeling is that we are directly using the validates on 36/37, and just about everything else. The high level of our system is like this:

Users enter orders directly via the client. This can be from 20-100 people, depending on the time of day.
We also have 2 other entities that create orders: web site and snail mail. The snail mail is scanned in, and OCRed, and we have about 80% success in digitally entering these orders. Web and Mail stuff ends up going through the exact same process:

An "order" is created using XML. The XML is placed in an MSMQ. NAS reads from this Queue, and picks up the next order. That order is then entered via NAS, and a response is sent to another queue indicating success or failure. We have a couple of different queues, depending on the type of data being entered. I believe we have 3 NAS instances running at this time, and can turn on a 4th when necessary.

The NAS instances are set to pick up 1 item every 15 seconds. When we exceed that rate, regardless of the number of NAS instances running, we begin to experience blocking. The faster we try to pick them up, the more items end up blocked.



Based on the comments thus far, and based on our process, it seems that we are almost definitely writing directly to 36 and 37 (and, again, everything else that updates).

We've begun trying to "de-stress" the system by offloading some functionality from NAS. For example, I have one process that allows a user to update their payment date via our website. I basically read data from our read only copy of NAV, save changes to a staging table, then once a day a process runs to move the data from that staging table into a completely flat table in NAV. A Process Only report is then run to actually move the data into the appropriate journals.


Can someone explain to me the staging table strategy, in a nutshell? Is the process I'm currently using similar to what is meant by "staging tables?" If so, how is that maintained in real time (or is it?!?) ?

Thanks very much for the help.

Chad Schrieber
Joyce Meyer Ministries
Fenton, MO USA

ara3n

does the xml message that come from msmq contain one order or multiple orders?

I suggest to separate the msmq message to one order if their are not and it should minimize the blocking issues.

cschrieber

Our orders go in one at a time.

ara3n

How long does it take to process one message?

Is there a reason why you have 3 NAS running? Do you need them to process as fast as possible?

cschrieber

We have 3 NAS running because they run different processes. Each one checks a different Message Queue for items to process. (Basically, we were told, "this is the way you do it...")

We were given the impression that more NAS clients running equated to more orders processed, but reviewing the code, it appears that it is single threaded, so I don't believe more equals faster.

As far as how long it takes--I don't have that data any longer. I'll see if we can get some stats on that. What we were told was it should be able to do "several per second."

Thank you!


Chad S.

cschrieber

Regarding my previous post and "several per second..."

In discussing with my teammates, we all recall our vendor saying that, but we also recall he quickly changed the subject when we mentioned that we get 1 per 15 seconds through...

We thought "many per second" seemed very high, but expected something closer to 1 per second.

Chad S.

ara3n

Each NAS is one thread so adding mulitple NAS will increase the thread to monitor the MSMQ.

Nav waits for 10 seconds (based on setup in file->database->alter->Advanced tab.


I suggest to increase that to 20 seconds depending on how long it takes to create the order.
The goal is to process the orders in a manner that does not starve other users using the system.

I'm also assuming that as each message is processed and each order is created that NAS Commits the transaction.

davmac1

Staging tables that I have used are custom NAV tables that other SQL processes write to directly.
I then used a single job queue run by NAS to process the data in the staging tables. This can include sales order tables, payment tables, purchase table, item transactions, etc.
The job queue is used to run codeunits primarily at set intervals.
Since it is one queue, it eliminates locking problems.
A more sophisticated solution could be used in a larger environment, but you environment seems like it would fit well within what the above method will handle.

Something like online order entry could be managed using the application tier if you need an interactive response.
If you are doing a lot of posting, then running the posting processes thru a single thread like a job queue will cut down locking problems.

cschrieber

stryk wrote:

Hi,

maybe this helps you a little to identify the (b)locking issues:

http://dynamicsuser.net/blogs/stryk/archive/2010/05/19/decisions-spring-2010-nav-sql-performance-blocks-and-deadlocks.aspx
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

Regards,
Jörg

Thanks, Jörg! Your blog is a favorite amongst my DBAs.

Regards,
Chad Schrieber

cschrieber

davmac1 wrote:

Staging tables that I have used are custom NAV tables that other SQL processes write to directly.
I then used a single job queue run by NAS to process the data in the staging tables. This can include sales order tables, payment tables, purchase table, item transactions, etc.
The job queue is used to run codeunits primarily at set intervals.
Since it is one queue, it eliminates locking problems.
A more sophisticated solution could be used in a larger environment, but you environment seems like it would fit well within what the above method will handle.

Something like online order entry could be managed using the application tier if you need an interactive response.
If you are doing a lot of posting, then running the posting processes thru a single thread like a job queue will cut down locking problems.

David, we're going to look into our code a little closer to ensure this isn't actually what's happening. But, all indications are that the orders (XML in the MSMQ) are being processed and written directly into the tables. I can't readily find where any interstitial table gets used, or where the data actually sits in a table while waiting to be processed.

Thanks for the info!

Chad Schrieber