بهينه‌سازي Indexها براي هر نوع Qurey

[A.Farzin]

با سلام

هميشه مي‌گوئيم كه براي بهينه‌سازي Queryها، ابتدا بايد مهمترين و پراستفاده‌ترين Queryها را شناسايي و براي آنها Index خاص ساخت. با اين ترتيب كه معمولاً اگر 20% از مهمترين و پركاربردترين Queryها بهينه شوند، 80% به Performance ديتابيس افزوده خواهد شد (قاعده 20-80).
حالا فرض كنيد كه برنامه‌اي مثل General Report در اختيار كاربر گذاشته شده است و يا اينكه كليه فيلدهاي جدول در يك Grid در اختيار كاربر گذاشته شده و كاربر قرار است با Filter كردن ركوردهاي مورد نظر خود را در اختيار داشته باشد. در اينجا كه نمي‌دانيم كه چه Requestي به ديتابيس ارسال خواهد شد تا آنرا بهينه كنيم.
اگر براي هر فيلد يك Index بسازيم، گذشته از حجم بالائي كه ايندكسها اشغال مي‌كنند، در مواقعي كه كاربر در شرط Where خود چندين فيلد قرار مي‌دهد، باز هم Query Optimizer عملاً از ايندكس مورد نظر استفاده نكرده و Bookmark Lookup مي‌زند.
براي اين حالتها بايد چكار كرد؟
ممنون

[AminSobati]

سلام دوست عزیزم،
کاری که در این مواقع میشه کرد، مانیتور کردن کوئری کاربرها است. یعنی بالاخره کاربرها به تناسب نیاز، عموما کوئری ها Typical بوجود میارن که باز هم میشه با بررسی کردن Planشون، ایندکس پرکاربرد رو تشخیص داد. توصیه نمیکنم برای همه فیلدها بدون شناخت از نوع کوئری، ایندکس بسازین

[A.Farzin]

تا زماني كه در شرط WHERE فقط يك فيلد داشته باشيم، مسلماً SQL براي پاسخ به درخواست از آن ايندكس استفاده مي‌كند.
وقتي در شرط WHERE چندين فيلد با هم تركيب مي‌شوند، Query Optimizer بايد بهترين Index را شناسايي و در صورت وجود استفاده كند. در اين حالات اگر Index مناسب ساخته نشده باشد، اقدام به Clustered Index Scan مي‌كند كه براي پاسخ به درخواستها بايد بسته به حجم ديتاها زمان صرف كند.
من روي يك Table بزرگ تعداد 3 درخواست پركاربرد دارم. هر Indexي كه مي‌سازم باز هم از نظر SQL مناسب نيست و Clustered Index Scan مي‌كند. وقتي Clustered Index را براساس شرايط يكي از اين كوئري‌ها تغيير مي‌دهم آن كوئري در حد بسيار عالي بهينه مي‌شود و Clustered Index Seek مي‌كند. فقط مشكلي كه پيش مي‌آيد روي 2 كوئري ديگه است. هر ايندكسي كه مي‌سازم بازهم SQL سراغ Clustered Index Scan مي‌رود. واقعاً كلافه شده‌ام. كوئري‌هاي من روي حدود 15 ميليون ركورد انجام مي‌شود و چيزي حدود 40 فيلد در جلوي SELECT قرار مي‌گيرند در شرط WHERE هم گذشته از اينكه فيلدهاي مختلفي شركت دارد، نحوه شركت آنها با شروط <> و > و < هست.
سئوال
1) اگر به جاي اينكه روي بهينه كردن ايندكسها فكر كنم، آيا صحيح است كه هر دستور را به چند دستور شكسته و اطلاعات را با استفاده از شروط مناسبتر ابتدا در يك يا چند Table# ريخته و روي برخي از ستونهاي آن Table#ها Index لازم بسازم و مجدداً از جداول موقت SELECT بزنم؟
2) در اين حالت براي اجراي Transaction روي داده‌ها آيا بهتر است از تريگر استفاده كنم و يا اينكه از Join ؟
مشكلي كه حدس مي‌زنم با اين روش پيش آيد، Dedloack شدن زياد كاربران با همديگر است.
ممنون

[AminSobati]

سلام دوست عزیزم،
جمله اول جای تامل داره. داشتن فقط یک فیلد در WHERE باز هم تضمین کننده استفاده از ایندکس نیست. اینکه چند رکورد از WHERE استخراج میشه و اینکه آیا ایندکس، Query شما رو Cover میکنه یا خیر (فیلدهای مورد نیاز کوئری در ایندکس موجود باشه) بسیار تعیین کننده هستند.
با توجه به توصیف شما، حدس من این هست که تعداد رکورد استخراج شده از WHERE زیاد هست و چون ایندکس موجود، کوئری رو پوشش نمیده، برای استفاده از ایندکس SQL Server ناچار خواهد بود Lookupهای بسیاری روی اصل جدول بزنه که این کوئری رو کندتر خواهد کرد، لذا ترجیح میده Scan کنه. اگر از نسخه 2005 یا بالاتر استفاده میکنید، این امکان وجود داره که فیلدهای بیشتری در ایندکس Include بشن. ضمن اینکه Indexed View هم یک راه حل برای نسخه های پایین تر هست. اینکه اشاره کردین ساخت Clustered Index نتیجه رو بهتر میکنه، باعث میشه به حدسم مطمئن تر بشم، چون در این ایندکس همه فیلدها وجود دارند و کوئری کاملا Cover میشه
در مورد سوال اول، اگر هربار کوئری که قراره اجرا بشه، ایندکس بسازین، این نتیجه اش بدتر خواهد بود. ضمنا خود جداول موقتی به اندازه ای کافی I/O دارند که زمان شما تلف بشه!
در مورد سوال دوم، چون پیشنهاد میکنم از روش قبلی صرف نظر کنید، لذا روی این سوال بحثی نمیکنیم.

