راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server

تعریف مجموعه و مدل تصمیم‌گیری

این مجموعه در خانواده بصری «Performance / Tuning / Optimization» قرار می‌گیرد، زیرا اعضای آن داده یا فرمانی برای تحلیل Performance تولید می‌کنند. مدل پیشنهادی چهار گام دارد: Scope را محدود کنید، Snapshot بگیرید، سیگنال را با منبع دوم هم‌بسته سازید و فقط سپس اقدام قابل بازگشت تعریف نمایید.

در خانواده 14، هر عضو باید نقش متفاوتی داشته باشد. یکی Baseline می‌سازد، دیگری جزئیات ساختاری یا عملیاتی می‌دهد و عضو بعدی نتیجه را به تصمیم نگهداری نزدیک می‌کند. اگر دو ابزار پاسخ یکسان می‌دهند، Query اضافی حذف و ابزار کم‌هزینه‌تر انتخاب می‌شود.

این تصویر، جایگاه Index Performance DMVs را با تمرکز بر SYS_DM_DB_INDEX_USAGE_STATS، SYS_DM_DB_INDEX_OPERATIONAL_STATS و SYS_DM_DB_INDEX_PHYSICAL_STATS نشان می‌دهد؛ پنل عددی پایین شکل برای جداکردن مشاهده خام از تصمیم اجرایی طراحی شده است.

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

جدول مقایسه تصمیم‌محور اعضای مجموعه

ارتباط داده‌ها از Baseline تا اقدام

برای این مجموعه، Snapshotها باید با یک Capture ID مشترک ذخیره شوند. نام دیتابیس، زمان محلی و UTC، نسخه SQL Server، وضعیت سرویس و Scope Query کنار خروجی قرار می‌گیرد تا مقایسه میان sys.dm_db_index_usage_stats و sys.dm_db_column_store_object_pool از نظر زمانی معتبر باشد.

مرحله بعد تبدیل سیگنال به فرضیه است. برای نمونه، عدد بزرگ فقط می‌گوید کجا نگاه کنیم؛ علت با Plan، Query Text، نوع Storage، تغییرات داده یا تنظیم Maintenance روشن می‌شود. این تفکیک مانع آن است که ابزار مشاهده به ماشین تولید تغییرات خودکار تبدیل شود.

در نمودار دوم، مسیر SYS_DM_DB_INDEX_USAGE_STATS تا SYS_DM_DB_COLUMN_STORE_ROW_GROUP_PHYSICAL_STATS به‌صورت مرحله‌ای ترسیم شده تا ورودی، تبدیل و خروجی Index Performance DMVs در یک نگاه قابل دنبال‌کردن باشد.

شش مثال ترکیبی و مستقل برای خانواده

مثال ترکیبی 1: ساخت نمای پایه برای خانواده ابزارها

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد.

SELECT DB_NAME(u.database_id) AS database_name,OBJECT_NAME(u.object_id,u.database_id) AS table_name,i.name,
       u.user_seeks+u.user_scans+u.user_lookups AS reads,u.user_updates AS writes
FROM sys.dm_db_index_usage_stats AS u JOIN sys.indexes AS i ON i.object_id=u.object_id AND i.index_id=u.index_id
WHERE u.database_id=DB_ID() ORDER BY reads DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود.

مثال ترکیبی 2: محدودکردن تحلیل به موارد قابل اقدام

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد. این نکته در تحلیل خانواده 14 با محور Index Performance DMVs به‌صورت مستقل ارزیابی می‌شود.

SELECT OBJECT_NAME(ps.object_id) AS table_name,i.name,ps.page_count,ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,N'LIMITED') AS ps
JOIN sys.indexes AS i ON i.object_id=ps.object_id AND i.index_id=ps.index_id
WHERE ps.index_level=0 AND ps.page_count>=1000 ORDER BY ps.avg_fragmentation_in_percent DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود. برای موضوع Index Performance DMVs در مجموعه 14، همین قاعده باید با داده همان Scope تطبیق داده شود.

مثال ترکیبی 3: اتصال دو منبع برای ایجاد Context

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد. در Runbook خانواده 14، تفسیر این بند به شواهد اختصاصی Index Performance DMVs وابسته است.

SELECT OBJECT_NAME(os.object_id) AS table_name,i.name,os.leaf_insert_count,os.page_latch_wait_count,os.page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) AS os
JOIN sys.indexes AS i ON i.object_id=os.object_id AND i.index_id=os.index_id
WHERE os.page_latch_wait_count>0 ORDER BY os.page_latch_wait_in_ms DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود. کاربرد این اصل در Index Performance DMVs با معیارهای ویژه مجموعه 14 سنجیده می‌شود.

مثال ترکیبی 4: تبدیل Metadata به گزارش تصمیم

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد. در مجموعه 14، این تصمیم برای Index Performance DMVs باید جدا از خانواده‌های دیگر ثبت شود.

SELECT OBJECT_NAME(object_id) AS table_name,state_desc,total_rows,deleted_rows,
       100.0*deleted_rows/NULLIF(total_rows,0) AS deleted_percent
FROM sys.dm_db_column_store_row_group_physical_stats ORDER BY deleted_percent DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود. برای Index Performance DMVs، اجرای این توصیه در خانواده 14 نیازمند Baseline مخصوص همان موضوع است.

مثال ترکیبی 5: بررسی سناریوی نسخه یا قابلیت خاص

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد. این نکته در تحلیل خانواده 14 با محور Index Performance DMVs به‌صورت مستقل ارزیابی می‌شود.

SELECT OBJECT_NAME(object_id) AS table_name,row_group_id,scan_count,rowgroup_lock_wait_count,rowgroup_lock_wait_in_ms
FROM sys.dm_db_column_store_row_group_operational_stats
WHERE rowgroup_lock_wait_count>0 ORDER BY rowgroup_lock_wait_in_ms DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود. برای موضوع Index Performance DMVs در مجموعه 14، همین قاعده باید با داده همان Scope تطبیق داده شود.

مثال ترکیبی 6: تهیه خروجی نهایی برای Runbook

این مثال ترکیبی از اعضای خانواده «راهنمای جامع DMVهای سنجش کارایی ایندکس در SQL Server» استفاده می‌کند تا یک سؤال سطح‌بالا را بدون کپی‌کردن مثال‌های مقاله‌های فرزند پاسخ دهد. در Runbook خانواده 14، تفسیر این بند به شواهد اختصاصی Index Performance DMVs وابسته است.

SELECT OBJECT_NAME(object_id,database_id) AS table_name,object_type_desc,SUM(memory_used_in_bytes) AS memory_bytes,SUM(access_count) AS accesses
FROM sys.dm_column_store_object_pool WHERE database_id=DB_ID()
GROUP BY object_id,database_id,object_type_desc ORDER BY memory_bytes DESC;

نکته این سناریو، ترکیب کنترل‌شده منابع خانواده 14 است؛ نتیجه باید قبل از هر تغییر با Workload و Plan واقعی تطبیق داده شود. کاربرد این اصل در Index Performance DMVs با معیارهای ویژه مجموعه 14 سنجیده می‌شود.

سناریوهای واقعی استفاده

در Incident، ابتدا ارزان‌ترین عضو خانواده 14 برای محدودکردن دامنه اجرا می‌شود. اگر سیگنال پایدار بود، ابزار جزئیات روی همان Object فراخوانی و خروجی با Query Store یا Plan متناظر می‌شود. این مسیر زمان جمع‌آوری را کوتاه و بار تشخیص را کنترل می‌کند.

در Capacity Planning، Snapshotهای هفتگی از معیارهای اصلی نگهداری و با Deploymentها علامت‌گذاری می‌شوند. تغییر روند پس از Release مهم‌تر از یک عدد مطلق است و کمک می‌کند بودجه سخت‌افزار یا بازطراحی قبل از اشباع واقعی برنامه‌ریزی گردد.

در آموزش تیم، هر عضو به یک سؤال مشخص متصل می‌شود. فراگیر باید توضیح دهد چه چیزی را مشاهده کرده، چه چیزی را هنوز نمی‌داند و کدام منبع دوم برای رد یا تأیید فرضیه لازم است؛ این تمرین از حفظ Syntax مفیدتر است.

اشتباهات رایج در استفاده ترکیبی

• ترکیب داده‌های قبل و بعد از Restart بدون ثبت مرز Reset؛ اصلاح: زمان شروع موتور و Capture ID ذخیره شود.
• مقایسه دیتابیس‌های با حجم و Workload متفاوت با Threshold یکسان؛ اصلاح: Baseline محلی و واحد مشترک تعریف گردد.
• اجرای ابزار عمیق روی همه اشیا؛ اصلاح: ابتدا با Query سبک فهرست کوتاه ساخته شود.
• کپی‌کردن Recommendation به DDL؛ اصلاح: هم‌پوشانی، هزینه نگهداری و Plan در محیط آزمایش سنجیده شود.
• نداشتن مالک و تاریخ بازبینی؛ اصلاح: هر Finding به Ticket، مسئول و معیار بسته‌شدن متصل گردد.

Performance مجموعه و هزینه جمع‌آوری

