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

چی کار کنیم سرعت کوئری ما بالا بره؟

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

چرا یک کوئری کند اجرا می شود ؟

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

1 – Hardware : ضعف سخت افزار ، مخصوصا RAM ، چون اسکیوال سرور باید دائم اطلاعات را از دیسک به رم و بالعکس منتقل کند. بهترین حالت زمانی است که میزان رم شما به اندازه داده های شما باشد.

تذکر : توصیه می شود که برای افزایش سرعت کوئری ها بهتر است دیسک های مربوط به سرور خود را SSD نمایید.

تذکر : هنگام RAID بندی سرور خود، بهتر است از RAID10  جهت افزایش کارایی و افزایش سرعت کوئری های خود استفاده کنید.

2 – Locking : بحث مربوط به Locking . بعضی از وقت ها کوئری ما یک ثانیه ایی اجرا می شود ولی بعضی از وقت ها کوئری ما پنج ثانیه ایی اجرا می شود. چرا ؟ چون سری دوم کوئری ما  گیر Locking افتاده است.

یعنی یه رکوردی که لاک هستش رو می خواهید بخونید می رید توی لاک. برای حل مشکل از دستور With(nolock) می توانیم استفاده نماییم.  یعنی برو رکوردهای قفل شده رو بخون که به این کار میگن Dirty Read. که این کار خطرناک است. چون اگر کاری که داره انجام میده رو Rollback کنه ، گزارش شما غلط خواهد بود.

3 – Bad Query Techniques : شما نه تنها باید بتوانید کوئری های صحیح بنویسید ، بلکه باید کوئری شما بهترین Performance را داشته باشد. مثلا توابع اسکالر باعث کندی می شود. یا مثلا به جای کوئری های تو در تو از Join جداول استفاده کنید.

4 – محدود کردن میزان رم مصرفی اسکیوال سرور :

برای اینکه بین سیستم عامل و اسکیوال سرور بر سر منابع خصوصا RAM مشکلی پیش نیاید بهتر است میزان رم مصرفی اسکیوال سرور را محدود کنیم. برای این کار حدودا میزان 4 گیگابایت را برای ویندوز کنار گذاشته و بقیه را به اسکیوال سرور اختصاص دهید.

کافیست میزان کل رم سرور مثلا فرض کنید میزان کل رم سرور 32 می باشد، را از عدد 4 کم کرده ، که میشود 28 گیگ. حال عدد 24*1024 می کنیم که می شود 24576  مگابایت ، حال این عدد 24576 را باید در قسمت  قرار دهیم.

5 – تنظیمات TempDB :

برای افزایش Performance اسکیوال سرور و کوئری خود می توانید دیتا فایل، دیتابیس سیستمی TempDB را به هشت فایل تقسیم نمایید. دقت کنید که حجم اولیه و میزان رشد این هشت فایل را یکسان قرار دهید(مثلا سایز اولیه و میزان رشد این هشت فایل را یک گیگ قرار دهید).

6 – جدا کردن data file ها و log file های یک دیتابیس :

از آنجایی که خواندن داده های data file به صورت random صورت می گیرد و خواندن اطلاعات log file به صورت sequential صورت می گیرد، بهتر است که این دو فایل در دو درایو جداگانه قرار بگیرند.

7 – Lack of Indexes  : کمبود ایندکس مناسب می تواند به شدت باعث کندی کوئری های شما بشود.

ساخت ایندکس مناسب حتی می تواند مشکلات فوق را نیز حل کند.

بهتر است از یک جدول سنگین برای بحث ایندکس ها استفاده کرد.تذکر :

تذکر : هر ستونی که براش شرط Where می گذارید، توصیه می شود با احتیاط، برایش ایندکس ایجاد کنید.

تذکر : روی ستون هایی که به عنوان Foreign Key می باشد، ایندکس قرار دهید.

تعریف ایندکس :

یکی از آبجکت های اسکیوال سرور بوده که از آن برای افزایش سرعت کوئری Select می توانیم استفاده کنیم.

مقایسه ایندکس های کلاستر و نان کلاستر :

ایندکس کلاستر (Clustered Index):

1 – داده های یک جدول بر اساس این ایندکس ذخیره و مرتب  می شوند.

