Tìm những câu truy vấn nào đang bị block trong SQL Server?

Hôm nay do yêu cầu công việc, tôi cần truy vết những câu truy vấn nào đang bị block bởi những câu truy vấn khác trong SQL Server, có 4 cách ta có thể làm điều này:

1. Sử dụng Activity Monitor trong SQL Server Management Studio
2. Sử dụng System Stored Procedure sp_who2
3. Sử dụng một Stored Procedure nổi tiếng trong cộng đồng SQL Server là sp_WhoIsActive
4. Sử dụng DMV là sys.dm_exec_requests

Đoạn script sau đây sử dụng các DMV để tìm hiểu blocking trong hệ thống

SELECT
R.session_id
,DB_NAME(R.database_id) AS DatabaseName
,S.original_login_name AS LoginName
,S.host_name AS ClientMachine
,S.program_name AS ApplicationName
,R.start_time AS RequestStartTime
,ST.text AS SQLQuery
,QP.query_plan AS ExecutionPlan
,R.cpu_time AS CPUTime
,R.total_elapsed_time AS TotalTimeElapsed
,R.open_transaction_count AS TotalTransactionsOpened
,R.reads
,R.logical_reads
,R.writes AS TotalWrites
,CASE
WHEN R.wait_type IS NULL THEN 'Request Not Blocked'
ELSE 'Request Blocked'
END AS QueryBlockInfo
,blocking_session_id AS RequestBlockedBy
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
CROSS APPLY sys.dm_exec_query_plan (R.plan_handle) AS QP
ORDER BY TotalTimeElapsed DESC
GO

Kết quả, đây là trường hợp câu truy vấn không bị block.

Và đây, một câu truy vấn đang bị block

Sau đây là một ví dụ tạo ra blocking trong SQL Server, tôi sử dụng database AdventureWorks2008, bạn có thể download database mẫu này dễ dàng trên Internet.

Trong SSMS, tạo mới 1 session, execute đoạn script sau:

DECLARE @SalesOrderHeaderID INT

BEGIN TRANSACTION

INSERT INTO Sales.SalesOrderHeader (
 RevisionNumber
 ,OrderDate
 ,DueDate
 ,ShipDate
 ,STATUS
 ,OnlineOrderFlag
 ,PurchaseOrderNumber
 ,AccountNumber
 ,CustomerID
 ,SalesPersonID
 ,TerritoryID
 ,BillToAddressID
 ,ShipToAddressID
 ,ShipMethodID
 ,CreditCardID
 ,CreditCardApprovalCode
 ,CurrencyRateID
 ,Comment
 ,rowguid
 ,ModifiedDate
 )
VALUES (
 5
 ,'2011/06/20'
 ,'2011/06/25'
 ,'2011/06/30'
 ,5
 ,0
 ,NULL
 ,'10-4030-018749'
 ,18749
 ,NULL
 ,6
 ,28374
 ,28374
 ,1
 ,8925
 ,'929849Vi46003'
 ,NULL
 ,NULL
 ,NEWID()
 ,GETDATE()
 )

SET @SalesOrderHeaderID = @@IDENTITY

INSERT INTO Sales.SalesOrderDetail (
 SalesOrderID
 ,CarrierTrackingNumber
 ,OrderQty
 ,ProductID
 ,SpecialOfferID
 ,UnitPrice
 ,UnitPriceDiscount
 ,rowguid
 ,ModifiedDate
 )
VALUES (
 @SalesOrderHeaderID
 ,'4911-403C-98'
 ,15
 ,722
 ,1
 ,2039.994
 ,0
 ,NEWID()
 ,GETDATE()
 )
 ,(
 @SalesOrderHeaderID
 ,'4911-403C-98'
 ,4
 ,709
 ,1
 ,5.70
 ,0
 ,NEWID()
 ,GETDATE()
 )
 ,(
 @SalesOrderHeaderID
 ,'4911-403C-98'
 ,24
 ,716
 ,1
 ,28.8404
 ,0
 ,NEWID()
 ,GETDATE()
 )

Tiếp theo, mở 1 session thứ 2, execute đoạn script sau:

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
SELECT FirstName
 ,LastName
 ,SUM(soh.TotalDue) AS TotalDue
 ,MAX(OrderDate) AS LastOrder
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID
WHERE soh.OrderDate >= '2011/01/01'
GROUP BY c.CustomerID
 ,FirstName
 ,LastName

Chạy đoạn script tìm blocking ở trên, ta sẽ thấy kết quả.

Tham khảo: http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve