Nav 3.7 with Sql 2000 performance

ilayarajav

One of my client using Nav 3.7 with Sql 2000 database. Frequently database getting hang while positng sale invoices. Number of users is around 50, around 30 users loging through VPN connectivity. Volume of transaction is huge at anytime.

Is anyone tell me any problem with SQL 2000 database while using huge transaction at the time. What will be the remedial action for this issue.

kine

Are the users connecting through VPN using terminal services or Citrix or they just run the client on their PCs?

ilayarajav

around 30 users connecting through terminal server and 20 users accessing through desktop client.

kine

Ok, it is as any other NAV used on MS SQL -you need optimalisation of the NAV for SQL to be able to run it smooth. You can see that there is special section on this forum for that and you can read many tips there. There are some videos in download section about optim. SQL for NAV too.

garak

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

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

stryk

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

garak

also check with SQL Profiler the queries with a high duration and with a high read time.
Dont forget, that every read mean, that the server must read 8K......
So, if the reads are smaller, the server found the data faster.
Also you can anlyze the founded queries to see, whats the "costs" of this queries is
To find blocks you can use stryks little tool.
Sometimes it's a "hard work" to find out all the "bad programms". Also the NAV standard isn't perfomant ...
But if you find out all these programms, you can redesign your table structure / indexes, SIFTS, Buckets and your source self.

Here in this subforum are many post about the theme: How to perfom a NAV database to SQL.