Potential Deadlock Tool

Remco

Hi,

Is it useful to analyse a database with the Potential Deadlock tool, even though it is a SQL-database, or is this only useful for a C/side database.
It concerns a 4.0 SP3 database.

DenSter

If you are experiencing deadlocks, and you have a tool that can help you find them, yes I would say that is useful, wouldn't you?

Being pro-active and search for potential ones ahead of time is a noble approach, but I don't know if deadlocks are predictable enough for that to be useful.

Waldo

Remco, what are you trying to achieve?

Marije_Brummel

If that is a SQL tool then No, since SQL code is generated at runtime by the driver.

It that is a profiler or tracetool than maybe yes but most likely no.

You can use profiler to see when there are deadlocks and what users were causing them.

Most of the time deadlocks tend to disapear on systems that are tuned for performance and well maintained. If you are running 4.0SP3 that is definately not the case.

Try merging the indexes from 5.0 into 4.0SP3. That can be a major help. If you have customised mods, try to see how it is done and do it yourself in the mods.

Look at the Microsoft webcasts about how to change the code into better working with SQL.

If the entire system freezes every now and then for 3 - 15 minutes you might suffer from the parameter sniffing problem. I have written a whitepaper on how to handle that.

See if your hardware matches the requirements in the MS guidelines.

If after that you still have deadlocks than you have a challenge. Deadlocks can remain in long running transactions. MS generaly solves this with serialising the transaction but for some customers that is no solution.

Good luck!

Waldo

I can confirm Marks story.

One addition though ... to avoid deadlocking, always lock the tables in the same order.

Remco

Hi Guys,

Thanks for your answers. We are going LIVE within a few weeks with a big project. To optimize the performance and prevent deadlocks i'm trying to optimize the database before going LIVE. The customer is running on a SQL-database. I'm using the Potential Deadlock tool from the Navision Product-CD. From reading the manual I assumed that this tool was only for C/Side databases. But perhaps I misunderstood. I can imagine that it can also be very useful for customers using a Navision-database on SQL. It is what you say Waldo: with this tool I look if all processes that the customer uses lock the different tables in the same order. Any suggestions about in what order to setup the lockingrules are welcome (first table 37 then table 36, etc). All other suggestions about how to optimize the performance of the database are off course also more than welcome.
Mark, did Microsoft a lot of changes on the keys in 5.0. And if they did, what did they change: MaintainSQLIndex, MaintainSIFTIndex, Enabled??

Waldo

I did a similar project a few months ago, and what we did is:
- we made a list of the hot tables
- we checked the 5.0 version of those tables
- we pre-tuned our 4.0 tables like the 5.0 ones

Just like Mark suggested in his post:

Try merging the indexes from 5.0 into 4.0SP3. That can be a major help. If you have customised mods, try to see how it is done and do it yourself in the mods

Remco

I see in a standard 5.0 database that a lot of keygroups are disabled. Are they not used anymore in 5.0?? Is it advisable to also copy the SQLIndex, setup in the key, from 5.0 to the 4.0 SP3 database?

Thanx

Waldo

Definitally copy over the SQLindex column.

The Keygroups are there to be able to switch off in a more detailed level the keys you don't need. If needed, you have to switch them on. In you case, I would ignore the keygroups. Just enable the keys that are necessary for you.