Nav 3.7 with Sql 2000 performance

mainly read for SQL and PErfomance the posts in the "SQL Perfomance" subforum.
There are also some examples, ppt/pdf/wmf files, and there are explained how to optimizes NAV for SQL.

Regards

Hi!

Well, as my dear "collegues" already mentioned, there's a lot you could do to improve performance, even though your somewhat limited (technically) with NAV 3.70 and SQL 2000

Anyway, the problem you describe "smells" like a blocking issue which would not be unusual during Invoice postings. To start fixing the problem it might be feasible to monitor the "Who is Who" about blocks.

Firstly I'd like to recommend to read my BLOG abou that issue:
http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

The TSQL scripts are related to SQL 2005; so please find herewith some SQL 2000 variants:

The Table:

-- Create Block-Detection 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

The Stored Procedure:

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 = 0                   -- save output to table ssi_BlockCheck_Tab (0 = no, 1 = yes)
with encryption
as

if @mode <> 'once' begin
  print '*********************************************************'
  print '***              STRYK System Improvement             ***'
  print '***    Performance Optimization & Troubleshooting     ***'
  print '***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***'
  print '***                   www.stryk.info                  ***'
  print '*********************************************************'
  print '              Version 4.00, Date: 24.10.2008             '
  print ''
end

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

if @mode <> 'once' begin
  print 'Block Detection Mode  : ' + @mode
  print 'Block Threshold (msec): ' + convert(varchar(15), @threshold)
  print 'Check Frequency (sec) : ' + convert(varchar(10), @frequency)
  print 'Save Output to table  : ' + convert(varchar(10), @save)
  print ''
  print 'Searching for blocked processes ...'
  print ''
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
    print 'Checkpoint ' + convert(varchar(30), getdate())
       
    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

The Alert (monitoring "SQL Server: SQL Locks- Lock Wait Time (ms); > 3000) and the Job have to be created manually.

ONce you know where the blocks are coming from you might be able to fix it; e.g. by optimizing C/AL code etc.. Most important here is to optimize the SIFT structures! Again, I dare to link to my BLOG:
http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx

Hope this helps.

Cheers,
Jörg