خانه SQL Server “Optimize for Adhoc Workload” چطور بر Plan Cache تاثیر می‌گذارد SQL Server نوشته شده توسط: تورج عزیزی ۲۰ دی ۱۳۹۴ زمان مطالعه: 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 ذخیره شود باید ۲ بار کامپایل شود که در مقابل بردی که در مصرف حافظه می کنیم قابل چشم پوشی است. چه رتبه ای می‌دهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش برچسب ها # Ad-Hoc Query# Optimize for Ahoc Workload# Plan Cache# SQL Server# آموزش SQL Server معرفی نویسنده مقالات 17 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی پروفایل نویسنده معرفی محصول مسعود طاهری دوره آموزشی نگهداری از بانک‌های اطلاعاتی در SQL Server 890.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ فرشید علی اکبری ۲۱ / ۱۰ / ۹۴ - ۱۲:۵۶ سلام ممنون از مقاله خوب تون و تشکر از آقای طاهری بابت ارائه مطالب تکمیلی. پاسخ به دیدگاه فرشید علی اکبری ۲۱ / ۱۰ / ۹۴ - ۱۲:۵۶ سلام ممنون از مقاله خوب تون و تشکر از آقای طاهری بابت ارائه مطالب تکمیلی. پاسخ به دیدگاه Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۷:۰۲ گزینه Optimize for ad-hoc workloads برای محیط های Data Warehouse است نه برای محیط OLTP. بهتر است برای محیط OLTP برنامه و Query ها یک بار دیگر باز نویسی شوند. البته شما این تعداد Plan Stub دارید به دلیل وجود i.ToString() است. از SQLCommand.Prepare() استفاده کنید تا Query مورد نظر را به صورت پارامترایز تبدیل و به SQL Server ارسال کند. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۱۰:۵۹ سلام بر حمید عزیز و سایر دوستان استفاده از این گزینه در سیستم های OLTPی که سورس آن در دسترس نیست و کد نویسی آن برای چندین سال قبل است بسیار مفید است. برای مثال App را در نظر بگیرید که با VB6 و یا Delphi نوشته شده است تمامی کوئری ها به صورت ad-hoc داخل سورس نوشته شده متاسفانه این نوع کوئری ها ایراداتی نظیر SQL Injection ، کارایی و… دارند. برای رفع مشکلاتی که تورج عزیز اشاره کرد می توان روی این گزینه هم در سیستم های OLTP حساب باز کرد. معمولا در ایران و شاید خیلی از کشورهای دنیا شرکت های بزرگ تمایلی برای بازنویسی برنامه های جدید خود ندارند دلایل ۱- سیستم دارد کار می کند و نیازی به تغییر اون نیست ۲- هزینه تولید سیستم جدید به مراتب بالا است (در مواردی سیستم اگر بزرگ باد چند ده میلیارد تومان) ۳- اگر App ایردات امنیتی دارد دنبال راه حل جایگزین هستند که این موراد را پوشش دهد. به شرط اینکه هزینه آن از تولید سیستم جدید به مراتب پایین تر باشد (مانند انجام تنظیمات امنیتی در SQL Server ، استفاده از WAF، استفاده از DBF) پاسخ به دیدگاه Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۱:۱۲ اگر سیستم بزرگی که گفتید از این روش استفاده کرده باشه یعنی اینکه طراحی این سیستم از پایه و اساس اشتباه بوده. البته در محیط OLTP قابلیت Forced Parameterization هم فعال باشد تا تعداد Plan Stub ها به حداقل برسد. در محیط OLTP سرعت پردازش مهمترین اصل است ولی با فعال سازی این گزینه استفاده از پردازنده زیادتر شده و اینکه زمان تراکنش به دلیل عملیات اضافی بالا می رود. این گزینه از طرف مایکروسافت برای محیط های DW ارائه شده است. به هرحال میل خودتون در مورد استفاده از این قابلیت. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۰۳:۱۱ بلی حمید جان از سیستم ها تا دلتان بخواهد در ایران وجود دارد و هنوز که هنوز است کار می کند و متاسفانه برای سازمان ها قابل توجیه نیست که هزینه ای برای باز نویسی مجدد آن در نظر بگیرند. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۰۳:۵۰ در خصوص مصرف CPU هم باید اشاره کنم. همانطور هم که تورج عزیز در مقاله اشاره کرده مصرف CPU به ازای فعال شدن این پارامتر به اون شدتی نیست که دوستان فکر می کنند (مثلا ۵۰ درصد و…) مهمترین حسن این قابلیت مصرف کم حافظه نسبت به زمانی است که کدهای Ad-hoc به شدت در App نوشته شده است. در این حالت می توانیم روی این ویژگی حساب باز کنید. Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۵:۱۵ مطلع هستم مصرف پردازنده کم است ولی برای تعداد بسیار بالای کوری ها. در بهینه سازی یک مسئله است به این صورت که ممکن از یک دستور با یک بار اجراء سرعت سیستم را پایین آورد ولی یک دستور با اجراء بسیار زیاد سرعت سیستم را پایین آورد. Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۷:۰۲ گزینه Optimize for ad-hoc workloads برای محیط های Data Warehouse است نه برای محیط OLTP. بهتر است برای محیط OLTP برنامه و Query ها یک بار دیگر باز نویسی شوند. البته شما این تعداد Plan Stub دارید به دلیل وجود i.ToString() است. از SQLCommand.Prepare() استفاده کنید تا Query مورد نظر را به صورت پارامترایز تبدیل و به SQL Server ارسال کند. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۱۰:۵۹ سلام بر حمید عزیز و سایر دوستان استفاده از این گزینه در سیستم های OLTPی که سورس آن در دسترس نیست و کد نویسی آن برای چندین سال قبل است بسیار مفید است. برای مثال App را در نظر بگیرید که با VB6 و یا Delphi نوشته شده است تمامی کوئری ها به صورت ad-hoc داخل سورس نوشته شده متاسفانه این نوع کوئری ها ایراداتی نظیر SQL Injection ، کارایی و… دارند. برای رفع مشکلاتی که تورج عزیز اشاره کرد می توان روی این گزینه هم در سیستم های OLTP حساب باز کرد. معمولا در ایران و شاید خیلی از کشورهای دنیا شرکت های بزرگ تمایلی برای بازنویسی برنامه های جدید خود ندارند دلایل ۱- سیستم دارد کار می کند و نیازی به تغییر اون نیست ۲- هزینه تولید سیستم جدید به مراتب بالا است (در مواردی سیستم اگر بزرگ باد چند ده میلیارد تومان) ۳- اگر App ایردات امنیتی دارد دنبال راه حل جایگزین هستند که این موراد را پوشش دهد. به شرط اینکه هزینه آن از تولید سیستم جدید به مراتب پایین تر باشد (مانند انجام تنظیمات امنیتی در SQL Server ، استفاده از WAF، استفاده از DBF) پاسخ به دیدگاه Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۱:۱۲ اگر سیستم بزرگی که گفتید از این روش استفاده کرده باشه یعنی اینکه طراحی این سیستم از پایه و اساس اشتباه بوده. البته در محیط OLTP قابلیت Forced Parameterization هم فعال باشد تا تعداد Plan Stub ها به حداقل برسد. در محیط OLTP سرعت پردازش مهمترین اصل است ولی با فعال سازی این گزینه استفاده از پردازنده زیادتر شده و اینکه زمان تراکنش به دلیل عملیات اضافی بالا می رود. این گزینه از طرف مایکروسافت برای محیط های DW ارائه شده است. به هرحال میل خودتون در مورد استفاده از این قابلیت. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۰۳:۱۱ بلی حمید جان از سیستم ها تا دلتان بخواهد در ایران وجود دارد و هنوز که هنوز است کار می کند و متاسفانه برای سازمان ها قابل توجیه نیست که هزینه ای برای باز نویسی مجدد آن در نظر بگیرند. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۰۳:۵۰ در خصوص مصرف CPU هم باید اشاره کنم. همانطور هم که تورج عزیز در مقاله اشاره کرده مصرف CPU به ازای فعال شدن این پارامتر به اون شدتی نیست که دوستان فکر می کنند (مثلا ۵۰ درصد و…) مهمترین حسن این قابلیت مصرف کم حافظه نسبت به زمانی است که کدهای Ad-hoc به شدت در App نوشته شده است. در این حالت می توانیم روی این ویژگی حساب باز کنید. Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۵:۱۵ مطلع هستم مصرف پردازنده کم است ولی برای تعداد بسیار بالای کوری ها. در بهینه سازی یک مسئله است به این صورت که ممکن از یک دستور با یک بار اجراء سرعت سیستم را پایین آورد ولی یک دستور با اجراء بسیار زیاد سرعت سیستم را پایین آورد. مهدی ربانی ذبیحی ۲۰ / ۱۰ / ۹۴ - ۰۶:۵۶ بسیار عالی بود ممنون پاسخ به دیدگاه مهدی ربانی ذبیحی ۲۰ / ۱۰ / ۹۴ - ۰۶:۵۶ بسیار عالی بود ممنون پاسخ به دیدگاه میلاد فیروزی ۲۰ / ۱۰ / ۹۴ - ۰۳:۱۳ بسیار چالب بود ممنون پاسخ به دیدگاه میلاد فیروزی ۲۰ / ۱۰ / ۹۴ - ۰۳:۱۳ بسیار چالب بود ممنون پاسخ به دیدگاه