Possible to set deadlock priority on a login?
-
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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply