Can I be automatically notified of prolonged blocking in SQL server?

Question

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a certain form and is released once the user has finished scrolling through the form or closes it. Since many of our users open this form as reference, these locks stay around for a while. Any update to the table causes the blocking, and suddenly nobody can select from this table since they're all waiting on the first lock. This is quite a problem for us, since lots of apps rely on this data. I understand that this locking behavior is part of how Access works with linked tables.

I've been solving the problem from Activity Monitor, by killing whichever SELECT process is the Head Blocker whenever I find out about it. This is a problem not only because it takes me time to do it manually, but also because it's reactive. By the time I hear about it, it's already been a problem for a lot of people.

I'd like to know if there is an automatic way to check for these long-lasting blocking chains, and either be emailed or have the problem resolved automatically. The logic seems straightforward enough ("if any process matching this SELECT query has been blocking for longer than a minute, notify me/kill it") but I don't know how to implement this with SQL Server.

For what it's worth, I think the proper solution is to fix or rewrite the app. However, due to departmental politics, this is not an option for the next few months, so I'm looking for a stopgap.

Answer 1

Have you considered using snapshot isolation? Enabling read_committed_snapshot in the database will cause all reads (selects) to be lock free:

alter database [...] set read_committed_snapshot on;

No application changes. Some semantics change under snapshot and your application may react weirdly, but that is the exception not the norm. The vast majority of applications don't notice any difference, they just get a free performance boost.

Anyway, I though to answer also the original question: how to detect (and possibly kill) a long running query. Actually, the engine already does that for you. There is an event raised when a threshold is passed: Blocked Process Report Event Class. The threshold is configured via the blocked process threshold Option. Any trace event can be turned into an Event Notification and event notifications can activate procedures. Connect the dots and you have on-demand activated code that runs when the engine detects a query that has crossed an execution time threshold. No polling, no monitoring. Note though that the notification is asynchronous, by the time you process it the query may had completed so that has to be taken into account.

Here is an example:

use msdb;
go

create queue blocked_process_report_queue;
go

create service blocked_process_report_service
    on queue blocked_process_report_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification blocked_process_report_notification
    on server
    for BLOCKED_PROCESS_REPORT
    to service N'blocked_process_report_service',
          N'current database';
go  

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;

Now in a new query set up a WAITFOR expecting a notification:

use msdb;
waitfor(
   receive cast(message_body as xml), * 
   from  blocked_process_report_queue);

And go ahead and cause some blockage. I used a process that created a table and didn't commit, and from another query windows I tried to select from the table. In 20 seconds (my configured threshold above) I got the blocking report:

<blocked-process-report>
  <blocked-process>
    <process id="process1b5a24ca8" ...>
      <executionStack>
        <frame line="1" stmtstart="-1"... />
      </executionStack>
      <inputbuf>
          select * from t   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="51" ...>
      <executionStack />
      <inputbuf>
         begin transaction
         create table t (a int)   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

I'll leave the task of wrapping this up into an automated process as an exercise to the reader. And yes, the queue/service/activated procedure must be in [msdb].

Answer 2

You can either build your own monitoring tool or look to a 3rd party solution that can provide one for you. If you're interested in building your own, it depends on what version of SQL Server you're working with. If it's 2005, you can use the Blocked Process Report trace event. If you're running 2008 or above, I'd suggest using the equivalent extended event, blocked_process_report. Jonathan Kehayias has a good write up on how to use it.

If you're looking at 3rd party products, Red Gate software's SQL Monitor has blocked process and long running process alerts built in.

Answer 3

Although this does not address how to notify you of the problem, this procedure will show you how to query to see if blocking exists. It will also generate kill commands for you, if you pass in the correct parameter.

Hope this gives you some ideas.

IF (object_id('Who') IS NOT NULL)
BEGIN
  print 'Dropping procedure: Who'
  drop procedure Who
END
print 'Creating procedure: Who'
GO
CREATE PROCEDURE Who
(
  @db varchar(100) = '%',
  @kill char(1) = 'N',
  @tran char(1) = 'N'
)
as

/*  This proc should be rewritten to take advantage of:
  select * from sys.dm_exec_connections
  select * from sys.dm_exec_sessions
  select * from sys.dm_exec_requests
*/



---------------------------------------------------------------------------------------------------
-- Date Created: July 17, 2007
-- Author:       Bill McEvoy
-- Description:  This procedure gives a summary report and a detailed report of the logged-in
--               processes.  This procedure is a derivative of sp_who3 which I wrote in 2002.
--               
---------------------------------------------------------------------------------------------------
-- Date Revised: 
-- Author:       
-- Reason:       
---------------------------------------------------------------------------------------------------
set nocount on

---------------------------------------------------------------------
-- Validate input parameters                                       --
---------------------------------------------------------------------


---------------------------------------------------------------------
-- M A I N   P R O C E S S I N G                                   --
---------------------------------------------------------------------
--                                                                 --
-- Generate login summary report                                   --
--                                                                 --
--                                                                 --
---------------------------------------------------------------------


---------------------------------------------------------------------
-- Generate login summary report                                   --
---------------------------------------------------------------------

select 'loginame'   = convert(char(30),loginame),
       'connection' = count(*),
       'phys_io'    = str(sum(physical_io),10),
--       'cpu'        = sum(cpu),
       'cpu(mm:ss)' = str((sum(cpu)/1000/60),12) + ':' + case 
                                            when left((str(((sum(cpu)/1000) % 60),2)),1) = ' ' then stuff(str(((sum(cpu)/1000) %60),2),1,1,'0') 
                                            else str(((sum(cpu)/1000) %60),2)
                                         end,
       'wait_time'  = str(sum(waittime),12),
       'Total Memory(MB)' = convert(decimal(12,2),sum(convert(float,memusage) * 8192.0 / 1024.0 / 1024.0))
from master.dbo.sysprocesses
where db_name(dbid) like @db
  and not (loginame = 'sa' and program_name = '' and db_name(dbid) = 'master')
group by loginame
order by 3 desc



---------------------------------------------------------------------
-- Generate detailed activity report                               --
---------------------------------------------------------------------

select 'loginame'     = left(loginame, 30),
       'hostname'     = left(hostname,25),
       'database'     = left(db_name(dbid),25),
       'spid'         = str(spid,4,0),
       'block'        = str(blocked,5,0),
       'phys_io'      = str(physical_io,10,0),
       'cpu(mm:ss)'   = str((cpu/1000/60),10) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END,
       'mem(MB)'      = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
       'program_name' = left(program_name,50),
       'command'      = cmd,
       'lastwaittype' = left(lastwaittype,20),
       'login_time'   = convert(char(19),login_time,120),
       'last_batch'   = convert(char(19),last_batch,120),
       'status'       = left(nt_username,20)
  from master..sysprocesses
where db_name(dbid) like @db
  and not (loginame = 'sa' and program_name = '' and db_name(dbid) = 'master')
order by 5,4


---------------------------------------------------------------------
-- Generate KILL commands                                          --
---------------------------------------------------------------------

IF (upper(@Kill) = 'Y')
BEGIN
  select 'kill' + ' ' + str(spid,4,0)
    from master..sysprocesses
  where db_name(dbid) like @db
    and not (loginame = 'sa' and program_name = '' and db_name(dbid) = 'master')
  order by spid
END



---------------------------------------------------------------------
-- Report on open transactions                                     --
---------------------------------------------------------------------

