.::: How to Sample Simulation Blocking SQL Server & Trouble shoot :::.

 

1. Login to database and create database

create database blocking;

go 

2. Open 1 Session to New Quary Windows

Begin Tran ;

select 'query 1'

CREATE TABLE ##Employees (

    EmpId INT IDENTITY,

    EmpName VARCHAR(16),

    Phone VARCHAR(16)

)

GO

INSERT INTO ##Employees (EmpName, Phone)

VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')

GO

CREATE TABLE ##Suppliers(

    SupplierId INT IDENTITY,

    SupplierName VARCHAR(64),

    Fax VARCHAR(16)

)

GO

INSERT INTO ##Suppliers (SupplierName, Fax)

VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')

GO

 

3. Open 2 Session to New Quary Windows

Begin Tran ;

select 'query 2'

CREATE TABLE ##Employees (

    EmpId INT IDENTITY,

    EmpName VARCHAR(16),

    Phone VARCHAR(16)

)

GO

INSERT INTO ##Employees (EmpName, Phone)

VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')

GO

CREATE TABLE ##Suppliers(

    SupplierId INT IDENTITY,

    SupplierName VARCHAR(64),

    Fax VARCHAR(16)

)

GO

INSERT INTO ##Suppliers (SupplierName, Fax)

VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')

GO

4. Check proses tab session 1 (Blocking / Blk Bly example 59, SPID 58 54)

sp_who2 'active'

5. Check proses tab session 2

sp_who2 'active'

6. Check sp_WhoIsActive tab session 1

exec sp_WhoIsActive

7. Check sp_WhoIsActive tab session 2

exec sp_WhoIsActive

blank 

8. Check dbcc inputbuffer(58) tab session 1

dbcc inputbuffer(58)

9. Check dbcc inputbuffer(58) tab session 2

dbcc inputbuffer(58)

blank

10. check blocking

select @@servername,* from sys.sysprocesses where blocked > 0

dbcc inputbuffer(53)

 

11. Check header bloking

SET NOCOUNT ON

GO

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH

INTO #T

FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS

(

SELECT SPID,

BLOCKED,

CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

BATCH FROM #T R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

UNION ALL

SELECT R.SPID,

R.BLOCKED,

CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

R.BATCH FROM #T AS R

INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

)

SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +

CASE WHEN (LEN(LEVEL)/4 - 1) = 0

THEN 'HEAD -  '

ELSE '|------  ' END

+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

DROP TABLE #T

GO

11. Contoh hasil head blocking 

BLOCKING_TREE

    HEAD -  59 SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH  INTO #T  FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T  

    |         |------  54 select * from ##Employees

12. Trouble shoot with kill process

kill 59

kill 58

13. Session after kill process (using sp_who2 'active')

14. Clean Up (this step dont do in production)

DROP TABLE ##Employees

GO

DROP TABLE ##Suppliers

GO

15. Kesimpulan

Terdapat blocking Blk By 59 dengan spid 54/58 yang diakses user "sa" yang akses ke DBname="blocking" dengan aplikasi Microsoft SQL Server Management Studio - Query dengan status suspend 

saat query yang berjalan " select * from ##Employees" 

solusinya kill blocking proses nya

kill 58

kill 54

Blocking terjadi ketia sebuah coneksi ke sql server memblokir 1 atau lebih queri & coneksi lain ke sql server , conflik dengan query yang pertama. 

hal ini menyebabkan koneksi lainnya menunggu koneksi utama sampat lock nya terbuka,

1 koneksi memblokir koneksi lain tidak memandang dari aplikasi terpisah atau  dari clien yang berbeda

Sedikit bloking adalah normal dan dapat dihindari. tapi ketika bloking dalam jumlah banyak dapat menyebabkan koneksi yang lebih luas

yang dapat di selama pemblokiran adalah memblokir spid/sql prises id 

EventInfo = begin tran 

Select * 

from sysprocesses 

where blocked <> 0 ;

Select * from sysprocesses where spid=14 

==== 

16. Blocking from SQL Profiler

17. Script detail to collect blocking

SELECT @@servername as ServerName, DB_NAME() as DBName,s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc