روش‌های بهبود عملکرد کوئری در SQL

سرور MS SQL یک RDMS (مدیریت پایگاه داده رابطه‌ای) پرطرفدار است و ویژگی‌ها و فواید بسیاری دارد که عملکرد کارآمد آن را تقویت می‌کند. مانند هر پلتفرم قوی، (بویژه پلتفرمی که به اندازه سرور SQL به پختگی رسیده باشد) شیوه‌های بهتری وجود دارند که به مرور توسعه داده می‌شوند. این شیوه‌ها بهترین عملکرد، از جمله بهبود عملکرد کوئری در SQL را فراهم می‌کنند.

Microsoft SQL Server برای هر شرکتی که از آن استفاده می‌کند، در امر مدیریت و ذخیره اطلاعات یک شرکت حائز اهمیت است. در تجارت، زمان مانند پول بسیار ارزشمند است، بنابراین هر شرکتی که برای عملکرد به اطلاعات متکی باشد (که در عصر دیجیتال تقریبا تمامی شرکت‌ها اینگونه هستند) نیازمند دسترسی سریع به آن اطلاعات دارد. به دلیل اینکه اطلاعات اغلب از طریق پایگاه داده و کوئری بدست می‌آیند، بهینه‌سازی عملکرد کوئری SQL امری حیاتی است.

از آنجایی که از هر 10 مشتری که به شرکت‌ها مراجعه می‌کنند، 7 مورد آن‌ها با مشکلات عملکرد SQL و شکایت کاربران روبرو می‌شوند، تجربیات زیادی در این زمینه جمع‌آوری شده که در ادامه آن‌ها را به اشتراک می‌گذاریم. کارهای گسترده‌ای در زمینه حذف ناکارآمدی ورودی/خروجی و ساده‌سازی ورودی/خروجی برای عملکرد بهینه انجام شده است. به ویژه در SQL کاهش تعداد ورودی و خروجی‌های تصادفی و ورودی/خروجی‌های “نویزی” بسیار مهم است، زیرا می‌توانند کاملا مشکل‌آفرین باشند. برخی از بهترین اقدامات برای بهبود عملکرد کوئری در SQL در این مقاله بیان شده‌اند، برخی از این روش‌ها ممکن است زمانبرتر باشند و حتی به یک مشاور SQL نیاز داشته باشند و حل برخی از آن‌ها ساده است.

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

روش‌های بهبود عملکرد کوئری در SQL

1. تنظیم کوئری‌ها

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

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

• ذینفعان مربوطه را شناسایی کنید
• بر نتایج کسب و کار تمرکز کنید
• برای ایجاد الزامات یک کوئری خوب، سوالات درست را بپرسید
• الزامات بسیار خاصی ایجاد کنید و آن‌ها را با ذینفعان در میان بگذارید

2. اضافه کردن حافظه

اضافه کردن حافظه تقریبا همیشه به عملکرد کوئری در سرور SQL کمک می‌کند، زیرا سرورSQL از چندین طریق از حافظه استفاده می‌کند که عبارتند از:

• حافظه نهان
• cache plan، جایی که برنامه‌های کوئری برای استفاده مجدد در آن ذخیره می‌شوند
• buffer pool، که صفحاتی که اخیرا در آن نوشته شده، ذخیره شده است
• مرتب‌سازی و مطابقت داده‌ها، که همه در حافظه اتفاق می‌افتد

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

نکاتی برای کاربران کنونی Condusiv: (1) اگر حافظه اضافی برای ارائه دارید 4 تا 16 گیگابایت حافظه اضافی در اختیار سرور SQL قرار دهید. (2) استفاده از حافظه MS-SQL ، حافظه اضافی برای سیستم عامل و نرم‌افزار ما باقی می‌ماند. توجه – نرم افزار Condusiv از هر آنچه توسط سیستم عامل بلااستفاده است، استفاده می‌کند. (3) اگر حافظه اضافی برای اضافه کردن وجود ندارد، از حافظه SQL استفاده کنید و 8 گیگابایت برای سیستم عامل و نرم‌افزار  بگذارید. این کار عملکرد را 30 تا 50 درصد افزایش می‌دهد زیرا SQL با استفاده از حافظه همیشه کارآمد نیست.

3. نگهداری از شاخص

شاخص‌ها یک منبع کلیدی برای افزایش عملکرد پایگاه داده سرور SQL هستند. نکته منفی این است که شاخص‌های پایگاه داده با گذشت زمان کاهش می‌یابد.

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

