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

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

منبع اصلی داده در این موضوع «Collection Setها، SQL Agent و Upload به MDW» است و خروجی‌های شاخص آن شامل Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها می‌شود. بنابراین پیش از فعال‌سازی ابزار باید پرسش عملکردی مشخص باشد؛ برای نمونه، آیا تیم در پی ثبت Server Activity روزانه است یا می‌خواهد گردآوری Query Statistics را ارزیابی کند؟

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

تعریف و جایگاه Data Collector

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

در یک فرایند حرفه‌ای، ابتدا بازه زمانی و معیار موفقیت ثبت می‌شود. سپس داده از Collection Setها، SQL Agent و Upload به MDW جمع‌آوری می‌گردد و شاخص‌هایی مانند Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها کنار هم قرار می‌گیرند. اگر نتیجه با تجربه کاربران سازگار نبود، باید از ابزار مکمل در لایه سیستم‌عامل، موتور داده یا برنامه کاربردی کمک گرفت.

تصویر نخست، معماری اختصاصی Data Collector را از منبع داده تا خروجی‌های قابل استفاده برای ثبت Server Activity روزانه نشان می‌دهد.

نحو و الگوی خواندن داده در Data Collector

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

-- نمونه 1 اختصاصی Data Collector
        SELECT collection_set_id,name,is_running,collection_mode,days_until_expiration FROM msdb.dbo.syscollector_collection_sets ORDER BY collection_set_id;

ده مثال عملی و غیرتکراری برای Data Collector

مثال 1: ساخت خط مبنای اولیه با Data Collector

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

-- نمونه 1 اختصاصی Data Collector
        SELECT collection_set_id,name,is_running,collection_mode,days_until_expiration FROM msdb.dbo.syscollector_collection_sets ORDER BY collection_set_id;

نکته فنی نمونه 1 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره پایش مصرف دیسک تصمیم بگیرید.

مثال 2: تحلیل داده نمونه در محیط آزمایش با Data Collector

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

-- نمونه 2 اختصاصی Data Collector
        SELECT collector_type_uid,name,parameter_schema FROM msdb.dbo.syscollector_collector_types ORDER BY name;

نکته فنی نمونه 2 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره ساخت Collection Set سفارشی تصمیم بگیرید.

مثال 3: استفاده در گزارش روزانه با Data Collector

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

-- نمونه 3 اختصاصی Data Collector
        SELECT collection_item_id,collection_set_id,name,frequency,parameters FROM msdb.dbo.syscollector_collection_items ORDER BY collection_set_id,collection_item_id;

نکته فنی نمونه 3 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره تنظیم Upload Cache تصمیم بگیرید.

مثال 4: اعمال فیلتر هدفمند با Data Collector

در این سناریوی اختصاصی، هدف آن است که زمان‌بندی جمع‌آوری مجموعه‌های داده مدیریتی در SQL Server به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 4 روی موضوع «ساخت Collection Set سفارشی» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 4 اختصاصی Data Collector
        SELECT schedule_id,name,enabled,freq_type,freq_interval FROM msdb.dbo.sysschedules WHERE name LIKE N'%collection%';

نکته فنی نمونه 4 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره ثبت Server Activity روزانه تصمیم بگیرید.

مثال 5: ترکیب با نمای سیستمی مکمل با Data Collector

در این سناریوی اختصاصی، هدف آن است که زمان‌بندی جمع‌آوری مجموعه‌های داده مدیریتی در SQL Server به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 5 روی موضوع «تنظیم Upload Cache» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 5 اختصاصی Data Collector
        SELECT TOP (20) log_id,collection_set_id,start_time,finish_time,status FROM msdb.dbo.syscollector_execution_log ORDER BY log_id DESC;

نکته فنی نمونه 5 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره گردآوری Query Statistics تصمیم بگیرید.

تصویر دوم، جریان اجرای Data Collector را از دریافت ورودی تا تولید مشاهده قابل مقایسه برای پایش مصرف دیسک ترسیم می‌کند.

مثال 6: بررسی نبود داده یا مقدار NULL با Data Collector

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

-- نمونه 6 اختصاصی Data Collector
        SELECT TOP (20) collection_set_id,package_execution_id,failure_message FROM msdb.dbo.syscollector_execution_log WHERE status<>1 ORDER BY log_id DESC;

نکته فنی نمونه 6 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره پایش مصرف دیسک تصمیم بگیرید.

مثال 7: آزمون وضعیت مرزی و بار غیرمعمول با Data Collector

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

-- نمونه 7 اختصاصی Data Collector
        SELECT name,is_running,days_until_expiration FROM msdb.dbo.syscollector_collection_sets WHERE is_system=1;

نکته فنی نمونه 7 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره ساخت Collection Set سفارشی تصمیم بگیرید.

مثال 8: سناریوی عملیاتی سازمانی با Data Collector

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

-- نمونه 8 اختصاصی Data Collector
        SELECT collection_set_id,COUNT(*) AS item_count FROM msdb.dbo.syscollector_collection_items GROUP BY collection_set_id;

نکته فنی نمونه 8 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره تنظیم Upload Cache تصمیم بگیرید.

مثال 9: نمایش روش اشتباه و نسخه اصلاح‌شده با Data Collector

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

-- نمونه 9 اختصاصی Data Collector
        SELECT name,collection_mode,days_until_expiration FROM msdb.dbo.syscollector_collection_sets WHERE days_until_expiration>0 ORDER BY days_until_expiration;

نکته فنی نمونه 9 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره ثبت Server Activity روزانه تصمیم بگیرید.

مثال 10: کنترل کارایی و هزینه پایش با Data Collector

در این سناریوی اختصاصی، هدف آن است که زمان‌بندی جمع‌آوری مجموعه‌های داده مدیریتی در SQL Server به شکلی قابل اندازه‌گیری بررسی شود. نمونه شماره 10 روی موضوع «تنظیم Upload Cache» تمرکز دارد و خروجی آن برای تصمیم‌گیری عملیاتی استفاده می‌شود.

-- نمونه 10 اختصاصی Data Collector
        SELECT GETDATE() AS checked_at,COUNT(*) AS configured_sets FROM msdb.dbo.syscollector_collection_sets;

نکته فنی نمونه 10 در Data Collector آن است که نتیجه نباید جدا از بازه زمانی، بار جاری و پیکربندی سرور تفسیر شود. برای جلوگیری از نتیجه‌گیری شتاب‌زده، مقدار به‌دست‌آمده را با یک نمونه پیشین مقایسه کنید و سپس درباره گردآوری Query Statistics تصمیم بگیرید. در محیط پرترافیک، ابتدا هزینه خود ابزار را بسنجید؛ زیرا عدم تنظیم Retention و Schedule مناسب باعث رشد پایگاه مخزن می‌شود.

خطاهای رایج در کار با Data Collector

• در Data Collector، جمع‌آوری داده بدون سؤال مشخص خروجی حجیمی می‌سازد که هیچ تصمیمی را پشتیبانی نمی‌کند.
• نادیده گرفتن هشدار اصلی این ابزار: عدم تنظیم Retention و Schedule مناسب باعث رشد پایگاه مخزن می‌شود.
• در تحلیل Data Collector، مقایسه دو بازه با حجم Workload متفاوت نتیجه بهبود یا افت را مخدوش می‌کند.
• پس از مشاهده Data Collector، اعمال تغییر مستقیم در تولید بدون Baseline و معیار Rollback خطرناک است.
• تفسیر Data Collector با تمرکز روی یک عدد و حذف زمینه‌هایی مانند Wait، I/O، Lock و رفتار برنامه ناقص می‌ماند.

ملاحظات کارایی و بهترین روش‌ها در Data Collector

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

بهترین روش، ایجاد دفترچه تصمیم است: پرسش اولیه، زمان جمع‌آوری، نسخه SQL Server، تغییرات اخیر، معیارهای Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها، تفسیر، اقدام و نتیجه پس از اقدام ثبت شوند. برای سناریوی ساخت Collection Set سفارشی بهتر است داده قبل و بعد از تغییر با یک بار کاری مشابه مقایسه شود.

تصویر سوم، تصمیم فنی میان Baseline، مشاهده، هشدار Performance و Best Practice را برای Data Collector و سناریوی تنظیم Upload Cache خلاصه می‌کند.

سؤالات متداول اختصاصی Data Collector

پرسش 1: Data Collector دقیقاً چه مسئله‌ای را حل می‌کند؟

