RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

DEADLOCK_PRIORITY of a NAS

Setting DEADLOCK_PRIORITY with "sp_$ndo$loginproc"

Well, in a perfect world we would never encounter deadlocks with NAV, but unfortunately things are not that perfect ... so we will always have deadlocks, sometimes more, sometimes less. It is quite annoying, when a DL occurs and some unattended process, e.g. a NAS or NAV Job Scheduler, is killed, because these processes hardly could resume

I just found a way to - at least - define the DEADLOCK_PRIORITY for such processes with NAV, using the stored procedure "sp_$ndo$loginproc" (see "Application Designer's Guide" for details).

The procedure sets the DEADLOCK_PRIORITY of a NAS higher than a normal Client; thus in case of a DL always the client will be chosen as "victim" and killed, not the NAS:

 

GREAT STRYK Utility

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***/
/***                   http://www.stryk.info/                  ***/
/*********************************************************/

if exists (select [name] from sysobjects where [name] = 'sp_$ndo$loginproc' and [type] = 'P')
  drop procedure [sp_$ndo$loginproc]
go

create procedure [sp_$ndo$loginproc]
@appname varchar(64) = NULL,
@appversion varchar(16) = NULL
as
begin

  if charindex('2000', @@version) > 0 begin  -- SQL Server 2000
    if @appname like '%[Aa]pplication%' begin
      set deadlock_priority normal
      set lock_timeout -1
    end
    if @appname like '%[Cc]lient%'
      set deadlock_priority low
  end

  if charindex('2005', @@version) > 0 begin  -- SQL Server 2005
    if @appname like '%[Aa]pplication%' begin
      set deadlock_priority high
      set lock_timeout -1
    end
    if @appname like '%[Cc]lient%'
      set deadlock_priority low
  end

end
go

grant execute on [sp_$ndo$loginproc] to [public]
go

 

Microsoft Official NAV Locks Page

http://msdn.microsoft.com/en-us/library/dd338811.aspx

Categoria: Dynamics NAV ALL
giovedì, 03 giu 2010 Ore. 12.38
Statistiche
  • Views Home Page: 416.209
  • Views Posts: 844.852
  • Views Gallerie: 0
  • n° Posts: 345
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003