mibuso.com

Hello together,

I am experiencing dead locking problems with the standard functionality. Like posting of invoices, Payment journal bookings etc.
There were no major changes in the standard routines that handles booking. According to my knowledge the standard routines doesn't
deadlock as all the tables are locked in advance. Currently I cannot explain the behaviour, may does somebody have any suggestion
for a starting point? If it would be a hardware problem it would be slower but shouldn't deadlock, or?

Most problematic tables are:
G/L Entry
VAT Entry
Reservation Entry

Thanks in advance,

OldWarrior

What version of Nav. Pre or Post VSIFT.

Version of Navision is 2009R2 we are using the classic client. I will try this week to drop a few of unnecessary indexes on the Ledger tables
but I don't expect that the problem will be solved with it.

Thanks
OldWarrior

Be very careful with this.


One line would be enough... Also, there could be some code on some Validate trigger.

I would start analyzing the locking order to see what happens. If you remove some indexes maybe you will reduce the duration of some transactions but this does not mean that the problem will be solved. Maybe it will appear less often. I've solved such problems for some customers but it requires a lot of work and patience.

Are we talking about "deadlocks" or "excessive blocking"?

Hello,

No real deadlocks not expensive locks I am watching them since two weeks in the sql profiler.
Can this problem be connected to the size of the database, amount of rows in the tables or the hardware?
i.e. G/L Entry has 90.000.000 records.

Thanks
OldWarrior

Are you seeing deadlock error messages in Navision? Unless this is actually affecting your users, there are a lot more important things you need to address before deadlocking. Keep in mind that without completely restructuring your code the only way to remove deadlocks is to increase blocks, which in many cases can make things worse. On the other hand if you can focus on reducing locks by increasing speed, many deadlocks will simply vanish by them selves. Are you using dimensions or reservations or any of the typical block/deadlock tables?

Importantly what areas are the users reporting the deadlocks, is it more data entry or posting?

Have you added more users?
One of the things that can cause problems is if you are using terminal servers that are having memory constraints - not enough to handle all users without swapping tasks.

Check your memory usage on all your servers and the overall performance.

What SQL version?

Hello,


Dimension are heavily used like 4 - 8 on each booking also reservation entries.


Just posting never on the data entry.


We don't have much users on terminal services. Most of them are on locale clients. The performance of the terminal server is fine.
The memory usage on the sql server I already checked and the SQL Cache Hit Ration is >95 percent.



SQL Version is 2008 R2.

On thing that I saw was that the avg. disk queue length is more or less constantly > 3 or much more. Maybe this could also be connected to it.

Thanks for the help
OldWarrior

OK, so we're really talking about blocking and not deadlocks. Different problems with different diagnostics and resolutions.

Can we get some basic information?

1. Describe your SQL Server hardware and how it's configured. Most important describe the disk systems.

2. How large is the database? I see you mentioned having 90 million GL records. That, in of itself, I don't see as a problem.

3. Is it all posting that is slow? Or just certain functions?

4. What database maintenance is done regularly?

Oldwarrior, can you post a screen shot of the deadlock error message that you are getting in Navision please. Actually you can just CTRL-C and paste into a message when you get the error. Get your users to do it, basically as soon as they see the dead lock message open a new email and copy paste the error text.

In progress, I already informed the users that they should send me a screenshot the next time when they have an error. Nevertheless it will take me a few days to gather it.


HP Server
2dual processor Xeon 2,4 GHz
64 GB DDR3 memory
Gigabit ethernet
Windows Server 2008 R2

Disks:
System = Raid 10 FC 15k Disk Group: 1
Log = Raid 1 Disk Group: 2 --> slow disks
System = Raid 10 FC 15k Disk Group: 1
Master, Model, etc. = Raid 10 FC 15k Disk Group: 1
Temp DB = Raid 10 FC 15k Disk Group: 1
Storage is HP Eva (don't know the actual model. 4 years old)


I was watching the problems through the SQL Server profiler TSQL:Locks and there I see that the system records deadlocks. We have also a few wait timeouts but they are not the majority.


Database is approximatelly 600 GB in size.


The performance is not the biggest problem. The point is that we have 10 people working in the finance which are executing their jobs and getting the message that G/L Entry is locked or other tables. This forces them to restart the batches. Ofc it could be faster

[/quote]
A daily job is running through the SQL Agent that according to the fragmentation of the indexes reorganizes or rebuilds them.

Thanks for the help
OldWarrior

First you say "...We have also a few wait timeouts but they are not the majority...". Then you say "...and getting the message that G/L Entry is locked or other tables...". These statements seem to be in conflict with each other.

Users getting messages that tables are locked are wait timeouts caused by excessive blocking.

Maybe I was not precise enough. The company is shared over several locations and so I cannot just go into the other office to check with the users.
My statement was regarding the SQL Profiler Log there I have a bunch of Deadlocks recorded but just a few Lock:Timeouts.

Thanks
OldWarrior