افزایش سرعت دیتابیس در sql server

افزایش سرعت دیتابیس در sql server : اغلب برنامه نویس ها برای برقراری ارتباط میان بانک های اطلاعاتی یا دیتابیس ها و محیط کاربری نیاز به استفاده از دستورات SQL دارند. یافتن کوئری هایی که کند اجرا می شوند و علت این کندی ها می تواند به افزایش سرعت دیتابیس در sql server کمک کند.برنامه نویسان معمولا دسترسی به تکنیک های مختلف و ابزار های کمکی زیادی دارند تا بتوانند به خوبی سیستم را عیب یابی کرده و علت اصلی کند بودن کوئری ها را دریابند.
ابزارهای مختلفی برای افزایش سرعت دیتابیس در sql server و کشف کوئری های کند وجود دارد که آشنایی با آن‌ها برای مدیران بانک های اطلاعاتی اهمیت ویژه ای دارد. دانلود تعدادی از این ابزارها رایگان می باشد. ابزارهای که در ادامه به معرفی آن می پردازیم جزو رایج ترین روش های مورد استفاده جهت افزایش سرعت دیتابیس در sql server و کشف کوئری های کند هستند و استفاده از آن‌ها نتایج خوبی را به همراه داشته است.

روش های مورد نظر برای بررسی وضعیت کوئری های دیتابیس شامل زیر است:

* استفاده از Activity Monitor

* استفاده از Query Store

* استفاده از ApexSQL Plan

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

جهت افزایش سرعت دیتابیس در sql server می توان با با استفاده از 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

ابزار بعدی که جهت افزایش سرعت دیتابیس در sql server موثر می باشد و می خواهم به شما معرفی کنم، 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 استفاده شده و فقط یک کوئری داخل روال اجرا می شود که ۶۷۲ میلی ثانیه CPU مصرف می کند(زمان سپری شده= ۱۶۵۰ میلی ثانیه).

توجه کنید که ممکن است چندین کوئری داخل یک روال در حال اجرا باشند بنابراین فراموش نکنید که 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 این بار کل برنامه را شبیه سازی کنید. سپس با استفاده از تکنیک هایی که در بالا به آن اشاره کردیم کوئری های کند را پیدا کرده و اصلاح کنید.