افزایش سرعت و کارایی کوئری و دیتابیس - performance tuning and optimization

یک برنامه نرم افزاری در ابتدای کار و یا در زمان تست، در انجام عملیات (درج، حذف، خواندن و ...) از سرعت اجرای قابل قبولی برخوردار است. اما با افزایش تعداد کاربران و رشد دیتابیس به تدریج نشانه هایی از ناکارآمدی برنامه آغاز می شود. معمولا شروع مشکلات کارایی با اعلام نارضایتی کاربران و شکایات آن ها همراه است. با عباراتی مانند: صفحه ای که قبلا خیلی سریع لود می شده، در حال حاضر بارگذاری آن چند دقیقه طول می کشد و یا گزارشی که در عرض چند دقیقه تهیه می شده، الان چند ساعت زمان میبرد و ... در چنین شرایطی اولین سؤالی که مطرح می شود این است که جهت افزایش سرعت و کارایی کوئری ها و دیتابیس باید از کجا شروع کنیم؟ 

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

performance tuning and optimization is an art and a science

---

در مجموعه آموزش های افزایش سرعت و کارایی کوئری و دیتابیس چه خواهیم آموخت؟

• بررسی کارایی و سرعت فعلی سرور به همراه معرفی نرم افزارهای مانیتور و آنالیز کارایی
• کشف گلوگاه ها (Bottleneck) - عوامل اصلی کاهش کارایی به همراه ارائه راه حل ها،ابزارها و اسکریپت ها
• بررسی نحوه اجرای دستورات T-SQL توسط sql server - مراحل اجرای یک دستور در sql server
• بررسی عملگرهای خاص و پرکاربرد مانند Join - Pivot و پرداختن به جزئیات آن ها
• ایندکس گذاری صحیح، مانیتوز میزان کارایی ایندکس ها، نگهداری صحیح از ایندکس ها، عوامل مخرب کارایی ایندکس ها و نحوه هندل کردن آن ها و ...
• آشنایی با Statistics،نقش حیاتی آن ها در کارایی و نحوه بروزرسانی Statisticsها به همراه ارائه ابزارها و اسکریپت های مربوطه
• افزایش سرعت و کارایی کوئری ها  از طریق آشنایی با اصول طراحی کوئری بهینه و بازنویسی کوئری ها
• مباحث اینترنالی sql server مانند: page ،B-tree، معماری ایندکس و ...  
• بهینه سازی تنظیمات سطح دیتابیس و فایل های مربوطه
• بهینه سازی تنظیمات سطح سرور
• بهینه سازی سخت افزار سرور به همراه ارائه checklist 

      و...

در ادامه مروری خواهیم داشت بر تعدادی از آموزش های موجود در این مجموعه:

performance tuning and optimization is an art and a science

---

 افزایش سرعت و کارایی کوئری و دیتابیس- بررسی کارایی و سرعت فعلی سرور

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

در هر صورت ممکن است به هر دلیلی امکان نظارت لحظه ای وجود نداشته باشد (توصیه می شود این امکان را هر چه سریعتر فراهم کنید) پس با شروع گلایه های کاربران از چه مرحله ای  کشف و رفع مشکلات کارایی و کندی سرعت را شروع کنیم؟؟

همانگونه که پزشکان در موارد اورژانسی، ابتدا علائم حیاتی بیمار را کنترل می کنند و سپس به معاینه و درمان موارد دیگر می پردازند. یک متخصص و ادمین دیتابیس نیز با بروز مشکلات کارایی و کندی سرعت، در  اولین گام، اقدام به بررسی وضعیت کلی(علائم حیاتی) سرور (شامل سیستم عامل، ram، cpu، hdd، Network) می کند. از ساده ترین ابزارها برای مانیتور وضعیت سرورو می توان performance monitor ویندوز را نام برد. در گام بعدی با استفاده از ابزارهایی که معرفی خواهند شد، اقدام به بررسی وضعیت کارایی در سطح sql server میکنیم و مواردی مانند: کوئری های پرهزینه، wait ها، blocking ،deadlock، I/O bottleneck , ... که مهمترین عوامل کاهش کارایی و سرعت هستند استخراج خواهند شد و متناسب با نوع مشکل کشف شده، اصلاح مورد نیاز اعمال می شوند.

performance tuning and optimization is an art and a science

---

  افزایش سرعت و کارایی کوئری و دیتابیس- نحوه اجرای دستورات T-SQL

 یکی از آموزش های پایه ای که شخصا به آن علاقمند هستم (مانند execution plan) آشنایی با نحوه اجرا شدن یک کوئری در sql server است اینکه بعد از ارسال یک کوئری از سمت برنامه به sql server، چه مراحلی طی می شود؟ وجود ایندکس مناسب، یا بروز بودن statisticsها و مواردی مشابه که همیشه بر اهمیت آن ها تأکید می شود، در چه مرحله ای و چگونه بر سرعت اجرا تأثیر گذار هستند؟

یکی از دلایلی که مطالعه این آموزش را به شما پیشنهاد می کنم این است که اکثر برنامه نویسان از حوزه برنامه نویسی وارد مباحث sql server می شوند. به عبارتی دارای ذهنینت شی گرایی (object - oriented) هستند در حالیکه زبان T-SQL یک زبان شی گرا نیست و پایه و اساس این زبان ریاضیات است. (به همین دلیل است که زبان های برنامه نویسی دائماً دستخوش تغییرات پایه ای هستند، در حالیکه T-SQL همواره پایه ای ثابت دارد.) انتقال دید شی گرایی به زبان T-SQL و طراحی کوئری ها با ذهنیت کد نویسی زبان هایی چون#c منجر به کندی زیاد سرعت کوئری ها و در حالت بدتر، تولید خروجی اشتباه خواهد شد.به عنوان مثال:

در زبان برنامه نویسی #c ارزیابی عبارات منطقی از چپ به راست انجام می شود و زمانی که بین عبارات منطقی کلمه and وجود داشته باشد، اگر یکی اط شروط صحیح باشد، شرط های دیگر بررسی نمی شوند(short-circuit) در حالیکه در T-SQL ارزیابی عبارات منطقی در آنِ واحد (all-at-once) انجام می شود. به عبارتی دیگر مبانی تصمیم گیری جهت ارزیابی عبارات منظقی، هزینه است و هر روشی که عملیات ارزیابی مناسب تر (کم هزینه تر) باشد، استفاده خواهد شد.

به عنوان موردی دیگر، استفاده از حلقه ها (for each - while , ...) در زبان های برنامه نویسی بسیار رایج و معمول است. اما زبان T-SQL همواره با مجموعه ها(set) بهترین عملکرد را دارد. به این موارد در آموزش نحوه انجام دستورات T-SQL  ار مجموعه آموزش های افزایش سرعت و کارایی کوئری و دیتابیس به طور مفصل پرداخته خواهد شد.

performance tuning and optimization is an art and a science

---

 افزایش سرعت و کارایی کوئری و دیتابیس- ایندکس گذاری صحیح

تأثیر ایندکس بر کارایی sql server مانند تأثر لاستیک خودرو بر عملکرد آن است (از اونجایی که در اکثر موضوعات از مثال های دنیای واقعی استفاده می کنم). در صورتی که یک خودرو فاقد لاستیک باشه تنها ممکنه چند متر رو روی رینگ جلو بره و دیگه امکان ادامه مسیر وجود نداره. در sql server هم اگر جداول فاقد ایندکس باشند (در اکثر موارد به ویژه OLTP) تنها تا زمانی که اطلاعات کمی در اون ها وجود داره، عملیات با سرعت خوبی انجام میشه (معادل همون چند متر پیش روی خودروی بدون لاستیک) اما به محض افزایش تعداد رکوردها و درخواست های کاربران، به شدت با کندی سرعت اجرای کوئری ها مواجه خواهید شد.

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

• کاربرد ایندکس ها در SQLSERVER
• معیارهای انتخاب کلید ایندکس کلاستر
• مفهوم Page Split، دلیل رخ دادن آن و تأثیر آن بر کارایی
• تعداد مناسب ایندکس ها در یک جدول!
• مراحل گام به گام ایندکس گذاری یک جدول
• ایندکس گذاری بر اساس کلید خارجی، توابع تجمعی، گروهبندی و مرتب سازی
• اهمیت ترتیب فیلدها در ایندکس مرکب ((Multi-Column
• مفهوم Tipping Point و تأثیر آن در استفاده یا عدم استفاده از یک ایندکس
• روش های استخراج کوئری های نیازمند ایجاد ایندکس جهت بهبود کارایی
• آشنایی با عوامل مخرب کارایی ایندکس ها (ایندکس های مفقودی، بدون کاربرد، تکراری و Fragmentation) و راه حل های ممکن

      و ...

performance tuning and optimization is an art and a science

---

 افزایش سرعت و کارایی کوئری و دیتابیس- تنظیمات سطح دیتابیس، سرور، سخت افزار

اگر چه در اکثر موارد،  افزایش سرعت و کارایی کوئری و دیتابیس از طریق بهینه سازی سطح کوئری، طرحی و تنظیمات دیتابیس و سرور تأمین می شود. اما انتخاب صحیح سخت افزار و منابع سرور و همینطور ملاحظات مربوطه (مانند مجزا بودن سرور پایگاه داده از سرور وب) هم از اهمیت خاص خود برخوردار هستند. بهترین روشی که جهت انتخاب سخت افزار سرور دیتابیس می توانیم داشته باشیم. استفاده از best practice و استانداردهای معرفی شده توسط تیم مایکروسافت است که این انتخاب ها متناسب با حجم دیتابیس، تعداد تراکنش ها در ثانیه (TPS rate)، تعداد کاربران و ... معرفی خواهند شد. برخی از موضوعات مرتبط با تنظیمات سخت افزار که در این آموزش از مجموعه افزایش سرعت و کارایی کوئری و دیتابیس به آن خواهیم پرداخت شامل: پردازنده (CPU)، حافظه (Ram)، هاد دیسک (HDD)، شبکه (Network)، کارت شبکه (Network Adapter) هستند.

اما در مورد تنظیمات سطح سرور و دیتابیس و کوئری ها با توجه به گستردگی موضوعات و نرخ رخ دادن بیشتر مشکلات کارایی مرتبط با این قبیل موارد،سرفصل های متنوعی را خواهیم داشت. از تنظیم صحیح temp db در کاهش contention تا استفاده از دستورات sargable در کوئری ها.

• بعدی