[A.Farzin]

با سلام مجدد،
این مطلب را در مورد Index Optimization در http://www.mssqlcity.com/tips/tipind.htm دیدم. برایم سئوال پیش آمد که مگر Primary Key نیست که در تمامی Indexهای دیگه شرکت می‌کند، چرا اینجا گفته که این نقش را Clustered Index بازی میکند؟
در بسیاری از جداول من، Primary Keyها روی فقط یک ستون Identity قرار گرفته و ایندکس آن از نوع Clustered نیست و فقط Unique بودن را فراهم میکنند. در حالیکه معمولاً Clustered Indexها روی چندین فیلد ساخته شده و اگر این گفته صحت داشته باشد، از آنجا که ایندکسهای دیگری هم دارم، پس چقدر فضای اضافه اشغال شده است.
میشه این موضوع را بیشتر توضیح دهید؟

Create clustered index based on a single column that is as narrow as possibly.
Because nonclustered indexes contain a clustered index key within their leaf nodes and nonclustered indexes use the clustered index to locate data rows, creating clustered index based on a single column that is as narrow as possibly will reduce not only the size of the clustered index, but all nonclustered indexes on the table also.


ممنون

[AminSobati]

موضوع اینه که PK به صورت پیش فرض Clustered میسازه مگر اینکه موقع ساخت خودتون نوع ایندکس رو Non Clustered تعیین کنید. جدولی که ایندکس Clustered داشته باشه، فیلد (یا فیلدهای) شرکت کننده در ایندکس که روی اون Sort انجام دادیم اصطلاحا Clustered Key گفته میشه و مقدار این کلید، در تمام ایندکسهای Non clustered شرکت میکنه. به خاطر همین گفته Narrow (کم حجم) باشه تا ایندکسهای Non Clustered هم کم حجم تر باشن و هم موقع اجرای کوئری، در صورتیکه نیاز هست از Non Clustered به اصل جدول (Clustered) رجوع بشه، تعداد بایتهای کمتری در این عملیات Lookup مورد مقایسه قرار بگیرند.

[A.Farzin]

با سلام و عرض ادب،
بحثی نیست که وقتی PK دارای ایندکس Clustered میگردد، مسلماً ایندکس Clustered که همان PK است در مابقی ایندکسها شرکت میکند.

PK به صورت پیش فرض Clustered میسازه مگر اینکه موقع ساخت خودتون نوع ایندکس رو Non Clustered تعیین کنید.
سئوال من دقیقاً همین است که در حالتی که PK ایندکس Non Clustered داشته و ایندکس دیگری به عنوان Clustered ساخته شود، کدامیک از این دو در سایر ایندکسهای Non Clustered شرکت میکنند؟
الف) ایندکس مربوط به PK (که یک ایندکس Non Clustered است)، یا
ب) ایندکس Clustered
پاسخ شما نشان میدهد که در تمام حالات (چه PK ایندکس Clustered داشته و چه Non Clustered)، همواره ایندکس Clustered در سایر ایندکسها (حتی ایندکس Non Clustered ساخته شده روی PK) شرکت میکند. آیا این برداشت صحیح است؟
ممنون

[AminSobati]

دقیقا گزینه دوم درسته

[A.Farzin]

با سلام،
پس با اين شرايط بهتره كه از ساخت Clustered Index روي چند ستون اجتناب كنيم. (مخصوصاً در جداول بزرگ)
ضمن اينكه، اين نكته همچنان برايم مبهم مانده كه آخه Clustered Index الزاماً Unique نيست در حاليكه PK هميشه Unique است.
براي شركت در ساير ايندكسها چرا يك ايندكس UnUnique بر ايندكس Unique‌ ترجيح داده شده است؟
ممنون

[A.Farzin]

با سلام مجدد،

لطفاً با توجه به تصوير پيوست، در مورد Bookmark Lookupي كه 90% از cost را شامل مي‌شود اظهار نظر فرمائيد.
مي‌بينيد كه Operetorهاي قبل از آن همگي با Clustered Index Seek و يا Index Seek هستند ولي باز هم يك Bookmark Lookup در زنجيره عمليات وجود دارد.

ممنون

[AminSobati]

بله درسته، داشتن Clustered Index روی چند فیلد چندان جالب نیست. ضمنا معمولا چون PK ما Clustered هست پس یونیک بودنش فراهمه.
در مورد Lookup، چون این عمل Cost قابل توجهی داره (مخصوصا در تعداد رکورد بالا) پس طبیعیه که درصد زیادی رو نشون بده. اگر فیلدهایی که از Lookup دارن استخراج میشن رو بتونین در ایندکس قرار بدین، بار قابل توجهی از کوئری کم میشه

[A.Farzin]

با سلام
اگر به جای ساخت Clustered Index روی چند فیلد، بیایم یک CONSTRAINT UNIQUE CLUSTERED روی آن فیلدها بسازیم (با این هدف که می‌خواهیم UNIQUE بودن داده‌ها روی چند فیلد کنترل شود) و در عین حال یک فیلد ID منحصربفرد هم داشته باشیم که PK جدول بوده و ایندکس آن از نوع NonClustered است، در این صورت:
آیا این CONSTRAINT UNIQUE CLUSTERED نقش Clustered Index را هم بازی می‌کند؟
ممنون

[AminSobati]

سلام،
فرقی نداره، Unique Constraint هم یک ایندکس یونیک میسازه نهایتا. پس میتونه Clustered یا Non Clustered باشه.