مقدمه: از عدد خام تا تشخیص قابل اقدام

شمارنده‌های کارایی SQL Server زبان فشرده‌ای برای توصیف رفتار موتور پایگاه داده هستند. آن‌ها حجم Batch، چرخه تولید Plan، وضعیت Buffer Pool، انتظار Memory Grant، تعداد اتصال، رقابت Lock و فعالیت Transaction Log را به عدد تبدیل می‌کنند. اما یک Counter به‌تنهایی تشخیص نیست؛ عدد باید در یک بازه زمانی، در مقایسه با خط مبنا و در کنار شاخص‌های مکمل خوانده شود.

این راهنما یک مسیر عملی برای ساخت پایش قابل‌اعتماد ارائه می‌کند: کشف Counterها از sys.dm_os_performance_counters، تشخیص Gauge و Cumulative Rate و Fraction، نمونه‌برداری هم‌زمان، محاسبه Rate، مدیریت Restart، انتخاب Instance، ذخیره تاریخچه، ساخت Dashboard و اتصال Alert به Runbook. هدف نهایی تولید نمودار زیبا نیست؛ هدف کوتاه‌کردن فاصله میان رخداد، Evidence و اقدام ایمن است.

در SQL Server بسیاری از نام‌های دارای /sec در DMV مقدار تجمعی ارائه می‌کنند. برای نرخ واقعی باید دو Snapshot گرفته شود و اختلاف مقدار بر زمان واقعی تقسیم گردد. در مقابل Page Life Expectancy، Memory Grants Pending و User Connections Gauge هستند و مقدار جاری آن‌ها معنا دارد. Buffer Cache Hit Ratio نیز Fraction است و باید Numerator بر Base تقسیم شود. شناخت cntr_type پیش‌شرط هر محاسبه است.

خط مبنا از آستانه عمومی مهم‌تر است. سرور OLTP با هزاران Batch کوچک، انبار داده با Scanهای بزرگ و سامانه گزارش شبانه رفتارهای متفاوت دارند. Baseline باید ساعت عادی، اوج، پایان ماه، ETL، Backup و Maintenance را جدا کند. سپس Alert فقط وقتی فعال شود که انحراف چند نمونه ادامه داشته باشد و یک نشانه دیگر مانند Wait، Latency یا افت Throughput آن را تأیید کند.

فهرست دسترسی سریع

هر مورد زیر مقاله‌ای مستقل با تعریف، Query، ده مثال قابل اجرا، خروجی نمونه، FAQ و نکات کارایی دارد. ترتیب فهرست از زیرساخت جمع‌آوری تا بارکاری، حافظه، I/O، هم‌زمانی و تراکنش طراحی شده است.

• آموزش sys.dm_os_performance_counters
• آموزش Important Counter Categories
• آموزش Batch Requests/sec
• آموزش SQL Compilations/sec
• آموزش SQL Re-Compilations/sec
• آموزش Page Life Expectancy
• آموزش Buffer Cache Hit Ratio
• آموزش Lazy Writes/sec
• آموزش Page Reads/sec
• آموزش Page Writes/sec
• آموزش Checkpoint Pages/sec
• آموزش Memory Grants Pending
• آموزش Target Server Memory
• آموزش Total Server Memory
• آموزش User Connections
• آموزش Lock Waits/sec
• آموزش Deadlocks/sec
• آموزش Log Flushes/sec
• آموزش Transactions/sec

معماری شمارنده‌ها در SQL Server

نمای sys.dm_os_performance_counters ستون‌های object_name، counter_name، instance_name، cntr_value و cntr_type را در اختیار می‌گذارد. object_name معمولاً پیشوند Default یا Named Instance دارد؛ به همین دلیل Query قابل‌حمل می‌تواند انتهای نام Object را فیلتر کند. با این حال counter_name و instance_name باید تا حد امکان دقیق باشند تا هزینه، ابهام و دوباره‌شماری کم شود.

Gauge وضعیت همین لحظه را نشان می‌دهد. Memory Grants Pending، User Connections، Target Server Memory، Total Server Memory و Page Life Expectancy نمونه‌های این گروه‌اند. نگهداری Min و Max پنجره در کنار Average اهمیت دارد، زیرا میانگین می‌تواند یک افت یا جهش کوتاه اما مؤثر را مخفی کند. جهت مطلوب نیز یکسان نیست؛ PLE بالاتر معمولاً بهتر است، ولی Pending Grant پایین‌تر مطلوب است.

