Deadlocking of standard routines

oldwarrior · 2012-05-10

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

David_Singleton · 2012-05-10

What version of Nav. Pre or Post VSIFT.

oldwarrior · 2012-05-10

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

kapamarou · 2012-05-10

oldwarrior wrote:

I will try this week to drop a few of unnecessary indexes on the Ledger tables

Be very careful with this.

oldwarrior wrote:

There were no major changes in the standard routines that handles booking.

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.

bbrown · 2012-05-10

oldwarrior wrote:

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

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

oldwarrior · 2012-05-11

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

David_Singleton · 2012-05-11

oldwarrior wrote:

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?

davmac1 · 2012-05-11

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.

Dan77 · 2012-05-12

What SQL version?

oldwarrior · 2012-05-14

Hello,

Are you using dimensions or reservations or any of the typical block/deadlock tables?

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

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

Just posting never on the data entry.

davmac1 wrote:

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.

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.

Dan77 wrote:

What SQL version?

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

bbrown · 2012-05-14

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?

David_Singleton · 2012-05-14

David Singleton wrote:

Are you seeing deadlock error messages in Navision??

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.

oldwarrior · 2012-05-14

David Singleton wrote:

Are you seeing deadlock error messages in Navision??

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.

bbrown wrote:

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

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)

bbrown wrote:

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

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.

bbrown wrote:

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.

Database is approximatelly 600 GB in size.

bbrown wrote:

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

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 :-)

bbrown wrote:

4. What database maintenance is done regularly?

[/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

bbrown · 2012-05-14

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.

oldwarrior · 2012-05-14

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

bbrown · 2012-05-14

Is the 600 GB total data files size or used space? I work regularly with a database that while a bit smaller (540GB/420 used), has a much larger GL entry table (216 million records). About 90% is from the inventory activity. Value Entry is 114 million records.

In staying ahead with performance, I've found a few things that have been helpful. (no particular order and not all inclusive)

1. Having transaction log on a good fast dedicated disk. This is often over looked. The write performance of the transaction log can be the single most important disk system performance factor. Memory will buffer reads from the data disk, but not the transaction log.

2. Reducing unneeded indexes on ledger (and related tables). (This has probably helped the most)

3. Index maintenance (you are already doing this)

bbrown · 2012-05-14

oldwarrior wrote:

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

OK. I'm just trying to better understand your problem so I don't give you misleading advise.

Are those other locations connecting over terminal server (or similar solution)?

Are you seeing equal conplaints from both local and remote users?

oldwarrior · 2012-05-15

bbrown wrote:

Is the 600 GB total data files size or used space? I work regularly with a database that while a bit smaller (540GB/420 used), has a much larger GL entry table (216 million records). About 90% is from the inventory activity. Value Entry is 114 million records.

Total database size is around 550 GB the rest is free space.

bbrown wrote:

1. Having transaction log on a good fast dedicated disk. This is often over looked. The write performance of the transaction log can be the single most important disk system performance factor. Memory will buffer reads from the data disk, but not the transaction log.

2. Reducing unneeded indexes on ledger (and related tables). (This has probably helped the most)

Thanks bbrown for this two very good ideas. I will try them out for sure. How much performance did you gain by removing the indexes from the ledger tables?

bbrown wrote:

OK. I'm just trying to better understand your problem so I don't give you misleading advise.

Are those other locations connecting over terminal server (or similar solution)?

Are you seeing equal conplaints from both local and remote users?

The location in which we recently settled is not connected over terminal server, the other regions are. Both have similar problems. One information that I got last week was that the region that settled the last (finance department) has network issues. The average ping values are around ~ 10 ms. What do you think that this latency problems can also be a problem?

Thanks
OldWarrior

davmac1 · 2012-05-15

How many lines in your journal batches?
The ledger table is locked when it posts a batch so it can generate sequential entry numbers.

FDickschat · 2012-05-15

oldwarrior wrote:

The location in which we recently settled is not connected over terminal server, the other regions are. Both have similar problems. One information that I got last week was that the region that settled the last (finance department) has network issues. The average ping values are around ~ 10 ms. What do you think that this latency problems can also be a problem?

10ms is not bad but this is not the main question: What is the data rate on that WAN line? 2 MBit? If the line is blocked from e.g. a print job or simply 10 finance users working and someone tries to post a journal it will be slooow! During this time tables are locked unnecessarily long and so all the other users experience excessive blocking.

Deadlocking of standard routines

Comments

Categories