Frequent blocking in Nav on SQL Server

afarrafarr Member Posts: 287
edited 2010-05-14 in SQL Performance
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
Alastair Farrugia

Comments

  • ara3nara3n Member Posts: 9,255
    Have you tried and reboot the sql server?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • strykstryk Member Posts: 645
    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.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • afarrafarr Member Posts: 287
    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.
    Alastair Farrugia
Sign In or Register to comment.