در توسینسو تدریس کنید

و

با دانش خود درآمد کسب کنید

مستلزمات برای طراحی Index در SQL Server

در این مطلب می خواهم به مستلزمات یک طراحی عالی برای ایندکس ها در محیط SQL Server به پردازم.

Index چیست؟


یک نوع شیء در پایگاه داده است که تاثیر مستقیم بر روی روند عملیات فیزیکی و منطقی خواندن و نوشتن داده ها دارد که در آخر تاثیری بر روی بازدهی Query مورد نظر می شود. دو نوع Index در محیط SQL Server وجود دارد. نوع اول Clustered و نوع دوم Non-Clustered. نوع اول ساختار جدول مورد نظر را به طور کلی از Heap به B-Tree یا ColumnStore تغییر می دهد بدون آنکه از داده های آن کپی برداری کند. نوع دوم داده های جدول مورد نظر را در ساختار جدیدی از نوع B-Tree یا ColumnStore کپی کرده و مدیریت می کند. در نظر داشته باشید که Index با نوع ساختار B-Tree دارای چندین لایه است به نام های Root , Intemediate , Leaf.

مزایای استفاده از Index


Clustered Index بازدهی سرعت تغییرات بر داده ها را از طریق تغییر ساختار جدول مورد نظر بالا می برد. زمانی که این Clustered Index بر روی جدول ساخته شود تمامی داده های موجود به صورت فیزیکی مرتب شده و در فایل پایگاه داده ذخیره می شود. نوع Non-Clustered Index سرعت بازدهی واکشی داده ها را بالا برده و احتمال به وجود آمدن Deadlock و Blocking را از طریق کپی کردن داده ها به حداقل می رساند. برای بازدهی بهتر در ساخت Index بهتر است که Clustered Index ها قبل از هر نوع Non-Clustered Index ها بر روی جدول ساخته شوند. توجه داشته باشید که Non-Clustered Index ها می توانند در یک دیسک سخت دیگری ساخته شوند تا سرعت بازدهی واکشی داده ها را بالا ببرند.

هزینه استفاده از Index


جدول پایگاه داده که دارای یک یا چند Non-Clustered Index است نیاز به حافظه و فضای دیسک سخت بیشتری دارد و به دلیل وجود این نوع Index ها عملیات ورود و تغییرات داده ها در جدول زمان بیشتر خواهد برد. مدیریت داده ها و تراکنشها در جداولی که داده های آنها در حال تغییر است زمانبر خواهد بود. پس در نتیجه وجود Clustered Index هزینه بیشتری نسبت به Non-Clustered Index دارد.

پیشنهادات برای طراحی یک Index


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

پیشنهادات برای طراحی یک Clustered Index


در صورت ممکن این نوع Index اول بر روی جدول ساخته شود. همانظور که گفته شد از Narrow Index برای بالا بردن بازدهی واکشی داده ها استفاده شود. این نوع Index را بر روی ستونهایی که مقدار داده ای آنها به طور مستمر و با مدت زمان کوتاهی تغییر می کند. اعمال عملیات Rebuild باید به صورت یک تراکنش انجام شود در غیر اینصورت تمامی Non-Clustered Index های مرتبط با جدول دوباره سازی خواهند شد و این امکان دسترسی به جدول را کم می کند.

پیشنهادات برای طراحی یک Non-Clustered Index


در این نوع Index می تواند بر روی ستونهایی که مقدار داده ای آنها به صورت مستمر و با زمان کوتاهی تغییر می کنند ساخته شود و نوع داده ای ستون می تواند از نوع کرکتری باشد. دیگر ستونهای مورد نیاز نیز می تواند در قسمت Include قرار گیرند تا از عملیات Key Lookup و یا RID Lookup جلوگیری شود.

فشرده سازی Index


فشرده سازی Index در SQL Server 2008 ارایه شد و فقط در نسخه Enterprise و Developer موجود می باشد. این قابلیت باعث کاهش حجم داده ها شده و تعداد عملیات I/O را کاهش می دهد که این باعث بالا رفتن سرعت بازدهی واکشی داده ها می شود. این قابلیت یک مقدار بر استفاده از حافظه اصلی و پردازنده تاثیر می گذارد. توجه داشته باشید که فقط لایه Leaf در Index فشرده می شود.

Index بر روی جداولی با Partition


یک جدول می تواند با قابلیت Partitioning به قطعات کوچک منطقی تبدیل شود و این باعث می شود که Index مورد نظر بر روی هر یک از قطعات یا قسمتهای جدول ساخته و به صورت جداگانه مدیریت شوند.

با تشکر

حمید ج. فرد

متخصص پایگاه داده SQL Server

منبع: Fard Solutions

#index_در_sql_چیست #بهینه_سازی_index_های_sql_سرور #مزایای_استفاده_از_index_در_sql #طراحی_index_ها_در_sql_سرور #تنظیمات_index_در_sql_server #بالا_بردن_performance_در_sql_سرور #بالا_بردن_سرعت_sql_سرور #بهینه_سازی_index_ها_در_sql_سرور #بالا_بردن_کارایی_در_sql_سرور
عنوان
1 آیا NonClustered Columnstore Index گزینه مناسبی است؟ رایگان
2 مقایسه فنی Stream Aggregate و Hash Match رایگان
3 بهینه سازی بازدهی پایگاه داده SQL Server با Hypothetical Index رایگان
4 بازیابی داده های سالم از پایگاه داده خراب رایگان
5 تغییر در Optimizer Engine برای غیر فعال کردن مرتب سازی JOIN ها رایگان
6 اهمیت اجراء دستور DBCC CheckDB قبل از گرفتن فایل پشتیبان رایگان
7 اهمیت استراتژی پشتیبانگیری و بازیابی پایگاه داده در SQL Server رایگان
8 محدودیتهای بازدهی و راه حل آنها در SQL Server رایگان
9 مستلزمات برای طراحی Index در SQL Server رایگان
زمان و قیمت کل 0″ 0
3 نظر
فرشید علی اکبری

سلام

در صورت امکان از محدودیت های استفاده از ColumnStoredIndex در دو حالت (Clustered & Non Clustered) هم با توجه به تغییراتی که در SQL SERVER2016 داده شده است اطلاع رسانی کنید ممنون میشم.

با تشکر از شما.

حمید ج. فرد

اصولا Columnstore index ها در محیط های آنالایتیکس یا DW استفاده می شود. یعنی بر روی جداول Fact و Dim. اگر Query شما نیاز به خواندن تمامی رکوردها در جدول را دارد می توانید از این نوع Index استفاده کنید.

ولی اگر Query شما نیاز به خواندن یک قسمت کوچک از داده ها را دارد و همیشه به Index Seek برای بازیابی داده ها نیاز دارد باید از Rowstore Index یا ایندکس های معمولی استفاده شود.

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

حسن ضرابی

با سلام و خسته نباشید خدمت استاد عزیزم جناب آقای مهندس HamidJFard

بسیار لذت بردم فقط می تونم بگم عالی بود

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

ALTER INDEX <Index_Name> ON <Table_Name> REBUILD
ALTER INDEX <Index_Name> ON <Table_Name> REORGANIZE

با تشکر

نظر شما
برای ارسال نظر باید وارد شوید.
از سرتاسر توسینسو
تنظیمات حریم خصوصی
تائید صرفنظر
×

تو می تونی بهترین نتیجه رو تضمینی با بهترین های ایران بدست بیاری ، پس مقایسه کن و بعد خرید کن : فقط توی جشنواره تابستانه می تونی امروز ارزونتر از فردا خرید کنی ....