Hi all!
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: [I]
//
/ 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]
gocreate procedure [sp_$ndo$loginproc]
@appname varchar(64) = NULL,
@appversion varchar(16) = NULL
as
beginif 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
endif 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
endend
gogrant execute on [sp_$ndo$loginproc] to [public]
go
Of course, this procedure could be designed in any way to handle specific users/logins and could do MUCH MORE - “the limit is our imagination” [;)]
Hope it could help you as well! I appreciate to get your comments!
Best regards,
Jörg