کشف دلیل کندی سرعت کوئری در یک نگاه! - مقدمه

احتمالاً با مشاهده عنوان آموزش کشف دلیل کندی سرعت کوئری در یک نگاه آن را یک امر غیر ممکن تصور می کنید. اما در این آموزش مانند یک پزشک که در ابتدا با بررسی علائم ظاهری بیمار از قبیل: رنگ پریدگی، تپش قلب، ناآرامی و همینطور کنترل علائم حیاتی مانند: فشار خون، تنفس، درجه حرارت بدن، نبض و ... به علت بیماری فرد پی می برد، ما نیز با بررسی Execution Plan یک کوئری که معرف نحوه اجرای واقعی آن در sql server است(مانند علائم ظاهری یک فرد) علائمی را فرا می گیریم که مشاهده آن ها در Execution Plan به عنوان یک هشدار دهنده و علامتی بر وجود مشکل کارایی در کوئری ما هستند(مانند رنگ پریدگی یک فرد که می تواند علامتی بر بیماری او باشد).

خوب جدا از مبحث پزشک و بیمار، برای شروع آموزش کشف دلیل کندی سرعت کوئری در یک نگاه داشتن حداقل اطلاعات در sql server کفایت می کند. تنها پیشنهاد می کنم آموزش معرفی، کاربرد و اجزاء Execution Plan را مطالعه کنید تا به بهترین نحو این آموزش کاربردی را فرا بگیرید. اگر اجرای کوئری شما زمان بر باشد در اولین گام بایستی دلیل کندی سرعت اجرا را کشف کنید و سپس با اقدامات اصلاحی مانند: بازنویسی دستورات T-SQL، اصلاح/ایجاد ایندکس ها، اصلاح ساختار جدول و ... عامل ایجاد مشکل را حذف کنید. در آموزش کشف دلیل کندی سرعت کوئری در یک نگاه نشانه ها و علائمی را معرفی می کنیم که شما با مشاهده آن ها در Execution Plan دلیل کندی اجرای کوئری خود را پیدا می کنید و به عبارتی، عواملی را پیدا می کنید که باید در جهت رفع مشکل کارایی کوئری، آن ها را بررسی و اصلاح کنید. اما روش های برطرف کردن این مشکلات، یک بحث کاملاً تخصصی است و در آموزش های افزایش سرعت و کارایی به آن ها می پردازیم. 

دانلود فیلم قسمت اول و قسمت دوم از آموزش کشف دلیل کندی سرعت کوئری در یک نگاه

---

کشف دلیل کندی سرعت کوئری در یک نگاه! - علائم وجود مشکل در کوئری

1. مشاهده Warning در Execution Plan:

نمایش علامت  در Execution Plan یکی از علائم کشف دلیل کندی سرعت کوئری در یک نگاه است. وجود این علامت در کنار یک آیکن(عملگر) بیان کننده این موضوع است که آن عملیات به صورت بهینه اجرا نمی شود. در شکل زیر دو نمونه از این Warningها را می بینیم:

شخصاً مورد 1 را در بسیاری از کوئری ها دیده ام، یکی از دلایل وجود Type Conversion Warning در کوئری ها عدم هماهنگی بین طراح دیتابیس و برنامه نویس است. به عنوان مثال، طراح دیتابیس به هر دلیلی فیلد کدملی را در جدول، از نوع INT تعرف کرده است، در حالی که برنامه نویس در شرط کوئری خود، فیلد کد ملی را با یک مقدار رشته ای(به عنوان نمونه مستقیماً مقدار Text Box را پاس کرده) مقایسه کرده است(مانند مثال 1). در چنین شرایطی، شما در عملگر Select این Warning را مشاهده می کنید. دقت کنید این Warning با افزایش تعداد رکوردها، تأثیر زیادی در کاهش سرعت اجرای کوئری دارد.

نکته اول: برای مشاهده دلیل رخ دادن هشدار Type Conversion، از Tool Tip مربوط به آن عملگر استفاده کنید.(موس را روی آن عملگر نگه دارید)

در مثال 2 هم یک Warning برای عملیات مرتب سازی(Sort) داریم. این هشدار زمانی رخ می دهد که در حافظه اصلی(RAM) فضای کافی برای مرتب سازی رکوردها وجود نداشته باشد، بنابراین از فضای Tempdb استفاده می شود و چون این فضا بر روی دیسک قرار دارد در نتیجه سرعت به شدت کاهش پیدا می کند. یک راه حل برای رفع این مشکل، ایجاد ایندکس روی ستون مورد نظراست تا رکوردها از قبل مرتب شده باشند. این مورد در الگوریتم MERGE Join یا هر عملیاتی که نیاز به مرتب سازی تعداد زیادی رکورد داشته باشد ممکن است رخ دهد.

2. مشاهده فلش های ضخیم(تُپُل) در Execution Plan:

