کشف کوئری های کند و افزایش سرعت دیتابیس در sql server

یکی از متداولترین مشکلات سازمان هایی که با حجم زیادی از دیتا سر و کار دارند، کوئری هایی هستند که سرعت اجرای پایینی دارند. چالش برانگیزترین مشکل این است که بتوانیم تشخیص دهیم کدام کوئری کند اجرا می شود و علت این کندی که باعث افت کارایی شده است، چیست؟ خوشبختانه در اغلب موارد پیدا کردن آن بسیار ساده است.

پیشنهاد من این است که بیشتر وقت خود را جهت پیدا کردن علت واقعی که باعث این افت سرعت شده، صرف کنید تا راه حل های احتمالی که ممکن است وجود داشته باشد.

خوشبختانه، ابزار و تکنیک هایی برای توسعه دهنده یا مدیر بانک اطلاعاتی وجود دارد که باید همیشه از آن استفاده کند تا بتواند راه حل خوبی برای کوئری های کند ارائه دهد.

نکته: من در این مقاله برای تست های خودم از SQL Server 2016 استفاده می کنم. اگر شما از ورژن های قدیمی تر استفاده می کنید، Query Store را در دسترس نخواهید داشت اما بقیه ابزارها  وجود دارند.

ابزار و تکنیک ها

انجام هر کار تخصصی در دنیا نیاز به ابزار خاص خود دارد. فرآیند شناسایی کوئری های کند هم ازین قائده مستثنی نیست. در این مقاله ابزارهایی را معرفی می کنیم که یا روی سیستم شما وجود دارند و یا می توانید به رایگان آنها را دانلود کنید.

اولین ابزاری که قصد معرفی آن را داریم، ابزاری در SQL Server Management Studio یعنی “Activity Monitor” است. برای دیدن این ابزار روی instance خود در SQL Server Management Studio راست کلیک کرده و گزینه Activity Monitor را انتخاب کنید.

بررسی کوئری های دیتابیس با Activity Monitor

Activity Monitor فعالیت های جاری و اخیر در SQL Server Instance مورد نظر را به شما نمایش می دهد.

عکس فوق، پنجره overview  در  Activity Monitor را نشان می دهد. در این صفحه، نمودار زمان پردازش، نمودار وظایفی که در لیست انتظار هستند و نمودار درخواست های دسته ای را می بینید. به طور کلی هرچقدر این ارقام کمتر باشد کارایی بهتر خواهد بود. در سازمان های بزرگ با بار دیتای بالا، در این صفحه، درخواست های دسته ای بسیار زیاد با زمان پردازش بالا دیده می شود اما این ارقام لزوما به معنای کارایی پایین نیست.

بعد از مرور کلی این آمار و ارقام نوبت اینست که روی عملیاتی تمرکز کنید که به شما دسترسی دیدن تمام پردازش های در حال اجرا در instance تان را می دهد. در این مرحله باید نگاهی عمیق تر به تعداد پردازش هایی که منتظرند ،درحال بلاک شدن هستند یا بلاک شده اند بیاندازید. با این روش به وضعیت اجرای کوئری ها نظارت می کنید، مثل زمانیکه  اجرای یک کوئری به علت مسدود شدن توسط بقیه فرآیند ها به طول می انجامد یا برخی کوئری ها به علت زیاد بودن زمان انتظار به کندی اجرا می شوند. در این صفحه می توانید با راست کلیک روی هر کدام از فرآیند ها و کلیک روی گزینه Details کد TSQL واقعی که در حال اجراست را مشاهده کنید.

علت اینکه برخی کوئری ها بلاک می شوند اینست که به منابعی برای اجرا نیاز دارند که توسط بقیه فرآیند ها به کار گرفته شده اند. بنابراین، اگر با چنین کوئری مواجه شدید، کافیست با نگاه کردن به ستون بلاک کننده مبدا (root blocker) به سادگی پردازش مسدود کننده را پیدا کنید. سعی کنید فقط همان کوئری را بررسی کنید نه تمام فرآیند های مسدود شده را.