خود فرآیند مانیتورینگ باید Budget داشته باشد. در خانواده 14 Queryهای Catalog و DMV با Top، Predicate و Projection محدود اجرا می‌شوند؛ فرمان‌های اسکن یا Update نیز در پنجره جدا و با اندازه‌گیری Reads، CPU و مدت اجرا قرار می‌گیرند.

تعداد Snapshot بیشتر لزوماً دید بهتر ایجاد نمی‌کند. تناوب باید با سرعت تغییر سیگنال هماهنگ باشد: شمارنده سریع ممکن است هر چند دقیقه، Metadata پایدار روزانه و اسکن فیزیکی فقط پس از عبور از شرط اندازه یا رخداد نگهداری جمع‌آوری شود.

برای گزارش، داده خام کوتاه‌مدت و Aggregation بلندمدت جدا نگهداری شود. Retention نامحدود، Indexهای مخزن مانیتورینگ و گزارش‌های Sort سنگین می‌توانند هزینه‌ای بیشتر از سود تشخیص ایجاد کنند.

Best Practices برای پیاده‌سازی سازمانی

• هر Query را به یک سؤال عملی و یک مالک متصل کنید.
• Capture ID و Timestamp مشترک برای همه منابع یک تحلیل بسازید.
• نسخه SQL Server و مجوزهای لازم را در ابتدای Runbook ثبت نمایید.
• مرحله مشاهده را از مرحله تغییر جدا و Approval مستقل تعریف کنید.
• معیار قبل/بعد، زمان انتظار مشاهده اثر و Rollback را پیش از اجرا بنویسید.
• پس از هر Incident، Queryها و Thresholdها را با شواهد جدید بازبینی کنید.

نمای سوم، رابطه SYS_DM_DB_INDEX_OPERATIONAL_STATS، SYS_DM_DB_COLUMN_STORE_ROW_GROUP_OPERATIONAL_STATS و SYS_DM_DB_COLUMN_STORE_OBJECT_POOL را در کنار شاخص‌های تصمیم نمایش می‌دهد و مشخص می‌کند کدام مسیر برای Performance یا رفع خطا اولویت دارد.

مزایا، محدودیت‌ها و مرز استفاده

مزیت این خانواده، پوشش چند زاویه از مسئله ترکیب آمار استفاده، عملیات داخلی، وضعیت فیزیکی و رفتار Columnstore برای تصمیم‌گیری ایمن درباره طراحی و نگهداری ایندکس. است. کنارهم‌گذاشتن اعضا امکان می‌دهد نتیجه یک ابزار با ابزار دیگر کنترل شود و تصمیم از حالت تک‌منبعی خارج گردد.

محدودیت مهم، ناهمگونی زمان و ماندگاری داده‌هاست. برخی شمارنده‌ها با Restart صفر می‌شوند، بعضی خروجی لحظه‌ای‌اند و فرمان‌های Statistics یا DBCC اثر اجرایی دارند. گزارش بدون Timestamp و Context از نظر تحلیلی ناقص است.

این مجموعه برای اجرای کور در Incident، ساخت خودکار ایندکس یا تغییر سراسری تنظیمات مناسب نیست. وقتی Baseline ندارید، نخست باید مشاهده کم‌خطر و ثبت شواهد را انجام دهید و تغییر را به مرحله بعد منتقل کنید.

سؤالات متداول مجموعه

از میان اعضای Index Performance DMVs از کدام مورد شروع کنیم؟

شروع به مسئله بستگی دارد. برای Baseline نخست «sys.dm_db_index_usage_stats» و برای تکمیل Context سپس «sys.dm_db_index_operational_stats» را بخوانید؛ مقاله مادر ترتیب مفهومی را می‌دهد اما انتخاب عملی باید از سؤال Workload آغاز شود.

آیا اجرای همه ابزارهای خانواده 14 در یک Job مناسب است؟

خیر، هزینه و Scope اعضا متفاوت است. DMV سبک را می‌توان با تناوب بیشتر ثبت کرد، ولی فرمان یا اسکن پرهزینه باید پنجره جدا، فیلتر هدفمند و شرط توقف داشته باشد.

برای پروژه کوچک چند عضو این مجموعه کافی است؟

معمولاً یک ابزار Baseline، یک ابزار جزئیات و یک معیار اعتبارسنجی کافی است. در این خانواده می‌توان sys.dm_db_index_usage_stats را با sys.dm_db_column_store_object_pool ترکیب کرد و با رشد سیستم پوشش را توسعه داد.

خروجی این خانواده چگونه در گزارش مدیریتی استفاده شود؟

اعداد خام به زبان ریسک و اقدام تبدیل می‌شوند: شیء متاثر، شدت، زمان مشاهده، مالک، پیشنهاد آزمایش و معیار موفقیت. گزارش مدیریتی نباید ستون‌های فنی را بدون تفسیر نمایش دهد.

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

