"Optimize for Adhoc Workload" چطور بر Plan Cache تاثیر می‌گذارد

“Optimize for Adhoc Workload” چطور بر Plan Cache تاثیر می‌گذارد

سلام دوستان عزیز نیک آموزی،

امروز قصد دارم در مورد گزینه 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# نوشته شده بد را تصور کنید:

 for (int i = 1; i <= 10000; i++)
                {
                    cmd = newSqlCommand(
"SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID = " + i.ToString(), cnn);
SqlDataReader reader = cmd.ExecuteReader();
                    reader.Close();
                }

این برنامه یک دستور SELECT در یک حلقه به تعداد ۱۰۰۰۰ بار فراخوانی می کند – به همراه یک مقدار پارامتر Hard code شده. با وجود مقدار پارامتر Hard code شده مقدار Hash ای که SQL Server هر بار محاسبه می کند همیشه متفاوت خواهد بود و بنابراین SQL Server نخواهد توانست از یک plan موجود در cache دوباره استفاده کند. و به عنوان یک عارضه جانبی شما ۱۰۰۰۰ پلن اجرای متفاوت (اما تقریباً معادل) در Plan Cache خواهید داشت.

هر پلن اجرا هم مقداری حافظه مصرف می کند (در مورد کوئری بالا ۱۶ KB) بنابراین شما حدود ۱۶۰ 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 شما است و فقط ۱۰۴ بایت در SQL Server 2014 فضا میگیرد.

و در مورد کوئری های ما فقط به ۱ MB در Plan Cache برای تمام ۱۰۰۰۰ دستور SQL ارسال شده نیاز خواهیم داشت. که این یک اختلاف فاحش با مقدار ۱۶۰ MB قبلی است! با این مقدار stub ، SQL Server حالا می تواند به خاطر بسپارد که یک دستور SQL خاص قبلاً یکبار اجرا شده است.

حالا اگر دستورات SQL را دوباره اجرا کنید، SQL Server مقدار Stub را در Plan Cache پیدا می کند و پلن اجرای شما را دوباره کامپایل می کند و پلن اجرای حاصل را در Plan Cache ذخیره می کند. بنابراین SQL Server یک پلن اجرای کامل را فقط وقتی اجرا می کند که آن دستور SQL حداقل ۲ بار اجرا شده باشد. دستورات SQL ای که فقط یک مرتبه اجرا شده اند (دستورات Adhoc) فقط ۱۰۴ بایت در Plan Cache نیاز دارند.

بنابراین شما خواهید توانست با این گزینه مصرف حافظه را کاهش دهید- بدون اینکه مجبور باشید تغییری در Application بدهید!

خلاصه

در این پست من یک راه خیلی ساده برای تعامل با Application هایی ارائه کرده ام که بد نوشته شده اند و می توانند باعث شوند Plan Cache با پلن های اجرای تقریباً معادل که فرصت استفاده مجدد ندارند، پر شود (به این پدید Cache bloat هم گفته می شود). گزینهOptimize for Ahoc Workload هم یک گزینه است که من توصیه می کنم همیشه فعال باشد. این گزینه منجر به استفاده موثرتر از فضای Plan Cacheمی شود.

یک عارضه جانبی برای این Option هم این است که شما یک سربار CPU کوچک به سرور تحمیل می کنید چون هر پلن اجرا قبل از اینکه بتواند در Plan Cache ذخیره شود باید ۲ بار کامپایل شود که در مقابل بردی که در مصرف حافظه می کنیم قابل چشم پوشی است.

منبع: آموزش SQL Server نیک آموز

هیچوقت به داشتن سرکارگر عادت نکن

ژانویه ۲۰۱۶، تورج عزیزی

درباره نویسنده

تورج عزیزی

مشاوره، تحقیقات و انجام پروژه در زمینه SQL Server Performance Tuning

9 Comments

میلاد فیروزی
۱۳۹۴-۱۰-۲۰ at ۳:۵۱ ب.ظ
بسیار چالب بود

ممنون

پاسخ دادن
مهدی ربانی ذبیحی
۱۳۹۴-۱۰-۲۰ at ۶:۵۵ ب.ظ
بسیار عالی بود ممنون

پاسخ دادن
Hamid J. Fard
۱۳۹۴-۱۰-۲۱ at ۷:۱۷ ق.ظ
گزینه Optimize for ad-hoc workloads برای محیط های Data Warehouse است نه برای محیط OLTP. بهتر است برای محیط OLTP برنامه و Query ها یک بار دیگر باز نویسی شوند. البته شما این تعداد Plan Stub دارید به دلیل وجود i.ToString() است.

از SQLCommand.Prepare() استفاده کنید تا Query مورد نظر را به صورت پارامترایز تبدیل و به SQL Server ارسال کند.

پاسخ دادن
1. مسعود طاهری
  ۱۳۹۴-۱۰-۲۱ at ۱۰:۰۰ ق.ظ
  سلام بر حمید عزیز و سایر دوستان
  
  استفاده از این گزینه در سیستم های OLTPی که سورس آن در دسترس نیست و کد نویسی آن برای چندین سال قبل است بسیار مفید است.
  
  برای مثال App را در نظر بگیرید که با VB6 و یا Delphi نوشته شده است تمامی کوئری ها به صورت ad-hoc داخل سورس نوشته شده متاسفانه این نوع کوئری ها ایراداتی نظیر SQL Injection ، کارایی و… دارند. برای رفع مشکلاتی که تورج عزیز اشاره کرد می توان روی این گزینه هم در سیستم های OLTP حساب باز کرد.
  
  معمولا در ایران و شاید خیلی از کشورهای دنیا شرکت های بزرگ تمایلی برای بازنویسی برنامه های جدید خود ندارند دلایل
  
  1- سیستم دارد کار می کند و نیازی به تغییر اون نیست
  
  ۲- هزینه تولید سیستم جدید به مراتب بالا است (در مواردی سیستم اگر بزرگ باد چند ده میلیارد تومان)
  
  ۳- اگر App ایردات امنیتی دارد دنبال راه حل جایگزین هستند که این موراد را پوشش دهد. به شرط اینکه هزینه آن از تولید سیستم جدید به مراتب پایین تر باشد (مانند انجام تنظیمات امنیتی در SQL Server ، استفاده از WAF، استفاده از DBF)
  
  پاسخ دادن
  1. Hamid J. Fard
    ۱۳۹۴-۱۰-۲۱ at ۱:۰۰ ب.ظ
    اگر سیستم بزرگی که گفتید از این روش استفاده کرده باشه یعنی اینکه طراحی این سیستم از پایه و اساس اشتباه بوده. البته در محیط OLTP قابلیت Forced Parameterization هم فعال باشد تا تعداد Plan Stub ها به حداقل برسد. در محیط OLTP سرعت پردازش مهمترین اصل است ولی با فعال سازی این گزینه استفاده از پردازنده زیادتر شده و اینکه زمان تراکنش به دلیل عملیات اضافی بالا می رود.
    
    این گزینه از طرف مایکروسافت برای محیط های DW ارائه شده است.
    
    به هرحال میل خودتون در مورد استفاده از این قابلیت.
  2. مسعود طاهری
    ۱۳۹۴-۱۰-۲۱ at ۳:۰۱ ب.ظ
    بلی حمید جان از سیستم ها تا دلتان بخواهد در ایران وجود دارد و هنوز که هنوز است کار می کند و متاسفانه برای سازمان ها قابل توجیه نیست که هزینه ای برای باز نویسی مجدد آن در نظر بگیرند.
  3. مسعود طاهری
    ۱۳۹۴-۱۰-۲۱ at ۳:۵۱ ب.ظ
    در خصوص مصرف CPU هم باید اشاره کنم. همانطور هم که تورج عزیز در مقاله اشاره کرده مصرف CPU به ازای فعال شدن این پارامتر به اون شدتی نیست که دوستان فکر می کنند (مثلا ۵۰ درصد و…) مهمترین حسن این قابلیت مصرف کم حافظه نسبت به زمانی است که کدهای Ad-hoc به شدت در App نوشته شده است. در این حالت می توانیم روی این ویژگی حساب باز کنید.
  4. Hamid J. Fard
    ۱۳۹۴-۱۰-۲۱ at ۵:۱۸ ب.ظ
    مطلع هستم مصرف پردازنده کم است ولی برای تعداد بسیار بالای کوری ها. در بهینه سازی یک مسئله است به این صورت که ممکن از یک دستور با یک بار اجراء سرعت سیستم را پایین آورد ولی یک دستور با اجراء بسیار زیاد سرعت سیستم را پایین آورد.
فرشید علی اکبری
۱۳۹۴-۱۰-۲۱ at ۱۲:۲۹ ب.ظ
سلام

ممنون از مقاله خوب تون و تشکر از آقای طاهری بابت ارائه مطالب تکمیلی.

پاسخ دادن