SQL Server کند شده؛ از کجا شروع کنیم؟ راهنمای جامع کارایی

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

این مقاله همان مسیری است که خودم طی می‌کنم وقتی روی سروری می‌نشینم که «کند شده». ترتیبش مهم است: از سؤالی شروع می‌کنیم که جوابش مسیر را تعیین می‌کند، نه از عددی که در دسترس است.

اول: سرور منتظر چیست؟

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

SELECT TOP (10)
       wait_type,
       wait_time_ms / 1000.0                    AS wait_s,
       waiting_tasks_count                      AS tasks,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_s
FROM   sys.dm_os_wait_stats
WHERE  waiting_tasks_count > 0
  AND  wait_type NOT IN (
       N'CLR_SEMAPHORE', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR',
       N'BROKER_TASK_STOP', N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
       N'REQUEST_FOR_DEADLOCK_SEARCH', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE',
       N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'DIRTY_PAGE_POLL',
       N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'BROKER_EVENTHANDLE')
ORDER  BY wait_time_ms DESC;

آن فهرست حذف‌شده مهم است: این‌ها انتظارهای بی‌ضرر و همیشه‌بالای موتورند و اگر حذفشان نکنید، صدر جدول را می‌گیرند و چیز واقعی را دفن می‌کنند.

نکتهٔ دومی که کم‌تر گفته می‌شود: این آمار از آخرین ری‌استارت جمع شده. اگر سرور دو ماه بالا بوده، شما میانگین دو ماه را می‌بینید، نه مشکل امروز را. برای دیدن «همین حالا»، دو بار با فاصلهٔ چند دقیقه نمونه بگیرید و تفاضلشان را ببینید. (این یکی از کارهای پایهٔ DBMug است: هر دقیقه نمونه می‌گیرد و دلتا را نگه می‌دارد، چون عدد تجمعی برای عیب‌یابی تقریباً بی‌فایده است.)

ترجمهٔ رایج‌ترین انتظارها

• PAGEIOLATCH_* — منتظر خواندن صفحه از دیسک. یعنی داده در حافظه نیست: یا حافظه کم است، یا کوئری‌ها بیش از نیاز می‌خوانند (اسکن جای seek).
• WRITELOG — منتظر نوشتن در لاگ ترنزکشن. دیسک لاگ کند است، یا تراکنش‌های بسیار ریز و بسیار زیاد دارید.
• LCK_M_* — قفل. یعنی یک نشست منتظر نشست دیگری است. مشکل هم‌زمانی است، نه سخت‌افزار.
• CXPACKET / CXCONSUMER — هماهنگی بین رشته‌های یک کوئری موازی. به‌خودی‌خود بد نیست؛ اگر بالاست، اغلب یعنی کوئری‌ای که نباید موازی می‌شد، موازی شده.
• SOS_SCHEDULER_YIELD — فشار واقعی CPU.
• RESOURCE_SEMAPHORE — کوئری‌ها منتظر مجوز حافظه‌اند. یعنی کوئری‌هایی حافظهٔ بیش از نیاز درخواست می‌کنند (اغلب از تخمین غلط ردیف).
• ASYNC_NETWORK_IO — تقریباً همیشه تقصیر SQL Server نیست: اپلیکیشن ردیف‌ها را کند می‌خواند. کلاسیکش این است که برنامه یک میلیون ردیف می‌گیرد و در حافظه فیلترشان می‌کند.

دوم: کدام کوئری؟

بعد از فهمیدن نوع گلوگاه، سراغ مصرف‌کننده‌ها بروید. اگر SQL Server 2016 یا بالاتر دارید و Query Store روشن است، بهترین منبع همان است چون تاریخچه دارد و می‌توانید «قبل و بعد از انتشار جدید» را مقایسه کنید.

SELECT TOP (20)
       q.query_id,
       SUBSTRING(t.query_sql_text, 1, 200)      AS sql_text,
       rs.count_executions                      AS execs,
       rs.avg_duration / 1000.0                 AS avg_ms,
       rs.avg_logical_io_reads                  AS avg_reads,
       rs.avg_cpu_time / 1000.0                 AS avg_cpu_ms
FROM   sys.query_store_runtime_stats rs
JOIN   sys.query_store_plan p  ON p.plan_id  = rs.plan_id
JOIN   sys.query_store_query q ON q.query_id = p.query_id
JOIN   sys.query_store_query_text t ON t.query_text_id = q.query_text_id
WHERE  rs.last_execution_time > DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER  BY rs.avg_duration * rs.count_executions DESC;

به ترتیب مرتب‌سازی دقت کنید: مدت میانگین × تعداد اجرا، نه فقط مدت. کوئری‌ای که ۳۰ ثانیه طول می‌کشد و شبی یک بار اجرا می‌شود، مشکل شما نیست. کوئری‌ای که ۸۰ میلی‌ثانیه است و دقیقه‌ای ۴۰۰۰ بار اجرا می‌شود، همان چیزی است که سرور را زمین زده. این تله‌ای است که تیم‌ها مرتب در آن می‌افتند، چون فهرست «کندترین کوئری‌ها» چشم‌گیرتر است.

سوم: چرا این کوئری کند است؟

چهار علت، تقریباً به همین ترتیب فراوانی:

۱. ایندکس مناسب ندارد

پلن اجرا را ببینید. اگر Index Scan یا Table Scan روی جدول بزرگ دارید در حالی که فقط چند ردیف می‌خواهید، ایندکس مناسب نیست. SQL Server خودش پیشنهادهایی نگه می‌دارد، ولی کورکورانه اجرا نکنید: هر ایندکس، نوشتن را کند و فضا را مصرف می‌کند، و پیشنهادها اغلب هم‌پوشان و بی‌توجه به بقیهٔ بار سیستم‌اند. این را در مقالهٔ ایندکس گمشده جداگانه شرح داده‌ام.

۲. آمار قدیمی است

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

۳. کوئری به‌گونه‌ای نوشته شده که ایندکس را بی‌اثر می‌کند

کلاسیک‌ترین: تابع روی ستون در شرط.

-- ایندکس روی created_at بی‌استفاده می‌ماند
WHERE YEAR(created_at) = 2026

-- همین شرط، ایندکس‌پذیر
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

هم‌خانوادهٔ خطرناک‌ترش، ناسازگاری نوع است: مقایسهٔ ستون varchar با پارامتر nvarchar باعث تبدیل نوع ضمنی و اسکن کل ایندکس می‌شود. این یکی به‌ندرت در کد دیده می‌شود چون در لایهٔ ORM اتفاق می‌افتد.

۴. منتظر قفل است

کوئری کند نیست؛ منتظر است. کسی که واقعاً مسدود می‌کند را این‌طور پیدا کنید:

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       r.status, DB_NAME(r.database_id) AS db,
       SUBSTRING(t.text, (r.statement_start_offset/2) + 1, 200) AS running_sql
FROM   sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE  r.blocking_session_id <> 0;

ریشهٔ زنجیره را دنبال کنید، نه قربانی آخر را. و اگر مسدودکننده در حالت sleeping با تراکنش باز است، مشکل در اپلیکیشن است: تراکنشی باز مانده و بسته نشده.

چیزهایی که قبل از هر تغییر کدی باید چک کنید

چند تنظیم سطح سرور هستند که اگر غلط باشند، هر بهینه‌سازی کوئری را خنثی می‌کنند:

• سقف حافظه. max server memory پیش‌فرض بی‌نهایت است؛ باید مقداری بگذارید که برای ویندوز چند گیگ بماند.
• MAXDOP و آستانهٔ موازی‌سازی. آستانهٔ پیش‌فرض ۵ از سال ۱۹۹۵ مانده و روی سخت‌افزار امروز یعنی کوئری‌های کوچک هم موازی می‌شوند.
• رشد فایل. اگر رشد فایل داده روی درصد یا مقدار کوچک تنظیم باشد، سیستم مرتب برای بزرگ کردن فایل می‌ایستد.
• Instant File Initialization. بی آن، بزرگ شدن فایل داده یعنی صفر نوشتن روی تمام فضای جدید.
• تعداد فایل tempdb. یک فایل روی سرور چند هسته‌ای، گلوگاه تخصیص می‌سازد.

دامی که تقریباً همه در آن می‌افتند

وقتی سرور کند است، فشار زیادی برای «انجام دادن یک کاری» وجود دارد. سه کاری که در این حالت انجام می‌شود و کمکی نمی‌کند:

• ری‌استارت. کش را پاک می‌کند و همه‌چیز موقتاً تازه به نظر می‌رسد؛ چند ساعت بعد دقیقاً همان وضع برمی‌گردد، ولی حالا آمار انتظارها را هم از دست داده‌اید — همان چیزی که برای تشخیص لازم بود.
• بازسازی همهٔ ایندکس‌ها. گاهی کمک می‌کند، بیشتر وقت‌ها فقط آمار را به‌روز می‌کند و همان اثر را با کسری از هزینه می‌شد گرفت.
• اضافه کردن CPU یا RAM. اگر گلوگاه PAGEIOLATCH است، RAM بیشتر واقعاً کمک می‌کند. اگر گلوگاه قفل است، سخت‌افزار جدید هیچ اثری ندارد و پول را دور ریخته‌اید.

ترتیب کار، در یک نگاه

1. انتظارها را ببینید (دلتا، نه تجمعی). نوع گلوگاه را تعیین کنید.
2. در همان دسته، پرهزینه‌ترین کوئری‌ها را با معیار «مدت × تعداد اجرا» پیدا کنید.
3. برای هر کوئری، علت را از آن چهار خانواده تشخیص دهید.
4. تنظیمات سطح سرور را یک بار بازبینی کنید.
5. یک تغییر بدهید و اندازه بگیرید. اگر چند تغییر را با هم بدهید، نمی‌دانید کدام کار کرد.

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