پاک‌سازی Procedure Cache در سطح پایگاه داده؛ آموزش کامل CLEAR_PROCEDURE_CACHE در SQL Server

پاک‌کردن Cache گاهی سریع‌ترین راه بهترشدن موقت یک Query است و همین آن را خطرناک می‌کند: چون ممکن است علت واقعی پنهان بماند.

Clear کردن Procedure Cache برای تست کنترل‌شده یا خروج از Plan نامناسب مفید است، اما عملیات روزمره بی‌خطر نیست. بعد از آن Queryها دوباره Compile می‌شوند و CPU یا Latency موقتاً بالا می‌رود. در این مقاله از Syntax و خواندن مقدار فعلی شروع می‌کنیم و بعد به سناریوی واقعی، روش اندازه‌گیری، خطاهای رایج و تصمیم Production می‌رسیم.

برای دیدن ارتباط این گزینه با سایر تنظیمات، راهنمای جامع Database Scoped Performance Configuration را هم ببینید.

تعریف و منطق فنی

حذف Planهای Cache‌شده یک Database یا در نسخه‌های پشتیبانی‌شده یک Plan مشخص برای Compilation مجدد.

Clear کردن Procedure Cache برای تست کنترل‌شده یا خروج از Plan نامناسب مفید است، اما عملیات روزمره بی‌خطر نیست. بعد از آن Queryها دوباره Compile می‌شوند و CPU یا Latency موقتاً بالا می‌رود.

اصل عملی: تنظیم Performance فقط وقتی ارزش دارد که مسئله، معیار موفقیت و راه برگشت آن از قبل روشن باشد.

Syntax اصلی

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

مقادیر و رفتار

• مقدار یا فرمان: دستور اجرایی؛ ON/OFF ندارد.
• سناریوی مناسب: آزمایش قبل و بعد از تغییر Compatibility یا رفع Plan بد در پنجره نگهداری کنترل‌شده
• ریسک اصلی: پاک‌سازی گسترده در ساعت پیک می‌تواند Compile Storm ایجاد کند و فقط علامت را موقتاً پنهان کند.
• نکته نسخه: CLEAR PROCEDURE_CACHE در Database Scoped Configuration Cache همان Database را پاک می‌کند؛ پاک‌سازی Plan مشخص در نسخه‌های جدیدتر با Plan Handle پشتیبانی می‌شود.

این نقشه رابطه CLEAR_PROCEDURE_CACHE را با Plan Cache، Recompile و شاخص‌های Runtime نشان می‌دهد. تصویر برای فهم دامنه اثر است و جای Execution Plan واقعی را نمی‌گیرد.

چطور قبل از تغییر تصمیم بگیریم

قبل از اجرای تغییر، Queryهای نماینده را بر اساس اهمیت تجاری و مصرف منابع جدا کنید. Plan آنها را در Query Store نگه دارید و فقط یک متغیر را تغییر دهید. اگر هم‌زمان Index، Compatibility Level و CLEAR_PROCEDURE_CACHE را عوض کنید، Attribution نتیجه از بین می‌رود.

معیار تک Query و معیار کل Workload را جدا ببینید. ممکن است یک Query سریع‌تر شود اما Throughput کل افت کند. پاک‌سازی گسترده در ساعت پیک می‌تواند Compile Storm ایجاد کند و فقط علامت را موقتاً پنهان کند. به همین دلیل Concurrency و مصرف منابع باید کنار زمان پاسخ سنجیده شود.

اشتباه رایج: اجرای CLEAR PROCEDURE_CACHE در Production و ساعت پیک فقط برای اینکه یک Query کند موقتاً Plan دیگری بگیرد. راه حرفه‌ای این است که فرضیه، Baseline، Change و Rollback در یک سند کوتاه و قابل بازبینی ثبت شوند.

جریان CLEAR_PROCEDURE_CACHE از مشاهده وضعیت، اعمال Change، Compile یا Runtime Behavior و سپس Measure عبور می‌کند. نقاط اندازه‌گیری قبل و بعد باید یکسان باشند.

۱۰ مثال عملی و قابل اجرا

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'CLEAR_PROCEDURE_CACHE';

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

SELECT TOP (20) cp.usecounts,cp.size_in_bytes,st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.dbid=DB_ID()
ORDER BY cp.usecounts DESC;

SELECT DB_NAME() AS DatabaseName, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT TOP (20) qsq.query_id, qsp.plan_id, rs.avg_duration, rs.avg_cpu_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = qsp.plan_id
ORDER BY rs.avg_duration DESC;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (1000) object_id, name, type_desc
FROM sys.objects
ORDER BY object_id DESC;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%SQL Statistics%'
  AND counter_name IN (N'Batch Requests/sec',N'SQL Compilations/sec',N'SQL Re-Compilations/sec');

SELECT TOP (20) qs.execution_count, qs.total_worker_time, qs.total_elapsed_time,
       SUBSTRING(st.text,1,300) AS sample_sql
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;

SELECT cntr_value AS CompilationCounter
FROM sys.dm_os_performance_counters
WHERE counter_name=N'SQL Compilations/sec'
  AND object_name LIKE N'%SQL Statistics%';

SELECT TOP (20) qsq.query_id,qsp.plan_id,qsp.is_forced_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsp.query_id=qsq.query_id
ORDER BY qsq.query_id DESC;

خطاهای رایج

• اجرای CLEAR PROCEDURE_CACHE در Production و ساعت پیک فقط برای اینکه یک Query کند موقتاً Plan دیگری بگیرد.
• اعمال چند تغییر Performance در یک Release و از دست دادن امکان تشخیص علت.
• مقایسه Warm Cache با Cold Cache و نتیجه‌گیری قطعی.
• نادیده گرفتن تفاوت نسخه، CU و Compatibility Level بین Test و Production.
• تمرکز فقط روی Duration و ندیدن CPU، Reads، Memory، Spill، Blocking یا Throughput.

Performance Considerations

اثر CLEAR_PROCEDURE_CACHE را با Metric همان Feature بسنجید. مفاهیم کلیدی این مقاله: Procedure Cache, Plan Cache, Recompile, Plan Handle, CPU. اگر معیار انتخابی با این زنجیره ارتباطی ندارد، آزمایش ممکن است نتیجه گمراه‌کننده بدهد.

پاک‌سازی گسترده در ساعت پیک می‌تواند Compile Storm ایجاد کند و فقط علامت را موقتاً پنهان کند. این ریسک دلیل طراحی آزمایش بهتر است، نه دلیل تصمیم عجولانه. Query Store، Actual Plan و DMVهای Runtime را کنار هم ببینید.

بهترین روش‌ها

1. مقدار فعلی، نسخه Engine و Compatibility Level را ثبت کنید.
2. Baseline Queryهای حیاتی را بگیرید.
3. تغییر را ابتدا در محیط مشابه Production یا بازه کم‌ریسک اجرا کنید.
4. فقط یک متغیر Performance را عوض کنید.
5. Rollback را پیش از Deployment بنویسید.
6. Plan و Runtime را در چند بازه بررسی کنید.
7. اگر نتیجه مبهم است تغییر را دائمی نکنید.

سناریوی عملی CLEAR_PROCEDURE_CACHE سه بخش دارد: Baseline، تغییر کنترل‌شده و تصمیم بر اساس Measure. حذف هر کدام کیفیت تصمیم را پایین می‌آورد.

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

CLEAR_PROCEDURE_CACHE دقیقاً چه مشکلی را هدف می‌گیرد؟

برای حذف Planهای Cache‌شده یک Database یا در نسخه‌های پشتیبانی‌شده یک Plan مشخص برای Compilation مجدد طراحی شده است. ارزش آن زمانی روشن می‌شود که قبل از تغییر Baseline داشته باشید و اثر را روی Workload واقعی بسنجید.

مقدار فعلی CLEAR_PROCEDURE_CACHE را چگونه ببینیم؟

از sys.database_scoped_configurations شروع کنید و خروجی را کنار نسخه Engine، Compatibility Level و وضعیت Query Store ثبت کنید.

آیا CLEAR_PROCEDURE_CACHE همیشه SQL Server را سریع‌تر می‌کند؟

خیر. پاک‌سازی گسترده در ساعت پیک می‌تواند Compile Storm ایجاد کند و فقط علامت را موقتاً پنهان کند. بنابراین CPU، Duration، Reads و Metric مرتبط با Feature باید قبل و بعد مقایسه شوند.

چه سناریوی تجاری برای CLEAR_PROCEDURE_CACHE مناسب است؟

نمونه روشن: آزمایش قبل و بعد از تغییر Compatibility یا رفع Plan بد در پنجره نگهداری کنترل‌شده. در پروژه واقعی Change Window، Rollback و معیار موفقیت را قبل از تغییر تعریف کنید.

CLEAR_PROCEDURE_CACHE چه تفاوتی با تنظیم سراسری دارد؟

Database Scoped Configuration دامنه اثر را محدودتر می‌کند و برای Instanceهای چند Workload کنترل دقیق‌تری می‌دهد؛ با این حال Hintهای Query و تنظیمات دیگر ممکن است رفتار نهایی را تغییر دهند.

برای پیاده‌سازی حرفه‌ای CLEAR_PROCEDURE_CACHE چه کاری لازم است؟

بررسی Query Store، Execution Plan، Baseline، تست بار و Rollback. آموزش و مشاوره زمانی مفید است که تصمیم بر اساس داده همان سامانه گرفته شود.

رایج‌ترین خطا درباره CLEAR_PROCEDURE_CACHE چیست؟

اجرای CLEAR PROCEDURE_CACHE در Production و ساعت پیک فقط برای اینکه یک Query کند موقتاً Plan دیگری بگیرد. این کار اغلب یک درمان موقت را به سیاست دائمی تبدیل می‌کند.

اثر Performance CLEAR_PROCEDURE_CACHE را چگونه بسنجیم؟

بازه‌های هم‌سطح را مقایسه کنید و CPU، Duration، Reads و Metric تخصصی Feature را بسنجید. Query Store برای Plan Regression بسیار مفید است.

Best Practice تغییر CLEAR_PROCEDURE_CACHE چیست؟

تغییر را کوچک، مستند و قابل برگشت نگه دارید؛ فقط یک متغیر Performance را در هر آزمایش عوض کنید و نتیجه را چند بار بسنجید.

CLEAR_PROCEDURE_CACHE با چه نسخه‌هایی سازگار است؟

CLEAR PROCEDURE_CACHE در Database Scoped Configuration Cache همان Database را پاک می‌کند؛ پاک‌سازی Plan مشخص در نسخه‌های جدیدتر با Plan Handle پشتیبانی می‌شود. مستندات همان نسخه و Compatibility Level محیط خود را ملاک نهایی قرار دهید.

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

1. چرا برای CLEAR_PROCEDURE_CACHE Baseline لازم است؟
2. اگر بعد از تغییر CLEAR_PROCEDURE_CACHE یک Query بهتر و چند Query بدتر شوند چه می‌کنید؟
3. Query Store در ارزیابی پاک‌سازی Procedure Cache در سطح پایگاه داده چه نقشی دارد؟
4. چطور مشکل Statistics را از اثر CLEAR_PROCEDURE_CACHE جدا می‌کنید؟
5. Rollback شما برای CLEAR_PROCEDURE_CACHE در Production چیست؟

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

• مقدار فعلی CLEAR_PROCEDURE_CACHE ثبت شده است.
• Baseline مشخص است.
• نسخه و Compatibility Level کنترل شده‌اند.
• معیار موفقیت و شکست نوشته شده‌اند.
• Rollback آماده است.
• Query Store و Planهای مهم بعد از تغییر بررسی می‌شوند.

جمع‌بندی

پاک‌سازی Procedure Cache در سطح پایگاه داده زمانی مفید است که با مسئله واقعی و قابل اندازه‌گیری استفاده شود. حذف Planهای Cache‌شده یک Database یا در نسخه‌های پشتیبانی‌شده یک Plan مشخص برای Compilation مجدد. به‌جای تغییر بر اساس توصیه عمومی، Workload خودتان را بسنجید و تغییر را قابل برگشت نگه دارید.

برای مقایسه این گزینه با سایر اعضای همین خانواده به مقاله مادر تنظیمات Performance در سطح Database برگردید.