IF (UPPER(@Tran) = 'Y')
BEGIN

  -- Create the temporary table to accept the results.
  IF (object_id('tempdb..#Transactions') is NOT NULL)
    DROP TABLE #Transactions
  CREATE TABLE #Transactions
  (
    DatabaseName    varchar(30),
    TransactionName varchar(25),
    Details         sql_variant 
  )

  -- Execute the command, putting the results in the table.
  exec sp_msforeachdb '
  INSERT INTO #Transactions (TransactionName, Details)
     EXEC (''DBCC OPENTRAN([?]) WITH TABLERESULTS, NO_INFOMSGS'');
  update #Transactions 
     set DatabaseName = ''[?]''
   where DatabaseName is NULL'

  -- Display the results.
  SELECT * FROM #Transactions order by transactionname
END


go
IF (object_id('Who') IS NOT NULL)
  print 'Procedure created'
ELSE
  print 'Procedure NOT created'
GO


exec who @tran=Y

Answer 4

I'd suggest reading the following MSDN forum topic. It's about locking caused by Access to an SQL Server db. The suggestion is mainly to access the tables by queries using NOLOCK hint, so that the won't cause any locking problem. NOLOCK isn't the best solution, as it can cause other problems, but will reduce most of your locking problems.

The better solution would be to implement Remus's idea, setup snapshot isolation on your database. Or implement snapshot isolation level only for certain connections that you find causing blocking.

In order to properly monitor your server for blocking issues I'd suggest:

• build a server side trace that will monitor blocking issues longer than x seconds (I'd say 5 is good enough);
• save upper traces every day so you have a history for at least the last 30 days to see trends and patterns;
• have an hourly job that studies the current days' trace file and email to you any interesting blocking situations;

If you want a proactive response to this issue, instead of having a job every hour to monitor the traces, make it run every minute and kill any leading blocking Access session.

Answer 5

Following @Remus Rusanu's excellent answer, I have done the reader's task to connect the event to a stored procedure.

In my case the sp will write the xml of the blocking event to a table, but you are free to do whatever you want at that position.

So, follow Remus' code and create the queue, the service and the notification with a simple copy/paste from above. Add the sp_configure options and you are basically set.

The only thing left to do are

• Create a SP with no arguments.
• Create a table for the SP to write the data into (for my example, your SP may vary)
• Activate the SP on the queue

As soon as you activated the SP, the events will start flowing into your table.

I found out that the queue deactivates itself immediately, if the SP has an error. In that case, you need to go to Server Studio and activate it again in the context menu of the queue-entry ([msdb]->Service Broker->Warteschlangen in German version).

It took me quite some time to get this working and to find the right spots in the documentation, so I suppose this is helpful for others as well. I am using SQLServer 2005.

Create the SP with no arguments

CREATE PROCEDURE sp_addrecord
AS
  DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
  DECLARE @RecvReqMsg XML;
  DECLARE @RecvReqMsgName sysname;

  WHILE (1=1)
  BEGIN

    BEGIN TRANSACTION;

    WAITFOR
    ( RECEIVE TOP(1)
        @RecvReqDlgHandle = conversation_handle,
        @RecvReqMsg = message_body,
        @RecvReqMsgName = message_type_name
      FROM blocked_process_report_queue
    ), TIMEOUT 5000;

    IF (@@ROWCOUNT = 0)
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK;
    END

    IF @RecvReqMsgName = 
        N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    BEGIN
        print 'Insert SQL to pdix_lock_events'
        INSERT INTO pdix_lock_events
               VALUES(null, cast( @RecvReqMsg as xml));

    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END
    ELSE IF @RecvReqMsgName =
        N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    BEGIN
       END CONVERSATION @RecvReqDlgHandle;
    END

    COMMIT TRANSACTION;

  END
GO

Create the pdix_lock_events table

USE [msdb]
GO

/****** Object:  Table [dbo].[pdix_lock_events]    Script Date: 05/06/2015 17:48:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[pdix_lock_events](
    [locktime] [timestamp] NULL,
    [lockevent] [xml] NOT NULL
) ON [PRIMARY]

GO

Activate the SP on the queue

alter queue blocked_process_report_queue with activation( 
    procedure_name=sp_addrecord, 
    max_queue_readers = 1, 
    status = on, 
    execute as 'dbo');