SQL Sentry V10: تجزیه و تحلیل فهرست

هنگامی که جیسون هال (sqlsaurus) در اوایل این هفته V10 را اعلام کرد ، اعتراف می کنم ، من برخی از غاز ها را داشتم. من می دانستم که ماژول جدیدی که ماهها روی آن کار می کردیم سرانجام در حال دیدن نور روز بود. هیچ چیز هیجان انگیز تر از آن نیست که یک بار دیگر درها را از ابزارهای بومی منفجر کند و ویژگی دیگری را برای شما فراهم کند که به شما کمک می کند تا پرس و جو را خیلی سریعتر تنظیم کنید.

شما می گویید یک ویژگی جدید؟

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

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

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

از کجا می توانم آن را تهیه کنم؟

قبل از ورود به هرگونه جزئیات ، باید به شما بگویم که چگونه می توانید به این ویژگی جدید دسترسی پیدا کنید ، در صورتی که می خواهید دنبال کنید. تجزیه و تحلیل شاخص برای اولین بار در SQL Sentry V10 در دسترس قرار گرفته است ، اما بعداً در نسخه بعدی ما از Plan Explorer ویژگی پرچمدار خواهد بود. اگر هنوز به V10 ارتقا نداده اید ، باید پست جیسون را برای یک مرور کلی عالی بررسی کنید ، و حتی فقط لیست تغییر را مرور کنید. مشتری SQL Sentry (هنوز) نیست؟امروز آن را برای چرخش بگیرید!

در حال حاضر ، برای دسترسی به برگه تجزیه و تحلیل شاخص ، باید یک برنامه جدید یا تخمین زده شده جدید ایجاد کنید. با توجه به عملکرد و سربار ذخیره سازی ، ضبط همه این داده ها با هر طرحی که ما ضبط می کنیم امکان پذیر نبود ، اما ما در حال بررسی روش های جمع آوری این اطلاعات ارزشمند برای شما در پس زمینه هستیم.(در برنامه جدید یکپارچه Explorer ، در صورت باز کردن طرحی که در SSMS یا نسخه قبلی Plan Explorer ایجاد شده است ، هیچ داده تجزیه و تحلیل شاخص نیز وجود نخواهد داشت ، زیرا این داده ها در زمان اجرا جمع آوری نشده است.)

You can start a new integrated Plan Explorer session in the SQL Sentry Client by going to File>جلسه جدید برنامه اکتشافی.

چه شکلی است؟

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

ایجاد روش dbo. indexanalysisdemominproductid int ، maxproductid int ، minsalesorderid int ، maxsalesorderid int به عنوان شروع nocount on ؛ProductId ، salesorderid ، salesorderdetailid ، carriertrackingnumber ، orderqty ، unitprice از فروش را انتخاب کنید. salesorderdetail که در آن productId بین minproductid و maxproductid و salesorderid بین minsalesorderid و maxsalesordordid ؛پایان

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

این به نظر می رسد یک برنامه بسیار کارآمد است. پس از همه ، این یک جستجوی است ، درست است؟خوب ، ما می توانیم با متن قرمز برای شمارش ردیف ببینیم که تخمین های ما از بین می رود. و هنگامی که نسخه SQL Server زیربنایی از آن پشتیبانی می کند ، برنامه گرافیکی ما اکنون هشدار مفیدی را برای مواردی مانند I/O باقیمانده نشان می دهد - اگر برای دیدن Tooltip حرکت کنید ، این را به سمت پایین پیدا خواهید کرد:

برای اطلاعات بیشتر ، به KB #3107397 مراجعه کنید ، "چه موقع به دنبال جستجوی نیست؟"توسط پل وایت ، و "یک ابرقدرت جدید برای تیونرهای SQL" توسط راب فارلی.

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

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

در نگاه اول بسیار زیاد است ، بنابراین اجازه دهید ابتدا مناطق مختلف عملکردی را در این برگه برجسته کنم ، سپس به هر یک حفر می کنم (دوباره ، برای بزرگنمایی کلیک کنید):

اکنون این چهار زمینه را که برجسته کرده ام توضیح خواهم داد:

ستون

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

سفارش توسط salesorderid desc ، salesorderdetailid ؛

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

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

شاخص های شبکه

در امتداد بالا می بینید که برای هر فهرست یک ستون نمایشگر وجود دارد (عکس صفحه من نام ستون را نشان نمی دهد ؛ آنها در سمت چپ کل شبکه هستند). اگر یک شاخص خوشه ای وجود داشته باشد ، ابتدا در سمت چپ ذکر می شود.

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

در مرحله بعد ، در صورت پیشنهاد ، شاخص های مفقود شده را نشان خواهیم داد. در بعضی موارد ، ما یک شاخص را توصیه خواهیم کرد ، اما اگر یک پیشنهاد شاخص گمشده ارائه دهد (در اینجا اینگونه نبود) همیشه به SQL Server تعویض خواهیم کرد. گاهی اوقات یک پیشنهاد شاخص از دست رفته از SQL Server * یا * یک فهرست توصیه شده از ما وجود نخواهد داشت.

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