کوئری های که منتظر منبع خاصی برای اجرا هستند، شما را متوجه Wait Resource یا منبعی که باعث ایجاد صف انتظار شده است می کنند، بنابراین شما می توانید منبعی که کمبود آن باعث انتظار فرآیندها شده است را  از ستون Wait Type خوانده و به دنبال راه حلی برای آن باشید. برخی از شایعترین آمار انتظار درSQL Shack  در بخش SQL Server Wait Type وجود دارد. برای انجام اقدامات بعدی به بررسی جزئیات بیشتری می پردازیم.

قسمت های Active Expensive  و Recent Expensive به شما اطلاعاتی در مورد کوئری هایی که مصرف بالای cpu دارند، زمان زیادی برای خواندن نیاز دارند یا زمان زیادی از شروع اجرای آنها سپری شده است، می دهد.

می توانید برای دیدن کوئری های پرهزینه جاری یا اخیر (Active Expensive  و Recent Expensive) به هر کدام ازین بخش ها بروید. آنها را به ترتیب بر اساس زمان سپری شده (Elapsed Time ) ، زمان خواندن منطقی و زمان استفاده از CPU مرتب کنید و برنامه اجرایی آنها را چک کنید. در برنامه اجرایی، متوجه خواهید شد چرا اجرای این کوئری های پرهزینه، به طور غیرطبیعی زمان می برد، بنابراین می توانید اقدامات مناسبی برای حل آنها برگزینید. من در ادامه مقاله به شما آموزش می دهم چطور به بررسی برنامه اجرایی کوئری SQL بپردازید ، پس با من همراه باشید.

بررسی کوئری های دیتابیس با Query Store

ابزار بعدی که می خواهم به شما معرفی کنم، Query Store است.  این ابزار خیلی به کار شما خواهد آمد، مثلا هنگامیکه نیمه شب با شما به عنوان مدیر پایگاه داده تماس می گیرند و در مورد علت کندی سرعت یک ساعت پیش SQL سوال می پرسند.

به طور کلی،  در نسخه های قبل از SQL Server 2016، شما بدون برنامه کمکی یا راه حل های سفارشی نمی توانستید از لیست و جزئیات کوئری هایی که  قبلا روی سرور اجرا شده اند مطلع شوید. بنابراین، Query Store  قابلیت های با ارزش و بسیار کاربردی را در این زمینه به SQL Server اضافه کرده است.

اگر از SQL Server 2016 به بالا استفاده می کنید، اول باید Query Store را از قسمت properties بانک اطلاعاتی خود فعال کنید. پس از فعال سازی Query Store، صفحه properties بانک اطلاعاتی شما شبیه تصویر زیر خواهد بود:

پس از فعال سازی Query Store همانطور که در تصویر زیر می بینید، باید آبجکت های بانک اطلاعاتی خود را باز کرده و در قسمت Query Store به گزینه Top Resource Consuming Queries مراجعه کنید:

نکته: بهتر است یکی دو روز به Query Store  فرصت دهید تا بار واقعی کوئری ها را بدست آورد، پس از گذشت این زمان به راحتی می توانید با داده های واقعی با Query Store کار کنید.

روی Top Resource Consuming Queries راست کلیک کرده و گزینه  View Top Resource Consuming Queries را انتخاب کنید. بدین ترتیب وارد صفحه ای خواهید شد که کوئری هایی که بیشترین مصرف منابع را دارند نمایش می دهد. می توانید این صفحه را برطبق گزینه های مورد نظر خود تنظیم کنید. اولین مورد انتخاب معیار اندازه گیری یا Metric است که می تواند روی  گزینه Duration (مدت زمان)  – CPU Time  (زمان استفاده از پردازنده) – Logical read (زمان خواندن منطقی) یا Memory Consumption  (میزان مصرف حافظه) بگزارید.  دومین موردی که می توانید در این صفحه تغییر دهید statistic است ، می توانید آن را روی گزینه Min ،  Max یا Avg تنظیم کنید. من انتخاب گزینه Average Statistic  با همه گزینه های Metric را برای گرفتن نتیجه دقیق و مناسب پیشنهاد می کنم.