2 – شما زمانی که یک کلید اصلی در یک جدول ایجاد می کنید ، اسکیوال سرور به طور اتوماتیک یک کلاستر ایندکس نیز ایجاد می نماید.

3 – هر جدول فقط می تواند یک ایندکس کلاستر داشته باشد.

ایندکس نان کلاستر (NonClustered Index):

1 – ایندکس نان کلاستر در یک ساختار جدا از جدول ذخیر می شود.

2 – داده ها در ایندکس نان کلاستر به کمک کلید اصلی (در اینجا اصطلاحا به آن Bookmark گفته می شود)، با جدول اصلی در ارتباط هستند.

3 – معمولا ایندکس هایی که در شرط به کار می روند ، برایشان ایندکس نان کلاستر می سازیم.

فرمول ساخت ایندکس کلاستر :

Create Clustered Index IX_ ColumnName ON TblTest(ColumnName)

فرمول ساخت ایندکس نان کلاستر :

Create NonClustered Index IX_ ColumnName ON TblTest(ColumnName)

تذکر : در فرمول بالا کلمه NonClustered اختیاری می باشد.

برای یادگیری بهتر ، به مثال زیر توجه فرمایید :

مثال اول : کوئری زیر را اجرا می کنیم. می دانیم روی فیلد OrderQty ایندکسی وجود ندارد. لذا مطابق شکل زیر می بینیم که پلن به سمت Cluster Index Scan می رود. باید کاری کنیم که به سمت Index Seek برود. راه حل مطابق شکل زیر در خط سبز هم در شکل زیر اشاره شده که باید روی فیلدی که در شرط کوئری وجود دارد ایندکس، زده شود.

تذکر : در شکل فوق آیکون اول 94 درصد 71 میلی ثاینه طول کشیده تا اطلاعات را بخواند.

تذکر : ایندکس بی نهایت سرعت را بالا می برد.

خب حالا ایندکس را می گذاریم :

Create Index IX_OrderQty ON [Sales].[SalesOrderDetail](OrderQty)

بعد از ایندکس گذاری می بینیم که پلن به سمت Index Seek (جستجو در ایندکس) می رود و کوئری فوق العاده سریع اجرا می شود. کلمه Seek یعنی جستجو کردن.

تذکر مهم : در شکل فوق در آیکون اول 83 درصد یک میلی ثانیه خواندن اطلاعات طول کشیده است.

برای حذف ایندکس باید کوئری زیر را اجرا کنیم :

Drop index  IX_OrderQty

وقتی جدولی ایندکس ندارد. باید از بالا تا پایین یکی یکی رکوردها رو بخواند که به این عمل Scan می گویند.

تذکر : پلن را از راست به چپ و از پایین به بالا می خوانیم.

تذکر : کلاستر ایندکس همان جدول است. وقتی جدولی کلید اصلی دارد ، براش کلاستر ایندکس ساخته می شود.

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

تذکر : ستون ShipCountry  (همان کشور مشتری)،ستونی است که کشور مقصد حمل در آن نشسته است. این ستون در جدول orders (به عنوان جدول Details)نیز آورده شده است. در واقع این ستون کشور مشتری است. طراح در جدول orders افزونگی ایجاد کرده است و آدرس مشتری را آورده و در جدول orders قرار داده است. چرا ؟ چون فرض کرده آدرس کشور مشتری ممکن است عوض بشه، برای حفظ سابق اش(وقتی رکورد Master با زمان عوض میشه، برای حفظ سابق میتونه رکورد Master رو کپی کنه توی Detail و اون فیلدهایی که لازم داری رو بزاری توی جدول Details)، اون فیلد یعنی ShipCountry را در جدول Details یعنی جدول Orders آورده است.

ایندکس مرکب :

در این مرحله ستون SpecialOfferID را نیز در Where اضافه می نماییم. در این صورت خواهیم دید که برای واکشی اطلاعات به سمت Clustered Index Scan خواهد رفت.

KeyLookup:

یعنی آدرس رکورد را از ایندکس بردار ، مراجعه کن به اصل جدول و رکورد را بخوان

تذکر : ترجیح مان این است که یک کوئری نیازی به KeyLookup نداشته باشد.

برای حل مشکل Clustered Index Scan شکل فوق ، روی ستون SpecialOfferID یک ایندکس نان کلاستر ایندکس می سازیم.

Create Index IX_SpecialOfferID ON [Sales].[SalesOrderDetail](UnitPrice)

در این صورت پلن به صورت زیر تغییر خواهد کرد و به سمت Seek خواهد رفت.

بعضی از کوئری ها زمان اجرای بسیار کوتاهی دارند ولی روزانه میلیون ها بار اجرا می شوند. پس باید باز هم روی Tuning آنها کار شود.

برای اینکه دو تا Seek فوق را داشته باشیم می توانیم کاری کنیم که یک Seek داشته باشیم. چه باید کرد؟ باید ایندکس مرکب بسازیم. به عبارت بهتر به دو تا جستجو، با یک جستجو اطلاعات را نمایش دهد.

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

 قانون : روی هر ستونی که جلو Where می آید توصیه می شود که یک ایندکس بسازید.

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

تذکر : برای ایندکس های ترکیبی فرقی نمی کند که اول ستون A را قرار دهیم یا اینکه اول ستون B را قرار دهیم.

همانطور که در شکل زیر می بینیم بعد از ایجاد ایندکس مرکب ، دو تا Seek ، تبدیل به یک Seek شدند.

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

1 – کوئری زیر را اجرا می کنیم : همانطور که می بینید از ایندکس اول یعنی IX_OrderQty استفاده خواهد کرد.

2 – کوئری زیر را اجرا می کنیم : همانطور که می بینید از ایندکس دوم یعنی IX_UnitPrice استفاده می کند.

در تست بعدی دو تا ایندکس تکی را پاک می کنیم ولی ایندکس مرکب را داریم.

الف) اگر در شرط  فقط OrderQty را قرار دهیم مطابق شکل زیر از ایندکس مرکب استفاده کرده و به سمت Index Seek خواهد رفت.

تذکر مهم : از اول کلیدهای یک ایندکس مرکب می توانیم برای جستجو استفاده نماییم. 

ب ) اگر در قسمت شرط بر روی ستون دوم یعنی SpecialofferID جستجو نماییم. در این صورت درسته که داره از ایندکس مرکب استفاده میکنه ولی به سمت Index Scan خواهد رفت. به عبارتی در این جا اسکیوال سرور هوشمندانه عمل کرده است و به جای اینکه کل جدول را جستجو کند، ایندکس را Scan می کند.(دقت کنید که Clustered Index Scan ننوشته است).

دقت کنید که Index Scan به مراتب بهتر از Clustered Index Scan می باشد.

تذکر مهم مهم : اگر شما می خواهید روی ستون های OrderQty و SpecialpfferID جستجو داشته باشید (شرط داشته باشید)، ایندکس مرکب بسازید. همچنین برای ستون دوم هم جداگانه ایندکس بسازید.

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

بررسی شرط OR :

تذکر مهم : ایندکس مرکب به درد شرط OR نمی خورد. برای شرط OR ایندکس های تکی می خواهیم. مطابق شکل زیر اگر OR بگذاریم ، کوئری سراغ ایندکس های تکی خواهد رفت.

تذکر مهم : برای شرط AND ، ایندکس مرکب نیاز است و برای شرط OR ایندکس تکی نیاز است.

آزمایش سوم : روی ستون LineTotal جمع می زنیم آنهم با شرط های زیر :

در این صورت به سمت Clustered Index Scan خواهیم رفت.(چون تا قبل از این روی ستون salesorderID که کلید اصلی بود ما count می زدیم).

Included Columns: ستون هایی هستند که جزء کلید نیستند اما در کنار کلید نگه داشته میشوند تا کوئری را بی نیاز کنند از مراجعه به جدول (عمل Lookup). تعداد این ستون های Included Columns می تواند در ایندکس نامحدود باشند.

تذکر مهم : به این ایندکسی که کوئری را از مراجعه به جدول بی نیاز می کند اصطلاحا Covering Index یا ایندکس پوشاننده می گویند.

خبر خوب : اسکیوال سرور اکثر مواقع به ما می گه که چه ایندکس هایی نیاز دارد.

نویسنده : مهندس غلامحسین عبادی