صبر کنید ، این نمره ای که از آن صحبت می کنید چیست؟

من فکر کردم که من آن را بی سر و صدا معرفی می کنم تا اطمینان حاصل کنم که شما هنوز توجه می کنید ، حتی اگر این واقعاً یکی از مفاهیم تعیین کننده این ویژگی باشد.:-) با مقدار فوق العاده ای از ورودی از Paul White (sql_kiwi) ، ما یک الگوریتم برای کسب هر یک از شاخص ها ، بر اساس تعدادی از عوامل - چگالی/انتخاب ، خواه آنها از پرس و جو استفاده کنند ، خواه از جستجوی پشتیبانی کنند ، ایجاد کرده ایم. و انواع مختلفیرنگ پشت این نمره به روشی بصری مقیاس می شود ، حتی اگر قبلاً با نرم افزار ما آشنا نیستید: سبز خوب است ، زرد خوب است ، قرمز بد است. ممکن است توجه داشته باشید که تنها نمره مناسب در اینجا به شاخصی اختصاص داده شده است که حتی وجود ندارد.

همچنین توجه به این نکته حائز اهمیت است که نمره 100 ٪ همیشه قابل دستیابی نیست و الگوریتم امتیاز دهی می تواند با گذشت زمان تغییر کند.

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

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

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

نمره شاخص های موجود را بهبود بخشید

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

شما می توانید با تأثیر تخمین زده شده از تغییر شاخص های موجود در اینجا در شبکه آزمایش کنید تا ببینید که آیا ترفندهای جزئی به آن شاخص ها می توانند نمره خود را بهبود بخشند. از آنجا که تغییرات مفیدی را در یک شاخص ایجاد می کنید ، نمره آن را در بالای تغییر ستون مشاهده خواهید کرد. سپس می توانید با نگارش یک قطره و ایجاد مجدد یک شاخص با تغییرات خود ، تغییرات واقعی در پایگاه داده را در نظر بگیرید. به عنوان مثال ، بیایید بگوییم که من می خواستم شاخص IX_SALESORDERDETAIL_PRODUCTID (در حال حاضر در 80 ٪ به ثمر برسد) با استفاده از سه ستون دیگر مورد نیاز پرس و جو. این نمره شاخص را به 100 ٪ بهبود می بخشد ، همانطور که در سمت راست مشاهده می کنید (البته از یک صفحه جداگانه صفحه نمایش):

یک شاخص جدید با نمره بهتر ایجاد کنید

آخرین شاخص کاملاً خالی است به جز یک دکمه + در پایین. این ستون در اینجا است تا بتوانید فهرست خود را ایجاد کنید. در این حالت ، نکته زیادی وجود ندارد - امکان انجام بهتر از شاخص SQL Sentry که قبلاً توصیه کرده است امکان پذیر نیست. اما هنوز هم ، به خاطر آزمایش ، بیایید دکمه + را بزنید و یک شاخص جدید ایجاد کنیم که از شاخص توصیه شده تا حدی ، با یک ستون کلید متفاوت تقلید کند. در اینجا به راحتی می توان آن نمره 100 ٪ را نیز بدست آورد ، و من می توانم دکمه را در پایین ستون ضربه بزنم تا یک اسکریپت برای فهرست جدید تولید کنم:

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

آمار را مستقیماً به روز کنید

همچنین یک دکمه آمار به روزرسانی وجود دارد که به نظر می رسد: (). اگر متوجه شوم که آخرین بروزرسانی آمار مدتها قبل بوده است ، یا اگر از تغییر داده های اساسی اخیر مطلع شده ام ، می توانم آمار را به روز کنم. بسته به گزینه هایی که انتخاب می کنید ، ممکن است در مورد تأثیر عملکرد هشدار دهید:

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

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

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

پارامترهای شبکه

اگر قبلاً با Plan Explorer آشنا هستید ، این شبکه کمی آشنا به نظر می رسد ، زیرا مقادیر کامپایل شده و زمان اجرا هر پارامتر را نشان می دهد. اما در اینجا نیز قابلیت های اضافی نیز وجود دارد ، از جمله تعداد کل ردیف های پیش بینی شده ، و عملکرد تخمین زده شده که با آن مقادیر انجام می شود.

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

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

بافت نگار

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

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

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

هارون برتراند

Aaron (Aaronbertrand) یک پلت فرم داده MVP با تجربه صنعت است که قدمت آن به ASP Classic و SQL Server 6. 5 باز می گردد. او سردبیر وبلاگ مربوط به عملکرد ، sqlperformance. com است. وبلاگ هارون بر روی عادات بد T-SQL و بهترین شیوه ها و همچنین پوشش به روزرسانی ها و ویژگی های جدید در Plan Explorer ، Sentryone و SQL Server تمرکز دارد.