انتظارها در SQL Server: سرور منتظر چیست؟

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

مدل ذهنی

هر درخواست در SQL Server یکی از سه حالت را دارد: در حال اجرا روی CPU، آمادهٔ اجرا ولی در صف CPU، یا منتظر چیزی (دیسک، قفل، حافظه، شبکه). مجموع زمان‌های حالت سوم، همان آمار انتظارهاست.

پس «سرور کند است» یعنی یکی از این دو: یا CPU کم داریم (حالت دوم بزرگ است)، یا منتظر چیزی هستیم (حالت سوم بزرگ است). آمار انتظارها می‌گوید کدام، و اگر دومی است، منتظر چه.

خواندن آمار

SELECT TOP (10)
       wait_type,
       wait_time_ms / 1000.0                          AS total_s,
       waiting_tasks_count                            AS tasks,
       wait_time_ms / NULLIF(waiting_tasks_count, 0)  AS avg_ms,
       signal_wait_time_ms / 1000.0                   AS cpu_queue_s
FROM   sys.dm_os_wait_stats
WHERE  waiting_tasks_count > 0
  AND  wait_type NOT LIKE N'SLEEP%'
  AND  wait_type NOT IN (N'WAITFOR', N'XE_TIMER_EVENT', N'BROKER_TASK_STOP',
                         N'CHECKPOINT_QUEUE', N'LOGMGR_QUEUE', N'DIRTY_PAGE_POLL',
                         N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_DISPATCHER_WAIT')
ORDER  BY wait_time_ms DESC;

دو ستون را با هم بخوانید. مجموع می‌گوید کدام نوع انتظار بیشترین وقت را گرفته. میانگین می‌گوید هر بار چقدر طول کشیده. صد هزار انتظار یک‌میلی‌ثانیه‌ای و صد انتظار یک‌ثانیه‌ای می‌توانند مجموع یکسان داشته باشند، ولی دو مشکل کاملاً متفاوتند.

signal_wait_time هم مهم است: این بخشی از انتظار است که منبع آماده شده ولی کار هنوز منتظر نوبت CPU مانده. نسبت بالای آن یعنی فشار واقعی CPU.

مشکل بزرگ این کوئری: تجمعی است

آمار از آخرین ری‌استارت جمع می‌شود. روی سروری که سه ماه بالا بوده، شما میانگین سه ماه را می‌بینید. مشکلی که امروز از ساعت ۱۰ شروع شده، در این عدد گم است.

برای دیدن «همین حالا»، دو نمونه با فاصله بگیرید و تفاضل را ببینید:

SELECT wait_type, wait_time_ms, waiting_tasks_count
INTO   #w1
FROM   sys.dm_os_wait_stats;

WAITFOR DELAY '00:05:00';

SELECT TOP (10)
       w2.wait_type,
       (w2.wait_time_ms - w1.wait_time_ms) / 1000.0 AS delta_s
FROM   sys.dm_os_wait_stats w2
JOIN   #w1 w1 ON w1.wait_type = w2.wait_type
WHERE  w2.wait_time_ms > w1.wait_time_ms
ORDER  BY delta_s DESC;

DROP TABLE #w1;

این تفاوت، بین «سرور در طول عمرش چه کرده» و «الان چه خبر است» است. هر ابزار پایش جدی همین کار را خودکار می‌کند: نمونهٔ منظم و نگه‌داری دلتا.

و هشدار: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) شمارنده‌ها را صفر می‌کند. وسوسه‌انگیز است ولی روی سرور تولیدی نزنیدش — تاریخچه‌ای که پاک می‌کنید ممکن است تنها چیزی باشد که برای تشخیص مشکل بعدی لازم دارید.

ترجمهٔ عملی

تلهٔ رایج

CXPACKET بالا، سال‌ها به‌اشتباه «مشکل» خوانده می‌شد و راه‌حل رایجش این بود که MAXDOP را روی ۱ بگذارند. این کار CXPACKET را از جدول پاک می‌کند و در عوض کوئری‌های بزرگ را کندتر می‌کند — یعنی معیار را درست کردید، نه سیستم را.

موازی‌سازی به‌خودی‌خود خوب است. مشکل وقتی است که کوئری‌های کوچک هم موازی می‌شوند، و علتش معمولاً آستانهٔ پیش‌فرض ۵ است که از دههٔ نود مانده. آستانه را بالا ببرید (مثلاً ۵۰) و MAXDOP را متناسب با هسته‌ها تنظیم کنید؛ این دو با هم، نه یکی‌شان.

و یک قاعدهٔ ساده برای شروع

انتظار شمارهٔ یک را بردارید و فقط روی همان کار کنید. حل کردنش معمولاً کل توزیع را عوض می‌کند و انتظار شمارهٔ دو دیگر آن چیزی نیست که فکر می‌کردید. بهینه‌سازی هم‌زمان چند چیز، بی اندازه‌گیری بین مراحل، همان کاری است که باعث می‌شود بعداً ندانید کدام تغییر اثر داشت.