آموزش جامع sys.dm_exec_cached_plans در SQL Server

تعریف و جایگاه موضوع

sys.dm_exec_cached_plans در SQL Server به‌طور مشخص برای مشاهده Cache Entryهای Plan Cache و تحلیل نوع Object، اندازه Plan و تعداد استفاده مجدد به‌کار می‌رود. جایگاه آن در چرخه Troubleshooting میان جمع‌آوری Evidence، تحلیل Root Cause، اجرای Change و کنترل نتیجه قرار می‌گیرد.

مفاهیم کلیدی این مقاله شامل cacheobjtype، objtype، usecounts، size_in_bytes، plan_handle است. این مفاهیم باید در کنار هم دیده شوند؛ برای نمونه افزایش cacheobjtype بدون بررسی objtype الزاماً نشانه مشکل یا موفقیت نیست.

این تصویر جایگاه آموزش جامع sys.dm_exec_cached_plans در SQL Server را میان اجزای مرتبط نشان می‌دهد و مشخص می‌کند cacheobjtype چگونه به objtype و usecounts متصل می‌شود.

Scope، Permission و ستون‌های کلیدی

این DMV یا View داده لحظه‌ای یا تجمعی را از Engine ارائه می‌کند و معمولاً برای خواندن آن Permission سطح Server یا Database لازم است.

Syntax یا Query پایه

SELECT TOP (50) *
FROM sys.dm_exec_cached_plans;

رفتارهای ویژه و محدودیت سازگاری

رفتار sys.dm_exec_cached_plans ممکن است با Version، Edition، Compatibility Level و Permission تغییر کند. مقدار NULL، نبود داده، Restart سرویس، Eviction از Cache یا READ_ONLY شدن Query Store باید به‌عنوان حالت معتبر در طراحی Query در نظر گرفته شود.

اجزای اصلی و منطق اجرا

این جریان، مسیر واقعی از ورودی و State اولیه تا نتیجه قابل‌مشاهده در آموزش جامع sys.dm_exec_cached_plans در SQL Server را نمایش می‌دهد؛ نقاط کنترل cacheobjtype، objtype و size_in_bytes در آن برجسته شده‌اند.

مثال‌های عملی از ساده تا پیشرفته

مثال 1: مشاهده خروجی پایه

اولین گام برای شناخت sys.dm_exec_cached_plans مشاهده Snapshot فعلی و نام ستون‌ها است.

SELECT TOP (20) *
FROM sys.dm_exec_cached_plans;

نکته عملی: این Query نقطه شروع است؛ پیش از ساخت Alert باید ستون‌ها و Scope واقعی در نسخه نصب‌شده بررسی شود.

مثال 2: بررسی Metadata ستون‌ها

برای نوشتن Query پایدار، نوع داده و نام ستون‌های DMV را از Metadata استخراج می‌کنیم.

SELECT c.column_id, c.name, TYPE_NAME(c.user_type_id) AS data_type
FROM sys.all_columns AS c
WHERE c.object_id = OBJECT_ID(N'sys.dm_exec_cached_plans')
ORDER BY c.column_id;

نکته عملی: این روش از حدس‌زدن نوع ستون جلوگیری می‌کند و برای مستندسازی نسخه‌های مختلف مناسب است.

مثال 3: کنترل Permission

قبل از اجرای مانیتورینگ در Production باید Permission حساب سرویس مشخص شود.

SELECT
    HAS_PERMS_BY_NAME(NULL, NULL, N'VIEW SERVER STATE') AS HasViewServerState,
    HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', N'VIEW DATABASE STATE') AS HasViewDatabaseState;

نکته عملی: Permission را حداقلی نگه دارید و از اجرای Job با حساب sysadmin فقط برای رفع خطای دسترسی پرهیز کنید.

مثال 4: شمارش Snapshot

تعداد ردیف‌ها یک Baseline ساده برای مقایسه تغییرات بعدی ایجاد می‌کند.

SELECT COUNT_BIG(*) AS snapshot_rows
FROM sys.dm_exec_cached_plans;

نکته عملی: این عدد به‌تنهایی Metric Performance نیست؛ فقط حجم Snapshot را نشان می‌دهد.

مثال 5: نمونه‌برداری تکرارشونده

دو Snapshot با فاصله کوتاه کمک می‌کند تغییرپذیری داده مشخص شود.

SELECT SYSDATETIME() AS captured_at, COUNT_BIG(*) AS snapshot_rows
FROM sys.dm_exec_cached_plans;
WAITFOR DELAY '00:00:01';
SELECT SYSDATETIME() AS captured_at, COUNT_BIG(*) AS snapshot_rows
FROM sys.dm_exec_cached_plans;

نکته عملی: در Job واقعی فاصله Capture را متناسب با نرخ تغییر Workload تعیین کنید.

مثال 6: ساخت Snapshot موقت

برای تحلیل بدون چندبار خواندن DMV، خروجی را در Temp Table نگه می‌داریم.

SELECT TOP (100) *
INTO #TopicSnapshot
FROM sys.dm_exec_cached_plans;

SELECT COUNT_BIG(*) AS captured_rows
FROM #TopicSnapshot;

نکته عملی: Snapshot موقت Consistency تحلیل را بهتر می‌کند، ولی TempDB مصرف می‌کند.

مثال 7: تشخیص Reset یا Eviction

زمان Start سرویس را کنار Snapshot ثبت می‌کنیم تا Reset شدن Counterها قابل تفسیر باشد.

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT COUNT_BIG(*) AS snapshot_rows
FROM sys.dm_exec_cached_plans;

نکته عملی: بعد از Restart یا Eviction، مقایسه با Baseline قدیمی معتبر نیست.

مثال 8: گزارش قابل Audit

خروجی را با Context Database و Login ثبت می‌کنیم تا منبع Snapshot معلوم باشد.

SELECT DB_NAME() AS database_name, ORIGINAL_LOGIN() AS login_name, SYSDATETIME() AS captured_at;
SELECT TOP (10) *
FROM sys.dm_exec_cached_plans;

نکته عملی: ثبت Context برای Incident Review و مقایسه بین Environmentها ضروری است.

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

خواندن همه ستون‌ها و همه ردیف‌ها در Loop مانیتورینگ می‌تواند Overhead ایجاد کند.

-- روش نامناسب برای Polling پرتکرار:
-- SELECT * FROM sys.dm_exec_cached_plans;

-- نسخه کنترل‌شده:
SELECT TOP (20) *
FROM sys.dm_exec_cached_plans;

نکته عملی: ستون‌های لازم را صریح انتخاب کنید و Polling Interval را از نیاز عملی استخراج کنید.

مثال 10: اندازه‌گیری هزینه Query مانیتورینگ

برای ارزیابی Overhead خود Query مانیتورینگ، Statistics IO و Time فعال می‌شود.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (50) *
FROM sys.dm_exec_cached_plans;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

نکته عملی: مانیتورینگ نباید خود به منبع مصرف CPU یا Contention تبدیل شود.

کاربردهای واقعی در پروژه

در پروژه واقعی، sys.dm_exec_cached_plans زمانی ارزشمند است که به یک Runbook متصل شود. Trigger استفاده، Owner تصمیم، Query جمع‌آوری Evidence، مقدار Baseline، محدوده Change و معیار توقف باید پیش از Incident نوشته شده باشد.

برای Workload سازمانی می‌توان خروجی cacheobjtype را با objtype، Query Store، Extended Events و Metricهای سیستم‌عامل هم‌بسته کرد. این هم‌بستگی کمک می‌کند مشکل Application، Storage، Compile یا Concurrency با یک علامت منفرد اشتباه نشود.

اشتباهات رایج و روش اصلاح

1. اقدام بر اساس یک Snapshot: راه اصلاح، Capture چندبازه‌ای و مقایسه cacheobjtype با Baseline است.
2. نادیده‌گرفتن Scope: Database، Instance، Session یا Plan هدف را قبل از اجرای sys.dm_exec_cached_plans مشخص کنید.
3. اجرای Change بدون معیار موفقیت: CPU، Reads، Latency، Compile و Blocking را قبل و بعد ثبت کنید.
4. فرض دائمی‌بودن Counterها: Restart، Eviction، Cleanup یا Reset می‌تواند تاریخچه objtype را تغییر دهد.
5. استفاده از Permission بیش از نیاز: دسترسی حداقلی و حساب سرویس مجزا برای مانیتورینگ تعریف کنید.

Performance Considerations

هزینه اصلی در این موضوع به نرخ Polling، حجم خروجی، Compile، I/O و Scope Change وابسته است. Query مانیتورینگ باید فقط ستون‌های لازم را بخواند و از SELECT ستاره در Loop پرتکرار دوری کند. برای Changeهای Cache یا Query Store، اثر روی CPU و Latency بعد از تغییر باید در Window کوتاه کنترل شود.

SARGability و Index زمانی مهم می‌شوند که داده sys.dm_exec_cached_plans در Repository دائمی ذخیره و گزارش‌گیری شود. روی جدول Snapshot، Columnهای زمان Capture، Database ID، Query ID یا Plan ID را متناسب با الگوی گزارش Index کنید؛ خود DMVها را نمی‌توان Index کرد.

Best Practices

1. Scope را کوچک و قابل‌اندازه‌گیری انتخاب کنید.
2. قبل از تغییر، Baseline مربوط به cacheobjtype و objtype را ذخیره کنید.
3. Version، Edition و Compatibility Level را در Runbook بنویسید.
4. Change را در Test یا Canary اجرا و سپس مرحله‌ای گسترش دهید.
5. پس از اجرا، usecounts، size_in_bytes و Metricهای کاربر نهایی را دوباره اندازه بگیرید.
6. برای عملیات برگشت‌ناپذیر، Backup Evidence و تأیید دومرحله‌ای داشته باشید.

