سلام دوستان عزیز نیک آموزی،
امروز قصد دارم در مورد گزینه Optimize for Adhoc Workload و چگونگی تاثیر آن بر Plan Cache در SQL Server صحبت کنم. قبل از اینکه بخواهم وارد جزئیات بشوم، قصد دارم به شما نشان دهمSQL Server
چطور پلن های اجرایی cache شده را دوباره استفاده می کند.
Execution Plan Reuse
هر باری که شما یک کوئری به سمت SQL Server ارسال می کنید، SQL Server فضای Plan Cache را چک می کند تا ببیند پلن اجرای قابل استفاده مجدد برای این کوئری بخصوص وجود دارد یا خیر. SQL Server یک مقدار hash برای دستور SQL ارسال شده محاسبه می کند و از این مقدار hash برای جستجو در Plan Cache استفاده می کند (در واقعیت این امر پیچیده تر است چون سایر گزینه ها مانند SET هم میتواند بر Execution plan reuse تاثیر بگذارد).
اگر یک پلن cache شده پیدا شود reuse می شود. در غیر اینصورت یک پلن اجرای جدید توسط Query Optimizer کامپایل می شود و در Plan Cache برای reuse قرار داده می شود. و حالا این برنامه C# نوشته شده بد را تصور کنید:
[csharp] for (int i = 1; i <= 10000; i++) |
این برنامه یک دستور SELECT در یک حلقه به تعداد 10000 بار فراخوانی می کند – به همراه یک مقدار پارامتر Hard code شده. با وجود مقدار پارامتر Hard code شده مقدار Hash ای که SQL Server هر بار محاسبه می کند همیشه متفاوت خواهد بود و بنابراین SQL Server نخواهد توانست از یک plan موجود در cache دوباره استفاده کند. و به عنوان یک عارضه جانبی شما 10000 پلن اجرای متفاوت (اما تقریباً معادل) در Plan Cache خواهید داشت.


هر پلن اجرا هم مقداری حافظه مصرف می کند (در مورد کوئری بالا 16 KB) بنابراین شما حدود 160 MBاز Plan Cache را “هدر” می دهید! حالا تصور کنید که نمی توانید نحوه پیاده سازی برنامه تان را تغییر دهید، اما هنوز این امکان وجود دارد که مصرف حافظه Plan Cache در SQL Server را بهینه کنید. این همان جایی است که Optimize for Ahoc Workload وارد بازی می شود.
Optimize for Ahoc Workload
برای فعال کردن این گزینه از طریق پنجره Server Properties عمل کنید:


این گزینه در SQL Server 2008منتشر شد و روشی که SQL Server پلن های اجرا را cache و reuse می کند را تغییر داد. به محض اینکه شما این گزینه را فعال کنید، SQL Serverدیگر پلن اجرای شما را به طور کامل cache نمی کند. SQL Server فقط یک به اصطلاح Compiled Plan Stub ذخیره می کند. این مقدار stub فقط یک مقدار hashبه ازای دستور SQL شما است و فقط 104 بایت در SQL Server 2014 فضا میگیرد.
و در مورد کوئری های ما فقط به 1 MB در Plan Cache برای تمام 10000 دستور SQL ارسال شده نیاز خواهیم داشت. که این یک اختلاف فاحش با مقدار 160 MB قبلی است! با این مقدار stub ، SQL Server حالا می تواند به خاطر بسپارد که یک دستور SQL خاص قبلاً یکبار اجرا شده است.


حالا اگر دستورات SQL را دوباره اجرا کنید، SQL Server مقدار Stub را در Plan Cache پیدا می کند و پلن اجرای شما را دوباره کامپایل می کند و پلن اجرای حاصل را در Plan Cache ذخیره می کند. بنابراین SQL Server یک پلن اجرای کامل را فقط وقتی اجرا می کند که آن دستور SQL حداقل 2 بار اجرا شده باشد. دستورات SQL ای که فقط یک مرتبه اجرا شده اند (دستورات Adhoc) فقط 104 بایت در Plan Cache نیاز دارند.
بنابراین شما خواهید توانست با این گزینه مصرف حافظه را کاهش دهید- بدون اینکه مجبور باشید تغییری در Application بدهید!
خلاصه
در این پست من یک راه خیلی ساده برای تعامل با Application هایی ارائه کرده ام که بد نوشته شده اند و می توانند باعث شوند Plan Cache با پلن های اجرای تقریباً معادل که فرصت استفاده مجدد ندارند، پر شود (به این پدید Cache bloat هم گفته می شود). گزینهOptimize for Ahoc Workload هم یک گزینه است که من توصیه می کنم همیشه فعال باشد. این گزینه منجر به استفاده موثرتر از فضای Plan Cacheمی شود.
یک عارضه جانبی برای این Option هم این است که شما یک سربار CPU کوچک به سرور تحمیل می کنید چون هر پلن اجرا قبل از اینکه بتواند در Plan Cache ذخیره شود باید 2 بار کامپایل شود که در مقابل بردی که در مصرف حافظه می کنیم قابل چشم پوشی است.
منبع: آموزش SQL Server نیک آموز
هیچوقت به داشتن سرکارگر عادت نکن
ژانویه 2016، تورج عزیزی
9 دیدگاه
میلاد فیروزی
بسیار چالب بود
مهدی ربانی ذبیحی
بسیار عالی بود ممنون
Hamid J. Fard
مسعود طاهری
سلام بر حمید عزیز و سایر دوستان
Hamid J. Fard
مسعود طاهری
بلی حمید جان از سیستم ها تا دلتان بخواهد در ایران وجود دارد و هنوز که هنوز است کار می کند و متاسفانه برای سازمان ها قابل توجیه نیست که هزینه ای برای باز نویسی مجدد آن در نظر بگیرند.
مسعود طاهری
در خصوص مصرف CPU هم باید اشاره کنم. همانطور هم که تورج عزیز در مقاله اشاره کرده مصرف CPU به ازای فعال شدن این پارامتر به اون شدتی نیست که دوستان فکر می کنند (مثلا 50 درصد و…) مهمترین حسن این قابلیت مصرف کم حافظه نسبت به زمانی است که کدهای Ad-hoc به شدت در App نوشته شده است. در این حالت می توانیم روی این ویژگی حساب باز کنید.
Hamid J. Fard
فرشید علی اکبری
سلام