آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server

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

DBCC FREEPROCCACHE(plan_handle) در SQL Server به‌طور مشخص برای حذف هدفمند یک Plan مشخص از Cache با استفاده از plan_handle به‌کار می‌رود. جایگاه آن در چرخه Troubleshooting میان جمع‌آوری Evidence، تحلیل Root Cause، اجرای Change و کنترل نتیجه قرار می‌گیرد.

مفاهیم کلیدی این مقاله شامل plan_handle، targeted eviction، sys.dm_exec_query_stats، compile، single plan است. این مفاهیم باید در کنار هم دیده شوند؛ برای نمونه افزایش plan_handle بدون بررسی targeted eviction الزاماً نشانه مشکل یا موفقیت نیست.

این تصویر جایگاه آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server را میان اجزای مرتبط نشان می‌دهد و مشخص می‌کند plan_handle چگونه به targeted eviction و sys.dm_exec_query_stats متصل می‌شود.

Syntax، سطح دسترسی و اثر عملیاتی

DBCC Command باید با Scope روشن، Permission مناسب و درنظرگرفتن اثر روی Production اجرا شود. گزینه WITH NO_INFOMSGS فقط پیام‌های اطلاع‌رسانی را کم می‌کند و ریسک Command را تغییر نمی‌دهد.

Syntax یا Query پایه

-- مقدار Hex واقعی را از sys.dm_exec_query_stats دریافت کنید
DBCC FREEPROCCACHE (0x06000100ABCDEF) WITH NO_INFOMSGS;

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

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

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

این جریان، مسیر واقعی از ورودی و State اولیه تا نتیجه قابل‌مشاهده در آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server را نمایش می‌دهد؛ نقاط کنترل plan_handle، targeted eviction و compile در آن برجسته شده‌اند.

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

مثال 1: بررسی State قبل از اقدام

قبل از استفاده از DBCC FREEPROCCACHE(plan_handle) باید State فعلی ثبت شود.

SELECT TOP (20)
    cp.cacheobjtype, cp.objtype, cp.usecounts, cp.size_in_bytes, cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
ORDER BY cp.size_in_bytes DESC;

نکته عملی: Baseline امکان مقایسه Before/After و Rollback تصمیم را فراهم می‌کند.

مثال 2: Syntax پایه و کنترل‌شده

نمونه اصلی DBCC FREEPROCCACHE(plan_handle) با Scope روشن نمایش داده می‌شود.

-- مقدار Hex واقعی را از sys.dm_exec_query_stats دریافت کنید
DBCC FREEPROCCACHE (0x06000100ABCDEF) WITH NO_INFOMSGS;

نکته عملی: Commandهای تغییردهنده را ابتدا در Test اجرا کنید و در Production بدون Change Approval استفاده نکنید.

مثال 3: کنترل نسخه SQL Server

نسخه Engine و Compatibility Level می‌تواند Availability یا رفتار گزینه را تغییر دهد.

SELECT
    SERVERPROPERTY(N'ProductVersion') AS product_version,
    SERVERPROPERTY(N'ProductLevel') AS product_level,
    SERVERPROPERTY(N'Edition') AS edition,
    compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

نکته عملی: مستندات داخلی سازمان باید نسخه و Edition هدف را صریح ثبت کند.

مثال 4: کنترل Permission اجرا

برای جلوگیری از خطای Runtime، Permission مرتبط پیش از Change Window بررسی می‌شود.

SELECT
    IS_SRVROLEMEMBER(N'sysadmin') AS IsSysadmin,
    IS_MEMBER(N'db_owner') AS IsDbOwner,
    ORIGINAL_LOGIN() AS OriginalLogin;

نکته عملی: به‌جای افزایش دائمی Permission، مجوز حداقلی و فرایند کنترل‌شده تعریف کنید.

مثال 5: ثبت Baseline Plan Cache

برای موضوع‌های Cache یا Recompile، وضعیت Plan Cache قبل و بعد مقایسه می‌شود.

SELECT
    COUNT_BIG(*) AS cached_plan_count,
    SUM(CONVERT(bigint, size_in_bytes)) / 1024 AS cached_size_kb,
    SUM(CONVERT(bigint, usecounts)) AS total_usecounts
FROM sys.dm_exec_cached_plans;

نکته عملی: تغییر Count بدون مشاهده CPU و Latency برای نتیجه‌گیری کافی نیست.

مثال 6: ثبت Baseline Query Store

در Databaseهای دارای Query Store، Runtime Statistics برای مقایسه نگه‌داری می‌شود.

SELECT TOP (10)
    q.query_id, p.plan_id,
    SUM(rs.count_executions) AS executions,
    AVG(rs.avg_duration) AS avg_duration_microseconds
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, p.plan_id
ORDER BY avg_duration_microseconds DESC;

نکته عملی: اگر Query Store خاموش است، این مثال باید با Baseline دیگری جایگزین شود.

مثال 7: اجرای Transactional Guard

برای Commandهای Metadata یا Configuration، ابتدا Database Context و شرط ایمنی کنترل می‌شود.

IF DB_NAME() IN (N'master', N'model', N'msdb')
    THROW 51000, N'برای این مثال یک Database کاربری انتخاب کنید.', 1;

-- پس از تأیید Change Window اجرا شود:
-- مقدار Hex واقعی را از sys.dm_exec_query_stats دریافت کنید
DBCC FREEPROCCACHE (0x06000100ABCDEF) WITH NO_INFOMSGS;

نکته عملی: Guard ساده جایگزین Backup و Change Management نیست، اما خطای Context را کم می‌کند.

مثال 8: سناریوی Rollback یا بازگشت State

هر تغییر باید یک مسیر بازگشت روشن داشته باشد.

SELECT N'ثبت State قبل از تغییر' AS step_name, SYSDATETIME() AS step_time;
-- دستور Rollback متناسب با Change Plan در این بخش ثبت می‌شود.
SELECT N'کنترل State بعد از Rollback' AS step_name, SYSDATETIME() AS step_time;

نکته عملی: برای عملیات حذف داده یا Cache، Rollback واقعی ممکن نیست؛ در آن حالت Recovery Plan و Monitoring جایگزین است.

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

اجرای Command بدون Scope، Baseline و هشدار Production یک Anti-pattern است.

-- روش اشتباه:
-- اجرای فوری بدون ثبت Baseline

-- نسخه اصلاح‌شده:
SELECT TOP (20)
    cp.cacheobjtype, cp.objtype, cp.usecounts, cp.size_in_bytes, cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
ORDER BY cp.size_in_bytes DESC;
-- سپس اجرای Change در Window تأییدشده

نکته عملی: هر Command باید Owner، زمان، هدف، معیار موفقیت و تصمیم توقف داشته باشد.

مثال 10: مقایسه Performance قبل و بعد

Metricهای CPU، Reads و Execution Count قبل و بعد از اقدام مقایسه می‌شوند.

SELECT TOP (20)
    qs.plan_handle,
    qs.execution_count,
    qs.total_worker_time,
    qs.total_logical_reads,
    qs.total_elapsed_time
FROM sys.dm_exec_query_stats AS qs
ORDER BY qs.total_worker_time DESC;

نکته عملی: بهبود یک Metric نباید با بدترشدن Blocking، Compile یا I/O پنهان شود.

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

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

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

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

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

Performance Considerations

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

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

Best Practices

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

این پنل تصمیم نشان می‌دهد در سناریوی آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چه زمانی مسیر پیشنهادی انتخاب شود، کجا Overhead یا ریسک افزایش می‌یابد و چگونه plan_handle با single plan سنجیده شود.

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

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

آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server دقیقاً چه مسئله‌ای را حل می‌کند؟

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

برای شروع کار با آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چه پیش‌نیازی لازم است؟

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

آیا استفاده از آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server هزینه زیرساخت را کاهش می‌دهد؟

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

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

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

آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server با روش‌های جایگزین چه تفاوتی دارد؟

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

برای پیاده‌سازی حرفه‌ای آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server می‌توان از خدمات تخصصی استفاده کرد؟

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

رایج‌ترین خطا در استفاده از آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چیست؟

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

آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چه اثری بر Performance دارد؟

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

Best Practice اصلی برای آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چیست؟

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

آیا آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server در همه نسخه‌های SQL Server یکسان است؟

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

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

1. چگونه Scope و Reset Condition مربوط به آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server را توضیح می‌دهید؟
2. برای اندازه‌گیری اثر آموزش جامع DBCC FREEPROCCACHE(plan_handle) در SQL Server چه Baseline و Metricهایی انتخاب می‌کنید؟
3. تفاوت Diagnostic Action و Change Action در این موضوع چیست؟
4. اگر plan_handle بهتر ولی targeted eviction بدتر شود، تصمیم شما چیست؟
5. چه Rollback Plan و Audit Trail برای استفاده از آموزش جامع DBCC FREEPROCCACHE(plan_handle) در 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 خانواده ثبت کنید.