بهینه سازی عملکرد SQL Server مدیریت حافظه پنهان(Cache)

بهینه‌سازی عملکرد SQL Server: راهنمای جامع تعامل با حافظه پنهان داده و رویه (Cache)

در دنیای SQL Server، حافظه پنهان (Cache) نقش حیاتی در بهینه‌سازی عملکرد کوئری‌ها و عملیات پایگاه داده ایفا می‌کند. درک نحوه عملکرد این حافظه و توانایی تعامل با آن برای هر متخصص SQL Server ضروری است. حافظه پنهان SQL Server به دو بخش اصلی تقسیم می‌شود: حافظه پنهان داده (Data Cache یا Buffer Pool) و حافظه پنهان رویه (Procedure Cache یا Plan Cache). با استفاده از Dynamic Management Views (DMVs) و دستورات DBCC، می‌توانیم دید عمیقی نسبت به این حافظه‌ها پیدا کرده و در صورت نیاز، آن‌ها را مدیریت کنیم.

حافظه پنهان داده (Buffer Pool) محل ذخیره‌سازی صفحات داده‌ای است که اخیراً از دیسک خوانده شده‌اند. هدف اصلی این حافظه کاهش تعداد عملیات ورودی/خروجی دیسک و در نتیجه افزایش سرعت دسترسی به داده‌ها است. برای مشاهده جزئیات صفحات موجود در بافر پول و اینکه کدام پایگاه داده و شیء از آن استفاده می‌کند، می‌توانیم از DMV `sys.dm_os_buffer_descriptors` استفاده کنیم. این DMV اطلاعاتی مانند شناسه پایگاه داده، شناسه فایل، شناسه صفحه و نوع صفحه را ارائه می‌دهد:

SELECT
    database_id,
    COUNT(*) AS cached_pages_count
FROM
    sys.dm_os_buffer_descriptors
GROUP BY
    database_id
ORDER BY
    cached_pages_count DESC;

دستور بالا به شما نشان می‌دهد که هر پایگاه داده چه تعداد صفحه در حافظه پنهان دارد. اگر می‌خواهید با جزئیات بیشتری بدانید کدام اشیاء (جداول، ایندکس‌ها) از این حافظه استفاده می‌کنند، می‌توانید این DMV را با `sys.allocation_units` و `sys.partitions` و `sys.objects` جوین کنید. برای مثال، برای مشاهده صفحات موجود در کش برای یک پایگاه داده خاص:

SELECT
    OBJECT_NAME(p.object_id) AS ObjectName,
    p.index_id,
    COUNT(*) AS cached_pages_count
FROM
    sys.dm_os_buffer_descriptors AS bd
INNER JOIN
    sys.allocation_units AS au ON bd.allocation_unit_id = au.allocation_unit_id
INNER JOIN
    sys.partitions AS p ON au.container_id = p.hobt_id
WHERE
    bd.database_id = DB_ID('YourDatabaseName') -- نام پایگاه داده خود را اینجا وارد کنید
    AND p.object_id > 255 -- فیلتر کردن اشیاء سیستمی
GROUP BY
    OBJECT_NAME(p.object_id), p.index_id
ORDER BY
    cached_pages_count DESC;

گاهی اوقات، برای تست عملکرد یا شبیه‌سازی اولین اجرای یک کوئری، نیاز به پاک کردن حافظه پنهان داده دارید. دستور `DBCC DROPCLEANBUFFERS` تمام بافرهای داده‌ای “پاک” (Clean) را از حافظه بافر پول حذف می‌کند. توجه داشته باشید که این دستور فقط بافرهای پاک (که تغییراتی روی آن‌ها اعمال نشده و نیاز به نوشتن روی دیسک ندارند) را پاک می‌کند و برای استفاده در محیط تولید (Production) باید با احتیاط فراوان و درک کامل تاثیرات آن استفاده شود. استفاده از این دستور باعث بارگذاری مجدد صفحات از دیسک می‌شود که می‌تواند زمان پاسخگویی را برای اولین اجرای کوئری‌ها افزایش دهد.

DBCC DROPCLEANBUFFERS;