Counter تجمعی از شروع سرویس افزایش می‌یابد. Batch Requests/sec، SQL Compilations/sec، Page Reads/sec، Lock Waits/sec، Log Flushes/sec و Transactions/sec برای ساخت نرخ به دو نمونه نیاز دارند. Collector باید SampleTime دقیق و sqlserver_start_time را ذخیره کند. اگر مقدار جدید کوچک‌تر یا Startup Time متفاوت شد، نخستین نمونه Baseline جدید است و نباید با مقدار قبلی Delta ساخته شود.

Fraction از دو ردیف مرتبط ساخته می‌شود. Buffer Cache Hit Ratio و Buffer Cache Hit Ratio Base باید در یک Statement و یک Snapshot خوانده شوند؛ سپس Numerator بر Base تقسیم و در صد ضرب می‌شود. NULLIF از تقسیم بر صفر جلوگیری می‌کند. ذخیره هر دو مقدار خام امکان بازحساب درصد، کنترل Formula و مهاجرت سامانه مانیتورینگ را حفظ می‌نماید.

Instance دامنه Counter را مشخص می‌کند. Objectهای Databases و Locks ردیف‌هایی برای پایگاه، نوع Lock و _Total دارند. انتخاب همه ردیف‌ها و جمع‌زدن آن‌ها ممکن است _Total را دوباره بشمارد. در تاریخچه، نام Instance را ستون مستقل قرار دهید و Dashboard را مجبور کنید دامنه انتخاب‌شده را در عنوان و Tooltip نمایش دهد.

دسته‌بندی منطقی شاخص‌های ضروری

این دسته‌ها باید روی یک Timeline مشترک قرار گیرند. برای نمونه، افت PLE بدون افزایش Page Reads ممکن است معنایی متفاوت از افت PLE همراه با Lazy Writes و PAGEIOLATCH داشته باشد. افزایش Compilation بدون رشد Batch می‌تواند از Queryهای Ad-hoc ناشی شود، در حالی که رشد هم‌زمان هر دو شاید فقط نتیجه افزایش طبیعی تقاضا باشد.

1. نمای سیستمی sys.dm_os_performance_counters

دسترسی مستقیم به شمارنده‌های داخلی SQL Server بدون نیاز به Performance Monitor است. این مورد در گروه «زیرساخت پایش» قرار می‌گیرد و واحد تحلیلی آن «ردیف شمارنده» است. هر ردیف یک شمارنده، شیء و در صورت لزوم یک نمونه را نشان می‌دهد و روش تفسیر آن به cntr_type وابسته است.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: بعد از راه‌اندازی مجدد سرویس، شمارنده‌های تجمعی از نو آغاز می‌شوند و نام object_name پیشوند نام نمونه SQL Server را دارد. برای تأیید نتیجه، روند آن را با sys.dm_os_sys_info، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل sys.dm_os_performance_counters با ده مثال عملی و خروجی نمونه

2. دسته‌بندی شمارنده‌های مهم SQL Server

انتخاب مجموعه‌ای متوازن از شمارنده‌های بارکاری، حافظه، ورودی/خروجی، قفل و لاگ است. این مورد در گروه «طراحی پایش» قرار می‌گیرد و واحد تحلیلی آن «دسته» است. یک داشبورد سالم باید هم‌زمان فشار، ظرفیت، انتظار و نرخ کار انجام‌شده را نمایش دهد؛ یک عدد منفرد برای تشخیص کافی نیست.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: آستانه ثابت بدون خط مبنا و بدون توجه به نوع بارکاری می‌تواند هشدار کاذب ایجاد کند. برای تأیید نتیجه، روند آن را با sys.dm_os_wait_stats، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Important Counter Categories با ده مثال عملی و خروجی نمونه

3. شمارنده Batch Requests/sec

اندازه‌گیری نرخ Batchهای دریافت‌شده و نمایش حجم کلی فعالیت موتور است. این مورد در گروه «SQL Statistics» قرار می‌گیرد و واحد تحلیلی آن «درخواست در ثانیه» است. افزایش آن معمولاً نشانه رشد بار است؛ ارزش تحلیلی اصلی زمانی ایجاد می‌شود که با CPU، زمان پاسخ و Compilation مقایسه شود.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: cntr_value نرخ آماده نیست و برای نرخ واقعی باید اختلاف دو نمونه بر فاصله زمانی تقسیم شود. برای تأیید نتیجه، روند آن را با SQL Compilations/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Batch Requests/sec با ده مثال عملی و خروجی نمونه

