راهنمای جامع ابزارهای گرافیکی و خارجی پایش عملکرد SQL Server
پایش عملکرد SQL Server تنها به نوشتن یک Query محدود نمیشود. مدیر پایگاه داده باید بتواند نمای لحظهای، تاریخچه اجرای Query، پلن، رویداد، Counter سیستمعامل، بسته تشخیصی و سرویسهای ابری را کنار هم قرار دهد. این راهنما ۲۲ ابزار و قابلیت را در یک نقشه تصمیم یکپارچه معرفی میکند و برای هر مورد مسیر آموزش مستقل ارائه میدهد.
هدف مقاله مادر، انتخاب ابزار مناسب برای پرسش مناسب است. برای تشخیص کندی لحظهای میتوان از Activity Monitor یا DMVهای زنده آغاز کرد؛ برای رگرسیون تاریخی Query Store مناسبتر است؛ برای رخدادهای دقیق Extended Events کاربرد دارد؛ برای همبستگی منابع سیستمعامل Windows Performance Monitor مفید است؛ و برای سناریوهای عمیق پشتیبانی، SQLDiag، PSSDiag و SQL Nexus ارزش بیشتری دارند.
فهرست دسترسی سریع به آموزشهای تخصصی
این نقشه مفهومی، چهار لایه اصلی پایش را شامل موتور SQL Server، رابط SSMS، سیستمعامل Windows و سرویس Azure به یک فرایند تصمیم متصل میکند.
چگونه ابزار مناسب را انتخاب کنیم؟
انتخاب ابزار از نوع سؤال شروع میشود. سؤال «الان چه Sessionی مسدود شده؟» به داده زنده نیاز دارد، در حالی که سؤال «بعد از انتشار نسخه جدید چرا Query کند شد؟» به تاریخچه و پلنهای قبلی وابسته است. سؤال «آیا گلوگاه در دیسک است یا در Query؟» نیز بدون ترکیب Counter سیستمعامل و شاخصهای موتور پاسخ قابل اتکا ندارد.
دومین معیار، مدت رخداد است. مشکل پایدار را میتوان با نمونهبرداری دستی دید، اما Spike چندثانیهای نیازمند Session از پیش آماده یا جمعآوری پیوسته است. سومین معیار، سطح دسترسی و هزینه پایش است؛ ابزار انتخابی باید با مجوز، فضای ذخیرهسازی، حساسیت داده و پنجره عملیاتی سازگار باشد.
شش سناریوی ترکیبی برای عیبیابی عملکرد
سناریوی ۱: تشخیص Queryهای CPUبر
ابتدا Queryهای پرمصرف از Cache استخراج میشوند و سپس با Query Store مقایسه میگردند.
SELECT TOP (10) qs.total_worker_time,qs.execution_count,st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_worker_time DESC;
| خروجی نمونه | کاربرد |
|---|
| فهرست ده Query با بیشترین CPU تجمعی | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 1 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
سناریوی ۲: شناسایی زنجیره Blocking
درخواستهای مسدودشده و Session مسدودکننده بهصورت همزمان دیده میشوند.
SELECT session_id,blocking_session_id,wait_type,wait_time,wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id<>0;
| خروجی نمونه | کاربرد |
|---|
| Sessionهای دارای Blocking Session | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 2 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
سناریوی ۳: ارزیابی I/O فایلها
آمار فایل برای تفکیک کندی Storage از Query استفاده میشود.
SELECT DB_NAME(v.database_id) AS database_name,m.physical_name,v.num_of_reads,v.io_stall_read_ms/NULLIF(v.num_of_reads,0) AS avg_read_ms FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v JOIN sys.master_files AS m ON m.database_id=v.database_id AND m.file_id=v.file_id ORDER BY avg_read_ms DESC;
| خروجی نمونه | کاربرد |
|---|
| میانگین زمان خواندن هر فایل | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 3 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
سناریوی ۴: مرور وضعیت Query Store
پیش از اتکا به گزارش تاریخی، حالت و ظرفیت Query Store کنترل میشود.
SELECT actual_state_desc,desired_state_desc,current_storage_size_mb,max_storage_size_mb,readonly_reason FROM sys.database_query_store_options;
| خروجی نمونه | کاربرد |
|---|
| حالت عملیاتی و اندازه Query Store | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 4 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
سناریوی ۵: نمونهبرداری Counterهای داخلی
Counterهای تجمعی و لحظهای برای ساخت Baseline خوانده میشوند.
SELECT object_name,counter_name,instance_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters WHERE counter_name IN (N'Batch Requests/sec',N'Page life expectancy',N'SQL Compilations/sec');
| خروجی نمونه | کاربرد |
|---|
| مقادیر Counter همراه با نوع Counter | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 5 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
سناریوی ۶: کنترل توصیههای Automatic Tuning
در Azure SQL وضعیت توصیهها بدون اعمال کورکورانه بررسی میشود.
SELECT name,desired_state_desc,actual_state_desc,reason_desc FROM sys.database_automatic_tuning_options ORDER BY name;
| خروجی نمونه | کاربرد |
|---|
| وضعیت گزینههای تنظیم خودکار | مقایسه با Baseline و انتخاب ابزار بعدی |
این سناریو مرحله 6 از فرایند تصمیم است و باید با زمان رخداد و تغییرات اخیر سامانه ثبت شود.
جریان دوم نشان میدهد که داده زنده، تاریخچه Query، پلن، رخداد و Counter چگونه به یک Timeline مشترک تبدیل میشوند.
دستهبندی ابزارها بر اساس نوع داده
- تاریخچه و رگرسیون: Query Store Reports و Automatic Tuning برای مقایسه رفتار در طول زمان.
- نمای زنده: Activity Monitor و Live Query Statistics برای رخداد جاری و اپراتور در حال اجرا.
- پلن اجرا: Actual و Estimated Execution Plan برای تحلیل روش دسترسی و برآورد ردیف.
- رویداد و Trace: Extended Events و XEvent Profiler؛ Profiler و SQL Trace فقط برای Legacy.
- سیستمعامل و Counter: Performance Monitor و SQL Server Performance Counters برای CPU، حافظه و I/O.
- جمعآوری و تحلیل عمیق: SQLDiag، PSSDiag، SQL Nexus، Data Collector و MDW.
- ابر و ناوگان: Azure Monitor و SQL Insights برای Metric، Log، Alert و دید متمرکز.
خطاهای رایج در طراحی پایش
- فعالکردن همه رویدادها و Counterها بدون پرسش فنی مشخص.
- مقایسه بازههای زمانی با حجم تراکنش متفاوت.
- نادیده گرفتن ساعت سیستم، UTC و اختلاف زمانی منابع Azure و سرور محلی.
- اعمال پیشنهاد ایندکس بدون بررسی هزینه Write و فضای ذخیرهسازی.
- اشتراکگذاری بستههای تشخیصی بدون حذف داده حساس.
- استفاده از Profiler یا SQL Trace برای طراحی جدید به جای Extended Events.
نمودار سوم، انتخاب میان مشاهده سریع، جمعآوری عمیق و پایش بلندمدت را بر اساس خطر سربار و ارزش تصمیم نمایش میدهد.
سؤالات متداول راهنمای ابزارهای Performance
پرسش 1: از کدام ابزار برای شروع کندی ناگهانی استفاده کنیم؟
برای نمای اولیه، Sessionهای فعال، Wait و Blocking را با Activity Monitor یا DMVها ببینید؛ سپس بر اساس علامت غالب به Query Store، پلن یا Counter بروید.
پرسش 2: آیا Query Store جای Extended Events را میگیرد؟
خیر. Query Store تاریخچه Query و Plan را نگه میدارد، اما Extended Events برای رخدادهای دقیق، خطا، Deadlock و رویدادهای سفارشی مناسبتر است.
پرسش 3: فرق پلن تخمینی و واقعی چیست؟
پلن تخمینی بدون اجرای Query ساخته میشود؛ پلن واقعی پس از اجرا آمار Runtime مانند Actual Rows و هشدارهای عملیاتی را اضافه میکند.
پرسش 4: چرا Counter سیستمعامل کنار DMV لازم است؟
DMV رفتار موتور را نشان میدهد، اما کمبود CPU، Latency دیسک یا فشار حافظه سیستمعامل فقط با Counterهای بیرونی کامل میشود.
پرسش 5: آیا DTA هر پیشنهاد ایندکس را باید اجرا کند؟
خیر. پیشنهاد باید با حجم Write، فضای دیسک، همپوشانی ایندکسها و برنامه نگهداری بررسی شود.
پرسش 6: برای پروژه تجاری چه بسته پایشی پیشنهاد میشود؟
ترکیبی از Query Store، Extended Events محدود، Baseline Counterها و Alertهای هدفمند معمولاً نقطه شروع خوبی است و باید با SLA پروژه تنظیم شود.
پرسش 7: بزرگترین خطر جمعآوری تشخیصی چیست؟
حجم زیاد، سربار و افشای داده حساس سه خطر اصلیاند؛ فیلتر، Retention و بازبینی امنیتی ضروری است.
پرسش 8: چگونه Performance خود ابزار پایش سنجیده میشود؟
مصرف CPU، I/O، حافظه، نرخ رشد فایل و اثر بر Duration Query قبل و بعد از فعالسازی مقایسه میشود.
پرسش 9: بهترین روش نگهداری Baseline چیست؟
Baseline را برای ساعات عادی، اوج و عملیات دورهای جدا ذخیره کنید و نسخه، پیکربندی و حجم Workload را کنار آن ثبت کنید.
پرسش 10: ابزارهای Azure با SQL Server محلی سازگارند؟
بعضی قابلیتها مختص Azure SQL هستند و بعضی از طریق Agent یا Azure Arc به منابع دیگر متصل میشوند؛ Edition و معماری باید پیش از طراحی کنترل شود.
سؤالات مصاحبه برای متخصص پایش SQL Server
- برای یک CPU Spike پنجدقیقهای چه ابزارهایی را به چه ترتیبی انتخاب میکنید؟
- چگونه رگرسیون Plan را با Query Store اثبات و بدون ریسک اصلاح میکنید؟
- چه زمانی PSSDiag و SQL Nexus نسبت به Extended Events برتری دارند؟
- چگونه Counter تجمعی را به نرخ قابل مقایسه تبدیل میکنید؟
- برای مهاجرت از Profiler به Extended Events چه نگاشتی میسازید؟
جمعبندی و مسیر مطالعه
یک سامانه پایش حرفهای از ابزار زیاد ساخته نمیشود؛ از پرسش دقیق، Baseline معتبر، جمعآوری محدود و تصمیم قابل بازگشت ساخته میشود. برای رخداد جاری از نمای زنده، برای تاریخچه از Query Store، برای رویداد دقیق از Extended Events، برای منابع میزبان از Performance Monitor و برای پروندههای پیچیده از بستههای تشخیصی استفاده کنید.
مسیرهای تخصصی این مجموعه: Query Store Reports، Activity Monitor، Live Query Statistics، Actual Execution Plan، Estimated Execution Plan، Extended Events، XEvent Profiler، Database Engine Tuning Advisor، SQL Server Management Studio Reports، Windows Performance Monitor، SQL Server Performance Counters، SQL Server Profiler (Deprecated)، SQL Trace (Deprecated)، Distributed Replay، SQLDiag، SQL Nexus، PSSDiag، Data Collector، Management Data Warehouse، Azure SQL Database Automatic Tuning، Azure Monitor، SQL Insights.
خدمات برنامهنویسی و پایگاه داده
برنامهنویسی در اصفهان؛ قبول سفارشهای برنامهنویسی و پایگاه داده با شماره 09131253620.
انجام پروژه، آموزش برنامهنویسی و آموزش SQL Server توسط مجموعهای معتبر با سابقه فعالیت حرفهای از سال ۱۳۷۵ شمسی تاکنون.
برای سفارش پروژههای برنامهنویسی، پایگاه داده، سیستمهای تحت وب، وبسایت و راهکارهای نرمافزاری با 09131253620 تماس بگیرید.
ایتا، واتساپ و تماس مستقیم: +989131253620؛ تماس با ما.