شاخص های غیر خوشه ای را با پیشنهادات شاخص گمشده تنظیم کنید

ویژگی شاخص های گمشده ابزاری سبک برای یافتن شاخص های گمشده است که ممکن است عملکرد پرس و جو را به میزان قابل توجهی بهبود بخشد. در این مقاله نحوه استفاده از پیشنهادات شاخص از دست رفته برای تنظیم مؤثر شاخص ها و بهبود عملکرد پرس و جو توضیح داده شده است.

محدودیت ویژگی شاخص گمشده

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

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

• پیشنهادات شاخص از دست رفته براساس تخمین های انجام شده در هنگام بهینه سازی یک پرس و جو واحد ، قبل از اجرای پرس و جو است. پیشنهادات فهرست گمشده پس از اجرای پرس و جو مورد آزمایش یا به روزرسانی قرار نمی گیرد.
• ویژگی شاخص گمشده تنها شاخص های Rowstore مبتنی بر دیسک مبتنی بر دیسک را نشان می دهد. شاخص های منحصر به فرد و فیلتر شده پیشنهاد نشده است. پیشنهاد شده است ، اما این پیشنهاد ترتیب آن ستون ها را مشخص نمی کند. برای کسب اطلاعات در مورد ستونهای سفارش ، به بخش پیشنهادهای شاخص Apply Missing این مقاله مراجعه کنید. پیشنهاد شده است ، اما سرور SQL در مورد اندازه شاخص حاصل ، در صورت پیشنهاد تعداد زیادی از ستون های شامل ، تجزیه و تحلیل هزینه و فایده ای را انجام نمی دهد.
• درخواست های شاخص از دست رفته ممکن است تغییرات مشابهی از شاخص ها را در همان جدول و ستون (های) در سراسر نمایش داده شود. مهم است که پیشنهادات شاخص را مرور کنید و در صورت امکان ترکیب شوید.
• پیشنهاداتی برای برنامه های پرس و جو بی اهمیت ارائه نشده است.
• اطلاعات مربوط به هزینه برای پرس و جوهای مربوط به محورهای نابرابری کمتر دقیق است.
• پیشنهادات برای حداکثر 500 گروه شاخص از دست رفته جمع آوری می شود. پس از رسیدن به این آستانه ، دیگر داده های گروهی از دست رفته جمع آوری نشده است.

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

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

مشاهده توصیه های فهرست گمشده

ویژگی شاخص های گمشده شامل دو مؤلفه است:

• عنصر MissingIndexes در XML برنامه های اعدام. این به شما امکان می دهد با همبستگی با همبستگی که بهینه ساز پرس و جو از دست رفته در سؤالاتی که برای آنها گم شده است ، در نظر بگیرید.
• مجموعه ای از دیدگاه های مدیریت پویا (DMV) که می توانند برای بازگشت اطلاعات در مورد شاخص های مفقود شده ، پرس و جو شوند. این به شما امکان می دهد همه توصیه های فهرست گمشده را برای یک پایگاه داده مشاهده کنید.

مشاهده پیشنهادات فهرست گمشده در برنامه های اجرا

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

• هنگام نوشتن یا تنظیم پرس و جو ، می توانید از SQL Server Management Studio (SSMS) برای نمایش برنامه اجرای تخمین زده شده بدون اجرای پرس و جو استفاده کنید ، یا پرس و جو را اجرا کنید و یک برنامه اجرای واقعی را نمایش دهید.، در صورت فعال بودن ، برنامه های اعدام را جمع آوری می کند.
• شما می توانید برنامه های اجرای ذخیره شده را با پرس و جو DMV مانند sys. dm_exec_text_query_plan شناسایی کنید.

به عنوان مثال ، می توانید از پرس و جو زیر برای تولید درخواست های فهرست گمشده در برابر پایگاه داده نمونه AdventureWorks استفاده کنید.

برای تولید و مشاهده درخواست های فهرست گمشده:

SSMS را باز کنید و یک جلسه را به نسخه خود از پایگاه داده نمونه AdventureWorks وصل کنید.

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

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