4. شمارنده SQL Compilations/sec

محاسبه نرخ تولید Planهای اجرایی و ارزیابی میزان استفاده مجدد از Plan Cache است. این مورد در گروه «SQL Statistics» قرار می‌گیرد و واحد تحلیلی آن «کامپایل در ثانیه» است. نسبت Compilation به Batch Requests از خود مقدار مهم‌تر است و بالا بودن پایدار آن می‌تواند نشانه Queryهای Ad-hoc یا ناپایداری Plan باشد.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: کامپایل طبیعی است؛ فقط نرخ بالا همراه با فشار CPU یا نسبت نامناسب باید بررسی شود. برای تأیید نتیجه، روند آن را با Batch Requests/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل SQL Compilations/sec با ده مثال عملی و خروجی نمونه

5. شمارنده SQL Re-Compilations/sec

ردیابی بازتولید Plan به علت تغییر Schema، آمار، گزینه RECOMPILE یا عوامل دیگر است. این مورد در گروه «SQL Statistics» قرار می‌گیرد و واحد تحلیلی آن «بازکامپایل در ثانیه» است. مقدار کم و مقطعی طبیعی است اما جهش پایدار همراه با CPU بالا نیازمند یافتن علت Recompile است.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: این شمارنده علت بازکامپایل را نشان نمی‌دهد و برای ریشه‌یابی باید Extended Events نیز به کار رود. برای تأیید نتیجه، روند آن را با SQL Compilations/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل SQL Re-Compilations/sec با ده مثال عملی و خروجی نمونه

6. شمارنده Page Life Expectancy

برآورد مدت ماندگاری صفحه داده در Buffer Pool بدون دسترسی مجدد است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «ثانیه» است. کاهش ناگهانی نسبت به خط مبنای همان سرور معمولاً از فشار حافظه یا اسکن حجیم خبر می‌دهد؛ عدد ۳۰۰ یک قانون همیشگی نیست.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: در سرورهای NUMA باید PLE هر Buffer Node نیز بررسی شود و فقط مقدار کلی مبنا قرار نگیرد. برای تأیید نتیجه، روند آن را با Lazy writes/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Page Life Expectancy با ده مثال عملی و خروجی نمونه

7. شمارنده Buffer Cache Hit Ratio

محاسبه سهم درخواست‌های صفحه که از حافظه پاسخ گرفته‌اند است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «درصد» است. نسبت نزدیک به صد معمولاً مطلوب است اما میانگین بلندمدت می‌تواند افت‌های کوتاه و مهم را پنهان کند.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: مقدار numerator باید بر Buffer cache hit ratio base تقسیم شود؛ خواندن مستقیم cntr_value درصد واقعی نیست. برای تأیید نتیجه، روند آن را با Page reads/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Buffer Cache Hit Ratio با ده مثال عملی و خروجی نمونه

8. شمارنده Lazy Writes/sec

اندازه‌گیری صفحاتی که Lazy Writer برای آزادکردن Buffer به دیسک می‌نویسد است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «صفحه در ثانیه» است. رشد پایدار همراه با افت PLE می‌تواند نشان‌دهنده فشار Buffer Pool باشد.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: نوشتن ناشی از Checkpoint با Lazy Write متفاوت است و باید جداگانه تحلیل شود. برای تأیید نتیجه، روند آن را با Page life expectancy، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Lazy Writes/sec با ده مثال عملی و خروجی نمونه

9. شمارنده Page Reads/sec

محاسبه تعداد خواندن فیزیکی صفحه از ذخیره‌ساز است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «صفحه در ثانیه» است. نرخ بالا همراه با تأخیر PAGEIOLATCH و افت Cache Hit Ratio می‌تواند فشار I/O یا Queryهای پرخواندن را آشکار کند.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: صفحه منطقی با صفحه فیزیکی یکسان نیست؛ این شمارنده فقط خواندن فیزیکی را پوشش می‌دهد. برای تأیید نتیجه، روند آن را با Buffer cache hit ratio، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Page Reads/sec با ده مثال عملی و خروجی نمونه

10. شمارنده Page Writes/sec

نمایش نرخ نوشتن فیزیکی صفحات داده از Buffer Pool است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «صفحه در ثانیه» است. جهش آن هنگام Checkpoint یا بار نوشتنی طبیعی است؛ تداوم همراه با latency بالا باید بررسی شود.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: این شمارنده با Log Flushes/sec یکی نیست و نوشتن Data Page را نشان می‌دهد. برای تأیید نتیجه، روند آن را با Checkpoint pages/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Page Writes/sec با ده مثال عملی و خروجی نمونه

11. شمارنده Checkpoint Pages/sec

اندازه‌گیری صفحاتی که Checkpoint در هر ثانیه به دیسک می‌فرستد است. این مورد در گروه «Buffer Manager» قرار می‌گیرد و واحد تحلیلی آن «صفحه در ثانیه» است. قله‌های دوره‌ای قابل انتظارند اما موج‌های سنگین و latency بالا می‌توانند نیاز به تنظیم Recovery و ذخیره‌ساز را نشان دهند.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: مقدار هدف Recovery Time و رفتار Indirect Checkpoint روی الگو اثر مستقیم دارد. برای تأیید نتیجه، روند آن را با Page writes/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Checkpoint Pages/sec با ده مثال عملی و خروجی نمونه

12. شمارنده Memory Grants Pending

شمارش Queryهایی که برای Sort، Hash یا عملیات حافظه‌بر منتظر Memory Grant هستند است. این مورد در گروه «Memory Manager» قرار می‌گیرد و واحد تحلیلی آن «درخواست منتظر» است. صفر وضعیت مطلوب است؛ مقدار مثبت پایدار با زمان انتظار RESOURCE_SEMAPHORE نشانه مهم فشار Workspace Memory است.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: یک نمونه لحظه‌ای ممکن است جهش کوتاه را ثبت کند؛ پایش پیوسته و Planهای مصرف‌کننده حافظه لازم است. برای تأیید نتیجه، روند آن را با Total Server Memory (KB)، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Memory Grants Pending با ده مثال عملی و خروجی نمونه

13. شمارنده Target Server Memory

نمایش حافظه‌ای که موتور SQL Server با توجه به پیکربندی و فشار سیستم‌عامل تمایل دارد در اختیار داشته باشد است. این مورد در گروه «Memory Manager» قرار می‌گیرد و واحد تحلیلی آن «کیلوبایت» است. مقایسه Target با Total Server Memory سرعت رسیدن Buffer Pool به وضعیت پایدار یا وجود فشار بیرونی را روشن می‌کند.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: نام شمارنده در نسخه‌های رایج پسوند (KB) دارد و باید از تبدیل درست واحد استفاده شود. برای تأیید نتیجه، روند آن را با Total Server Memory (KB)، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Target Server Memory با ده مثال عملی و خروجی نمونه

14. شمارنده Total Server Memory

نمایش مقدار فعلی حافظه Commitشده توسط Memory Manager SQL Server است. این مورد در گروه «Memory Manager» قرار می‌گیرد و واحد تحلیلی آن «کیلوبایت» است. نزدیک‌شدن تدریجی به Target معمولاً طبیعی است؛ افت ناگهانی یا فاصله پایدار با Target نیازمند تحلیل فشار حافظه است.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: این مقدار کل مصرف Working Set پردازش sqlservr نیست و با Private Bytes تفاوت دارد. برای تأیید نتیجه، روند آن را با Target Server Memory (KB)، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Total Server Memory با ده مثال عملی و خروجی نمونه

15. شمارنده User Connections

نمایش تعداد فعلی اتصال‌های کاربری به نمونه SQL Server است. این مورد در گروه «General Statistics» قرار می‌گیرد و واحد تحلیلی آن «اتصال» است. رشد اتصال بدون رشد درخواست می‌تواند از Connection Leak یا تنظیم نامناسب Pooling خبر دهد.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: هر اتصال الزاماً فعال نیست؛ برای تفکیک Session خوابیده و در حال اجرا باید DMVهای Session و Request بررسی شوند. برای تأیید نتیجه، روند آن را با Batch Requests/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل User Connections با ده مثال عملی و خروجی نمونه

16. شمارنده Lock Waits/sec

اندازه‌گیری درخواست‌های قفلی که نتوانسته‌اند بلافاصله Grant شوند است. این مورد در گروه «Locks» قرار می‌گیرد و واحد تحلیلی آن «انتظار در ثانیه» است. افزایش همراه با Lock Wait Time و Blocking Sessionها نشانه رقابت هم‌زمانی است.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: این شمارنده چند Instance مانند Key، Page و _Total دارد؛ جمع‌زدن نادرست می‌تواند دوباره‌شماری ایجاد کند. برای تأیید نتیجه، روند آن را با Number of Deadlocks/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Lock Waits/sec با ده مثال عملی و خروجی نمونه