این پنل تصمیم نشان می‌دهد در سناریوی آموزش جامع sys.dm_exec_cached_plans در SQL Server چه زمانی مسیر پیشنهادی انتخاب شود، کجا Overhead یا ریسک افزایش می‌یابد و چگونه cacheobjtype با plan_handle سنجیده شود.

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

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

آموزش جامع sys.dm_exec_cached_plans در SQL Server دقیقاً چه مسئله‌ای را حل می‌کند؟

مسئله اصلی، مشاهده Cache Entryهای Plan Cache و تحلیل نوع Object، اندازه Plan و تعداد استفاده مجدد است. ارزش واقعی زمانی ایجاد می‌شود که خروجی با Baseline و Context درست تفسیر شود.

برای شروع کار با آموزش جامع sys.dm_exec_cached_plans در SQL Server چه پیش‌نیازی لازم است؟

دسترسی مناسب، شناخت Scope، ثبت State اولیه و آشنایی با cacheobjtype و objtype ضروری است.

آیا استفاده از آموزش جامع sys.dm_exec_cached_plans در SQL Server هزینه زیرساخت را کاهش می‌دهد؟

در صورت استفاده هدفمند، می‌تواند هزینه ناشی از CPU، I/O، Incident و زمان عیب‌یابی را کاهش دهد؛ نتیجه باید با Metric مالی و فنی سنجیده شود.

این موضوع برای پروژه‌های سازمانی چه ارزشی دارد؟

در پروژه سازمانی، استانداردسازی cacheobjtype و usecounts باعث Audit بهتر، تصمیم سریع‌تر و کاهش ریسک Change می‌شود.

آموزش جامع sys.dm_exec_cached_plans در SQL Server با روش‌های جایگزین چه تفاوتی دارد؟

تفاوت اصلی در Scope، میزان جزئیات، اثر عملیاتی و قابلیت بازگشت است؛ انتخاب باید بر اساس سناریو باشد نه محبوبیت ابزار.

برای پیاده‌سازی حرفه‌ای آموزش جامع sys.dm_exec_cached_plans در SQL Server می‌توان از خدمات تخصصی استفاده کرد؟

بله، آموزش، مشاوره، طراحی Runbook و اجرای پروژه SQL Server می‌تواند متناسب با Workload و محدودیت سازمان انجام شود.

رایج‌ترین خطا در استفاده از آموزش جامع sys.dm_exec_cached_plans در SQL Server چیست؟

رایج‌ترین خطا، اقدام بدون Baseline و تفسیر جداگانه cacheobjtype بدون توجه به size_in_bytes است.

آموزش جامع sys.dm_exec_cached_plans در SQL Server چه اثری بر Performance دارد؟

اثر به نرخ اجرا، Scope و Workload وابسته است. CPU، Logical Reads، Compile، Blocking و Latency باید هم‌زمان بررسی شوند.

Best Practice اصلی برای آموزش جامع sys.dm_exec_cached_plans در SQL Server چیست؟

با Scope کوچک شروع کنید، State را ثبت کنید، معیار موفقیت تعریف کنید و پس از تغییر، objtype و plan_handle را دوباره اندازه بگیرید.

آیا آموزش جامع sys.dm_exec_cached_plans در SQL Server در همه نسخه‌های SQL Server یکسان است؟

خیر. Availability گزینه‌ها، Permissionها، ستون‌ها و رفتار ممکن است با Version، Edition و Compatibility Level تفاوت داشته باشد؛ محیط هدف را مستقیماً بررسی کنید.

سؤالات مصاحبه

1. چگونه Scope و Reset Condition مربوط به آموزش جامع sys.dm_exec_cached_plans در SQL Server را توضیح می‌دهید؟
2. برای اندازه‌گیری اثر آموزش جامع sys.dm_exec_cached_plans در SQL Server چه Baseline و Metricهایی انتخاب می‌کنید؟
3. تفاوت Diagnostic Action و Change Action در این موضوع چیست؟
4. اگر cacheobjtype بهتر ولی objtype بدتر شود، تصمیم شما چیست؟
5. چه Rollback Plan و Audit Trail برای استفاده از آموزش جامع sys.dm_exec_cached_plans در SQL Server تعریف می‌کنید؟

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

1. Database و Instance هدف را تأیید کنید.
2. Permission لازم را بدون افزایش دائمی Role بررسی کنید.
3. Baseline و زمان شروع اندازه‌گیری را ذخیره کنید.
4. Query یا Command را ابتدا با Scope محدود اجرا کنید.
5. Output، Messages و Errorها را در Change Log ثبت کنید.
6. Metricهای Before/After را با SLA مقایسه کنید.
7. در صورت Regression، Rollback یا توقف مرحله بعد را اجرا کنید.
8. نتیجه و درس‌آموخته را در Runbook خانواده ثبت کنید.