Question Status

Verified
Ganriver asked a question on 8 Apr 2016 2:14 PM

Hi Friends,


Once a while, we had problem of blocking. I try to find user associated with blocking SPID, from AX online user form, it's hard since some time/some users it shows SPID, but, most of time, I can't find it.

From SQL, since it's under AX system account, so, I cant' get it from there.


Any suggestions?

Reply
André Arnaud de Calavon responded on 8 Apr 2016 3:13 PM

Hi Ganriver,

In SQL management studio you can open the activity monitor and view the processes. You have information who is the head blocker. The SPID for this process is also visible in the online users form in AX 200. You should be able to combine these.

In addition you can view the SQL statements (SQL activity monitor) which are used by the head blocker. This might give some idea which process is being started.

Reply
Ganriver responded on 8 Apr 2016 3:25 PM

Hi Andre,

In SQL, I can only see AX system user, since it's all under it regardless of end user.

In Online user form, that's what I said, not all SPID showed for users.

River

Reply
André Arnaud de Calavon responded on 8 Apr 2016 3:32 PM

There are some SQL processes which might not be related to AX. Then you indeed will not see those in the online users form. You can see the database name in SQL, also the SQL details (statement). It is sometimes indeed cumbersome to find the user/process which is causing the block.

Reply
Ganriver responded on 11 Apr 2016 9:10 AM

Thanks, Andre.

I am seeing a sql statement 'update ledgertrans', so it's most likely a AX operation, but, its SPID is not showing up in online user form.

In fact, there are a lot of sql SPID not showing up in Form, but, I am pretty sure there are AX related.

Maybe, in online user form, it's only show top SPID?

Reply
Suggested Answer
Tommy Skaue responded on 11 Apr 2016 9:17 AM

Here is a query you can use to get a more detailed view on what is happening on the SQL Server, and perhaps help you map the SPID in "Online Users" against SPID on the SQL Server.

-- forums.learnsqlserver.com/SqlServerTopic40.aspx

USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
    DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
    @spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */ 
    , @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */ 
)
AS

CREATE TABLE #locktable (
    spid SMALLINT
    , loginname NVARCHAR(128) 
    , hostname NVARCHAR(128)
    , dbid INT
    , dbname NVARCHAR(128) 
    , objId INT
    , ObjName  NVARCHAR(128)
    , IndId INT
    , Type NVARCHAR(4)
    , Resource NVARCHAR(16)
    , Mode NVARCHAR(8)
    , Status NVARCHAR(5)
)

SET NOCOUNT ON

IF @spid2 IS NOT NULL AND @spid1 IS NULL
    SET @spid1 = @spid2

DECLARE @object_id INT,
  @dbid INT,
  @DynamicSql NVARCHAR(255)

/***** @spid1 is  provided so show only the locks for @spid1 and @spid2 *****/ 
IF @spid1 IS NOT NULL
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS'      AND  u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)

ELSE /***** @spid1 is not provided so show all the locks *****/ 
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)

