SQL Server Performance Forum – Threads Archive
[solved] SET DEADLOCK_PRIORITY
Hi all! Well, I want to prevent that a specific process/client – a MS Dynamics NAV Application Server (NAS) – is selected as deadlock victim.I know that I could use the SET DEADLOCK_PRIORITY command to do this, but I only could set it for the current session. With a NAS I have no chance to set the DEADLOCK_PRIORITY, so is there a possibility to set this – or similar – from a second process? Means that for example SPID 51 sets the DEADLOCK_PRIORITY for SPID 52? Thanks in advance!
Best regards, Jörg Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info
http://mssqltips.com/tip.asp?tip1210 fyi on your doubts. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Dear Satya, thank you for your reply. So this means that there is no chance to set the DEADLOCK_PRIORITY for a "remote" SPID?! Pity … [V]
So I’ll into a way, how this NAS could set its DEADLOCK_PRIORITY itself. Kind regards,
Jörg Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info
> Means that for example SPID 51 sets the DEADLOCK_PRIORITY for SPID 52? Couldnt SPID 51 simply SET DEADLOCK_PRIORITY LOW ?
Meaning that SPID 52 (NAV) would not be the victim
quote:Couldnt SPID 51 simply SET DEADLOCK_PRIORITY LOW ?
Unfortunately not. The situation I have is this: There are about 20 to 150 NAV Clients; connected via their own application running on a thing called C/SIDE. This C/SIDE Client is connecting to the SQL Server and sending the queries. NAV uses its own programming language C/AL. The C/SIDE client translates this C/AL to SQL – no chance to send any statements directly. Then I have 1 to 5 NAV Application Servers (NAS), which are actually "Clients" without a GUI, used for automated, unattended processing. The way of querying the SQL Server is the same as with the NAV Clients. Thus, if a deadlock occurs, I want to avoid that these NAS are choosen as victim, I always want a GUI process to be killed instead, because after being killed, the NAS services have problems to recover. The only chance to send direct SQL statements from NAV is sending them via ADO, but this would establish a second process – another SPID. Finally, the problem is, that neither the C/SIDE Clients could set DEADLOCK_PRIORITY LOW not the NAS could set DEADLOCK_PRIORITY HIGH. So I’m looking for a way to "influence" this from outside … any ideas are welcome! Jörg A. Stryk
Freelance NAV System Consultant (MCP, MBSCP)
STRYK System Improvement
www.stryk.info
Sounds like you have a bit of a job on your hands with that one <img src=’/community/emoticons/emotion-6.gif’ alt=’
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
Hi!<br /><br />Thanks for your reply! Yep, this C/SIDE & C/AL stuff is somewhat tricky … it’s designed by Microsoft, actually from the former Navision A/S guys from Danmark …<br /><br />But: I got it! [<img src=’/community/emoticons/emotion-4.gif’ alt=’
![Stick Out Tongue :p :p](styles/default/xenforo/smilies/tongue.png)
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
![Big Grin :D :D](styles/default/xenforo/smilies/biggrin.png)
]]>