خانه SQL Server نحوه صحیح ایجاد Nonclustered Index SQL Server نوشته شده توسط: مهدی قپانوری ۱۶ مهر ۱۴۰۱ زمان مطالعه: 10 دقیقه ۵ (۶) هنگامی که در شرط کوئری شما دو ستون وجود دارد و عملگر بین آنها And هست، کدام ستون را در کلید Index اول قرار دهید، کوئری با Performance بهتری اجرا خواهد شد؟ لازم به ذکر است که، حالتهای متعددی وجود دارد و ما در این مقاله یک نمونه جالب را بررسی خواهیم نمود. کوئری زیر را در نظر بگیرید: Select ID, Location, UpVotes, DownVotes, LastAccessDate From dbo.Users Where UpVotes > 100 And DownVotes = 0 این کوئری رکوردهایی را نمایش می دهد که UpVotes آن ها بزرگتر از ۱۰۰ و DownVotes آنها برابر با صفر است. در اینجا هدف ما نمایش دادن اطلاعات کامل افراد نیست بنابراین فقط پنج ستون را در Select List قرار دادهایم. میخواهیم یک Nonclustered Index ایجاد نمائیم که سرعت اجرای کوئری مورد بحث را افزایش دهد. کدام ستون را در کلید ایندکس اول قرار دهیم کوئری Performance بهتری خواهد داشت؟ نکته: جدول Users در StackOverFlow Database دارای یک Clustered Index بر روی ستون ID است. راه حلی که در نگاه اول به ذهن میرسد این است که، تعداد رکوردهایی که UpVotes آنها بیشتر از ۱۰۰ است چند تاست؟ تصویر زیر نشان میدهد که تعداد این رکوردها برابر با ۱۷۶۶۰۰ است. همچنین تصویر زیر تعداد رکوردهایی که DownVotes آنها برابر با صفر است را نمایش میدهد: همان طور که در تصویر مشاهده مینمائید DownVotes بیش از هشت میلیون و شش صد هزار نفر برابر با صفر است. خب، به نظر میرسد بهتر است که SQL Server ابتدا صد و هفتاد هزار رکوردی، که UpVotes آنها بزرگتر از ۱۰۰ است را پیدا نماید و بعد در بین این رکوردها به دنبال آنهایی بگردد کهDownVotes شان برابر با صفر است. یعنی برای ایجاد ایندکس ابتدا ستون UpVotes آورده شود، دستور زیر این ایندکس را ایجاد مینماید: Create Index IXNCUpVotesDownVotes_Include On dbo.Users(UpVotes, DownVotes) Include (Location, LastAccessDate) تصویر زیر Plan اجرائی کوئری را نمایش میدهد: همان طور که مشاهده مینمائید Query Optimizer از ایندکسی که ایجاد کردیم به خوبی استفاده نمود. تصویر بعدی تعداد Logical Reads را نمایش میدهد: در واقع SQL Server برای اجرای این کوئری مجبور شده است که ۱۱۰۳ صفحه دیتا یا اصطلاحا Data Page را بخواند. آیا کوئری میتواند با Performance بهتری اجرا شود؟ پاسخ مثبت است. برخلاف تصور فوق، اگر ابتدا ستون DownVotes در کلید ایندکس قرار گیرد تعداد Logical Reads مربوط به کوئری کاهش خواهد یافت، دستور زیر ایندکس دیگری روی جدول Users ایجاد مینماید: Create Index IXNCDownVotesUpVotes_Include On dbo.Users(DownVotes, UpVotes) Include (Location, LastAccessDate) تفاوت این ایندکس با ایندکس قبلی این است که ترتیب ستونها در کلید ایندکس تغییر یافته است. تصویر زیر Plan اجرائی کوئری را نمایش میدهد: تصویر نشان میدهد که Query Optimizer از ایندکس جدید استفاده نموده و این ایندکس را جهت اجرای کوئری مناسبتر از ایندکس قبلی تشخیص داده است. تصویر بعدی تعداد Logical Reads را نمایش میدهد: تصویر نشان میدهد که تعداد Logical Reads برابر با ۲۰۹ است و تقریبا به یک پنجم کاهش یافته است. اینکه چرا این اتفاق میافتد نیاز به یک مقاله جداگانه دارد. هدف ما از این مقاله این است که نشان دهیم، عموما ایندکسی برای اجرای کوئری مناسب تر است که باعث شود SQL Server تعداد Data Page کمتری را بخواند و همان طور که در این مقاله اثبات شد اینکه کدام ستون در کلید ایندکس اول قرار بگیرد بسیار حائز اهمیت میباشد. نکته دیگر اینکه هر آنچه در نگاه اول منطقی به نظر میرسد لزوما بهترین حالت ممکن نیست و نیاز به تست دارد. این نکته نیز ارزش بیان نمودن را دارد که اگر ما یک کوئری را به شکل Stored Procedure بنویسیم و مقادیری که در شرط کوئری استفاده می شوند را به پروسیجر پاس نمائیم، آیا ایندکسی که ایجاد شده است به ازای همه مقادیر ورودی پروسیجر مناسب خواهد بود؟ بستگی دارد، زیرا با وجود ایندکس مشاهده میشود که اجرای یک پروسیجر گاهی سریع و گاهی کند است. بنابراین نباید انتظار داشت که وقتی یک ایندکس ایجاد مینمائیم اجرای پروسیجر هموراه سریع باشد زیرا که مشکل مرموزی به نام Parameter Sniffing بر سرعت اجرای Stored Procedure ها و Parameterized Queries تاثیر منفی میگذارد. با این وجود Index گذاری مناسب یکی از مهمترین راههای افزایش Performance کوئریها میباشد. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۶ اولین نفر باش برچسب ها # sql# SQL Server# SQL Server 2019# آموزش SQL# آموزش SQL Server# آموزش دستورات SQL Server دانلود مقاله نحوه صحیح ایجاد Nonclustered Index فرمت PDF 4 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 15 مقاله توسط این نویسنده محصولات 1 دوره توسط این نویسنده مهدی قپانوری مهدی قپانوری بیش از 6 سال است که در زمینههای نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزههایPerformance Tuning، Database Administration، Database Development و طراحی سیستمهای OLTP میباشد. مهدی علاقهمند به R&D در حوزههای نوین SQL Server است. پروفایل نویسنده معرفی محصول مسعود طاهری دوره آموزشی Performance Tuning در SQL Server (گروه 15) ثبت نام حضوری8.700.000 تومانثبت نام غیرحضوری5.700.000 تومان مقالات مرتبط ۱۰ اردیبهشت SQL Server استفاده از Credential و Proxy در SQL Server Agent حسن سلیمانی ۰۷ اردیبهشت SQL Server استفاده از Operator ها در SQL Server Agent حسن سلیمانی ۰۵ اردیبهشت SQL Server بررسی نحوه ایجاد Job در SQL Server حسن سلیمانی ۲۹ فروردین SQL Server آشنایی با بخش های مختلف SQL Server Agent حسن سلیمانی دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ ناشناس ۲۰ / ۰۷ / ۰۱ - ۰۴:۰۰ بسیار عالی استاد بزرگوار ۱ پاسخ به دیدگاه