/**********************************************************************************************
    Because the locks exist in any database, you must USE <database name> before running OBJECT_NAME 

    We use a dynamic SQL loop to loop through each row from #locktable

    A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

WHILE @dbid IS NOT NULL
    BEGIN
        SELECT @DynamicSql = 
         'USE ' + DB_NAME(@dbid) + char(13) 
         + 'UPDATE #locktable SET ObjName = OBJECT_NAME(' 
            + CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId) 
         + ' AND objid = ' + CONVERT(VARCHAR, @object_id) 

        EXEC sp_executesql @DynamicSql

        SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
        SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
    END

SELECT * FROM #locktable 
    WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table 
        AND objid > 100 -- do not return system table locks
        AND objname <> 'spt_values'
GO

You run the query above to install the sp_lock3 procedure on the instance, and after that it is available globally on that SQL Server instance. You then simply open a new query window and run "sp_lock3". 

Reply
Ganriver responded on 11 Apr 2016 9:33 AM

Tommy, Thanks a lot!

Reply
Tommy Skaue responded on 11 Apr 2016 9:46 AM

My pleasure. Hope it helps. :-)

Remember to tick it as an answer - if it works.

Reply
Dick Wenning responded on 11 Apr 2016 1:42 PM

Real live Blocking case

So even when optimistic concurrency is used, database blocking can occur. Database blocking is that a second processes want to update the same record or page. He has to wait until the previous process is ready.

Demo (Occ is disabled on the SalesLine)

Let’s say there are 2 users processes that both want to update a record in the SalesLine table. User  A starts a transaction and did not jet send a commit. Meanwhile a user B starts and also would like to do an update on a different sales line of the same sales order. the same customer table record. Also this record did not send the commit to the database. If we look at the online users in Dynamics AX we see that both online users have a SPID id.  A SPID means, it has an active Query.

If we look in the database Activity Monitor of SQL server studio 2008 (right mouse button activity monitor on instance):

Reply
Verified Answer
Ganriver responded on 12 Apr 2016 1:53 PM

Figured out, we have a cluster of AOS servers, AX 2009 only show user's SPID of AOS where your current session is in.

You need to check/login to each AOS to find out all SPIDs of users.

Reply
Verified Answer
Ganriver responded on 12 Apr 2016 1:53 PM

Figured out, we have a cluster of AOS servers, AX 2009 only show user's SPID of AOS where your current session is in.

You need to check/login to each AOS to find out all SPIDs of users.

Reply
Suggested Answer
Tommy Skaue responded on 11 Apr 2016 9:17 AM

Here is a query you can use to get a more detailed view on what is happening on the SQL Server, and perhaps help you map the SPID in "Online Users" against SPID on the SQL Server.

-- forums.learnsqlserver.com/SqlServerTopic40.aspx

USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
    DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
    @spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */ 
    , @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */ 
)
AS

CREATE TABLE #locktable (
    spid SMALLINT
    , loginname NVARCHAR(128) 
    , hostname NVARCHAR(128)
    , dbid INT
    , dbname NVARCHAR(128) 
    , objId INT
    , ObjName  NVARCHAR(128)
    , IndId INT
    , Type NVARCHAR(4)
    , Resource NVARCHAR(16)
    , Mode NVARCHAR(8)
    , Status NVARCHAR(5)
)

SET NOCOUNT ON

IF @spid2 IS NOT NULL AND @spid1 IS NULL
    SET @spid1 = @spid2

DECLARE @object_id INT,
  @dbid INT,
  @DynamicSql NVARCHAR(255)

/***** @spid1 is  provided so show only the locks for @spid1 and @spid2 *****/ 
IF @spid1 IS NOT NULL
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS'      AND  u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)

ELSE /***** @spid1 is not provided so show all the locks *****/ 
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (v.name,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (u.name,  1,  8)
        , SUBSTRING (x.name,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)

/**********************************************************************************************
    Because the locks exist in any database, you must USE <database name> before running OBJECT_NAME 

    We use a dynamic SQL loop to loop through each row from #locktable

    A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

WHILE @dbid IS NOT NULL
    BEGIN
        SELECT @DynamicSql = 
         'USE ' + DB_NAME(@dbid) + char(13) 
         + 'UPDATE #locktable SET ObjName = OBJECT_NAME(' 
            + CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId) 
         + ' AND objid = ' + CONVERT(VARCHAR, @object_id) 

        EXEC sp_executesql @DynamicSql

        SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
        SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
    END

SELECT * FROM #locktable 
    WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table 
        AND objid > 100 -- do not return system table locks
        AND objname <> 'spt_values'
GO

You run the query above to install the sp_lock3 procedure on the instance, and after that it is available globally on that SQL Server instance. You then simply open a new query window and run "sp_lock3". 

Reply