SQL Server Performance

[solved] SET DEADLOCK_PRIORITY

Discussion in 'Performance Tuning for DBAs' started by stryk, Jul 19, 2007.

  1. stryk New Member

    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
  2. satya Moderator

    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.
  3. stryk New Member

    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
  4. Chappy New Member

    > 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
  5. stryk New Member

    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
  6. Chappy New Member

    Sounds like you have a bit of a job on your hands with that one <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Is the C/AL written by yourself, or third party? In T-SQL you can structure queries to prevent deadlocks, by following a few basic rules (ie, select as much data as possible at the top of a procedure, before you actually use it). <br /><br />Maybe theres some way to influence the C/AL to do the same thing, by firing off a few C/AL statements which achieve the same thing in T-SQL. Sounds like a big job though, but ultimately the best solution if its possible.<br /><br />Alternatively, does the NAS or NAV run any sort of stored procedure when it first logs in, or maybe some area of C/SIDE where you can influence what it does when a connection is made (maybe by getting it to call a procedure without creating a new ado connection)? I guess youve looked into this already though<br /><br />One possibility would be to modify one of the system stored procedures, to check which user the connection belongs to, and then issue a deadlock priority statement. This is a real bodge, and not recommended, but it could work <img src='/community/emoticons/emotion-5.gif' alt=';)' /> <br />Youd split NAV and NAS into two seperate user groups, and then check that on login.<br /><br />If you could upgrade to SQL 2005, I think 2005 allows you to set some sort of trigger for when a user logs in. You could then maybe do the same in the trigger, Im not sure. <br />Maybe one of the guys using 2005 can advise<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
  7. stryk New Member

    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=':p' />]<br />Actually, I was REALLY lucky - today I have been reading a technical doc about the most recent version 5.0 and found something which I could use. NAV is indeed executing a kind of start-up stored procedure, which has to be created by oneself, it does not exist "out of the box". So I created this procedure, where I could now set the DEADLOCK_PRIORITY as required [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Pity, why didn't I find this chapter earlier [:I]<br /><br />Thank you very much for your support!<br /><br />Best regards,<br />Jörg<br /><br /><br /><b>Jörg A. Stryk</b><br /><font size="1">Freelance NAV System Consultant (MVP, MCP, MCBMSS)</font id="size1"><br /><b>STRYK System Improvement</b><br />www.stryk.info

Share This Page