تکنیکهای بهینه سازی پرس و جوها در SQL Server: اصول پایه – بخش دوم

تکنیکهای بهینه سازی پرس و جوها در SQL Server: اصول پایه – بخش دوم

جهت مشاهده قسمت اول، اینجا کلیک کنید.

ابزارها

برای ساده کردن کار، فقط از کاربردی ترین ابزارهای استفاده می کنیم.

طرح های اجرایی (Execution Plans)

یک طرح اجرایی (Execution Plan) تصویر گرافیکی از نحوه اجرای پرس و جو (Query) توسط بهینه ساز پرس و جو (Query Optimizer) ارائه می دهد:

برنامه اجرایی به ما نشان می دهد که به کدام جداول دسترسی پیدا کرده ایم، این دسترسی به چه نحوی بوده است، جداول چگونه بهم پیوسته اند (Join) و هر رخداد دیگری که در طول مسیر اتفاق افتاده است. این برنامه اجرایی، هزینه های اجرای پرس و جو را نیز  تخمین می زند و هزینه اجرای کل پرس و جو، مشخص می گردد. مجموعه بسیار ارزشمندی از داده ها همچون اندازه هر رکورد، هزینه CPU، هزینه I/O و جزئیات مربوط به استفاده از این فهرست ها نیز درج شده است.

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

STATISTICS IO

این امکان به ما اجازه می دهد تا خوانش های منطقی انجام شده در حال اجرای پرس و جوی مورد نظر را مشاهده کنیم. برای فعال کردن آن کافی است از طریق  SQL Server Management Studio  دستور TSQL زیر را اجرا نمایید:

SET STATISTICS IO ON;

با فعال کردن این گزینه می توانید اطلاعات جدید اضافه تری را در پنل Message مشاهده نمایید:

در بخش Logical reads تعداد خوانش های انجام شده از کش بافر را مشاهده می کنید. به این اعداد زمانی مراجعه می کنیم که بخواهیم درباره تعداد خوانش های یک پرس و جو تحقیق کنیم یا ببینیم این پرس و جو باعث چقدر عملیات ورودی / خروجی شده است.

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

به طور کلی، IO یکی از گلوگاه های اصلی به هنگام مطالعه در خصوص بررسی دلیل کند بودن اجرای یک پرس و جو است. 

زمان اجرای پرس و جو (Query Duration)

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

ما برای کار خودمان، زمان اجرای پرس و جوها را از بخش زمان محاسبه شده در پایین سمت راست پنجره اجرای پرس و جو در SSMS به دست می آوریم.

روش های دیگری هم برای اندازه گیری دقیق مدت زمان پرس و جو وجود دارد، مانند تنظیم STATISTICS TIME، اما ما روی پرس و جوهایی که به حسابی کند هستند تمرکز خواهیم کرد که چنین سطح دقتی در محاسبه زمان اجرای آنها، لازم نخواهد بود. مثلا به راحتی می توانیم مشاهده کنیم که یک پرس و جو که ۳۰ ثانیه برای اجرای آن زمان نیاز بوده، پس از بهبود و در زمان اجرای دوم ۵ ثانیه زمان گرفته است. این امر همچنین باعث تقویت نقش کاربر به عنوان منبع اصلی بازخورد می شود زیرا سعی می کنیم سرعت یک برنامه را بر اساس نظر وی بهبود بخشیم.

چشمهای ما

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

هر چه پرس و جوهای بیشتری را بهینه سازی کنیم و در آن تجربه به دست آوریم، الگوهایی در ذهن ما شکل می گیرد که به سرعت شناسایی نقاط ضعف یک پرس و جو را برای ما ممکن خواهد ساخت و بدون نیاز به تحقیقات بسیار وقت گیر، قادر خواهیم بود به سرعت مشکل را برطرف کنیم.

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

Query Optimizer چکار می کند؟

هر پرس و جو در راه اجرای کامل روی SQL Server، مسیر یکسان و مشخصی را طی می کند:

Parsing یا تجزیه، فرایندی است که صحت ساختار نوشتاری (syntax) پرس و جو بررسی می شود. آیا کلمات کلیدی معتبر هستند و آیا قوانین زبان TSQL به درستی رعایت شده است. اگر خطای املایی ایجاد کرده اید، با استفاده از یک کلمه رزرو شده، یک ستون را نامگذاری کرده اید و یا یک سمی کالن (;) را قبل از شروع سطر جدید دستورات فراموش کرده اید، در اینجا پیام های خطایی دریافت می کنید که شما را از این مشکلات مطلع می کند.

Binding یا صحت استفاده، فرآیندی است که در آن تمام اشیاء ارجاع شده در TQL شما را در برابر کاتالوگ های سیستم و اشیاء موقتی تعریف شده در کد شما بررسی می کند تا تشخیص دهد که آیا آنها هم معتبر هستند و هم به درستی از آنها استفاده شده است. اطلاعات کامل در مورد این اشیاء مانند انواع داده ها، محدودیت ها و اینکه آیا یک ستون اجازه می دهد NULL در آن ذخیره شود یا خیر و مواردی از این دست بازیابی می شوند. نتیجه این مرحله یک درخت پرس و جو است که از یک لیست اساسی از فرآیندهای لازم برای اجرای پرس و جو تشکیل شده است. این دستورالعملهای اساسی را ارائه می دهد، اما هنوز جزئیاتی را در بر نمی گیرد، از جمله اینکه از کدام ایندکس ها استفاده می شود یا اینکه کدام جداول قرار است با هم ترکیب (join) شوند.

Optimization یا بهینه سازی، روندی است که ما بیشتر در اینجا به آن اشاره خواهیم کرد. Optimizer یا بهینه ساز به طور مشابه با رایانه شطرنج باز (یا هر بازی دیگری) عمل می کند. باید تعداد بی شماری از حرکتهای ممکن را در اسرع وقت در نظر بگیرد، گزینه های ضعیف را حذف کرده و با انتخاب بهترین حرکت ممکن، کار را به پایان برساند. در هر برش از زمان ، ممکن است میلیون ها ترکیب از حرکت در نظر گرفته شده برای این رایانه وجود داشته باشد، اما فقط تعداد انگشت شماری جزو بهترین حرکتهای ممکن هستند. هرکسی که در برابر رایانه شطرنج بازی کرده است می داند که هر رایانه زمان کمتری برای محاسبه داشته باشد، احتمال بروز خطا (بازی بد) نیز بیشتر است.

در دنیای SQL Server، ما به جای حرکت های شطرنج، در مورد برنامه های اجرای پرس و جو صحبت خواهیم کرد. برنامه اجرا مجموعه مراحل خاصی است که موتور دیتابیس، اجرای آن را برای پردازش یک پرس و جو دنبال می کند. هر پرس و جو گزینه های بسیاری برای رسیدن به آن برنامه اجرا دارد و باید در مدت زمان بسیار کوتاهی این کار را انجام دهد.

این گزینه ها شامل سوالاتی از قبیل موارد زیر، هستند:

  • به چه ترتیب هایی باید جداول بهم متصل (Join) شوند؟
  • چه نوع اتصالی باید بین جداول برقرار گردد؟
  • از کدام ایندکس ها باید استفاده شود؟
  • آیا لازم است از یک جستجوی کامل یا اسکن برای جدول مشخصی استفاده شود؟
  • آیا در ذخیره داده ها به صورت worktable یا spooling برای استفاده بعدی فایده ای وجود دارد؟

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

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

معیار مورد استفاده برای انتخاب از بین برنامه های اجرایی و تصمیم گیری برای حذف برخی از این برنامه های اجرایی، هزینه پرس و جو است. هزینه هیچ واحدی ندارد و یک اندازه گیری نسبی از منابع مورد نیاز برای اجرای هر مرحله از یک طرح اجرایی است. هزینه کلی پرس و جو مجموع هزینه های هر مرحله در یک پرس و جو است. شما می توانید این هزینه ها را در هر برنامه اجرایی مشاهده کنید:

مقدار Subtree costs برای هر مولفه یک پرس و جو محاسبه شده و برای هر یک از موارد زیر، استفاده می گردد:

  1. حذف تمامی برنامه های اجرا با هزینه خیلی بالا یا نزدیک به آن، جهت محدودتر شدن تعداد برنامه های اجرای موجود برای انتخاب.
  2. رتبه دهی به برنامه های اجرای باقیمانده بر اساس اینکه چقدر هزینه اجرای آنها کمتر است.

در حالی که هزینه پرس و جو یک معیار مفید برای درک چگونگی بهینه سازی یک پرس و جو توسط SQL Server است، لازم است به یاد داشته باشید که هدف اصلی آن کمک به بهینه سازی پرس و جو در انتخاب برنامه های اجرای خوب است. این یک اندازه گیری مستقیم مصرف ورودی / خروجی، CPU، حافظه، مدت زمان دقیق اجرا یا هر اندازه گیری دیگری نیست که واقعا برای کسی که پای سیستم نشسته و منتظر دریافت نتیجه خروجی است، مهم باشد. هزینه کم پرس و جو لزوما به معنی سریع ترین یا بهترین برنامه اجرا نیست. از طرف دیگر، ممکن است هزینه بالا برای اجرای یک پرس و جو گاهی اوقات، قابل قبول باشد. در نتیجه، بهتر است به عنوان یک معیار عملکرد به هزینه پرس و جو تکیه نکنید.

از آنجا که ملاک بهینه ساز پرس و جو از طریق بررسی بین برنامه های اجرای کاندید شده است، آنها را از کمترین هزینه تا بالاترین هزینه رتبه بندی می کند. سرانجام ، بهینه ساز به یکی از نتیجه گیری های زیر می رسد:

  • همه برنامه های اجرا ارزیابی شده و بهترین مورد انتخاب خواهد شد.
  • زمان کافی برای ارزیابی همه برنامه ها وجود ندارد، در نتیجه بهترین موردی که تاکنون شناسایی شده، انتخاب خواهد شد.

پس از انتخاب یک برنامه اجرا، کار ما در مرحله بهینه سازی پرس و جو کامل شد و می توانیم به مرحله نهایی پردازش پرس و جو برویم.

Execution یا اجرا مرحله آخر است. SQL Server برنامه اجرایی را که در مرحله بهینه سازی مشخص شده است می گیرد و برای اجرای پرس و جو از آن دستورالعمل ها پیروی می کند.

نکته ای در خصوص plan reuse یا استفاده مجدد از برنامه های قبلی: از آنجا که بهینه سازی یک فرآیند ذاتاً گران است، SQL Server در یک حافظه نهانُ برنامه اجرایی انتخاب شده را نگه می دارد که جزئیات مربوط به هر پرس و جو اجرا شده بر روی یک سرور و نقشه ای را که برای آن انتخاب شده است، ذخیره می کند. به طور معمول، پایگاه داده ها در موارد مشابه، همین طرح ها را بارها و بارها مورد استفاده قرار خواهد داد. استفاده مجدد به ما این امکان را می دهد تا از فرآیند بهینه سازی گران قیمت خودداری نموده و به کارهایی که قبلاً برای بهینه سازی یک پرس و جو انجام داده ایم، تکیه کنیم.

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

ادامه دارد …..

 

جهت مشاهده قسمت اول، اینجا کلیک کنید.

تغییر رویکرد سازمانی از پروژه محور به محصول محور: منظور چیست و چرا اهمیت دارد؟ - بخش دوم
نظر خود را بگوئید

پیغام بگذارید

ایمیل شما با دیگران به اشتراک گذاشته نخواهد شد *

X

Hello,
Your location data will be used to help identify you and allow us to offer you great products and services. Your consent is important for us to do this and you are not obliged in any way to share this information with us. Thank you