بخش دوم، حافظه پنهان رویه (Procedure Cache یا Plan Cache)، محل ذخیره‌سازی برنامه‌های اجرایی (Execution Plans) کوئری‌ها، رویه‌ها و توابع است. وقتی یک کوئری برای اولین بار اجرا می‌شود، SQL Server یک برنامه اجرایی برای آن ایجاد کرده و آن را در این حافظه ذخیره می‌کند. در دفعات بعدی اجرای همان کوئری، اگر برنامه اجرایی در حافظه پنهان موجود باشد، SQL Server از آن استفاده می‌کند و نیازی به کامپایل مجدد کوئری نخواهد بود که این امر منجر به بهبود چشمگیر عملکرد می‌شود.

برای مشاهده برنامه‌های اجرایی ذخیره شده در حافظه پنهان رویه، از DMV `sys.dm_exec_cached_plans` استفاده می‌کنیم. این DMV اطلاعاتی مانند نوع طرح، استفاده مجدد، و مصرف حافظه را فراهم می‌کند:

SELECT
    cp.objtype AS ObjectType,
    cp.cacheobjtype AS CacheObjectType,
    cp.refcounts AS ReferenceCount,
    cp.usecounts AS UseCount,
    cp.size_in_bytes AS SizeInBytes,
    st.text AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE
    cp.cacheobjtype = 'Compiled Plan' -- فیلتر برای نمایش فقط برنامه های کامپایل شده
ORDER BY
    cp.usecounts DESC;

کوئری بالا نه تنها اطلاعاتی درباره طرح‌های ذخیره شده ارائه می‌دهد، بلکه متن کوئری اصلی و خود برنامه اجرایی به فرمت XML را نیز برمی‌گرداند که می‌توانید آن را برای تحلیل بیشتر باز کنید. فیلتر کردن بر اساس `objtype` می‌تواند مفید باشد، مثلاً برای دیدن برنامه‌های اجرایی برای Ad-hoc queries (‘Adhoc’), Stored Procedures (‘Proc’), یا Prepared statements (‘Prepared’).

اگر نیاز به پاک کردن حافظه پنهان رویه دارید – مثلاً پس از تغییرات گسترده در ایندکس‌ها یا برای تست برنامه‌های اجرایی جدید – می‌توانید از دستور `DBCC FREEPROCCACHE` استفاده کنید. این دستور تمام یا بخشی از برنامه‌های اجرایی ذخیره شده را از حافظه پنهان حذف می‌کند. پاک کردن کل حافظه پنهان رویه می‌تواند تأثیر قابل توجهی بر عملکرد کلی سرور داشته باشد، زیرا SQL Server مجبور به کامپایل مجدد تمام کوئری‌ها خواهد شد. می‌توانید این دستور را با یک `plan_handle` یا `resource_pool_name` خاص برای پاک کردن انتخابی نیز استفاده کنید.

DBCC FREEPROCCACHE;

برای پاک کردن حافظه پنهان برای یک برنامه اجرایی خاص، ابتدا `plan_handle` آن را از `sys.dm_exec_cached_plans` به دست آورید و سپس آن را به دستور `DBCC FREEPROCCACHE` بدهید:

DECLARE @plan_handle VARBINARY(64);
-- فرض کنید plan_handle مورد نظر را از DMV ها به دست آورده اید
SELECT @plan_handle = plan_handle FROM sys.dm_exec_cached_plans WHERE ...; -- شرایط خاص برای یافتن طرح مورد نظر
DBCC FREEPROCCACHE (@plan_handle);

همچنین، برای مشاهده یک خلاصه از وضعیت حافظه SQL Server، `DBCC MEMORYSTATUS` یک ابزار قدرتمند است. این دستور جزئیات مختلفی از نحوه استفاده SQL Server از حافظه، از جمله جزئیات بافر پول و حافظه کش، را ارائه می‌دهد. این اطلاعات برای تشخیص مشکلات مربوط به حافظه و بهینه‌سازی کلی سرور بسیار مفید است. خروجی این دستور شامل چندین جدول با اطلاعات دقیق است.

DBCC MEMORYSTATUS;

با درک عمیق از نحوه کار حافظه پنهان داده و رویه و با استفاده از DMVs و دستورات `DBCC`، می‌توانید به طور موثرتری عملکرد SQL Server خود را نظارت، تجزیه و تحلیل و بهینه‌سازی کنید. این ابزارها به شما کمک می‌کنند تا bottlenecks را شناسایی کرده و تجربه‌ای روان‌تر برای کاربران و برنامه‌های خود فراهم آورید.