17. شمارنده Deadlocks/sec

محاسبه نرخ Deadlockهای شناسایی‌شده توسط موتور قفل است. این مورد در گروه «Locks» قرار می‌گیرد و واحد تحلیلی آن «بن‌بست در ثانیه» است. حتی مقدار کم تکرارشونده مهم است و باید نمودار Deadlock برای یافتن ترتیب قفل‌ها استخراج شود.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: نام واقعی شمارنده در DMV معمولاً Number of Deadlocks/sec است و عدد به‌تنهایی Queryهای درگیر را مشخص نمی‌کند. برای تأیید نتیجه، روند آن را با Lock Waits/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Deadlocks/sec با ده مثال عملی و خروجی نمونه

18. شمارنده Log Flushes/sec

اندازه‌گیری دفعات ارسال بلوک‌های Log از حافظه به فایل تراکنش است. این مورد در گروه «Databases» قرار می‌گیرد و واحد تحلیلی آن «Flush در ثانیه» است. نرخ بالا همراه با WRITELOG و Log Flush Wait Time ممکن است تراکنش‌های بسیار کوچک یا ذخیره‌ساز کند را نشان دهد.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: برای تحلیل درست باید Instance پایگاه داده یا _Total را آگاهانه انتخاب کرد و نرخ را با حجم Log Bytes مقایسه نمود. برای تأیید نتیجه، روند آن را با Transactions/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Log Flushes/sec با ده مثال عملی و خروجی نمونه

19. شمارنده Transactions/sec

نمایش نرخ تراکنش‌های آغازشده برای هر پایگاه داده یا کل نمونه است. این مورد در گروه «Databases» قرار می‌گیرد و واحد تحلیلی آن «تراکنش در ثانیه» است. روند آن شاخص مهم Throughput است اما بدون Latency، Rollback و Log Flush تصویر کاملی نمی‌دهد.

در هنگام جمع‌آوری باید این محدودیت را جدی گرفت: مقادیر پایگاه master و _Total معنی متفاوت دارند؛ Instance موردنظر را صریح انتخاب کنید. برای تأیید نتیجه، روند آن را با Log Flushes/sec، زمان پاسخ Queryها و رخدادهای عملیاتی همان بازه مقایسه کنید.

مطالعه آموزش کامل Transactions/sec با ده مثال عملی و خروجی نمونه

جدول مقایسه همه موضوع‌ها

این جدول برای انتخاب سریع مقاله و یادآوری نوع تفسیر طراحی شده است. توضیح کوتاه جای Formula، Baseline و مثال‌های مقاله مستقل را نمی‌گیرد.

مثال‌های ترکیبی و مستقل

مثال 1: Snapshot گروه بارکاری

Batch، Compilation و Recompilation در یک Snapshot خوانده می‌شوند تا حجم کار و هزینه تولید Plan کنار هم قرار گیرد.

SELECT counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:SQL Statistics'
  AND counter_name IN
      (N'Batch Requests/sec', N'SQL Compilations/sec', N'SQL Re-Compilations/sec');

نکته کاربردی: برای نسبت‌ها و نرخ‌ها از Delta دو Snapshot هم‌زمان استفاده کنید.

مثال 2: محاسبه نسبت Compilation به Batch

داده نمونه نرخ‌های از قبل محاسبه‌شده را به درصد تبدیل می‌کند. بالا رفتن پایدار نسبت می‌تواند نشانه استفاده مجدد کم از Plan باشد.

DECLARE @BatchRate decimal(18,2) = 1500,
        @CompileRate decimal(18,2) = 75;
SELECT CAST(100.0 * @CompileRate / NULLIF(@BatchRate, 0) AS decimal(6,2)) AS CompilationPercent;

نکته کاربردی: نسبت را همراه CPU، نوع Query و گزینه optimize for ad hoc workloads بررسی کنید.

مثال 3: Snapshot گروه حافظه

Target، Total و Memory Grants Pending ظرفیت و فشار Workspace را در کنار هم نمایش می‌دهند.

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:Memory Manager'
  AND counter_name IN
      (N'Target Server Memory (KB)', N'Total Server Memory (KB)', N'Memory Grants Pending');

نکته کاربردی: فاصله Total و Target بدون بررسی فشار OS و زمان Uptime نتیجه قطعی نمی‌دهد.

مثال 4: Snapshot گروه Buffer

PLE، Lazy Writes و Page Reads سه زاویه از ماندگاری صفحه، فشار تخلیه و I/O فیزیکی ارائه می‌کنند.

SELECT counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:Buffer Manager'
  AND counter_name IN
      (N'Page life expectancy', N'Lazy writes/sec', N'Page reads/sec');

نکته کاربردی: Rateها را تبدیل کنید و افت PLE را نسبت به خط مبنای سرور بسنجید.

مثال 5: مقایسه خواندن و نوشتن صفحه

اختلاف دو Snapshot نمایشی به نرخ Page Read و Page Write تبدیل می‌شود تا جهت غالب I/O مشخص گردد.

DECLARE @Seconds decimal(10,2) = 10,
        @ReadDelta bigint = 8200,
        @WriteDelta bigint = 2100;
SELECT @ReadDelta / @Seconds AS PageReadsPerSec,
       @WriteDelta / @Seconds AS PageWritesPerSec,
       CAST(1.0 * @ReadDelta / NULLIF(@WriteDelta, 0) AS decimal(10,2)) AS ReadWriteRatio;

نکته کاربردی: نسبت بار را توصیف می‌کند؛ برای تشخیص کندی latency فایل‌ها و Wait Stats نیز ضروری است.

مثال 6: اتصال Memory Grant به Wait Stats

شمارنده انتظار Grant با RESOURCE_SEMAPHORE ترکیب می‌شود تا رخداد لحظه‌ای با زمان انتظار تجمعی اعتبارسنجی شود.

SELECT pc.cntr_value AS PendingGrants,
       ws.waiting_tasks_count, ws.wait_time_ms
FROM sys.dm_os_performance_counters AS pc
LEFT JOIN sys.dm_os_wait_stats AS ws
  ON ws.wait_type = N'RESOURCE_SEMAPHORE'
WHERE pc.object_name LIKE N'%:Memory Manager'
  AND pc.counter_name = N'Memory Grants Pending';

نکته کاربردی: Planهای دارای Sort و Hash بزرگ و تخمین Cardinality را در مرحله بعد بررسی کنید.

طراحی Collector و پایگاه تاریخچه

Collector حرفه‌ای ابتدا یک Snapshot خام از Counterهای منتخب می‌گیرد و SampleTime، ServerName، InstanceName، Replica، sqlserver_start_time، object_name، counter_name، instance_name، cntr_value و cntr_type را ذخیره می‌کند. لایه پردازش سپس بر اساس Metadata نسخه‌دار، Gauge، Rate و Ratio را محاسبه می‌نماید. جداسازی Raw و Derived امکان Audit و بازپردازش را حفظ می‌کند.

فاصله نمونه‌برداری باید با هدف سازگار باشد. بازه یک تا پنج ثانیه برای عیب‌یابی کوتاه و کنترل‌شده مفید است؛ سی تا شصت ثانیه نقطه شروع رایج برای روند عملیاتی است. داده قدیمی را به پنجره‌های پنج‌دقیقه‌ای و ساعتی Rollup کنید و Count، Min، Max، Avg و صدک را نگه دارید. شکاف داده، Restart و Maintenance باید Event مستقل باشند.

سامانه هشدار بهتر است سه لایه داشته باشد: Warning برای انحراف اولیه، Critical برای تداوم یا اثر بالا و Recovery برای بازگشت پایدار. پیام باید Metric، مقدار، Baseline، مدت، دامنه Instance، سیگنال مکمل، Dashboard و Runbook را داشته باشد. هشدار بدون مالک و اقدام بعدی فقط نویز عملیاتی تولید می‌کند.

امنیت Collector نیز مهم است. حساب اختصاصی با کمترین مجوز لازم، Connection رمزگذاری‌شده، Secret خارج از کد و Queryهای پارامتری استفاده کنید. Collector نباید به دلیل خطای یک Counter کل Snapshot را از دست بدهد؛ Availability، ErrorCode و مدت جمع‌آوری را برای پایش خود سامانه مانیتورینگ ثبت نمایید.

خطاهای تحلیلی که باید حذف شوند

• نمایش مستقیم cntr_value برای Counterهای تجمعی با برچسب در ثانیه.
• محاسبه Buffer Cache Hit Ratio بدون Counter پایه یا با دو زمان متفاوت.
• استفاده از عدد ۳۰۰ برای PLE به‌عنوان قانون جهانی و بی‌توجهی به Baseline و NUMA.
• جمع‌زدن ردیف‌های جزئی همراه _Total در Locks یا Databases.
• یکسان‌دانستن Missing، NULL و صفر واقعی در نمودار و SLA.
• محاسبه Delta روی مرز Restart، Failover یا Reset شمارنده.
• تشخیص علت از یک Metric بدون Wait Stats، Query Store یا Plan.
• استفاده از Average بلندمدت بدون نگهداری Max و افت‌های کوتاه.
• کپی آستانه اینترنتی بدون سنجش بار، سخت‌افزار و اهمیت سرویس.
• ساخت Alert بدون تداوم، Evidence، مالک و Runbook قابل اجرا.

سؤالات متداول

آیا sys.dm_os_performance_counters جای PerfMon را می‌گیرد؟

برای Counterهای داخلی SQL Server منبع بسیار مفیدی است، اما Counterهای CPU، Disk، Network و Process سیستم‌عامل همچنان به ابزارهای سیستم‌عامل نیاز دارند. بهترین معماری هر دو منبع را روی Timeline مشترک قرار می‌دهد.

چرا Counter دارای /sec مقدار بزرگی نشان می‌دهد؟

زیرا بسیاری از این ردیف‌ها در DMV مقدار تجمعی از شروع سرویس هستند. دو Snapshot بگیرید، Delta را بر ثانیه واقعی تقسیم کنید و cntr_type را پیش از اعمال Formula کنترل نمایید.

بهترین فاصله نمونه‌برداری چند ثانیه است؟

یک پاسخ ثابت وجود ندارد. عیب‌یابی زنده به نمونه سریع‌تر و روند بلندمدت به بازه بزرگ‌تر نیاز دارد. سی تا شصت ثانیه نقطه شروع رایج است و باید با سرعت تغییر سیگنال و هزینه Retention تنظیم شود.

چگونه Baseline قابل اعتماد بسازیم؟

چند هفته داده سالم را با برچسب ساعت عادی، اوج، Batch، پایان ماه و Maintenance جمع کنید. به‌جای میانگین واحد، توزیع و صدک‌ها را در پنجره‌های هم‌نوع مقایسه نمایید.

آیا آستانه‌های عمومی برای Production کافی هستند؟

خیر. آن‌ها فقط نقطه شروع آزمایش‌اند. سخت‌افزار، نسخه، نوع بار، SLA و الگوی رشد تفاوت دارند؛ Alert نهایی باید از Baseline محلی، تداوم و سیگنال مکمل استفاده کند.

بعد از Restart با تاریخچه چه کنیم؟

sqlserver_start_time را با هر Snapshot نگه دارید. با تغییر آن سری تجمعی جدید آغاز می‌شود؛ نخستین مقدار Baseline است و نباید با آخرین مقدار پیش از Restart Delta شود.

چرا _Total می‌تواند گزارش را خراب کند؟

زیرا _Total معمولاً جمع داخلی Instanceهاست. اگر آن را دوباره با ردیف‌های جزئی جمع کنید، مقدار دو بار شمرده می‌شود. دامنه گزارش را صریح انتخاب و نام Instance را ذخیره کنید.

برای ریشه‌یابی پس از هشدار چه ابزارهایی لازم است؟

Query Store، Wait Stats، Extended Events، Execution Plan، DMVهای Session و Request، آمار فایل و Counterهای OS بسته به حوزه استفاده می‌شوند. Counter محل جست‌وجو را کوچک می‌کند ولی علت قطعی نیست.

پایش حرفه‌ای چه منفعت تجاری دارد؟

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

آیا این Queryها در همه نسخه‌های SQL Server یکسان‌اند؟

اصل DMV پایدار است، اما حضور و نام برخی Counterها، مجوز لازم و رفتار Editionها ممکن است فرق کند. پس از Upgrade یا مهاجرت، مرحله Discovery و آزمون Formulaها را دوباره اجرا کنید.

سؤالات مصاحبه‌ای کلیدی

تفاوت Gauge، Cumulative Rate و Fraction چیست؟

Gauge مقدار لحظه‌ای دارد، Cumulative Rate برای محاسبه نرخ به Delta و زمان نیاز دارد و Fraction از Numerator و Base ساخته می‌شود. cntr_type و مستندات Counter تعیین‌کننده Formula هستند.

چرا Startup Time بخشی از کلید سری زمانی است؟

شمارنده تجمعی پس از Restart از نو آغاز می‌شود. بدون Startup Time افت مقدار ممکن است نرخ منفی یا افت بار تلقی گردد. ترکیب Server، Instance، Counter، CounterInstance و StartTime سری درست را مشخص می‌کند.

چگونه Compilation Pressure را تشخیص می‌دهید؟

نرخ Compilation و Re-Compilation را با Batch Rate، CPU، Plan Cache و نوع Query مقایسه می‌کنم. نسبت پایدار بالا و رشد CPU نشانه قوی‌تری از عدد Compilation تنهاست؛ سپس Query Store و Plan Attributes بررسی می‌شوند.

افت PLE چه زمانی مهم است؟

وقتی نسبت به Baseline همان Node یا سرور غیرعادی، پایدار و همراه Lazy Writes، Page Reads یا فشار حافظه OS باشد. عدد ۳۰۰ به‌تنهایی تشخیص عمومی و مناسبی برای سرورهای امروزی نیست.

برای Deadlock فقط Counter کافی است؟

خیر. Counter فراوانی را نشان می‌دهد ولی Session، Statement و ترتیب منابع را نمی‌دهد. Extended Events و Deadlock XML برای ریشه‌یابی و طراحی ترتیب دسترسی یا ایندکس لازم‌اند.

یک Alert خوب چه اجزایی دارد؟

Metric و واحد، مقدار و Baseline، مدت، دامنه، سیگنال مکمل، شدت، مالک، لینک Dashboard، Runbook و شرایط Recovery. همچنین Maintenance و Missing Data باید از Incident واقعی تفکیک شوند.

چک‌لیست اجرای عملی

• Counterهای موجود روی هر Instance را کشف و نسخه‌برداری کرده‌ایم.
• Metadata شامل cntr_type، واحد، Formula، Base و دامنه Instance است.
• Snapshot خام با SampleTime و sqlserver_start_time ذخیره می‌شود.
• Rateها از Delta و زمان واقعی و Ratioها از جفت هم‌زمان محاسبه می‌شوند.
• Missing، Reset، Maintenance و Failover وضعیت‌های جدا دارند.
• Baseline برای پنجره‌های کسب‌وکار متفاوت ساخته شده است.
• Dashboard هم‌زمان Throughput، Pressure، Wait و Latency را نمایش می‌دهد.
• Alert چندنمونه‌ای، چندسطحی و متصل به Evidence و Runbook است.
• Raw Data با Rollup و Retention کنترل‌شده نگهداری می‌شود.
• Collector با حداقل مجوز و پس از هر Upgrade دوباره آزموده می‌شود.

جمع‌بندی و مسیر مطالعه

پایش صحیح SQL Server با جمع‌کردن عددهای بیشتر به دست نمی‌آید؛ با انتخاب Counter متوازن، Formula درست، زمان مشترک، Baseline و Runbook به دست می‌آید. از حجم کار شروع کنید، فشار حافظه و I/O را بسنجید، رقابت و Log را اضافه کنید و برای هر هشدار یک مسیر ریشه‌یابی با Query Store، Wait Stats یا Extended Events داشته باشید.

برای پیاده‌سازی مرحله‌ای، ابتدا مقاله نمای سیستمی و دسته‌بندی را بخوانید، سپس شاخص‌های مرتبط با درد فعلی سامانه را انتخاب کنید. همه آموزش‌های مستقل در فهرست زیر دوباره آمده‌اند تا مسیر مطالعه و ساخت Dashboard روشن بماند.

• راهنمای تخصصی sys.dm_os_performance_counters
• راهنمای تخصصی Important Counter Categories
• راهنمای تخصصی Batch Requests/sec
• راهنمای تخصصی SQL Compilations/sec
• راهنمای تخصصی SQL Re-Compilations/sec
• راهنمای تخصصی Page Life Expectancy
• راهنمای تخصصی Buffer Cache Hit Ratio
• راهنمای تخصصی Lazy Writes/sec
• راهنمای تخصصی Page Reads/sec
• راهنمای تخصصی Page Writes/sec
• راهنمای تخصصی Checkpoint Pages/sec
• راهنمای تخصصی Memory Grants Pending
• راهنمای تخصصی Target Server Memory
• راهنمای تخصصی Total Server Memory
• راهنمای تخصصی User Connections
• راهنمای تخصصی Lock Waits/sec
• راهنمای تخصصی Deadlocks/sec
• راهنمای تخصصی Log Flushes/sec
• راهنمای تخصصی Transactions/sec

برای آموزش SQL Server، مشاوره طراحی مانیتورینگ، قبول سفارش پروژه‌های برنامه‌نویسی و پایگاه داده در اصفهان می‌توانید با شماره 09131253620 تماس بگیرید.