افزایش عملکرد SQL Server: پین کردن داده‌ها در کش با PINTABLE
اگر جدولی دارید که همیشه به صورت مکرر استفاده می‌شود و می‌خواهید مطمئن شوید که همیشه در کش داده (data cache) قرار دارد، می‌توانید از گزینه DBCC PINTABLE برای اطلاع دادن به SQL Server استفاده کنید تا تلاش کند جدول را در کش داده نگه دارد. مزیت اصلی این کار این است که داده‌ها همیشه در حافظه RAM در دسترس هستند و نیاز به خواندن از دیسک را از بین می‌برد، که می‌تواند به طور قابل توجهی عملکرد را برای درخواست‌های آن جدول بهبود بخشد. با استفاده از این قابلیت مدیریت حافظه SQL Server، زمان پاسخگویی برای دسترسی به داده‌های کلیدی کاهش می‌یابد.
در ادامه به شما نشان خواهیم داد که چگونه این قابلیت را آزمایش کنید. برای این مثال از پایگاه داده pubs و جدول authors استفاده خواهیم کرد. اگر هنوز پایگاه داده pubs را نصب نکرده‌اید، می‌توانید آن را از وبسایت مایکروسافت دریافت کنید. اولین کاری که باید انجام دهیم این است که شناسه شیء (Object ID) جدول authors را در پایگاه داده pubs پیدا کنیم. برای این کار از دستور OBJECT_ID استفاده می‌کنیم:
USE pubs
SELECT OBJECT_ID('authors')این دستور شناسه شیء جدول authors را برمی‌گرداند. در محیط آزمایش من، این عدد 2073058421 بود. اکنون که شناسه شیء را داریم، می‌توانیم از دستور PINTABLE برای پین کردن این جدول استفاده کنیم:
DBCC PINTABLE (0, 2073058421)در دستور بالا، 0 به معنای پایگاه داده جاری (current database) است و 2073058421 شناسه شیء جدول authors است. اگر می‌خواهید جدول را از یک پایگاه داده دیگر پین کنید، باید شناسه پایگاه داده (database ID) را به جای 0 وارد کنید. حال که جدول پین شده است، بیایید کش داده را پاک کنیم و ببینیم آیا داده‌ها پس از پاکسازی کش همچنان در حافظه باقی می‌مانند یا خیر. این کار به ما کمک می‌کند تا تأثیر PINTABLE بر دسترسی پایدار به داده‌ها را مشاهده کنیم.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREEORPHANEDTEMPDB
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEOSCACHEاکنون که کش‌ها پاک شده‌اند، یک دستور SELECT بر روی جدول authors اجرا می‌کنیم. داده‌های جدول پین شده بلافاصله بدون نیاز به خواندن از دیسک در دسترس قرار می‌گیرند، در حالی که برای یک جدول غیر پین شده، باید از دیسک خوانده شود. این نشان‌دهنده بهینه‌سازی SQL Server با استفاده از PINTABLE برای بهبود عملکرد است.
USE pubs
SELECT * FROM authorsبرای مشاهده اینکه جدول واقعاً پین شده است، می‌توانید DBCC BUFFER (pubs, 1, 0, 1) را اجرا کنید (جایی که 1 شناسه فایل و 0 شماره صفحه است) و باید نتایجی شبیه به این ببینید: Pinned: 1. این تأیید می‌کند که داده‌ها به درستی در کش حافظه RAM قرار گرفته‌اند.
توجه داشته باشید که با پین کردن جداول، این جداول فضای RAM مصرف می‌کنند که به معنی در دسترس نبودن آن فضای حافظه برای سایر پردازش‌ها یا جداول است. اگر تعداد زیادی جدول کوچک پین کنید، ممکن است در نهایت حافظه زیادی را مصرف کنید که منجر به کاهش عملکرد کلی SQL Server شود. بنابراین، مدیریت حافظه بهینه برای حفظ عملکرد سیستم حیاتی است. این ویژگی بیشتر برای جداول کوچک بسیار پرکاربرد مناسب است تا کش داده SQL Server را به طور موثر بهینه کند.
برای غیر پین کردن یک جدول و حذف آن از کش حافظه، از دستور DBCC UNPINTABLE استفاده کنید:
DBCC UNPINTABLE (0, 2073058421)این قابلیت PINTABLE مشابه قابلیت sp_procoption است که به شما امکان می‌دهد رویه‌های ذخیره‌شده (stored procedures) را پین کنید. استفاده از sp_procoption برای رویه‌های ذخیره‌شده، تضمین می‌کند که طرح اجرایی آن‌ها در کش رویه (procedure cache) باقی بماند. اما یک تفاوت مهم بین PINTABLE و sp_procoption وجود دارد.
با sp_procoption، اگر یک رویه ذخیره‌شده را برای پین شدن تنظیم کنید، آن رویه حتی پس از راه‌اندازی مجدد (reboot) سرور SQL همچنان پین شده باقی می‌ماند. این در حالی است که با DBCC PINTABLE، جداول فقط تا زمانی پین شده باقی می‌مانند که سرور SQL مجدداً راه‌اندازی نشده باشد. پس از هر بار راه‌اندازی مجدد سرور، باید دستور DBCC PINTABLE را دوباره اجرا کنید تا جداول مورد نظر خود را پین کنید و از بهینه‌سازی SQL Server برای دسترسی به داده‌ها اطمینان حاصل کنید. این به معنای نیاز به یک برنامه ریزی دقیق برای حفظ پیکربندی PINTABLE پس از هر بار ری‌استارت است.
