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

نوشته شده توسط: تورج عزیزی

۲۰ دی ۱۳۹۴

زمان مطالعه: 8 دقیقه

مقدمه

امروز قصد دارم در مورد گزینه 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 ذخیره شود باید ۲ بار کامپایل شود که در مقابل بردی که در مصرف حافظه می کنیم قابل چشم پوشی است.