Database Sessions, NAV performance

gadzilla1

Hello all,

When we experience performance issues with NAV I usually check database information, sessions, current sessions to check if a user is performing some task that is bogging down the system. Are there other ways to identify processes that slow the system? I'm relatively new to NAV admin.

Any advice is much appreciated. Chris

kine

SQL or Native DB?

gadzilla1

Thanks for the reply Kamil. We run NAV 4.03, SQL 2005.

garak

Are there other ways to identify processes that slow the system

You are run under sQL So you can use the SQL Profiler to analyse the user transactions.
Also, to check your hardware you can use the good old perfmon.
Also on SQL you can use the Actiitiy Monitor (here you see also blocks) and with a little sql query (over sys.dm_tran_locks) you can get the actual locks.

And other tool to identified the performance problems is the ClientMonitor (Check ToolsCD). There are also a perfomance manual on ToolsCD. This is a god starting point.

Read this post and follow the link (need PartnerSource access)

viewtopic.php?f=23&t=27702

Regards

gadzilla1

Thanks for all of the replies...very informative.

Could you explain the following...'and with a little sql query (over sys.dm_tran_locks)'...where do I run the query and what is that query syntax?

Chris

garak

SQL Manager -> New Query -> select * from sys.dm_tran_locks where HERE YOUR CONDITIONS

Also take a look behind the other sys.dm_tran_XXXXX tables / views

gadzilla1

Thank you very much - I will try this today. Chris

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

stryk

Hi!

Have in mind that blocking problems could be worsened if you are running a "buggy" NAV client! Some verrsions of 4.0 SP3 do not properly release locks, thus severe blocking issues may occur. Check Waldo's BLOG to verify your version: http://dynamicsuser.net/blogs/waldo/archive/2008/09/13/platform-updates-overview-3-70-b-nav2009-ctp4.aspx

If you want to establish some kind of automatic Block Detection, I dare to refer to my BLOG: http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

Besides blocking also other issues could cause performance problems: I/O caused by "bad" queries or insufficient hardware. Make sure that default "Index Hinting" is disabled (depends on NAV version) and use the SQL Profiler to search for queries consuming a lot of Page READS (e.g. > 1000), as this could indicate sub-optimal indexing or filtering, etc..


Hope this helps you.

Best regards,
Jörg