NAV/SQL Troubleshooting - Blocks & Deadlocks v2

Administrator
Administrator Member, Moderator, Administrator Posts: 2,506
edited 2008-07-10 in Download section
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.

Comments

  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    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
    stryk Member Posts: 645
    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
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Miklos_Hollender
    Miklos_Hollender Member Posts: 1,598
    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
    stryk Member Posts: 645
    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
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • martynyuk_v
    martynyuk_v Member Posts: 1
    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
    stryk Member Posts: 645
    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 ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Administrator
    Administrator Member, Moderator, Administrator Posts: 2,506
    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.

Categories