NAV/SQL Troubleshooting - Blocks & Deadlocks v2

Administrator

NAV/SQL Troubleshooting - Blocks & Deadlocks (Directions EMEA 2008)
This is the "Presentation Package" from the session presented at "Directions EMEA 2008", including the slides and various TSQL scripts to implement the introduced solutions.

>>>
Abstract:
This session is an excerpt from Jörg Stryk's training-course "Advanced NAV/SQL Performance Optimization" and will explain in detail the different locking mechanisms in C/SIDE Server and SQL Server; advanced methods for automatic block-/deadlock-detection and -analysis; and how to avoid and fix blocking-problems.

With this session practical advices, examples and templates are provided, to be instantly used in any NAV (or other Dynamics product) installation to improve the systems' performance.
<<<

Further information: http://dynamicsuser.net/blogs/stryk/arc ... erver.aspx

http://www.mibuso.com/dlinfo.asp?FileID=958

Discuss this download here.

Miklos_Hollender

Thank you very much! I'm testing the scripts on a local SQL server called TM0697\MHSQL2005 but it seems the alert does not want to accept @performance_condition=N'TM0697\MHSQL2005:General Statistics|Processes blocked||>|0', what am I doing wrong?

stryk

Hi!

Can you create the Alert manually?

1. Right-Click in "Alerts" - "New"
2. Type in Name: "SSI: Block Detection"
3. Select Type "SQL Performance Counter"
4. Select Object "TM0697\MHSQL2005:General Statistics"
5. Select Indicator "Processes blocked"
6. Select Alert if Indicator "Raises above"
7. Type in Value "0"
8. Response: Tick/Select "Execute Job" - "SSI: Block Detection"
9. Options: Type in Delay of 10 Seconds

Does this work?

Regards,
Jörg

Miklos_Hollender

Yes, thanks. It's @performance_condition=N'MSSQL$MHSQL2005:General Statistics|Processes blocked||>|0',

So it seems instead of computername\serverinstancename it expects MSSQL$serverinstancename

stryk

Hi!

I got some feedback on the TSQL scripts, so I could further improve them. The download here includes still the "old" versions, the new stuff is available from my BLOG:
http://dynamicsuser.net/blogs/stryk/archive/2008/04/18/blocks-amp-deadlocks-in-nav-with-sql-server.aspx
Regards,
Jörg

martynyuk_v

Hi! Is there any solution for NAV 3.60 and SQL 2000? Everything stops when one try to create the new alarm. The main problem is a counter "Processes blocked" which is not present in General Statistics of this version of SQL.

stryk

With SQL 2000 you could monitor the "SQL Server:Locks" "Lock Wait Time" raises above 5000 (for example).
But that is not as reliable as the SQL 2005 "Processes blocked" and you may have to adjust the threshold ...

Administrator

NAV/SQL Troubleshooting - Blocks & Deadlocks (Directions EMEA 2008) v2
This is the "Presentation Package" from the session presented at "Directions EMEA 2008", including the slides and various TSQL scripts to implement the introduced solutions.

>>>
Abstract:
This session is an excerpt from Jörg Stryk's training-course "Advanced NAV/SQL Performance Optimization" and will explain in detail the different locking mechanisms in C/SIDE Server and SQL Server; advanced methods for automatic block-/deadlock-detection and -analysis; and how to avoid and fix blocking-problems.

With this session practical advices, examples and templates are provided, to be instantly used in any NAV (or other Dynamics product) installation to improve the systems' performance.
<<<

Further information: http://dynamicsuser.net/blogs/stryk/arc ... erver.aspx

Version 2:
TSQL scripts have been improved as shown in my BLOG

http://www.mibuso.com/dlinfo.asp?FileID=958

Discuss this download here.