Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wondering what causes Attention events in a SQL server Profiler trace and how these events can result into severe blocking problems? I will attempt to provide some answers in this post.
First of all let’s talk about what Attentions events are and what may cause them. According to BOL –
"The Attention event class indicates that an attention event, such as cancel, client-interrupt requests, or broken client connections, has occurred. Cancel operations can also be seen as part of implementing data access driver time-outs"
There are three most common reasons of Attention events that I have seen in my experience so far. In each case, Attention events were causing open transactions and resulting in massive blocking issues. Here are those three scenarios -
1. Query Cancellation
User may cancel the currently executing query/batch request at any time - may be the user got tired of waiting on the screen! If the query or the batch was in an explicit user transaction (BEGIN TRAN …. END TRAN), these Attention events could result in open transactions and severe blocking problems. We can reproduce this scenario from within SQL Server Management Studio. Try the following -
USE TEMPDB
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID (N'[DBO].[TEST]') AND TYPE in (N'U'))
DROP TABLE [DBO].[TEST]
GO
--Create a Test table and Insert few records in it
CREATE TABLE TEST (C1 INT, C2 VARCHAR (100))
GO
INSERT INTO TEST VALUES (1, 'AAA')
GO
INSERT INTO TEST VALUES (2, 'BBB')
GO
-- Simulate a user transaction that will take a several seconds to execute
-- we will use the WAITFOR command to simulate such a transaction
BEGIN TRAN
UPDATE TEST SET C2 = 'MODIFY1'
WHERE C1 = 1
WAITFOR DELAY '00:00:45'
COMMIT TRAN
SELECT @@TRANCOUNT GO
SELECT * FROM TEST WHERE C1=1 GO
So the question is how long will this blocking go on for? Actually this blocking chain will go indefinitely until one of the following events occurs –
2. Query Timeouts:
This is the second most common reason I have seen in my experience, which causes Attention events, open transactions and blocking issues. The default query timeout in SQL Server is 0, which means that a query will run indefinitely until completion. However, application developers can change the default query timeout value in SQL Server connection string. Also, OLEDB and ODBC providers of SQL Server have non-zero query timeout values as discussed in this article – DBA’s Quick Guide to Timeouts. Query timeouts, if not handled properly, can cause blocking issues in SQL Server. Let’s see how that can happen -
USE TEMPDB
GO
-- Run a TSQL batch request that would take a few seconds to execute
-- We will use the WAITFOR command to simulate such a query
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[TEST2]') AND TYPE IN (N'U'))
DROP TABLE [DBO].[TEST2]
GO
--Create a Test table and Insert few records in it
CREATE TABLE TEST2 (C1 INT, C2 VARCHAR (100))
GO
INSERT INTO TEST2 VALUES (1, 'AAA')
GO
INSERT INTO TEST2 VALUES (2, 'BBB')
GO
--Start a user transaction that would take about 10 seconds to complete
BEGIN TRAN
UPDATE TEST2 SET C2 = 'MODIFY1'
WHERE C1 = 1
WAITFOR DELAY '00:00:45'
COMMIT TRAN
After 15 seconds, which is the query timeout value that we had set while connecting to the server, the batch will terminate with the following timeout error –
Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
What about Lock timeout errors? Will they also result in Attention events?
The answer is NO! Lock Timeout errors do not cause Attention events and execution will proceed to the next statements in the transaction. See it for yourself!
Run the following batch from a new query window (if you are using an existing query window that is already open, make sure to roll back all open transactions in that window) -
USE TEMPDB
GO
-- Create a Test table and Insert some rows in it
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[TEST3]') AND TYPE IN (N'U'))
DROP TABLE [DBO].[TEST3]
GO
CREATE TABLE TEST3 (C1 INT, C2 VARCHAR (100))
GO
INSERT INTO TEST3 VALUES (1, 'AAA')
GO
INSERT INTO TEST3 VALUES (2, 'BBB')
GO
-- Run a TSQL batch request that will start a
-- transaction but do not COMMIT or ROLLBACK the transaction
-- I am doing this to create a classic blocking
-- scenario to test lock timeout errors
BEGIN TRAN
UPDATE TEST3 SET C2 = 'MODIFY1'
WHERE C1 = 1
GO
USE Tempdb GO SET LOCK_TIMEOUT 5000 GO BEGIN TRAN UPDATE TEST3 SET C2 = 'MODIFYAGAIN' WHERE C1 = 1 COMMIT TRAN
Msg 1222, Level 16, State 45, Line 3 Lock request time out period exceeded. The statement has been terminated.
3. Applications not processing the result set completely
I have seen this problem while working with a customer who had an ODBC application fetching data from SQL Server. The application was coded to ask for a result set in a cursor, fetch the first few records (and not all of the requested rows) and discard the rest. This problem has been described in detail in this blog. Unfortunately, I don’t have an application to demonstrate this behavior. But if you are noticing the following characteristics with your application and seeing a large number of Attention events and blocking chains in SQL Server, this could be the case.
Solutions:
So now that we know how Attention events can result in open transactions and blocking problems, what can we do to resolve this problem? Here are two solutions that I have successfully tried while working with customers -
IF @@TRANCOUNT > 0 ROLLBACK
Important: This (or setting it at the connection level) does not guarantee desired behavior in case of an Attention event though, since application code can override the setting. The only completely reliable way would be to set XACT_ABORT to ON before every BEGIN TRAN
Anonymous
May 21, 2012
It’s good to come across a post every once in a while that isn’t the same outdated information.I’ve bookmarked your site and I’m including your RSS feeds to my RSS Reader.
Perhaps readers of this post could find interesting the following post: www.agile-code.com/.../an-easy-way-to-avoid-sql-server-nested-transactions which explains how to avoid nested transactions and how to manage them.
Anonymous
January 16, 2015
There is one more. It appears that SqlBulkCopy.WriteToServer() method was fine on .NET 2 but somewhere between that and .NET 4 it now raises an Attention alert directly after all of the INSERT BULK statements have completed (whether you dispose or not, it happens immediately). What a pain. I haven't been able to find this documented anywhere else though one person had mentioned seeing similar on MSDN.
Anonymous
March 25, 2015
we have a sp that does update and insert but there isn't any Begin Transaction... commit/rollback used in the sp. If a user issues a cancel or connection is broken while the sp is running, would that cause open transaction and therefore blocking and the attention alert? This SP could have up to few hundred executions in a 1~2 mins and constantly encountered blocking (idle blocker) during this heavy usage.
Anonymous
June 02, 2015
Thanks for the great article. this helped in my analysis of blocking events
Please sign in to use this experience.
Sign in