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