آموزش جامع شمارنده‌های عملکرد SQL Server با مثال‌های عملی SQL Server

SQL Server Performance Counters یکی از ابزارها یا قابلیت‌های مهم اکوسیستم Microsoft SQL Server برای اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد است. این مقاله از تعریف پایه آغاز می‌کند و سپس به ساخت خط مبنا، خواندن خروجی، کنترل سربار و تبدیل مشاهده فنی به اقدام قابل سنجش می‌رسد. تمام مثال‌ها برای همین موضوع طراحی شده‌اند تا متن با مقاله‌های دیگر مجموعه هم‌پوشانی محتوایی نداشته باشد.

منبع اصلی داده در این موضوع «sys.dm_os_performance_counters و Windows Counters» است و خروجی‌های شاخص آن شامل Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks می‌شود. بنابراین پیش از فعال‌سازی ابزار باید پرسش عملکردی مشخص باشد؛ برای نمونه، آیا تیم در پی محاسبه نرخ Batch است یا می‌خواهد بررسی فشار Buffer Pool را ارزیابی کند؟

مسیر دسترسی سریع مقاله SQL Server Performance Counters: بازگشت به راهنمای ابزارهای گرافیکی و خارجی پایش عملکرد SQL Server.

تعریف و جایگاه SQL Server Performance Counters

جایگاه SQL Server Performance Counters در چرخه عیب‌یابی بین مشاهده، تفسیر و اقدام قرار می‌گیرد. مشاهده خام تنها می‌گوید چه چیزی رخ داده است؛ تفسیر باید آن رخداد را با ظرفیت سرور، الگوی مصرف و تغییرات نرم‌افزار مرتبط کند؛ اقدام نیز باید فرضیه‌ای قابل بازگشت داشته باشد. این ابزار برای اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد مناسب است، اما به‌تنهایی علت ریشه‌ای را تضمین نمی‌کند.

در یک فرایند حرفه‌ای، ابتدا بازه زمانی و معیار موفقیت ثبت می‌شود. سپس داده از sys.dm_os_performance_counters و Windows Counters جمع‌آوری می‌گردد و شاخص‌هایی مانند Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks کنار هم قرار می‌گیرند. اگر نتیجه با تجربه کاربران سازگار نبود، باید از ابزار مکمل در لایه سیستم‌عامل، موتور داده یا برنامه کاربردی کمک گرفت.

تصویر نخست، معماری اختصاصی SQL Server Performance Counters را از منبع داده تا خروجی‌های قابل استفاده برای محاسبه نرخ Batch نشان می‌دهد.

نحو و الگوی خواندن داده در SQL Server Performance Counters

همه قابلیت‌های SQL Server Performance Counters یک دستور واحد ندارند، اما Query زیر نقطه شروع عملی برای مشاهده بخشی از داده مرتبط است. آن را ابتدا در محیط آزمایش اجرا کنید و نام پایگاه، مجوزها و نسخه موتور را با شرایط واقعی تطبیق دهید.

-- نمونه 1 اختصاصی SQL Server Performance Counters
        SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters WHERE counter_name=N'Batch Requests/sec';

ده مثال عملی و غیرتکراری برای SQL Server Performance Counters

مثال 1: ساخت خط مبنای اولیه با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 1 روی موضوع «محاسبه نرخ Batch» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 1 اختصاصی SQL Server Performance Counters
        SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters WHERE counter_name=N'Batch Requests/sec';

نکته فنی نمونه 1 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مقایسه Compilation با Batch تصمیم بگیرید.

مثال 2: تحلیل داده نمونه در محیط آزمایش با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 2 روی موضوع «بررسی فشار Buffer Pool» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 2 اختصاصی SQL Server Performance Counters
        SELECT counter_name,cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Buffer Manager%' AND counter_name=N'Page life expectancy';

نکته فنی نمونه 2 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره پایش Lock Wait تصمیم بگیرید.

مثال 3: استفاده در گزارش روزانه با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 3 روی موضوع «مقایسه Compilation با Batch» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 3 اختصاصی SQL Server Performance Counters
        SELECT counter_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters WHERE counter_name IN (N'SQL Compilations/sec',N'SQL Re-Compilations/sec');

نکته فنی نمونه 3 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره اندازه‌گیری Log Flush تصمیم بگیرید.

مثال 4: اعمال فیلتر هدفمند با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 4 روی موضوع «پایش Lock Wait» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 4 اختصاصی SQL Server Performance Counters
        SELECT counter_name,instance_name,cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Locks%' AND counter_name IN (N'Lock Waits/sec',N'Average Wait Time (ms)');

نکته فنی نمونه 4 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره محاسبه نرخ Batch تصمیم بگیرید.

مثال 5: ترکیب با نمای سیستمی مکمل با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 5 روی موضوع «اندازه‌گیری Log Flush» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 5 اختصاصی SQL Server Performance Counters
        SELECT counter_name,instance_name,cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Databases%' AND counter_name IN (N'Log Flushes/sec',N'Log Flush Wait Time');

نکته فنی نمونه 5 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بررسی فشار Buffer Pool تصمیم بگیرید.

تصویر دوم، جریان اجرای SQL Server Performance Counters را از دریافت ورودی تا تولید مشاهده قابل مقایسه برای مقایسه Compilation با Batch ترسیم می‌کند.

مثال 6: بررسی نبود داده یا مقدار NULL با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 6 روی موضوع «محاسبه نرخ Batch» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 6 اختصاصی SQL Server Performance Counters
        SELECT counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters WHERE cntr_type IN (272696576,272696320) ORDER BY object_name,counter_name;

نکته فنی نمونه 6 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره مقایسه Compilation با Batch تصمیم بگیرید.

مثال 7: آزمون وضعیت مرزی و بار غیرمعمول با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 7 روی موضوع «بررسی فشار Buffer Pool» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 7 اختصاصی SQL Server Performance Counters
        DECLARE @a bigint=(SELECT TOP (1) cntr_value FROM sys.dm_os_performance_counters WHERE counter_name=N'Batch Requests/sec'); WAITFOR DELAY '00:00:01'; SELECT @a AS first_sample,(SELECT TOP (1) cntr_value FROM sys.dm_os_performance_counters WHERE counter_name=N'Batch Requests/sec') AS second_sample;

نکته فنی نمونه 7 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره پایش Lock Wait تصمیم بگیرید.

مثال 8: سناریوی عملیاتی سازمانی با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 8 روی موضوع «مقایسه Compilation با Batch» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 8 اختصاصی SQL Server Performance Counters
        SELECT object_name,COUNT(*) AS counter_count FROM sys.dm_os_performance_counters GROUP BY object_name ORDER BY counter_count DESC;

نکته فنی نمونه 8 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره اندازه‌گیری Log Flush تصمیم بگیرید.

مثال 9: نمایش روش اشتباه و نسخه اصلاح‌شده با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 9 روی موضوع «پایش Lock Wait» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود. روش اشتباه، اجرای پایش بدون فیلتر و بدون تعیین مدت است؛ نسخه اصلاح‌شده Query را محدود می‌کند تا داده مرتبط با SQL Server Performance Counters استخراج شود.

-- نمونه 9 اختصاصی SQL Server Performance Counters
        SELECT counter_name,instance_name,cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Memory Manager%' ORDER BY counter_name;

نکته فنی نمونه 9 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره محاسبه نرخ Batch تصمیم بگیرید.

مثال 10: کنترل کارایی و هزینه پایش با SQL Server Performance Counters

در این سناریوی اختصاصی، هدف آن است که اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 10 روی موضوع «اندازه‌گیری Log Flush» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 10 اختصاصی SQL Server Performance Counters
        SELECT counter_name,instance_name,cntr_value FROM sys.dm_os_performance_counters WHERE counter_name LIKE N'%Connection%' ORDER BY object_name,counter_name;

نکته فنی نمونه 10 در SQL Server Performance Counters آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره بررسی فشار Buffer Pool تصمیم بگیرید. در محیط پرترافیک، ابتدا هزینه خود ابزار را بسنجید؛ زیرا بعضی Counterها تجمعی هستند و باید اختلاف دو نمونه در زمان محاسبه شود.

خطاهای رایج در کار با SQL Server Performance Counters

• در SQL Server Performance Counters، جمع‌آوری داده بدون سؤال مشخص خروجی حجیمی می‌سازد که هیچ تصمیمی را پشتیبانی نمی‌کند.
• نادیده گرفتن هشدار اصلی این ابزار: بعضی Counterها تجمعی هستند و باید اختلاف دو نمونه در زمان محاسبه شود.
• در تحلیل SQL Server Performance Counters، مقایسه دو بازه با حجم Workload متفاوت نتیجه بهبود یا افت را مخدوش می‌کند.
• پس از مشاهده SQL Server Performance Counters، اعمال تغییر مستقیم در تولید بدون Baseline و معیار Rollback خطرناک است.
• تفسیر SQL Server Performance Counters با تمرکز روی یک عدد و حذف زمینه‌هایی مانند Wait، I/O، Lock و رفتار برنامه ناقص می‌ماند.

ملاحظات کارایی و بهترین روش‌ها در SQL Server Performance Counters

هزینه پایش باید بخشی از طراحی باشد. برای SQL Server Performance Counters دامنه داده را به رویداد، Database، Session یا بازه لازم محدود کنید. نرخ تولید داده را در پنج دقیقه نخست اندازه بگیرید؛ اگر رشد خروجی از انتظار بیشتر بود، فیلتر یا فاصله نمونه‌برداری را اصلاح کنید. نتیجه معتبر زمانی ایجاد می‌شود که خود ابزار باعث تغییر محسوس در رفتار Workload نشود.

بهترین روش، ایجاد دفترچه تصمیم است: پرسش اولیه، زمان جمع‌آوری، نسخه SQL Server، تغییرات اخیر، معیارهای Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks، تفسیر، اقدام و نتیجه پس از اقدام ثبت شوند. برای سناریوی پایش Lock Wait بهتر است داده قبل و بعد از تغییر با یک بار کاری مشابه مقایسه شود.

تصویر سوم، تصمیم فنی میان Baseline، مشاهده، هشدار Performance و Best Practice را برای SQL Server Performance Counters و سناریوی اندازه‌گیری Log Flush خلاصه می‌کند.

سؤالات متداول اختصاصی SQL Server Performance Counters

پرسش 1: SQL Server Performance Counters دقیقاً چه مسئله‌ای را حل می‌کند؟

در زمینه SQL Server Performance Counters، این ابزار برای اندازه‌گیری شاخص‌های داخلی موتور با Counterهای استاندارد طراحی شده است و داده را از sys.dm_os_performance_counters و Windows Counters می‌گیرد. ارزش اصلی آن زمانی آشکار می‌شود که نتیجه با خط مبنا و هدف کسب‌وکار مقایسه شود.

پرسش 2: برای شروع کار با SQL Server Performance Counters چه پیش‌نیازی لازم است؟

در زمینه SQL Server Performance Counters، دسترسی مشاهده DMVها یا مجوز مناسب ابزار، تعیین بازه پایش و شناخت بار عادی سامانه لازم است. پیش از اجرا نیز باید مشخص شود کدام‌یک از خروجی‌های «Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks» واقعاً اهمیت دارد.

پرسش 3: آیا SQL Server Performance Counters برای پروژه تجاری کوچک هم ارزش دارد؟

در زمینه SQL Server Performance Counters، بله، اما دامنه جمع‌آوری باید متناسب با اندازه سامانه باشد. در پروژه کوچک می‌توان از یک سناریوی محدود مانند محاسبه نرخ Batch شروع کرد و تنها در صورت نیاز جزئیات بیشتری ثبت کرد.

پرسش 4: چگونه خروجی SQL Server Performance Counters به کاهش هزینه عملیاتی کمک می‌کند؟

در زمینه SQL Server Performance Counters، با شناسایی دقیق منبع مصرف یا رگرسیون، تیم از تغییرات حدسی دور می‌شود. این موضوع زمان عیب‌یابی، ریسک توقف سرویس و هزینه تهیه سخت‌افزار بدون ضرورت را کاهش می‌دهد.

پرسش 5: SQL Server Performance Counters با ابزارهای نزدیک چه تفاوتی دارد؟

در زمینه SQL Server Performance Counters، تمایز اصلی در منبع داده و عمق تحلیل است؛ این موضوع بر sys.dm_os_performance_counters و Windows Counters تکیه دارد، در حالی که ابزار دیگر ممکن است فقط نمای لحظه‌ای یا گزارش سطح سیستم‌عامل ارائه کند.

پرسش 6: برای پیاده‌سازی حرفه‌ای SQL Server Performance Counters چه خدماتی مفید است؟

در زمینه SQL Server Performance Counters، طراحی Baseline، انتخاب فیلتر، ساخت گزارش، تحلیل خروجی و مستندسازی اقدام اصلاحی از خدمات رایج مشاوره SQL Server هستند. این مراحل باید با نیاز واقعی پروژه هماهنگ شوند.

پرسش 7: رایج‌ترین خطا هنگام استفاده از SQL Server Performance Counters چیست؟

در زمینه SQL Server Performance Counters، رایج‌ترین خطا تفسیر یک نمونه منفرد بدون زمینه است. همچنین باید به این هشدار توجه شود که بعضی Counterها تجمعی هستند و باید اختلاف دو نمونه در زمان محاسبه شود.

پرسش 8: اثر Performance خود SQL Server Performance Counters چگونه کنترل می‌شود؟

در زمینه SQL Server Performance Counters، رویدادها یا Counterها را محدود، مدت جمع‌آوری را مشخص و حجم خروجی را اندازه‌گیری کنید. سپس مصرف CPU، I/O و فضای ذخیره‌سازی ابزار را جدا از Workload اصلی ثبت کنید.

پرسش 9: بهترین روش عملی برای SQL Server Performance Counters چیست؟

در زمینه SQL Server Performance Counters، با یک پرسش مشخص مانند «بررسی فشار Buffer Pool» شروع کنید، داده حداقلی لازم را جمع‌آوری کنید، نتیجه را با Baseline بسنجید و اقدام اصلاحی را همراه با معیار بازگشت ثبت کنید.

پرسش 10: سازگاری نسخه‌ای SQL Server Performance Counters را چگونه بررسی کنیم؟

در زمینه SQL Server Performance Counters، قابلیت‌ها و نام DMVها میان نسخه‌های SQL Server، Azure SQL و SSMS می‌توانند تفاوت داشته باشند. قبل از اجرای اسکریپت در تولید، آن را روی همان Edition و Compatibility Level آزمایش کنید.

سؤالات مصاحبه درباره SQL Server Performance Counters

1. توضیح دهید چرا SQL Server Performance Counters برای محاسبه نرخ Batch مناسب است.
2. چگونه سربار SQL Server Performance Counters را در یک سرور پرترافیک اندازه‌گیری می‌کنید؟
3. بین خروجی‌های Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks کدام معیار را برای تشخیص اولیه انتخاب می‌کنید و چرا؟
4. اگر داده SQL Server Performance Counters با تجربه کاربر ناسازگار باشد، چه منابع مکملی را بررسی می‌کنید؟
5. یک برنامه بازگشت برای تغییری که بر اساس SQL Server Performance Counters پیشنهاد شده طراحی کنید.

چک‌لیست نهایی SQL Server Performance Counters

• در SQL Server Performance Counters پرسش پایش برای محاسبه نرخ Batch نوشته شده است.
• برای SQL Server Performance Counters مجوز دسترسی به sys.dm_os_performance_counters و Windows Counters بررسی شده است.
• بازه زمانی و حجم خروجی SQL Server Performance Counters محدود شده است.
• برای SQL Server Performance Counters حداقل دو معیار از Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks ثبت شده است.
• تأثیر SQL Server Performance Counters بر CPU، I/O و فضای دیسک سنجیده شده است.
• اقدام اصلاحی مبتنی بر SQL Server Performance Counters و برنامه بازگشت مستند شده است.

جمع‌بندی آموزش SQL Server Performance Counters

SQL Server Performance Counters زمانی بیشترین ارزش را دارد که برای یک مسئله محدود مانند محاسبه نرخ Batch به کار رود و خروجی آن با Baseline سنجیده شود. داده‌های Batch Requests/sec، Page Life Expectancy، Buffer Cache Hit Ratio، Compilations و Locks باید در کنار زمینه Workload تفسیر شوند. با رعایت فیلتر، زمان‌بندی و کنترل سربار می‌توان از این قابلیت برای تصمیم‌های قابل دفاع در SQL Server استفاده کرد.

برای مقایسه SQL Server Performance Counters با تمام گزینه‌های مجموعه، راهنمای جامع ابزارهای گرافیکی و خارجی عملکرد SQL Server را مطالعه کنید.