فرگمنت شدن ایندکس پایگاه داده به دو شکل وجود دارد:

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

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

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

اقدامات دیگری نیز وجود دارند، مانند یافتن و حذف شاخص‌های بلااستفاده، شناسایی و ایجاد شاخص‌های از دست رفته و بازسازی یا سازماندهی مجدد شاخص‌ها به صورت هفتگی.

توصیه می شود چنین اقداماتی را انجام ندهید ، مگر اینکه DBA باشید و یا درک کاملی از سرور SQL  داشته باشید.

4. افزودن اسپیندل یا درایو فلش اضافی

مانند افزایش حافظه، افزایش ظرفیت ذخیره‌سازی نیز می‌ تواند مفید باشد.

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

5. بهینه‌سازی زیر سیستم ورودی/خروجی

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

سه معیار اصلی در اندازه‌گیری عملکرد زیر سیستم ورودی/خروجی وجود دارد:

• تأخیر، زمانی است که برای تکمیل ورودی/خروجی لازم است.
• عملیات ورودی/خروجی در هر ثانیه که مستقیما با تاخیر ارتباط دارد.
• توان توالی، یعنی سرعت انتقال داده‌ها.

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

ابزار دیگری که می‌توان استفاده کرد ابزار ارزیابی ورودی/خروجی Condusiv است که برای شناسایی سیستم‌ها‌یی که از مشکلات ورودی و خروجی رنج می‌برند و سیستم‌هایی که چنین مشکلی ندارند استفاده می‌شود. این ابزار سیستم‌ها را طبق بیشترین مشکلات ورودی/خروجی شناسایی و رتبه‌بندی می‌کند و با شناسایی انحرافات عملکرد در هنگام بیشترین بارگیری، میزان این مسائل را در 11 معیار اصلی عملکرد اصلی نشان می‌دهد.

6. از نرم‌افزار سریع داده DymaxIO استفاده کنید

تعداد ورودی/خروجی‌هایی را که انجام می‌دهید کاهش دهید. به یاد داشته باشید، سریع‌ترین خواندن از دیسکی که می‌توانید انجام دهید، موردی است که به هیچ وجه انجامش نمی‌دهید.

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

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

علاوه بر این، هنگامی که ویندوز از لایه فیزیکی جدا می‌شود، از خواندن و نوشتن تصادفی بسیار کوچک که دارای کارآیی کمتری نسبت به خواندن و نوشتن مجاور بزرگتر هستند، استفاده می‌کند. عملکرد سرورSQL  نه یک بار، بلکه دو بار تاوان این کار را می‌دهد. اثر خالص این است که ویژگی‌های ورودی/خروجی بیش از آنچه که لازم است شکسته و تصادفی می‌شوند.

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

اگرچه سخت‌افزار می‌تواند به مشکلات عملکردی که در بالا گفته شد کمک کند، اما این عمل موقتی است، زیرا دلیل عدم کارایی ورودی/خروجی ویندوز بررسی نمی‌شود. بسیاری از سایت‌ها کشف کرده‌اند که نرم‌افزار داده سریع DymaxIO، در هر سرور ویندوز (مجازی یا فیزیکی) یک راه حل سریعتر و مقرون به صرفه است. DymaxIO نوشتن‌های کوچک را با نوشتن بزرگ، تمیز و مجاور جایگزین می‌کند تا با هر عملکرد ورودی/خروجی، بار بیشتری تحویل داده شود. ورودی و خروجی ذخیره‌سازی با ایجاد یک استراتژی tier cache 0 کاهش می‌یابد که بواسطه آن به طور خودکار خواندن را از حافظه بیکار و در غیر این صورت از حافظه استفاده نشده انجام می‌دهد. این نرم‌افزار خود را لحظه به لحظه تنظیم می‌کند تا فقط از حافظه استفاده نشده، استفاده کند.

استفاده از DymaxIO می‌تواند 50 درصد یا بیشتر عملکرد را بهبود بخشد، از جمله عملکرد جستجوی SQL. بسته به میزان DRAM موجود، بسیاری از سایت‌ها دو برابر یا بیشتر بهبود می‌یابند. تکنولوژی‌های Condusiv، که توسعه دهنده DymaxIO می‌باشند، در واقع یک ضمانت بازگشت سرمایه برای DymaxIO ارائه می‌دهند که بتوان سخت‌ترین چالش‌های عملکرد برنامه در سیستم‌های فشرده ورودی/خروجی مانند سرور SQL را حل کرد.

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