Possible to set deadlock priority on a login?
-
Is it possible to set the deadlock priority on a user login?
We have phone system that ties to our database to do phone surveys. Our system is reaching it's limit and during peak times we are getting some deadlock issues that on ocasionnally havethe phones system processes to get the boot.
I've looked around and done some searching but come up with nada so figured I'd ask. Hopeing maybe there were be an easy fix like this so i don't have to go mucking around in the phone systems code
Thanks!
-
You can modify deadlock resolving using SET DEADLOCK_PRIORITY per session, but I don't know if it will help you. I suppose you would have to change this setting for all connections not related to phones system processes to LOW, so they are killed instead of the phones system ones. Anyway, users of those connections will not be happy either.
...and your only reply is slàinte mhath
-
Well it's a balanceing act. We are a company that does Mysteryshopping and web/phone surveys. I've found people that get a "we're sorry,please try again" message on a website are a lot less likely to complain than someone that has the phone hang up on them becuase the DB choked
Looks like we'll just have to dig into the systems and find out where the major contention locations are and adjust the dl priority manually. I was hoping maybe microsoft would be nice and provide a simple way to control performance for users.
Thanks!
-
You might be able to do it with a Logon Trigger.
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier> [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
However, if you can change it in the web side code directly that would be best.
-
If you do get to monkey'ing with the phone code, issuing the set deadlock priority statement just after you make the database connection would be best.
Logon trigger will work too. Not sure what will happen though if your code doesn't issue a reset connection after each disconnect.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply