چرا SQL Server کند شده است؟ بررسی مهم‌ترین دلایل

«SQL Server کند شده» یک شکایت رایج است، اما علت‌های آن می‌تواند بسیار متفاوت باشد — از یک Query بد نوشته‌شده تا کمبود منابع سخت‌افزاری. کلید عیب‌یابی درست، بررسی سیستماتیک منابع اصلی است: CPU، حافظه (Memory)، دیسک (I/O)، و قفل‌شدگی (Blocking) — نه حدس‌زدن.

مرحله ۱ — بررسی کلی با Activity Monitor

ساده‌ترین نقطه‌ی شروع، Activity Monitor در SSMS است (راست‌کلیک روی نام سرور → Activity Monitor). چهار نمودار اصلی (% Processor Time، Waiting Tasks، Database I/O، Batch Requests/sec) یک تصویر کلی سریع می‌دهند.

مرحله ۲ — بررسی فشار روی CPU

اگر مصرف CPU مدام بالا (نزدیک ۱۰۰٪) است، معمولاً یعنی Query هایی بدون ایندکس مناسب اجرا می‌شوند و SQL Server مجبور است حجم زیادی داده را در حافظه پردازش کند (Scan به‌جای Seek). کوئری زیر Query هایی که بیشترین CPU را مصرف می‌کنند نشان می‌دهد:

SELECT TOP 10
  qs.total_worker_time / qs.execution_count AS avg_cpu_time,
  qs.execution_count,
  SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(qt.text)
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_cpu_time DESC;

برای یافتن کوئری‌های کند به‌صورت اختصاصی‌تر، راهنمای نحوه پیدا کردن Queryهای کند در SQL Server را ببین.

مرحله ۳ — بررسی فشار حافظه (Memory Pressure)

SQL Server داده‌های پرکاربرد را در حافظه نگه می‌دارد (Buffer Pool). اگر حافظه‌ی کافی تخصیص نداده باشی، SQL Server مجبور می‌شود مدام از دیسک بخواند که بسیار کندتر از حافظه است. مقدار Page Life Expectancy (PLE) شاخص خوبی است:

SELECT cntr_value AS page_life_expectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Page life expectancy';

به‌طور کلی، هرچه این عدد (بر حسب ثانیه) بالاتر باشد بهتر است؛ افت ناگهانی و مکرر آن نشانه‌ی فشار حافظه است.

مرحله ۴ — بررسی گلوگاه دیسک (I/O)

کوئری زیر پایگاه‌داده‌هایی که بیشترین انتظار I/O را دارند نشان می‌دهد — اگر عدد io_stall_ms بالا باشد، دیسک احتمالاً گلوگاه است:

SELECT DB_NAME(database_id) AS database_name,
  io_stall_read_ms + io_stall_write_ms AS io_stall_ms,
  num_of_reads, num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_ms DESC;

مرحله ۵ — بررسی Blocking (قفل‌شدگی بین تراکنش‌ها)

گاهی سیستم کند نیست، بلکه یک تراکنش دیگر آن را مسدود (Block) کرده. این کوئری تراکنش‌های در حال انتظار را نشان می‌دهد:

SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

برای درک عمیق‌تر این موضوع، درس Blocking و Locking را در دوره‌ی SQLFarsi ببین.

خلاصه: چک‌لیست تشخیص کندی

جمع‌بندی

کندی SQL Server تقریباً همیشه یکی از این چهار علت را دارد: CPU، حافظه، دیسک، یا Blocking. با بررسی سیستماتیک (نه حدس‌زدن) و استفاده از Dynamic Management Views (مثل sys.dm_exec_query_stats و sys.dm_os_performance_counters)، می‌توانی به‌سرعت گلوگاه واقعی را پیدا کنی.