در این مقاله میخوانیم :
این مقاله به نسخه SQL Server 2017 و بالاتر مربوط میشه.
بهینه سازی خودکار قابلیتی در دیتابیس هست که مشکلات Performance کوئری ها رو مورد بررسی قرار داده و راه حل هایی پیشنهاد میده و همچنین میتونه به صورت خودکار این مشکلات را برطرف کنه.
قابلیت Automatic tuning در SQL Server 2017 جهت شناسایی مشکلات Performance در دیتابیس و فراهم کردن راهکارهای مناسب و همچنین فراهم کردن امکان رفع خودکار این مشکلات توسط Database Engine ارائه شد. Automatic Tuning امکان برطرف کردن مشکل performance کوئری ها رو که به دلیل انتخاب Query execution plan نامناسب بوده رو ایجاد میکنه.
در Azure SQL Database قابلیت Automatic index management هم وجود داره که امکان ساخت ایندکس های ضروری و حذف ایندکس های ناکارآمد رو فراهم میکنه.
برای آشنایی با مفهوم Execution plan در SQL Server به مقاله نوشته شده مراجعه کنید.
در SQL Server دیتابیس engine وظیفه مانیتور کردن کوئری های درحال اجرا رو داره تا به صورت اتوماتیک کارایی را بهبود بده. دیتابیس Engine یک مکانیسم داخلی هوشمند داره که میتونه به صورت اتوماتیک با آداپته کردن دیتابیس بر اساس workload ، اون رو رو بهینه کنه. دو قابلیت بهینه سازی اتوماتیک در حال حاضر وجود داره :
وظیفه شناسایی مشکلات پلن اجرا کوئری رو به عهده داره، مانند Parameter sensitivity و parameter sniffing. برطرف کردن مشکلات Performance مربوط به کوئری پلن با فورس کردن آخرین کوئری پلن مناسب اجرا شده قبل از بروز اشکال. این قابلین از نسخه 2017 در SQL Server افزوده شد.
شناسایی ایندکس هایی که نیاز هست به دیتابیس شما افزوده یا حذف شوند، این قابلیت فقط در Azure SQL Database وجود دارد.
سه وظیفه کلاسیک دیتابیس ادمین ها شامل ، مانیتورینگ workload دیتابیس ها، شناسایی کوئری های T_SQL مشکل ساز و شناسایی ایندکس هایی که باید اضافه یا حذف شوند تا کارایی دیتابیس بهبود یابد. SQL Server Engine جزئیات مورد نیاز کوئری های اجرا شده را جهت مانیتورینگ برای شما فراهم میکند، گرچه مانیتور کردن دیتابیس وظیفه سخت و خسته کننده ایه، به ویژه وقتی که با چندین دیتابیس کار میکنید. لذا بجای مدیریت و مانیتوریگ به صورت دستی بهتره مانیتورینگ و بهینه سازی رو به Database Engine بسپاریم تا از قابلیت Automatic tuning استفاده کنه.
بهینه سازی اتوماتیک یه چرخه از مانیتورینگ و آنالیز پردازش ها انجام میده تا یادگیری پیوسته درباره مشخصات Workload دیتابیس بدست آورده و اشکالات احتمالی را شناسایی و بهبود بده.
این فرآیند دیتابیس رو قادر میکنه به صورت اتوماتیک خود را با workload ورودی آداپته کنه و با پیدا کردن ایندکس ها و کوئری پلن های مناسب باعث بهبود Performance بشه.
مراحل انجام :
بر اساس این یافته ها، بهینه سازی اتوماتیک tuning مناسب برای workload دیتابیس رو اعمال میکنه و در ادامه automatic tuning کارایی دیتابیس را پس از اعمال هر تغییری مانیتور میکنه تا از موثر بودن تغییر اعمال شده اطمینان حاصل کنه لذا هر تغییری که موثر نباشه برگردانده میشه. این قابلیت ویژگی کلیدی هست که کمک میکنه تا ناخواسته باعث افت کارایی دیتابیس نشه.
ویژگی Automatic plan correction بخشی از بهینه سازی اتوماتیک هست که مشکل در کوئری پلن رو شناسایی میکنه و به صورت اتوماتیک با فورس کردن آخرین پلن مناسب اجرا شده اون رو برطرف میکنه.
برای اطلاعات بیشتر در مورد کوئری پلن میتونید مقاله موجود رو مطالعه کنید.
پایگاه داده SQL Server میتونه انتخاب های مختلفی برای پلن اجرا کوئری داشته باشه. این کوئری پلن بسته به Statistic ها ، ایندکس ها و فاکتورهای دیگه میتونه انتخاب بشه. پلن بهینه انتخابی که قراره کوئری T_SQL رو اجرا کنه ممکنه طی زمان و با تغییر هر کدوم از فاکتورها تغییر کنه. در برخی موارد ، پلن جدید ممکنه بهتر از پلن قبلی نباشه و باعث مشکل Performance بشه مثل Parameter sensitivity و parameter sniffing .
همانطور که تو عکس میبینید انتخاب نادرست پلن اتفاق افتاده، شما باید کوئری پلن قبلی مناسب رو پیدا کنین و اون رو جای پلن فعلی استفاده کنین. این کار میتونه با استفاده از
sp_query_store_force_plan
انجام بشه. Database engine از نسخه 2017 به بعد اطلاعات مربوط به این نوع خرابکاری های پلن رو نگهداری میکنه و پلن درست رو بهتون پیشنهاد میده. همچنین ، engine دیتابیس شما رو قادر میسازه کل این فرآیند رو اتوماتیک کنید و اجازه بدین که خود Engine این تصمیم تغییر پلن رو بگیره.
در صورت به وجود اومدن کوئری پلن نامناسب، Engine دیتابیس میتونه به صورت اتوماتیک آخرین پلن مناسب اجرا شده رو برگردونه.
در مرحله اول تغییر پلن نامناسب رو تشخیص میده و پلن قبلی مناسبی رو که باید به جای پلن جدید نامناسب جایگزین کنه رو فورس میکنه ، در مرحله دوم مجددا مانیتور میکنه تا ببینه پلن برگردونده شده بهینه تر عمل میکنه یا نه ؟ اگر پلن فورس شده از پلن جدید نامناسب هم کارایی کمتری داشته باشه (به دلیل تغییرات دیتابیس ممکنه پلن خوب قبلی هم دیگه کارایی نداشته باشه) این پلن هم از حالت فورس خارج میشه و Database Engine پلن جدیدی رو کامپایل میکنه. اما در صورتی که پلن فورس شده بهتر از پلن نامناسب ایجاد شده باشه، فورس بودن پلن تثبیت میشه، و تا وقتی که یک recompile رخ نده باقی می مونه (مثلا تغییر اسکیما یا Statistic ها )
نکته : اگر SQL Server قبل از تثبیت شدن پلن فورس شده ریست بشه، فورس پلن برداشته میشه در غیر اینصورت تثبیت شده باقی میمونه.
به ازاء هر دیتابیس میتونین این قابلیت فعال کنین تا دیتابیس خودش بتونه در صورت نیاز آخرین پلن مناسب اجرا شده رو فورس کنه.
ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
در صورت عدم استفاده از انتخاب اتوماتیک ، ادمین دیتابیس باید به صورت دوره ای سیستم رو مانیتور کنه و کوئری پلن های نامناسب رو تشخیص بده و میتونه با استفاده از sp_query_store_force_plan پلن های قبلی مناسب اون کوئری رو پیدا و به صورت دستی فورس کنه . برای غیر فعال کردن پلن فورس شده هم میتونه از sp_query_store_unforce_plan استفاده کنه.
منبع :
