Troubleshooting Blocking

Troubleshooting Blocking

برای اینکه SQL Server تمامیت داده ها (data integrity) را برای خواندن ها و نوشتن ها استفاده کند، از قفل هایی استفاده می کند، طوریکه تنها یک فرایند در هر زمان کنترل داده را دارد. قفل های زیادی وجود دارند که مورد استفاده قرار می گیرند، از جمله Shared, Update, Exclusive, Intent و غیره و هر کدام از آنها یک رفتار تاثیر متفاوت روی داده ی شما دارند.

وقتی قفل ها برای یک مدت طولانی حفظ می شود باعث مسدود شدن (blocking) می شوند که به این معناست که یک فرایند باید منتظر اتمام فرایند دیگر با داده شود و قبل از اینکه فرایند دیگر بتواند ادامه پیدا کند، قفل را آزاد کند. این مسئله مشابه deadlocking (قفل دوبل) می باشد که در آن دو فرایند در یک منبع در حال انتظار هستند، اما بر خلاف deadlocking، به محض اینکه فرایند اول منبع را رها می کند، blockingنیز آزاد می شود.

توضیحات

همانطور که در بالا توضیح داده شد blocking در نتیجه انتظار دو داده برای دسترسی به یک داده می باشد، و لازم است فرایند دوم منتظر شود تا فرایند اول منبع را رها کند. این چگونگی کار SQL Server در همه ی زمان ها می باشد، اما معمولا شما blocking را مشاهده نمی کنید، زیرا زمانیکه قفل ها حفظ می شوند خیلی کوتاه می باشد.

شاید متوجه شوید که قفل ها در هنگام آپدیت کردن داده حفظ می شوند، اما این قفل ها در هنگام خواندن داده نیز استفاده می شوند. وقتی داده آپدیت می شود، یک قفل Update استفاده می شود و وقتی که داده خوانده می شود یک قفل Shared مورد استفاده قرار می گیرد. یک قفل Update یک قفل انحصاری برای این فرایند روی داده ایجاد می کند و قفل Shared به دیگر فرایندها اجازه می دهد تا برای دسترسی به داده از یک قفل Shared استفاده کنند و زمانی که فرایندها سعی در دسترسی به یک داده ی مشابه دارند locking و blocking اتفاق می افتد.

در یک پنجره ی query این فرمان را اجرا کنید.

sp_who2‎

این خروجی گزارش داده شده می باشد. در اینجا می توانیم ستون BlkBy را مشاهده کنیم که نشان می دهد SPID 60 توسط SPID 50 مسدود شده است.

Activity Monitor

در SSMS روی نمونه ی SQL Server راست کلیک کنید و Activity Monitor را انتخاب کنید. در بخش Processes اطلاعاتی شبیه به تصویر زیر مشاهده خواهید کرد. در اینجا می توانیم اطلاعاتی مشابه sp_who2 ببینیم، اما همچنین می توانیم زمان انتظار (Wait Time)، نوع انتظار (Wait Type) و منبعی که SPID 60 منتظر آن است را مشاهده کنیم.

Report - All Blocking Transactions

روش دیگر استفاده از گزارش های داخلی SSMS می باشد. روی نام SQL Server راست کلیک کرده و Reports > Standard Reports > Activity - All Block Transactions را انتخاب کنید.

Querying Dynamic Management Views

می توانید از DMVs نیز برای گرفتن اطلاعات در مورد blocking استفاده کنید.

SELECT session_id, command, blocking_session_id, wait_type, wait_time, ‎wait_resource, t.TEXT
FROM sys.dm_exec_requests ‎
CROSS apply sys.dm_exec_sql_text(sql_handle) AS t
WHERE session_id > 50 ‎
AND blocking_session_id > 0‎
UNION
SELECT session_id, '', '', '', '', '', t.TEXT
FROM sys.dm_exec_connections ‎
CROSS apply sys.dm_exec_sql_text(most_recent_sql_handle) AS t
WHERE session_id IN (SELECT blocking_session_id ‎
‎                    FROM sys.dm_exec_requests ‎
‎                    WHERE blocking_session_id > 0)‎

و این تصویر خروجی می باشد، می توانیم اطلاعات Blocking به همراه فرمان های T-SQL را مشاهده کنیم.