در زمینه Data Collector، این ابزار برای زمان‌بندی جمع‌آوری مجموعه‌های داده مدیریتی در SQL Server طراحی شده است و داده را از Collection Setها، SQL Agent و Upload به MDW می‌گیرد. ارزش اصلی آن زمانی آشکار می‌شود که نتیجه با خط مبنا و هدف کسب‌وکار مقایسه شود.

پرسش 2: برای شروع کار با Data Collector چه پیش‌نیازی لازم است؟

در زمینه Data Collector، دسترسی مشاهده DMVها یا مجوز مناسب ابزار، تعیین بازه پایش و شناخت بار عادی سامانه لازم است. پیش از اجرا نیز باید مشخص شود کدام‌یک از خروجی‌های «Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها» واقعاً اهمیت دارد.

پرسش 3: آیا Data Collector برای پروژه تجاری کوچک هم ارزش دارد؟

در زمینه Data Collector، بله، اما دامنه جمع‌آوری باید متناسب با اندازه سامانه باشد. در پروژه کوچک می‌توان از یک سناریوی محدود مانند ثبت Server Activity روزانه شروع کرد و تنها در صورت نیاز جزئیات بیشتری ثبت کرد.

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

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

پرسش 5: Data Collector با ابزارهای نزدیک چه تفاوتی دارد؟

در زمینه Data Collector، تمایز اصلی در منبع داده و عمق تحلیل است؛ این موضوع بر Collection Setها، SQL Agent و Upload به MDW تکیه دارد، در حالی که ابزار دیگر ممکن است فقط نمای لحظه‌ای یا گزارش سطح سیستم‌عامل ارائه کند.

پرسش 6: برای پیاده‌سازی حرفه‌ای Data Collector چه خدماتی مفید است؟

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

پرسش 7: رایج‌ترین خطا هنگام استفاده از Data Collector چیست؟

در زمینه Data Collector، رایج‌ترین خطا تفسیر یک نمونه منفرد بدون زمینه است. همچنین باید به این هشدار توجه شود که عدم تنظیم Retention و Schedule مناسب باعث رشد پایگاه مخزن می‌شود.

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

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

پرسش 9: بهترین روش عملی برای Data Collector چیست؟

در زمینه Data Collector، با یک پرسش مشخص مانند «گردآوری Query Statistics» شروع کنید، داده حداقلی لازم را جمع‌آوری کنید، نتیجه را با Baseline بسنجید و اقدام اصلاحی را همراه با معیار بازگشت ثبت کنید.

پرسش 10: سازگاری نسخه‌ای Data Collector را چگونه بررسی کنیم؟

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

سؤالات مصاحبه درباره Data Collector

1. توضیح دهید چرا Data Collector برای ثبت Server Activity روزانه مناسب است.
2. چگونه سربار Data Collector را در یک سرور پرترافیک اندازه‌گیری می‌کنید؟
3. بین خروجی‌های Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها کدام معیار را برای تشخیص اولیه انتخاب می‌کنید و چرا؟
4. اگر داده Data Collector با تجربه کاربر ناسازگار باشد، چه منابع مکملی را بررسی می‌کنید؟
5. یک برنامه بازگشت برای تغییری که بر اساس Data Collector پیشنهاد شده طراحی کنید.

چک‌لیست نهایی Data Collector

• در Data Collector پرسش پایش برای ثبت Server Activity روزانه نوشته شده است.
• برای Data Collector مجوز دسترسی به Collection Setها، SQL Agent و Upload به MDW بررسی شده است.
• بازه زمانی و حجم خروجی Data Collector محدود شده است.
• برای Data Collector حداقل دو معیار از Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها ثبت شده است.
• تأثیر Data Collector بر CPU، I/O و فضای دیسک سنجیده شده است.
• اقدام اصلاحی مبتنی بر Data Collector و برنامه بازگشت مستند شده است.

جمع‌بندی آموزش Data Collector

Data Collector زمانی بیشترین ارزش را دارد که برای یک مسئله محدود مانند ثبت Server Activity روزانه به کار رود و خروجی آن با Baseline سنجیده شود. داده‌های Query Statistics، Disk Usage، Server Activity و تاریخچه نمونه‌ها باید در کنار زمینه Workload تفسیر شوند. با رعایت فیلتر، زمان‌بندی و کنترل سربار می‌توان از این قابلیت برای تصمیم‌های قابل دفاع در SQL Server استفاده کرد.

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