بر روی برنامه اجرای راست کلیک راست کرده و برنامه نمایش نمایش XML را انتخاب کنید. از منو

طرح اجرای XML به عنوان یک برگه جدید در داخل SSMS باز خواهد شد.

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

گفتگوی Find را با استفاده از میانبر Ctrl+F نمایش دهید.

جستجوی MissedIndex.

در این مثال ، دو عنصر MissedIndex وجود دارد.

• اولین فهرست گمشده نشان می دهد که پرس و جو ممکن است از یک فهرست در جدول شخصی استفاده کند. از جستجوی برابری در ستون StateProvinceID ، که شامل دو ستون دیگر ، شهر و postalcode است. در زمان بهینه سازی ، بهینه ساز پرس و جو معتقد بود که این شاخص ممکن است هزینه تخمینی پرس و جو را 34. 2737 ٪ کاهش دهد.
• شاخص دوم گمشده نشان می دهد که پرس و جو ممکن است از یک فهرست در جدول شخص استفاده کند که از جستجوی نابرابری در ستون FirstName پشتیبانی می کند. در زمان بهینه سازی ، بهینه ساز پرس و جو معتقد بود که این شاخص ممکن است هزینه تخمینی پرس و جو را 18. 1102 ٪ کاهش دهد.

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

مشاهده پیشنهادات فهرست گمشده در DMV

می توانید با پرس و جو از اشیاء مدیریت پویا ذکر شده در جدول زیر ، اطلاعات مربوط به فهرست های گمشده را بازیابی کنید.

پرس و جو زیر از DMV های شاخص گمشده برای تولید بیانیه های شاخص استفاده می کند. بیانیه های ایجاد شاخص در اینجا برای کمک به شما در ساخت DDL خود پس از بررسی کلیه درخواست های جدول به همراه شاخص های موجود در جدول ، به شما کمک می کند.

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

• هزینه تخمین پرس و جو از نمایش داده شدگان مرتبط با درخواست شاخص گمشده.
• تأثیر تخمین زده شده از افزودن شاخص. این برآوردی از میزان شاخص غیر خوشه ای است که هزینه پرس و جو را کاهش می دهد.
• مجموع اعدام های اپراتورهای پرس و جو (جستجو و اسکن) که برای نمایش داده شدگان مرتبط با درخواست شاخص گمشده اجرا شده اند. همانطور که در فهرست های مفقود شده با فروشگاه پرس و جو بحث می کنیم ، این اطلاعات به صورت دوره ای پاک می شود.

اسکریپت ایجاد شاخص در جعبه ابزار ببر مایکروسافت ، شاخص های گمشده DMV ها را بررسی می کند و به طور خودکار هرگونه شاخص پیشنهادی اضافی را حذف می کند ، شاخص های تأثیر کم را تجزیه می کند و اسکریپت های ایجاد شاخص را برای بررسی شما ایجاد می کند. همانطور که در پرس و جو فوق ، دستورات ایجاد شاخص را اجرا نمی کند. اسکریپت ایجاد شاخص برای SQL Server و Azure SQL به عنوان مثال مدیریت شده است. برای پایگاه داده Azure SQL ، اجرای تنظیم خودکار شاخص را در نظر بگیرید.

محدودیت های ویژگی شاخص گمشده و نحوه استفاده از پیشنهادات فهرست گمشده را قبل از ایجاد شاخص ها بررسی کنید و نام شاخص را برای مطابقت با کنوانسیون نامگذاری برای پایگاه داده خود تغییر دهید.

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

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

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

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

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

پیشنهادات فهرست گمشده را اعمال کنید

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

ساختار جدول پایه را مرور کنید

قبل از ایجاد شاخص های غیر خوشه ای در یک جدول بر اساس پیشنهادات فهرست گمشده ، فهرست خوشه ای جدول را مرور کنید.

یکی از راه های بررسی شاخص خوشه ای استفاده از روش ذخیره شده سیستم SP_HELPINDEX است. به عنوان مثال ، ما می توانیم خلاصه ای از فهرست ها را در جدول شخص مشاهده کنیم. با اجرای بیانیه زیر:

