Friday, June 27, 2008

Forcing a DEADLOCK_PRIORITY for a Login?

A question was asked on the MSDN Forums regarding the ability to force a DEADLOCK_PRIORITY level for a specific Login in the database engine.  This seemed at first like it would be something that was in the database engine that I had just never had the need for or opportunity to use.  One suggestion was to utilize a LOGON trigger to SET the option.  My first inclination was that this wouldn't work but rather than hitting the Books Online I decided to give it a shot with the following:

CREATE TRIGGER [EscalateDeadlockPriority]
ON ALL SERVER WITH EXECUTE AS CALLER
AFTER
LOGON
AS
BEGIN
IF
RTRIM(LTRIM(SUSER_SNAME())) = 'logintest'
BEGIN
--PRINT 'setting deadlock_priority high';
SET DEADLOCK_PRIORITY HIGH;
END
END



Then I created the testing Login:




CREATE LOGIN [logintest] WITH PASSWORD=N'logintest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF




and finally I tested the connection by using SQLCMD from the command prompt:




sqlcmd -U logintest -P logintest -S oshodb05 -Q "SELECT deadlock_priority from sys.dm_exec_sessions where session_id = @@spid "




This resulted in a return result of 0 which means Normal Priority.  So why won't this work?  The answer is actually really simple, something I already knew had to try this out anyway, and documented in the Books Online in SET (Transact-SQL)




If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.




This is scoped only to the Transaction inside the Trigger, so unfortunately this is not going to solve this problem.  I did some searching online and found the following connect item for SQL Server that documents a request for this:




https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263809&wa=wsignin1.0




Thankfully the response from Microsoft is that this is in consideration from Microsoft for a future release/edition, though it doesn't seem to have made it into the SQL Server 2008 product line as of RC0.

No comments:

Post a Comment