Frequent blocking in Nav on SQL Server

afarr

Our client has started experiencing lots of blocking, which is slowing down their system.
They only noticed this on Tuesday, it wasn’t an issue before that.

The blocking happens several times a minute, for just a few seconds each time.
It is experienced by many different users, in different areas of Navision, with no noticeable pattern.
In all cases, in the session table, the userID is the same as the “blocking user ID” (i.e. the user seems to be blocking their own ID), and the “blocking object” is blank.

So it is not clear what to investigate (and besides, no objects were modified in the last 12 days).

I sent the clie a document with several different suggestions (e.g. turning off “Find As You Type”, ensuring that there is sufficient free space, running SQL maintenance jobs regularly, turning off AutoCostPosting, etc.). They said that they implemented many of the suggestions without seeing any improvement.

ChangeLog is only activated on two tables, which are part of an add-on.

They are using Nav 3.70 B on SQL 2000, with a 60 GB database.

How can we find what is causing the blocking, when there is no blocking object (and no other user ID) mentioned?

Thanks,
Alastair

ara3n

Have you tried and reboot the sql server?

stryk

Hi,

maybe this could help you a little:
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

But this actually refers to SQL Server 2005 and up.
With SQL 2000 you have very limited options in recording blocks & deadlocks; maybe you could create a table ...

if not exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[ssi_BlockCheck_Tab]') and objectproperty(id, N'IsTable') = 1)
create table [dbo].[ssi_BlockCheck_Tab]
 (
  [entry_no] bigint identity constraint [ssi_BlockCheck_Tab$pk_ci] primary key clustered,
  [timestamp] datetime,
  [db] varchar(128) collate database_default,
  [waitresource] varchar(128),
  [table_name] varchar(128) collate database_default,
  [index_name] varchar(128) collate database_default,
  [waittime] bigint,
  [lastwaittype] varchar(128),
  [spid] int,
  [loginame] varchar(128) collate database_default,
  [hostname] varchar(128) collate database_default,
  [program_name] varchar(128) collate database_default,
  [cmd] nvarchar(255) collate database_default,
  [status] varchar(128) collate database_default,
  [cpu] bigint,
  [lock_timeout] int,
  [blocked by] int,
  [loginame 2] varchar(128) collate database_default,
  [hostname 2] varchar(128) collate database_default,
  [program_name 2] varchar(128) collate database_default,
  [cmd 2] nvarchar(255) collate database_default,
  [status 2] varchar(128) collate database_default,
  [cpu 2] bigint, 
  [block_orig_id] int,
  [block_orig_loginame] varchar(128) collate database_default
  )
go

... then a stored procedure to save the block data ...

create procedure dbo.ssi_blockcheck
  @mode varchar(10) = 'loop',         -- "loop" or "once"
  @threshold int = 1000,              -- Block threshold in milliseconds 
  @frequency int = 3,                 -- Check frequency in milliseconds
  @save tinyint = 1                   -- save output to table ssi_BlockCheck_Tab (0 = no, 1 = yes)
--with encryption
as

if (@mode not in ('loop', 'once')) begin
  raiserror ('ERROR: Invalid Parameter @mode: %s', 15, 1, @mode)
  return
end
if (@threshold < 1) begin
  raiserror ('ERROR: Invalid Parameter @threshold: %i', 15, 1, @threshold)
  return
end
if (@frequency < 1) begin
  raiserror ('ERROR: Invalid Parameter @frequency: %i', 15, 1, @frequency)
  return
end
if (@save not in (0,1)) begin
  raiserror ('ERROR: Invalid Parameter @save: %i', 15, 1, @save)
  return
end

set nocount on
set statistics io off
declare @spid int, @spid2 int, @loginame varchar(128), @blocked_by int, @blocked_by_name varchar(128), @orig_id int, @orig_name varchar(128), @timestmp datetime, @i int

if @mode = 'once'
  goto start_check

while 1 = 1 begin

  start_check: 

  if exists (select * from master..sysprocesses where [blocked] <> 0) begin
       
    if @save = 0 begin

      select [db] = db_name(s1.[dbid]), 
             ltrim(rtrim(s1.[waitresource])),
             [table_name] = object_name(sl.rsc_objid),            
             [index_name] = si.[name],
             s1.[waittime],
             s1.[lastwaittype],
             s1.[spid],
             s1.[loginame],
             s1.[hostname],
             s1.[program_name],
             s1.[cmd],
             s1.[status],
             s1.[cpu],
             [blocked by] = s1.[blocked],
             [loginame 2] = s2.[loginame],
             [hostname 2]=s2.[hostname],
             [program_name 2] = s2.[program_name],
             [cmd 2] = s2.[cmd],
             s2.[status],
             s2.[cpu] 
      -- Processes         
      from master..sysprocesses s1 (nolock)
      left outer join master.dbo.sysprocesses s2 (nolock) on s1.[blocked] = s2.[spid]
      -- Lock-Info
      left outer join master.dbo.syslockinfo sl (nolock) on s1.[spid] = sl.[req_spid]
      -- Indexes
      left outer join sysindexes si (nolock) on sl.rsc_objid = si.[id] and sl.rsc_indid = si.[indid]
      where s1.[blocked] <> 0 
            and (sl.rsc_type in (4,5,6,7)) and sl.rsc_indid <> 0 and sl.req_status = 3
            and s1.[waittime] >= @threshold

    end else begin

      set @timestmp = getdate()
      create table #dbcc_inputbuffer (EventType nvarchar(30), Parameter int, EventInfo nvarchar(255))

      insert into [ssi_BlockCheck_Tab]
      ([timestamp],[db],[waitresource],[table_name],[index_name],[waittime], [lastwaittype],[spid],[loginame],[hostname],[program_name],
       [cmd],[status],[cpu],[blocked by],[loginame 2],[hostname 2],[program_name 2],[cmd 2],[status 2],[cpu 2],[block_orig_id],[block_orig_loginame])
      select @timestmp,
             [db] = db_name(s1.[dbid]), 
             ltrim(rtrim(s1.[waitresource])),
             [table_name] = object_name(sl.rsc_objid),            
             [index_name] = si.[name],
             s1.[waittime],
             s1.[lastwaittype],
             s1.[spid],
             s1.[loginame],
             s1.[hostname],
             s1.[program_name],
             s1.[cmd],
             s1.[status],
             s1.[cpu],
             [blocked by] = s1.[blocked],
             [loginame 2] = s2.[loginame],
             [hostname 2]=s2.[hostname],
             [program_name 2] = s2.[program_name],
             [cmd 2] = s2.[cmd],
             s2.[status],
             s2.[cpu],
             null,
             null 
      -- Processes         
      from master..sysprocesses s1 (nolock)
      left outer join master.dbo.sysprocesses s2 (nolock) on s2.[spid] = s1.[blocked] 
      -- Lock-Info
      left outer join master.dbo.syslockinfo sl (nolock) on s1.[spid] = sl.req_spid
      -- Indexes
      left outer join sysindexes si (nolock) on sl.rsc_objid = si.[id] and sl.rsc_indid = si.[indid]
      where s1.[blocked] <> 0 
            and (sl.rsc_type in (4,5,6,7)) and sl.rsc_indid <> 0 and sl.req_status = 3
            and s1.[waittime] >= @threshold
      
      -- updating [cmd] from DBCC INPUTBUFFER
      declare spid_cur cursor for
        select [spid], [blocked by] from [dbo].[ssi_BlockCheck_Tab] where [timestamp] = @timestmp
        for update
      open spid_cur
      fetch next from spid_cur into @spid, @blocked_by
      while @@fetch_status = 0 begin
        -- SPID
        insert into #dbcc_inputbuffer exec('DBCC INPUTBUFFER (' + @spid + ') WITH NO_INFOMSGS')
        update [ssi_BlockCheck_Tab] set [cmd] = (select [EventInfo] from #dbcc_inputbuffer)
          where current of spid_cur
        delete from #dbcc_inputbuffer 
        -- BLOCKED BY
        insert into #dbcc_inputbuffer exec('DBCC INPUTBUFFER (' + @blocked_by + ') WITH NO_INFOMSGS')
        update [ssi_BlockCheck_Tab] set [cmd 2] = (select [EventInfo] from #dbcc_inputbuffer)
          where current of spid_cur
        delete from #dbcc_inputbuffer 

        fetch next from spid_cur into @spid, @blocked_by
      end
      close spid_cur
      deallocate spid_cur     
      drop table #dbcc_inputbuffer

      update [dbo].[ssi_BlockCheck_Tab] set [table_name] = '- unknown -' where [table_name] is null

      -- get block originator
      declare originator_cur cursor for select [blocked by], [loginame 2]
        from [dbo].[ssi_BlockCheck_Tab]
        where [timestamp] = @timestmp
        for update
      open originator_cur
      fetch next from originator_cur into @blocked_by, @blocked_by_name
      while @@fetch_status = 0 begin
        set @i = 0
        set @orig_id = @blocked_by   
        set @orig_name = @blocked_by_name 
        set @spid2 = @blocked_by
        while (@spid2 <> 0) and (@i < 100) begin
          if exists(select top 1 [blocked by] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)) begin
            select top 1 @spid = [blocked by], @loginame = [loginame 2] from [dbo].[ssi_BlockCheck_Tab] where ([timestamp] = @timestmp) and ([spid] = @spid2)
            set @orig_id = @spid
            set @orig_name = @loginame                       
            set @spid2 = @spid         
          end else
            set @spid2 = 0
          set @i = @i + 1   -- "Emergency Exit", to avoid recursive loop
        end 
        update [dbo].[ssi_BlockCheck_Tab] set [block_orig_id] = @orig_id, [block_orig_loginame] = @orig_name where current of originator_cur
        fetch next from originator_cur into @blocked_by, @blocked_by_name
      end
      close originator_cur
      deallocate originator_cur

    end    
  end

  end_check:

  if @mode = 'once'
    return

  waitfor delay @frequency
end

(Don't forget to install all this in you NAV database!)

Finally you could create a job which executes this TSQL query in the "Job Step":

create procedure dbo.ssi_blockcheck
  @mode = 'loop',         -- "loop" or "once"
  @threshold = 1000,      -- Block threshold in milliseconds 
  @frequency = 3,         -- Check frequency in milliseconds
  @save = 1               -- save output to table ssi_BlockCheck_Tab (0 = no, 1 = yes)

(Run in context of your NAV database!)

This would start the SP in a loop mode which checks every 3 seconds if there are blocks of more than 1000 milliseconds (change parameters on demand); if some detected it will write the info into the table. Then you could run some further investigations about blocking issues ...

Caution: in some cases this block-detection could cause heavy load (I/O, CPU) on the system!

Also have in mind that several NAV client versions are affected with some bugs :bug: which also could increase blocking issues!

Hope this could help you a little.

ALL SCRIPTS ARE PROVIDED "AS IS". NO WARRANTY, NO GUARANTEE, NO SUPPORT. USE AT OWN RISK.

afarr

Jorg (and Rashed), many thanks for your detailed reply.

I talked to the client again. They clarified that this was not a sudden problem – performance has suffered for some time, and this week they thought of checking if any blocking was occurring, but it might have been happening before that.

They have restarted the SQL Server, and the blocking is still occurring at the same rate.

They are going to look at other actions (allowing pagelocks on SQL Server, creating missing indexes, implementing AWE, and possibly increasing their RAM from the current 4 GB).

We will see if these help with the performance.