قدم بعدی علامت زدن کوئری هاییست که بیشترین منابع را استفاده می کنند.  بعد از علامت زدن مقادیر در نمودار سمت چپ صفحه ( مثل تصویر زیر) برنامه اجرای کوئری ها را در پایین صفحه مشاهده می کنید.

حالا می توانید در صفحه Query Store روی ستون های هایلایت شده کلیک کرده تا متن واقعی کوئری را برای تحلیل های بعدی بدست آورید.

بنابراین، از راه های مختلفی می توانید کوئری های پر مصرف را پیدا کنید.  در ادامه می آموزیم که چرا برخی کوئری ها به کندی اجرا می شوند و چه بخشی از آنها نیاز به اصلاح دارد؟

مثالی که من در اینجا از آن استفاده کردم مربوط به بانک اطلاعاتی نمونه مایکروسافت به اسم WideWorldImporters است و TSQL که اجرا می شود یک روال ذخیره شده  ( Stored prodedure) به اسم [Integration].[GetOrderUpdates] است.

فراخوانی این روال حدود یک ثانیه زمان می برد و نیازی به بهینه سازی ندارد. در این مثال قصد دارم به شما نشان دهم تک تک ثانیه ها در اجرای یک کوئری صرف چه عملیاتی می شود. همچنین مهم است تشخیص دهید چه بخشی از کوئری بیشترین زمان را می گیرد و بیشترین تمرکز روی چه جدولیست.

در تصویر زیر این روال فراخوانی و نتیجه آن نمایش داده شده است:

حال که روال را فراخوانی کردیم و نتیجه آن را می بینیم نگاه دقیقتری به آن میاندازیم.

در وحله اول، باید Query Statistics را برای این session فعال کنیم. با نوشتن کد “SET STATISTICS TIME, IO ON”   آمار CPU و IO را برای این جستجو فعال می کنیم.

بعد از اجرای TSQL ای که در صفحه فوق برای فعال کردن Statistics  به آن اشاره کردیم، حال می توانیم مصرف IO هر جدول را به صورت مجزا و همچنین مصرف CPU همه کوئری هایی که در داخل یک روال ) Stored Procedure) اجرا می شوند را همانطور که در تصویر زیر نشان داده شده است در تب Messages ببینیم.

چیزی که از مشاهده تصویر بالا به آن پی می بریم این است که بیشترین IO توسط جدول Orderlines استفاده شده و فقط یک کوئری داخل روال اجرا می شود که 672 میلی ثانیه CPU مصرف می کند(زمان سپری شده= 1650 میلی ثانیه).

توجه کنید که ممکن است چندین کوئری داخل یک روال در حال اجرا باشند بنابراین فراموش نکنید که Statistics هم زمان تک تک کوئری ها را نمایش می دهد و هم در انتها زمان کلی که برای اجرای روال صرف شده است را. بنابراین دقت کنید اگر به دنبال زمان اجرای یک کوئری خاص هستید به عددی که مقابل آن نوشته شده است نگاه کنید اما اگر زمان اجرای کل روال را می خواهید فقط به آخرین عدد توجه کنید.

حالا می دانیم جدول Orderlines بیشترین زمان خواندن منطقی را دارد.

در وحله بعد، Actual Execution Plan (برنامه اجرایی واقعی) را برای کوئری با کلیک کردن روی آیکن (Ctrl+ M) در SQL Server Management Studio  فعال می کنیم و سعی می کنیم به این سوال پاسخ دهیم که چرا این جدول در این حد IO مصرف می کند و کدام قسمت از برنامه اجرایست که بیشترین زمان را می گیرد.

