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:
- Create a table in the tempdb database:
- Create a stored procedure that causes blocking:
- Open two new query windows and execute the following script in each of them:
- 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.
- Close both query windows.
- Open a new query window and run the following query to clean up the example:
- To view the output in the trace file, run the following query:
USE tempdb
CREATE TABLE temp1 (rowid INT)
INSERT INTO temp1 VALUES (1)
CREATE PROCEDURE blockingprocedure AS
SELECT * FROM temp1
BEGIN TRAN
UPDATE temp1 SET rowid = rowid + 1
WAITFOR DELAY '00:00:20'
ROLLBACK
EXEC blockingprocedure
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
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.