این صفحه رابطه ابزارها، ترتیب استفاده و تصمیم ترکیبی را توضیح می‌دهد؛ صفحات فرزند Syntax، ستون‌ها، ده مثال و خطاهای همان موضوع را عمیق بررسی می‌کنند.

چه زمانی مشاوره تخصصی برای این مجموعه منطقی است؟

وقتی تحلیل به تغییر ایندکس، تنظیم Statistics، DDL یا عملیات گسترده منتهی می‌شود، بازبینی یک متخصص SQL Server می‌تواند ریسک Regression را کم کند. تهیه Snapshotهای این راهنما جلسه مشاوره را عملی و قابل اندازه‌گیری می‌سازد.

خطای رایج در ترکیب اعضای Index Performance DMVs چیست؟

رایج‌ترین خطا، کنارهم‌گذاشتن Snapshotهایی با زمان و Scope متفاوت است. همه منابع باید Timestamp مشترک یا بازه قابل تطبیق داشته باشند و Reset شدن شمارنده‌ها ثبت شود.

چگونه هزینه Performance گزارش جامع را کنترل کنیم؟

Queryها را مرحله‌ای اجرا کنید، ابتدا Top و فیلتر اندازه بگذارید، سپس فقط برای موارد مشکوک وارد اسکن عمیق شوید. ذخیره خروجی کوچک و تحلیل Offline معمولاً از تکرار Query سنگین بهتر است.

بهترین Practice برای تبدیل خروجی خانواده به اقدام چیست؟

هر اقدام باید یک فرضیه، شواهد از حداقل دو عضو، معیار قبل/بعد، مالک و Rollback داشته باشد. بدون این پنج جزء، خروجی در حد Recommendation غیرقطعی باقی می‌ماند.

سازگاری نسخه‌ای اعضای خانواده چگونه کنترل شود؟

وجود DMV یا Option، نام مجوز و ستون‌های قابل اتکا را با نسخه هدف بررسی کنید. Script باید نسخه را ثبت و برای قابلیت‌های جدید مسیر جایگزین یا پیام روشن داشته باشد.

سؤالات مصاحبه برای نقش DBA و Performance Engineer

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

ابزار نخست باید کم‌هزینه‌ترین منبعی باشد که Scope مسئله را محدود می‌کند؛ سپس فقط برای موارد منتخب سراغ جزئیات یا فرمان سنگین می‌روم. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

چرا Timestamp مشترک مهم است؟

زیرا هم‌بستگی دو منبع با زمان متفاوت می‌تواند علت و معلول ساختگی ایجاد کند. Capture ID مشترک مرز تحلیل را حفظ می‌کند. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

چه زمانی یک Finding به اقدام تبدیل می‌شود؟

وقتی از دو منبع مستقل پشتیبانی شود، منفعت قابل اندازه‌گیری داشته باشد و Rollback و مالک آن مشخص باشد. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

چگونه Threshold تعیین می‌کنید؟

از Baseline همان سیستم، SLA و هزینه اقدام استفاده می‌کنم؛ عدد عمومی فقط نقطه شروع آزمایش است. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

چرا خودکارسازی کامل خطرناک است؟

زیرا بسیاری از خروجی‌ها پیشنهاد یا Snapshot موقت‌اند و Context طراحی، Constraint، هزینه نوشتن و فصل کاری را نمی‌بینند. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

پس از تغییر چه چیزی را ثبت می‌کنید؟

زمان اجرا، Script دقیق، Metricهای قبل/بعد، Planهای متاثر و تصمیم نگهداری یا بازگشت را در Ticket و مخزن فنی ذخیره می‌کنم. این پاسخ در خانواده 14 باید با یکی از اعضای واقعی مجموعه مثال زده شود.

چک‌لیست نهایی خانواده

1. تعداد و نام اعضای همین مجموعه را با ورودی کنترل کنید.
2. برای هر عضو نقش Baseline، جزئیات، اعتبارسنجی یا اقدام را تعیین نمایید.
3. Slug و لینک همه صفحات فرزند را پیش از انتشار آزمایش کنید.
4. Capture ID، Timestamp و نسخه SQL Server را در خروجی‌ها نگه دارید.
5. Queryهای عمیق را فقط روی فهرست کوتاه و در زمان کنترل‌شده اجرا کنید.
6. هر Recommendation را با Plan و هزینه نگهداری اعتبارسنجی نمایید.
7. تغییر Production را با Approval، Backup و Rollback اجرا کنید.
8. اثر تغییر را با همان معیارهای Baseline دوباره بسنجید.