این نشانه، علی رغم ساده بودن، در کشف دلیل کندی سرعت کوئری در یک نگاه بسیار کاربردی است. به Execution Plan نمونه زیر دقت کنید:

 ضخامت فلش ها در Execution Plan متناسب با تعداد رکوردهای خروجی از یک عملگر(آیکن) است. در شکل بالا در مرحله اول(Index Scan) تعداد 19252 عدد رکورد استخراج شده است، در حالیکه تعداد رکورد مورد نیاز (خروجی مورد انتظار ما) تنها یک عدد رکورد است! (این مورد شبیه Differed Loading در Linq است: واکشی همه رکوردها در حافظه سرور، اعمال شرط روی آن ها)

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

3. مشاهده عملیات SCAN در Execution Plan:

scan به معنای خواندن تمام اطلاعات موجود در یک شئ(جدول،ایندکس)  است. معمولاً عملیات scan به ازای جدول(Table Scan) و یا ایندکس ها (Index Scan) رخ می دهد:

نکته دوم: همیشه وجود عملیات SCAN در Execution Plan، دلیل بر کندی سرعت کوئری نیست و گاهی این عملیات بهترین گزینه است.

نمایش عملیات Scan می تواند دلیلی بر عدم وجود ایندکس مناسب برای جدول، بهینه نبودن شرط کوئری و یا مرغون به صرفه نبودن عملیات seek باشد. در نقطه مقابل Scan عملیات Seek وجود دارد. فرض کنید شما قرار است یک مطلب از کتاب درسی تان را بخوانید، برای این کار در ابتدا سراغ فهرست کتاب می روید سپس شماره صفحه مرتبط با موضوع رو پیدا می کنید(Index Seek) و با رفتن به آن شماره صفحه(Look up)، مطالعه را شروع می کنید. اما آیا در شب امتحان که قرار است کل کتاب را مطالعه کنید همین کار را(مراجعه به فهرست، یافتن شماره صفحه و مطالعه) تکرار می کنید؟ در شب امتحان کل مطالب کتاب را از ابتدای آن شروع به مطالعه می کنید(scan).

در sql server معمولاً اگر تعداد رکوردهای بازگشتی کوئری بین 8 تا 10 درصد کل رکوردهای جدول باشد از Look Up + Seek و در صورتیکه رکوردهای بازگشتی بیش از 75 درصد رکوردهای جدول باشد از scan استفاده می شود.

با توجه به اینکه در اکثر کوئری ها، به کمتر از 75درصد رکوردهای یک جدول نیاز داریم، با مشاهده scan در Execution Plan، بایستی ایندکس ها و شرط کوئری را مورد بازبینی قرار دهیم. 

4. وجود عملگرهای اضافی در Execution Plan:

در ابتدا Execution Plan نمونه زیر را مشاهده کنید:

همانطور که در شکل بالا مشاهده می کنید، یک مجموعه از عملیات، در Execution Plan دقیقاً تکرار شده اند. به عبارتی دیگر عملیات مشابهی را تکرار کرده ایم. مثلاً اطلاعات را از جدول واکشی کرده ایم، یک تابع تجمعی (sum-avg) روی آن ها اعمال کرده ایم و در حالیکه این عملیات و محاسبه را در حافظه داریم، مجدداً همین عملیات را تکرار کرده ایم!

راه حل این گونه مشکلات، بازنویسی کوئری است و باید دستورات T-SQL را به نحوی تغییر دهیم تا از اطلاعات واکشی شده موجود استفاده شود.(در آموزش ترفندها یک نمونه ذکر خواهد شد)

5. اختلاف زیاد بین Estimated Row Number با Actual Row Number:

 قبل از اینکه کوئری به صورت واقعی در sql server اجرا شود، تعداد رکورد بازگشتی هر عملگر توسط Optimizer تخمین زده می شود(برای اینکه بتواند از بین چندین راه حل ممکن، بهترین را انتخاب کند) که به این تعداد تخمین زده شده Estimated Number Rows گفته می شود. پس از اجرا شدن کوئری نیز تعداد واقعی رکورد بازگشتی هر نمایشگر نمایش داده می شود که به این تعداد Actual Number Rows می گوییم. این دو مقدار در Tool Tip هر عملگر قابل مشاهده است.

در تصویر بالا، تعداد رکوزد بازگشتی(Estimated Number Rows) دارای اختلاف زیادی با تعداد واقعی آن(Actual Number Rows) است. وجود چینن اختلافی به این معناست که Optimizer از یک معیار اشتباه جهت انتخاب بهترین روش اجرای کوئری استفاده می کند. راه حل چینن مشکلی، بروزرسانی Statistics های جدول مربوطه است (در آموزش آشنایی با statistics بیشتر در این مورد بخوانید)

6. رخ دادن عملیات جستجوی اضافی(Residual Predicate) در اجرای کوئری:

این مورد کمی تخصصی تر از موارد قبلی است. زمانی که در کوئری شما چندین شرط وجود داشته باشد، بسته به نوع ایندکس، ممکن است که جستجو در دو مرحله انجام شود. برای آشنایی بهتر با این موضوع فرض کنید در یک دفتر تلفن، قصد دارید شماره تلفن فردی به نام محسن بندامیر را پیدا کنید. از آن جایی که دفتر تلفن بر اساس فامیل مرتب است. در ابتدا به قسمتی از دفتر تلفن که حاوی فامیل های شروع شونده با حرف "ب" است می رویم و در مرحله دوم از بین این فامیل ها، نام محسن را جستجو می کنیم. این دقیقاً مفهوم جستجوی اضافی است. یعنی بر اساس یک شرط، یک جستجوی اولیه صورت می گیرد و سپس در بین اطلاعات استخراجی، بر اساس شرطی دیگر جستجوی دوم را انجام می دهیم.  کوئری معادل با مثال جستجو در دفترچه تلفن(در این حالت، یک ایندکس روی فیلد famil در جدول phonebook داریم): 

Select phone_Number,email
from phonebook where famil=N'بندامیر' and name=N'محسن'

در تصویر بالا Tool Tip مربوط به عملگر Index Seek حاوی دو شرط Seek Predicate و Predicate است. Seek Predicate در واقع همان جستجوی اول است(مانند جستجو در دفتر تلفن بر اساس فامیل) و Predicate معادل با جستجوی دوم بر روی رکوردهای استخراجی شرط قبلی است. در  Predicate جستجو بر روی کلیه رکوردهای خروجی شرط اول انجام می شود(Scan). پس با مشاهده عملگر Index Seek بهتر است که Tool Tip آن را جهت اطمینان از عدم وجود Predicate بررسی کنیم. زیرا در این حالت سرعت اجرای کوئری حتی می تواند کند تر از Scan(خواندن تمام رکوردهای جدول) باشد.

نکته سوم: رخ دادن Residual Predicate، در عملگرهای Merge Join و Hash Match Join به این معناست که در ابتدا جوین بر اساس شرط ON روی دو جدول اعمال شده و سپس روی تک تک رکوردهای خروجیِ شرط ON، شرط دوم اعمال می گردد. 

علت رخ دادن عملیات جستجوی اضافی(Residual Predicate) عواملی مانند: استفاده از توابع و محاسبات در شرط کوئری، عدم وجود ایندکس مرکب روی ستون های موجود در شرط و ... است. راه حل Residual Predicate  اغلب شامل: اصلاح شرط کوئری و تغیر در ساختار ایندکس و یا ایجاد ایندکس مناسب جدید است.

7. عملگرهایی با بیشترین هزینه(Cost) در Execution Plan:

در مواقعی که علی رغم کندی سرعت اجرای کوئری، هیچ کدام یک از علائم 1 تا 6 کشف دلیل کندی سرعت کوئری در یک نگاه را در Execution Plan مشاهده نکردید. به دنبال عملگرهایی با بیشترین هزینه اجرایی بگردید و سعی کنید این عملگرها را بهینه سازی کنید.

در Execution Plan  بالا که با نرم افزار Sql Sentry تهیه شده است می بینیم که هزینه(Cost) عملگر Hash Match Join با رنگ قرمز نمایش داده است و دارای هزینه ای بیشتر از مابقی عملگرهای موجود می باشد. حالا که صحبت از Hash Match Join شد، یک نکته در مورد این عملگر اشاره کنیم:

نکته چهارم: وجود عملگر Hash Match Join نیز مانند SCAN، از مواردی هست که در صورت کند بودن سرعت اجرای کوئری باید بررسی و بهینه شود.

دقت کنید عملگر Hash Match زمانی رخ می دهد که: تعداد رکوردهای جداول شرکت کننده در جوین خیلی زیاد باشند، مرتب شده(Sort) هم نباشند و ایندکس/های مناسبی نیز در جداول ایجاد نشده باشد. همه این موارد یعنی انجام جوین در شرایط نامناسب. البته در برخی موارد هم، انجام Hash Match تنها راه ممکن برای انجام جوین است.

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

---

کشف دلیل کندی سرعت کوئری در یک نگاه! - جمع بندی

 همانطور که می دانید کشف دلیل کندی سرعت کوئری به عنوان مهمترین مرحله در رفع مشکلات کارایی محسوب می شود. به طور کلی تا زمانی که دلیل ایجاد یک مشکل به درستی شناسایی نشود، راه حل/های ارائه شده نیز بی فایده و بدون تأثیر خواهند بود. از طرفی در sql server، مهارت کشف دلیل کندی سرعت کوئری نیازمند تخصص و تجربه کافی در این زمینه است. اما در آموزش کشف دلیل کندی سرعت کوئری در یک نگاه سعی بر این بود که بر اساس تجارب کاری و با استفاده از مزیت شکل گرافیکی Execution Plan به صورت ابتکاری راه کارهایی در این جهت ارائه شود. دقت کنید که راه کارهای ارائه شده در کشف اکثر مشکلات رایج  کندی سرعت کوئری به شما کمک خواهند کرد.

 دانلود فیلم قسمت اول و قسمت دوم از آموزش کشف دلیل کندی سرعت کوئری در یک نگاه