Setting DEADLOCK_PRIORITY with sp_$ndo$loginproc

stryk · 2007-07-22

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: \:D/

/*********************************************************/
/***              STRYK System Improvement             ***/
/***    Performance Optimization & Troubleshooting     ***/
/***  (c) 2007, STRYK System Improvement, Jörg Stryk   ***/
/***                   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

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

colin_leversuch-roberts · 2007-07-23

Although this isn't something I'd recommend, I've been tracking the NAS service on our server as I was told it was suffering from deadlocks which were causing the service to fail.
Having now tracked the deadlocks I now know that the NAS failures are not deadlocks, in my case.
Just be careful making changes and make sure you have checked your deadlocks correctly - the only real way is to enable trace flags on the sql server to capture deadlock information.
Faster transactions usually negate deadlocks.

stryk · 2007-07-23

Hi Colin!

I fully agree with you! Solving or preventig Deadlocks is the primary approach! Unfortunately, this sometimes could be really difficult, and in many cases DL will remain.
The procedure for setting the DEADLOCK_PRIORITY just "limits the damage": if a DL is encountered, so that if it's "NAS vs GUI", always GUI will get killed, as it's easier for a user to resume/repeat a process than for a NAS.
And of course, this procedure is just considered a proposal or template!

colin_leversuch-roberts · 2007-07-24

I've linked a sql query which checks for a connection from NAS to a vbscript which restarts the NAS service if there is no connection. The Navision support were sure NAS was getting killed by deadlocks, I ran the trace flags 1204 and 1205 for a few days and we discovered that this was not the case. As is often the case there are no error messages anywhere which indicate why the NAS service stops - it seems to relate to how busy the server is - I'm following a couple of leads on possible SQL Server issues - here's one of them :- http://sqlblogcasts.com/blogs/grumpyold ... store.aspx

nachoporcar · 2007-10-01

Hello stryk,

I have SQL SERVER 2000, but I cannot found this process: 'sp_$ndo$loginproc' on my System, it executes when a user logs in? When my system executes this stored procedure 'sp_$ndo$loginproc'?

How can i use "SET DEADLOCK_PRIORITY" in a TRANSACT SQL not system process to change the priority of all the sessions on my system? only is valid your process named 'sp_$ndo$loginproc' ?

Thanks
Nacho Porcar

stryk · 2007-10-01

Hi!

First: I'm not that sure anymore if setting the DEADLOCK_PRIORITY via this procedure worked as intended ... the problem is, that usually the SET commands are just valid within the context of the procedure; thus when finishing the sp_$ndo$loginproc it could be that the parameters are reset ... questions are: is it so? maybe an exception? what is the default value? normal? I'm currently looking into that ...
(would appreciate to get your results & comments!)

have SQL SERVER 2000, but I cannot found this process: 'sp_$ndo$loginproc' on my System, it executes when a user logs in? When my system executes this stored procedure 'sp_$ndo$loginproc'?

The proc does not exist out of the box, you have to create it manually. See "Application Designer's Guide" for details. Once it is created, it's executed automatically by C/SIDE when a user logs on.

How can i use "SET DEADLOCK_PRIORITY" in a TRANSACT SQL not system process to change the priority of all the sessions on my system? only is valid your process named 'sp_$ndo$loginproc' ?

Well, basically you could use SET DEADLOCK_PRIORITY in any script. The problem is - as mentioned - the context: If you would execute it e.g. via ADO - or OSQL or SQLCMD - you run it under a different SPID. With the sp_$nod$loginproc it is executed within context of the original User SPID, but as stated above, maybe it does not work ...

Kind regards,
Jörg

Setting DEADLOCK_PRIORITY with sp_$ndo$loginproc

Comments

Categories