و سپس «افزودن به صفحه اصلی» ضربه بزنید
و سپس «افزودن به صفحه اصلی» ضربه بزنید
وقتی یک سرور SQL Server کند میشود، اولین سوال این است: «چرا کند شده؟ دلیل اصلی کجاست؟ CPU؟ I/O؟ Locking؟ حافظه؟»
اغلب مدیران پایگاه داده ابتدا به CPU یا Disk نگاه میکنند، اما دقیقترین و سریعترین راه برای پیدا کردن گلوگاه، تحلیل Wait-Stats است. Wait-Stats در SQL Server مانند نوار قلب برای بیمار است؛ به شما نشان میدهد سرور دقیقاً کجا منتظر است و چه چیزی آن را کند کرده است.
Wait-Stats چیست و چرا مهمترین ابزار ما در Performance است؟
هرگاه SQL Server منتظر یک منبع باشد، یک Wait Event ثبت میشود.
منابع شامل:
جمع شدن این Waitها به ما نشان میدهد:
«سرور بیشترین وقت خود را صرف منتظر ماندن برای چه چیزی میکند؟»
به عبارت دیگر:
ریشه مشکل عملکرد = بیشترین Wait
DMVهای مهم برای تحلیل Wait-Stats
sys.dm_os_wait_stats
اصلیترین DMV برای خواندن Waitهای کل سرور.
sys.dm_exec_requests
Waitهای Sessionهای فعلی.
sys.dm_os_waiting_tasks
نشان میدهد هر Task الان دقیقاً کجا گیر کرده.
sys.dm_exec_query_stats + dm_exec_sql_text
برای ارتباط دادن Waitها با Queryهای مشکلساز.
sys.dm_io_virtual_file_stats
برای تحلیل I/O واقعی هر فایل دیتابیس.
sys.dm_exec_sessions
برای دیدن Sessionهای فعال و Block شده.
اسکریپت اصلی برای تحلیل Wait-Stats
این اسکریپت حرفهای، Waitهای مهم و تأثیرگذار را نمایش میدهد:
SELECT
wait_type,
wait_time_ms / 1000.0 AS wait_seconds,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_seconds,
signal_wait_time_ms / 1000.0 AS signal_wait_seconds,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'SLEEP_TASK','BROKER_TASK_STOP','BROKER_TO_FLUSH','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP',
'SLEEP_SYSTEMTASK','XE_DISPATCHER_WAIT','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;
سناریوهای واقعی و تفسیر آنها
مشتری میگوید: «سرور کند شده، لطفاً تحلیل عملکرد انجام دهید.»
۱: Wait زیاد روی PAGEIOLATCH_XX → مشکل Disk I/O
معنی
SQL Server منتظر است صفحهای از دیسک خوانده شود.
این یعنی Disk شما کند است.
علائم
- Queryهای سنگین Read
- گزارشهای کند در ساعات شلوغ
- ارتفاع زیاد READ_LATENCY
اسکریپت بررسی I/O فایلها
SELECT DB_NAME(database_id) AS DBName, file_id,
num_of_reads, io_stall_read_ms,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS read_latency
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY read_latency DESC;
راهحلها
- جابجایی MDF روی SSD
- کاهش Scan با ایجاد Index مناسب
- افزایش RAM برای Reduced Disk Reads
- فعال کردن Read-Ahead مناسب
۲: CXPACKET و CXCONSUMER → موازیسازی بیش از حد
علائم
- CPU بالا
- Queryهای سنگین OLTP
معنی
SQL Server Query را در چند Thread اجرا کرده و Threadها منتظر هم هستند.
این همیشه خطا نیست ولی اغلب یعنی Parallelism تنظیم نیست.
اسکریپت بررسی Queryهای Parallel
SELECT * FROM sys.dm_exec_requests
WHERE dop > 1;
راهحلها
- تنظیم MAXDOP بر اساس تعداد هستهها (معمولاً ۸ یا ۴)
- تنظیم Cost Threshold for Parallelism روی ۳۰ یا ۵۰
- بهینهسازی Queryهای Scan با Index مناسب
۳: WRITELOG → گلوگاه Log File
معنی
SQL Server نتوانسته Log را سریع روی دیسک بنویسد.
علائم
- عملیات Insert/Update/Delete کند
- Log File در یک Drive شلوغ یا کند قرار دارد
- VLFهای بیش از حد زیاد
اسکریپت بررسی وضع Log
DBCC LOGINFO;
راهحل
- انتقال Log به SSD
- کاهش تعداد VLF با Shrink + Grow به اندازههای مشخص
- بهینهسازی Batchهای نوشتاری
۴: LCK_M_XX → مشکل Locking / Blocking
معنی
Queryها مسدود شدهاند و یکی جلو همه را گرفته.
اسکریپت پیدا کردن Blocker اصلی
SELECT
blocking_session_id, session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
راهحلها
- Isolation Level مناسب
- استفاده از Read Committed Snapshot Isolation
- اصلاح Query کند
- Index مناسب برای جلوگیری از Table Scan
۵: SOS_SCHEDULER_YIELD → فشار CPU
معنی
Thread CPU لازم دارد اما CPU آزاد نیست.
نشانهها
- CPU همیشه ۷۵–۹۵%
- Queryهای سنگین تکراری
اسکریپت Queryهای CPUبر
SELECT TOP 20
total_worker_time/1000 AS CPU_ms,
text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_worker_time DESC;
راهحل
- Indexهای مناسب
- Query Tuning
- Upgrade CPU
- کاهش Parallelism
۶: RESOURCE_SEMAPHORE → کمبود Memory برای Queryها
علائم
- Queryهای سنگین Sort/Hash
- ایستادن Query قبل اجرای واقعی
- RAM اشباع
راهحلها
- ساخت Index برای کاهش Sort
- افزایش RAM
- کوچک کردن Queryهای سنگین
- اجرای Query به صورت Batch کوچک
۷: PAGELATCH_XX → مشکل TempDB یا Insertهای زیاد
معنی
SQL Server روی Bufferهای حافظه دوباره Lock شده.
راهحل
- اضافه کردن File در TempDB
- استفاده از Trace Flag 1117/1118 (در نسخههای قدیمی)
- کاهش Insertهای پیاپی
- Partitioning
روش جامع لاندا برای تحلیل Wait-Stats
وقتی مشتری میگوید «سرور کند شده»، تیم لاندا این مراحل را انجام میدهد:
۱) گرفتن Snapshot از Wait-Stats
۳۰ دقیقه بعد دوباره مقایسه میشود.
۲) بررسی Active Waitها با DMVها
→ dm_exec_requests
→ dm_os_waiting_tasks
۳) تحلیل Queryهای سنگین
→ dm_exec_query_stats
→ dm_exec_sql_text
۴) بررسی I/O و TempDB
→ dm_io_virtual_file_stats
→ sys.master_files
۵) تحلیل CPU و Parallelism
→ Worker Time
→ Scheduling
۶) ارائه Report و پیشنهادهای بهینهسازی
به همراه یک Performance Checklist
اسکریپت حرفهای برای جمعآوری Waitها قبل و بعد از آزمایش
-- Snapshot 1
SELECT * INTO WaitStats_Before
FROM sys.dm_os_wait_stats;
-- پاک کردن Waitها برای شروع تحلیل
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- اجرای Workload یا بررسی چند دقیقه
-- Snapshot 2
SELECT * INTO WaitStats_After
FROM sys.dm_os_wait_stats;
-- مقایسه:
SELECT
a.wait_type,
a.wait_time_ms - b.wait_time_ms AS delta_wait_time,
a.waiting_tasks_count - b.waiting_tasks_count AS delta_tasks
FROM WaitStats_After a
JOIN WaitStats_Before b ON a.wait_type = b.wait_type
ORDER BY delta_wait_time DESC;
این روش استاندارد تیمهای حرفهای برای Performance Review است.
نتیجهگیری
Wait-Stats یکی از قدرتمندترین ابزارهای SQL Server برای تحلیل عملکرد است.
با یادگیری تفسیر Waitها و استفاده از DMVها:
- گلوگاه سرور را در چند دقیقه پیدا میکنید.
- نیازی به حدس زدن یا بررسی دستی طولانی ندارید.
- دقیقاً میدانید مشکل از CPU، دیسک، حافظه یا Lock است.
- میتوانید Performance را چند برابر بهینه کنید.
تحلیل Wait-Stats، پایهٔ اصلی Performance Tuning حرفهای و عملیات روزمره تیم توسعه فناوری اطلاعات لاندا در پروژههای SQL Server است.
سوالات متداول (FAQ)
۱. آیا بالا بودن CXPACKET همیشه مشکل است؟
خیر، اگر همراه با CXCONSUMER باشد طبیعی است. مشکل زمانی است که CXPACKET نفر اول لیست باشد.
۲. Wait زیاد روی PAGEIOLATCH یعنی دیسک من خراب است؟
نه لزوماً؛ ممکن است Query شما Scan بزرگی انجام میدهد یا RAM کم است.
۳. برای تحلیل عملکرد، چند دقیقه Wait بگیریم؟
حداقل ۱۵ دقیقه—بهتر ۳۰ دقیقه.
۴. آیا باید Waitها را صفر کنیم؟
برای تحلیل دقیق، بله، ولی قبلش Snapshot بگیرید.
۵. کدام DMV بهترین است؟
برای Root Cause: sys.dm_os_wait_stats
برای Query زنده: sys.dm_exec_requests
برای Task گیر کرده: sys.dm_os_waiting_tasks
تماس و مشاوره با لاندا
نیاز به تحلیل عملکرد سرور SQL دارید؟
اگر سرور SQL شما کند شده، Queryها طولانی اجرا میشوند یا CPU/I/O بالا دارید، تیم لاندا یک تحلیل کامل Wait-Stats + گزارش ریشه مشکل + راهحلهای دقیق و عملی برایتان انجام میدهد.
برای درخواست «تحلیل عملکرد سرور»، همین حالا با ما تماس ✆ بگیرید.
نظری داده نشده