ستون index_description را مرور کنید. یک جدول می تواند تنها یک شاخص خوشه ای داشته باشد. اگر یک شاخص خوشه ای برای جدول اجرا شود ، index_description شامل کلمه "خوشه ای" خواهد بود.

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

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

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

اسکریپت های موجود در یک جدول

یکی از راه های بررسی تعریف شاخص های موجود در یک جدول ، توصیف فهرست ها با جزئیات اکسپلورر شیء:

1. Object Explorer را به نمونه یا پایگاه داده خود وصل کنید.
2. گره را برای پایگاه داده مورد نظر در Object Explorer گسترش دهید.
3. پوشه جداول را گسترش دهید.
4. جدول را که می خواهید از فهرست ها استفاده کنید ، گسترش دهید.
5. پوشه Indexes را انتخاب کنید.
6. اگر صفحه جزئیات Object Explorer در حال حاضر باز نشده است ، در منوی View ، Object Explorer را انتخاب کنید یا F7 را فشار دهید.
7. تمام فهرست های ذکر شده در صفحه جزئیات Object Explorer را با میانبر Ctrl+A انتخاب کنید.
8. روی هر نقطه در منطقه انتخاب شده راست کلیک کرده و فهرست فهرست اسکریپت گزینه منو را به عنوان انتخاب کنید ، سپس پنجره ویرایشگر پرس و جو را ایجاد کنید.

فهرست ها را مرور کنید و در صورت امکان ترکیب کنید

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

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

به عنوان مثال ، شما ممکن است یک جدول ، person. address ، با یک فهرست موجود در ستون کلیدی StateProvinceID داشته باشید. برای ستون های زیر ممکن است توصیه های فهرست گمشده برای شخص را مشاهده کنید.

• فیلترهای برابری برای StateProvinceId و City
• فیلترهای برابری برای StateProvinceId و City ، شامل PostalCode

اصلاح شاخص موجود برای مطابقت با توصیه دوم ، یک شاخص با کلیدهای مربوط به StateProvinceID و City از جمله PostalCode ، احتمالاً نمایش داده هایی را که هر دو پیشنهاد شاخص ایجاد می کند ، برآورده می کند.

مبادلات در تنظیم شاخص رایج است. این احتمال وجود دارد که برای بسیاری از مجموعه داده ها ، ستون شهر انتخابی تر از ستون StateProvinceID باشد. با این حال ، اگر شاخص موجود ما در StateProvinceID به شدت مورد استفاده قرار گیرد ، و سایر درخواست ها تا حد زیادی در هر دو StateProvinceId و City جستجو می کنند ، به طور کلی برای داشتن یک بانک اطلاعاتی به طور کلی پایین تر است که یک شاخص واحد با هر دو ستون در کلید داشته باشد ، هرچند که منجر به StateProvinceID شود ، اگرچه اینانتخابی ترین ستون نیست.

شاخص ها ممکن است به روش های مختلفی اصلاح شوند:

• می توانید از عبارت Create Index با بند DROP_EXISTING استفاده کنید. شما ممکن است بخواهید فهرست ها را پس از اصلاح تغییر نام دهید تا بسته به کنوانسیون نامگذاری شما ، نام آن را به طور دقیق توصیف کند.
• می توانید از عبارت Drop Index (Transact-SQL) استفاده کنید و به دنبال آن یک بیانیه فهرست ایجاد شده است.

ترتیب کلیدهای شاخص هنگام ترکیب پیشنهادات شاخص اهمیت دارد: شهر به عنوان یک ستون پیشرو با StateProvinceID به عنوان یک ستون پیشرو متفاوت است. در دستورالعمل های طراحی شاخص غیر خوشه ای بیشتر بدانید.

هنگام ایجاد شاخص ها ، استفاده از عملیات شاخص آنلاین را در صورت موجود بودن در نظر بگیرید.

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

تأیید کنید که آیا تغییر شاخص شما موفقیت آمیز است

تأیید اینکه آیا تغییرات شاخص شما موفقیت آمیز بوده است ، مهم است: آیا بهینه ساز پرس و جو با استفاده از شاخص های شما است؟

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