problem with locking after client upgrade (SQL database)

Doomhammer

Some time ago, we did technologic upgrade on customer's database, which runs on SQL 2000.
previous version was 3.70B, we upgraded database to version 4.0 SP3 - we opened 3.70 DB in 4.0 client. we did not merge any objects modifications from 4.0

after few weeks, users started to complain on issues with locking. before upgrade, there were no such issues.
locks appear in process of posting sales documents and when creating or modifying documents.
locks appear at table document dimensions on most cases.

these locks are short - they appear and disappear on few seconds (i watch them in session list)

is there something we missed during upgrade? is there anything which is needed to set up after this kind of upgrade?
thanks in advance

Belias

there are a lot KB for your version, you should try to browse partnersource for them.
Otherwise, the way sql executes queries changed from your version to 4.sp3: what is your build no.?

p.s.: you can experience slow lists, for example. in this case, be aware to setcurrentkey instructions, because they can lead sql to choose the "wrong" key

Take a look here http://www.mibuso.com/forum/viewtopic.php?f=34&t=33102

Doomhammer

client build is 25143

i took a look on database properties. what will happen if i'll check option Allways Rowlock?

i did not notice slow lists or something else - i think, performance on lists and other data browsing is not slow.

Belias

as you can see, the Dynamic cursor was introduced from build no. 26414, therefore you don't have problems.
Anyway, maybe some KB fixed your problems (your build is the oldest for 4sp3 if i remember well).
you should check the list on waldo's blog or somewhere else

afarr

Most of our Nav 4.03 clients use build 23305, but you're right that the earliest 4.03 build listed on Waldo's blog is 25143, while the latest is now 28541.

The "Always Rowlock" option is not usually recommended. The following explanation is extracted from this page:
http://plataan.typepad.com/microsoftdyn ... t-the.html

Any application needs to lock records that it is working with. Individual rowlocks require memory to maintain, so some times, SQL Server will convert many rowlocks into one page lock or even a table lock when it thinks that it can use its memory better in other places.
• The up side of this is that it is a big reduction in lock maintenance and memory required for many rowlocks, which instead will be replaced with page locks (so "Always Rowlock" = FALSE gives improved performance).
• The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users (so "Always Rowlock = FALSE" gives reduced concurrency).

jannestig

Hej,

We had similiar issue with a very large installation, we approached the situation by firstly applying the latest 4.3 update build then
doing a technical upgrade of the client to 5.1 where a lot of performance gains are provided as well as the introduction of index views.

It is just something else to look at.

Doomhammer

unfortunately, upgrade to NAV 5 client is not an option because customer refused to buy SQL 2005 :-(

I'll take a look on newer 4.03 client builds. thanks