پس از اضافه کردن Actual Execution Plan  وقت آن است که کوئری را دوباره اجرا کنیم و برنامه اجرایی را ببینیم.

بررسی کوئری های دیتابیس با  ApexSQL Plan

گرچه، ما در داخل خود SQL Server Management Studio اطلاعات جزئی بسیار زیادی در مورد برنامه اجرایی کوئری ها بدست می آوریم اما، وسیله بسیار عالی دیگری هم به اسم ApexSQL Plan به طور رایگان در اینترنت وجود دارد که می توانید برنامه اجرای یک کوئری را از راه گرافیکی تر و قابل درک تری مورد بررسی قرار دهید.

بعد از نصب این ابزار، باید SQL Server Management Studio را دوباره راه اندازی کنید. سپس دوباره کوئری را اجرا کنید و برنامه اجرایی را بگیرید. بعد از اجرای کوئری، روی Execution Plan راست کلیک کنید، حال گزینه ” View With ApexSQL Plan” که به این منو اضافه شده ، قابل انتخاب است.

بعد از دیدن برنامه اجرایی در ApexSQL Plan، گزینه های هایلایت شده زیر را می بینید که هر یک در ApexSQL Plan باز خواهند شد.

حال به چند نکته در این زمینه توجه کنید:

*اگر جدول از Key Lookup استفاده می کند، با اضافه کردن ستون ها به ایندکسی که جدول از آن استفاده می کند، lookup را حذف کنید.

* اگر تعداد سطرهای برگردانده شده از کوئری در مقایسه با تعداد سطرهای برگردانده شده از جدول اپراتورها ( قسمت هایلایت شده در بخش پایین query Plan) بسیار بیشتر بود، دوباره کوئری را بنویسید این بار سعی کنید در فیلترهایی که می نویسید ستون های بیشتری را دخیل کنید تا خروجی که تولید می شود دارای سطر های کمتری باشد.

* اگر سطرهای تخمین زده شده و سطرهای واقعی تفاوت زیادی با هم داشتند، Statistics جداول را به روز رسانی کنید.

* اگر فکر می کنید جایگذاری برخی ایندکس ها در کوئری فراموش شده است، آنها را مورد بررسی قرار دهید، اگر به این نتیجه رسیدید که استفاده از این ایندکس ها به افزایش کارایی کمک می کند آنها را به جدول اضافه کنید.

نهایتا، اگر پس از این بررسی ها دریافتید که کوئری های شما هنگامی که به تنهایی اجرا می گردند سرعت و کارایی خوبی دارند و تنها زمان اجرا در بار کل برنامه دچار مشکل می شوند ، به آسانی می توانید با استفاده از ابزار  Machanic’s SQL Stress این بار کل برنامه را شبیه سازی کنید. سپس با استفاده از تکنیک هایی که در بالا به آن اشاره کردیم کوئری های کند را پیدا کرده و اصلاح کنید.

نتیجه گیری

ابزار، تکنیک ها و نکاتی که به آن اشاره کردیم، رایجترین و معمولی ترین راه حل هایی هستند که برای عیب یابی کوئری های کند مورد استفاده قرار می گیرند. این ها عمومی و رایج هستند و اغلب کوئری ها با استفاده از آنها بهتر می شوند.

به خاطر داشته باشید که راه میانبری برای بهبود کارایی و عیب یابی کوئری های کند وجود ندارد. در  واقع این مقاله مجموع مقالاتیست که من در این زمینه مطالعه کردم و سعی کردم در یک مقاله تمام ابزار و تکنیک های مهم، مفید و موثر را جمع آوری کنم. با استفاده از اطلاعات این مقاله عیب یابی اولیه بسیار آسان خواهد بود.

برگرفته از [sqlshack.com]