۲ نوامبر ۲۰۲۱، مایکروسافت جلسه ای برای معرفی بخشی از ویژگی های جدید SQL Server 2022 برگزار کرد که پس از سکوت طولانی در مورد نسخه جدید SQL Server بود. این معارفه مربوط به GA release میشد که قراره در سال ۲۰۲۲ منتشر بشه. یکی از قابلیت جدید معرفی شده مربوط میشه به Parameter Sensitive Plan Optimization که قصد دارم درمورد سابقه این مسئله و قابلیت جدیدی که مایکروسافت ارائه کرده صحبت کنم.
سابقه :
در مقاله متغیرهای لوکال و مشکلات Parameter Sniffing به بررسی performance در کوئری پلن های کش شده به ازاء پارامترهای ورودی مختلف پرداختم. به طور خلاصه :
زمانی که یک Stored Procedure فراخوانی میشه، SQL Server به دنبال Execution Plan مربوط به اون در حافظه Cache می گرده، اگه اونو پیدا کرد بلافاصله اجراش می کنه اما اگه Planی براش پیدا نکرد، باید تمام مراحل ساخت Execution Plan طی بشه.
دیتابیس SQL Server با استفاده از پارامترهایی که اولین بار به یک SP فرستاده میشه، یک Plan ایجاد کرده و اونو Cache میکنه. یعنی یکبار پلن رو می‌سازه تا چندین بار ازش استفاده کنه و در زمان صرفه جویی کنه.
گاهی پیش میاد که کش کردن پلن کوئری برای یه کوئری با پارامترهای مختلف اشکال ایجاد کنه به این معنی که با برخی پارامتر ها اون پلن کش شده سریع و بهینه باشه اما با پارامترهای متفاوت دیگه اون کوئری پلن مناسب نباشه،
به این پدیده، مشکل Parameter Sniffing گفته میشه. این مشکل مدت ها مدیران پایگاه داده SQL Server رو درگیر کرده و تا آخرین نسخه SQL server 2019 و حتی با اضافه شدن Query Store هم حل نشده، راه حل های مثل Recompile در هر بار اجرا کوئری، گزینه های موقتی بودن که میتونست این مسئله رو حل کنه. حالا در SQL Server 2022 مایکروسافت ادعا میکنه که تونسته این مشکل قدیمی رو به نحوی کنترل کنه.
مفاهیم پایه :
اول نیاز هست مفاهیمی رو که در SQL Server وجود داره و رفته رفته تکمیل تر شدن، باهم مرور کنیم :
مفهوم Intelligent query processing (IQP)
مفهوم Intelligent query processing به مجموعه ویژگی هایی گفته میشه که به صورت هوشمند سعی دارند هزینه اجرا کوئری در SQL Server رو کاهش بدن، برخی از این قابلیت ها از نسخه 2017 ایجاد شدن و برخی مربوط به نسخه 2019 هستند. در SQL Server 2022 شاهد نسل سوم IQP خواهیم بود.
مفهوم Query Store :
قابلیت Query Store از نسخه ۲۰۱۶ ایجاد شد که وظیفه ذخیره کردن سابقه کوئری های اجرا شده و کوئری پلن ها رو داره. این اطلاعات کمک میکنه تا مشکلات ناشی از تغییر کوئری پلن و performance به راحتی شناسایی بشن. حتی بعد از ریست شدن SQL بازهم این اطلاعات به خاطر ذخیره شدن تو دیسک در دسترس هستن.
مفهوم Cardinality estimator (CS) :
در SQL Server ابزار Query Optimizer بر مبنای هزینه عمل میکنه در واقع cost-based هست به این معنی که کوئری پلنی رو انتخاب میکنه که کمترین هزینه اجرا رو داره . Query Optimizer این هزینه رو با دو فاکتور حساب میکنه.
درواقع مجموع هزینه دیتا پردازش شده و هزینه عملیات های انجام شده رو در نظر میگیره. از اونجا که SQL Server هربار نمیتونه تعداد رکوردها رو بشمره، به کمک Cardinality estimator برآوردی از هزینه اجرا کوئری و تعداد رکورد ها بدست میاره.
مفهوم Parameter Sensitive Plan Optimization :
به قابلیت جدید ارائه شده در SQL Server 2022 که وظیفه رفع مسئله، کش شدن پلن ها بر اساس پارامترهای مختلف و در نتیجه جلوگیری از مشکلات Performance در دیتابیس رو داره Parameter Sensitive Plan Optimization گفته میشه.
بررسی یک مثال :
فرض کنید کاربر A میاد سراغ stored procedure ایجاد شده به اسم Myproc و اون رو با مقدار ورودی پارامتر 1 فراخوانی میکنه و کوئری خیلی سریع و بهینه با کوئری پلن زیر اجرا میشه . تو این مثال Index seek بهترین گزینه برای اجرا کوئری بوده.
بعد از مدتی کاربر B سراغ همون SP میاد با مقدار متغیر ورودی 10 فرخوانی میکنه. Engine تشخیص میده که Scan راه حل بهتری برای کوئری هست و کوئری پلن قبلی رو کنار میذاره . خب نکته اینه که در یک زمان فقط یه ورژن از کوئری پلن برای یک کوئری میتونه در حافظه وجود داشته باشه.
حالا اگر کاربر A دوباره بیاد سراغ کوئری و اون اجرا کنه، با توجه به اینکه کوئری پلن Scan تو حافظه کش شده پس کوئری با سرعت و هزینه نامناسبی اجرا میشه.
در SQL Server 2022 ادعا شده که این مشکل حل شده به این شکل که با استفاده از امکان Parameter Sensitive Plan میتونه برای کاربرهای A و B، دو کوئری پلن متفاوت ذخیره کنه . اینجوری هر دو نفر Performance مناسب و پایداری دارن، همچنین نیاز به تغییر کد هم نیست.
بریم دمو این تغییر رو تو SQL Server 2022 ببینیم :
یه SP داریم که با دو پارامتر 8 و 2 جداگانه و بدون نگه داشتن کش پلن قبلی اجرا میشه. همونطور که میبینیم پلن سمت چپ از ایندکس seek و پلن سمت راست از index Scan استفاده میکنه.
حالا بریم compatibility level رو به 16 تغییر بدیم، یعنی بریم روی نسخه SQL Server 2022.
این بار اگه هردو کوئری رو بدون پاک کردن پلن کش اجرا کنیم، خواهیم دید که دو کوئری پلن مختف کش میشه.
یعنی برای یک کوئری، دو پلن در کش ایجاد میشه.
همونطور که تو تصویر مشخصه، برای پلن کش شده نوشته Option PLAN PER VALUE که نشون میده کوئری برای کدوم پارامتر کش شده. همچنین در Query Store هم دقیقا Query_hash یکسانی برای هر دو پلن ذخیره شده.
مایکروسافت به همراه این قابلیت چندین قابلیت جدید هم معرفی کرده و احتمالا در آینده با انتشار نسخه عمومی SQL Server 2022 مقالات بیشتری در مورد این ویژگی ها منتشر خواهد شد، سعی میکنم به مرور به قابلیت های جدید بپردازم و در مورد اونها مطالبی رو باهم بررسی کنیم.
منبع:
