Published on

October 1, 2008

Understanding SQL Server Locking and Blocked Processes

Locking is a fundamental concept in SQL Server that ensures data integrity and prevents conflicts when multiple processes access the same data simultaneously. However, when a process holds locks for a long period of time, it can cause other processes to be blocked, resulting in performance issues and timeouts in the application.

In SQL Server 2005, a new feature called the Blocked Process Report was introduced to help identify and troubleshoot blocked processes. This feature provides detailed output whenever a blocked process exceeds a set system threshold.

To configure the blocked process threshold and generate the blocked process report, you can use the following SQL Server commands:

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

In this example, we set the blocked process threshold to 5 seconds. If a process is blocked for more than 5 seconds, it is considered a significant delay in performance. It’s important to note that setting the threshold too low can impact performance due to the constant monitoring of deadlocks.

Once the threshold is configured, you can create a server-side trace to capture the blocked process reports. The following script creates a trace that logs the reports:

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT

SET @maxfilesize = 50

EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, N'c:\BlockedProcessTrace', @maxfilesize, NULL

IF (@rc != 0) GOTO error

-- Set the events
DECLARE @on BIT
SET @on = 1

EXEC sp_trace_setevent @TraceID, 137, 15, @on
EXEC sp_trace_setevent @TraceID, 137, 1, @on
EXEC sp_trace_setevent @TraceID, 137, 13, @on

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- Display trace id for future references
SELECT TraceID = @TraceID

GOTO finish

error:
SELECT ErrorCode = @rc

finish:
GO

Make note of the TraceID that is output from running the above script. Also, ensure that the specified path for the trace file is correct. Keep in mind that not every SQL Server Service account has write access to the root of the C drive.

To test the blocked process report and see the output, you can follow these steps:

  1. Create a table in the tempdb database:
  2. USE tempdb
    CREATE TABLE temp1 (rowid INT)
    INSERT INTO temp1 VALUES (1)
    
  3. Create a stored procedure that causes blocking:
  4. CREATE PROCEDURE blockingprocedure AS
    SELECT * FROM temp1
    BEGIN TRAN
    UPDATE temp1 SET rowid = rowid + 1
    WAITFOR DELAY '00:00:20'
    ROLLBACK
    
  5. Open two new query windows and execute the following script in each of them:
  6. EXEC blockingprocedure
    
  7. Start the procedure in one window and then switch to the other window and start the procedure there. Wait for the second procedure to complete its execution.
  8. Close both query windows.
  9. Open a new query window and run the following query to clean up the example:
  10. DROP TABLE temp1
    DROP PROCEDURE blockingprocedure
    
    -- Stop the trace
    EXEC sp_trace_setstatus 2, 0
    
    -- Delete the trace but leave the file on the drive
    EXEC sp_trace_setstatus 2, 2
    
  11. To view the output in the trace file, run the following query:
  12. SELECT CAST(TextData AS XML), SPID, EndTime, Duration/1000/1000
    FROM fn_trace_gettable(N'c:\BlockedProcessTrace.trc', default)
    WHERE eventclass = 137
    

    By casting the TextData to an XML datatype, you can click on it and have it open up formatted in SQL Server Management Studio (SSMS). The output will show the blocked process report, including information about the blocking process and the blocked process.

    To further analyze the blocking process, you can use the sqlhandle from the execution stack. Copy the sqlhandle value and execute the following query:

    DECLARE @handle VARBINARY(64)
    SET @handle = 0x03000200a734c05067acb600229b00000100000000000000
    
    SELECT text FROM sys.dm_exec_sql_text(@handle)
    

    This query will display the SQL statement associated with the blocking process. You can use this information to identify and resolve the cause of the blocking.

    Understanding SQL Server locking and how to troubleshoot blocked processes is essential for maintaining optimal performance in your database environment. By utilizing the Blocked Process Report feature and analyzing the output, you can identify and resolve blocking issues efficiently.

    Hope this article helps you in understanding SQL Server locking and how to deal with blocked processes.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.