Session Monitor for SQL Server 2005

  • Article

Session Monitor is not a new tool, but it did stop working with SQL Server 2005. So I think that a lot of people stopped using it, which is a shame. The things that were broken now work again, and I would recommend anyone with any kind of blocking problems to implement this tool. With this post I hope to get some people to use it again, and to provide a new tool to anyone who did not know it exists.

Purpose of the tool

The purpose of the Session Monitor tool is to show a live picture of any current blocks in the Microsoft Dynamics NAV database. And to show it in a familiar environment, i.e. a NAV form. It is an extension of the information you get when going to File -> Database -> Information, and drill down on Sessions.

What the tool does:

  • Shows live information about who is blocked by whom
  • Shows which user is at the top of a blocking chain
  • Shows if a user is involved in blocking, i.e. either being blocked or is blocking someone. So you can filter out all sessions that are not involved in blocking
  • Shows the wait resource, i.e. on which table the block is

This is how the Session Monitor shows a blocking situation:

SessionMonitor

As default it refreshes every second.

Some of the useful features of Session Monitor are:

  • Filter on "Involved in Blocking". Then, as soon as there is a block it will show up. This is especially useful when you have more sessions that can fit on the screen, which means that there might otherwise be a block outside of the form.
  • Wait Resource tells you on which table the block is, which helps you get some idea about in which area of the application it is.
  • Originating blocker ID shows the head-blocker. This is useful when a user is blocked by someone who is blocked by someone else, etc.
  • Wait Time shows how long the block has been there.

How to get it to work

As mentioned, the tool stopped working in SQL Server 2005. But there are corrections for this now. First, download the tool, which is part of the "Performance Troubleshooting Guide", available for download here (PartnerSource login required):

Microsoft Navision SQL Resource Kit

The original tool contains a SQL query (Session Monitor (SQL Server).sql) which is the part that doesn't work for SQL Server 2005. So don't run this query.  Run the query from this KB article instead (PartnerSource login required here too):

KB 933042 - Error message when you use the Session Monitor feature in Microsoft Dynamics NAV: "Invalid length parameter passed to the substring function"

Note!: Run the query on the NAV database - not on Master, or any other database you have on SQL Server.

In NAV, import the object "Session Monitor (SQL Server).fob" from the Performance Troubleshooting guide.

Make one change - otherwise in some cases it will fail with an INSERT error:

Design codeunit 150011 "Session Monitor Mgt. (SQL Srv)", and replace this line:

RefreshUserActivity()
SELECTLATESTVERSION;
IF Session.FIND('-') THEN
  REPEAT
    SessionTmp := Session;
    // SessionTmp.INSERT; Remove this line and replace with:

    IF SessionTmp.INSERT THEN ; // New line  
  UNTIL Session.NEXT = 0;

Those are the changes needed to get it to run on SQL Server 2005,

Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.