Nav 3.7 with Sql 2000 performance

ilayarajavilayarajav Member Posts: 19
edited 2008-12-08 in SQL Performance
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.
Regards,
Raja

Comments

  • kinekine Member Posts: 12,562
    Are the users connecting through VPN using terminal services or Citrix or they just run the client on their PCs?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • ilayarajavilayarajav Member Posts: 19
    around 30 users connecting through terminal server and 20 users accessing through desktop client.
    Regards,
    Raja
  • kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • strykstryk Member Posts: 645
    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
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • garakgarak Member Posts: 3,263
    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.
    Do you make it right, it works too!
Sign In or Register to comment.