وقتی یک درخواست به SQL Server ارسال می شود، از لحظه ای که آن درخواست برای پردازش وارد CPU می‏شود تا زمانی که پردازش پایان یافته و نتیجه به کلاینت ارسال شود را زمان پاسخگویی یا Response Time می‏گوییم. در بهترین حالت این زمان برابر است با زمان پردازش CPU، ولی در اغلب موارد اینگونه نیست و پردازش یک درخواست به دلایل متعددی از جمله انتظار برای در دسترس قرار گرفتن دیسک، پاسخ شبکه و... متوقف شده و پس از فراهم شدن شرایط، مجددا ادامه می یابد.

هر زمان که پردازش یک درخواست متوقف شود، SQL Server علت و میزان توقف آن را ذخیره می‏کند که به عنوان Wait Statistics شناخته می شود. بررسی و تحلیل صحیح این توقف ها یکی از مهمترین روش های عیب یابی سرعت در دیتابیس شماست.


آگهی روز


برای پردازش هر درخواست، ممکن است یک یا چند Thread ایجاد شود. Thread بعد از ایجاد، وارد CPU شده و پردازش آغاز می شود و Thread در وضعیت RUNNING است، مدت زمانی که Thread در CPU در حال اجراست Service Time نام دارد.

در میانه پردازش ممکن است Thread به یک Resource نیاز داشته باشد که در آن لحظه در دسترس نیست. به عنوان مثال ممکن است Thread درخواست دسترسی به یک Page را بدهد که آن Page در حافظه وجود ندارد و باید از دیسک خوانده شود، در این حالت Thread از CPU خارج شده و وارد وضعیت SUSPENDED می‏شود.

SQL Server علت این توقف را (در این مثال با عنوان PAGEIOLATCH) ذخیره می‏کند. مدت زمانی که Thread باید منتظر بماند تا شرایط ادامه پردازش فراهم شود Wait Time نامیده می‏شود. پس از فراهم شدن شرایط Thread وارد وضعیت RUNNABLE می‏شود تا دوباره بتواند CPU را در اختیار گرفته و به پردازش خود ادامه دهد. مدت زمانی که Thread در حالت RUNNABLE  منتظر می‏ماند تا وارد CPU شود Signal Time نام دارد.


به عبارت دیگر Signal Time زمانی شمرده میشود که Resource مورد نیاز فراهم یا آزاد شده اما مشغله CPU هنوز اجازه ادامه کار را به Thread نداده.


بدیهی است زمان پاسخ یک درخواست به این صورت محاسبه می‏شود:

Response = Service + Wait + Signal (time)

از طریق دو View زیر تمام توقف هایی که در حال حاضر در سیستم وجود دارد و همچنین سابقه این توقف ها در اختیار شما قرار می گیرد.

sys.dm_os_waiting_tasks
sys.dm_os_wait_stats 

سابقه توقف ها که در View دوم نمایش داده می شود جمع تمام توقف های همه Thread ها از آخرین Restart شدن سرویس SQL Server است. همچنین شما می توانید با استفاده از DBCC به صورت دستی این سوابق را Reset کنید:

DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR)

توقف CXPACKET: احتمالا در سرور شما هم بیشترین توقف خواهد بود ولی خبر خوب این است که عدد بالای این توقف الزاما نشان دهنده ضعف CPU نیست. بصورت کلی، در هر سیستمی که بیش از یک پردازنده وجود داشته باشد این وقفه شایع است. هنگامی که کوئری بصورت Multi Thread اجرا میگردد و هر کدام از Threadها که کار خود را تمام میکنند، منتظر اتمام کار سایر Threadها هستند این وقفه شمرده میشود. باید با توجه به سایر وقفه های پررنگ تحلیل کرد چرا Threadها منتظر میمانند، آیا دیسک درگیر است یا CPU دیر نوبت میدهد.

توقف PAGEIOLATCH_XX: هر زمان که یک Page اطلاعات باید از دیسک خوانده شده و وارد حافظه یا Buffer Pool شود این توقف رخ می‏دهد. بنابراین میزان بالای این توقف نشان دهنده این است که هارد شما تحت فشار است ولی بروز این مساله می‏تواند دلایل متعددی داشته باشد از جمله کم بودن RAM، وجود Query های بد، ایندکس نامناسب و ...

توقف ASYNC_NETWORK_IO: عدد بالای این توقف نشان دهنده این است که SQL Server بعد از آماده شدن نتایج، زمان زیادی منتظر می‏ماند تا کلاینت همه نتایج را دریافت و استفاده کند. از نام این توقف بر می‏آید که مشکلات کندی شبکه باعث بروز آن باشد ولی در اغلب موارد دیده شده که این مشکل به علت برنامه نویسی ضعیف رخ می‏دهد، جایی که کلاینت داده زیادی را درخواست می‏دهد و از پردازش سطر به سطر استفاده می‏کند.

توقف WRITELOG: همانطور که از نام این توقف بر می آید در سرور شما حجم نوشتن لاگ زیاد است و دیسک لاگ تحت فشار قرار گرفته. در صورتی که واقعا حجم تراکنش ها خیلی بالاست باید فکری به حال RAID دیسک بکنید ولی همیشه اینطور نیست. دو مورد از دلایل اصلی نوشتن لاگ زیاد، وجود Index های زائد و همچنین Fragmentation بالاست.

بیش از 600 نوع توقف در SQL Server وجود دارد. شما باید وضعیت توقف‏های سرور خودتان را بررسی کرده و با تحلیل آنها نقاط ضعف سیستم را کشف نمایید.