خانه SQL Server حل هزینه Lookup با استفاده از مفهوم Cover Index SQL Server افزایش سرعت SQL Server نوشته شده توسط: میلاد فیروزی تاریخ انتشار: ۱۶ دی ۱۳۹۴ آخرین بروزرسانی: ۱۰ اردیبهشت ۱۴۰۱ زمان مطالعه: 7 دقیقه ۴ (۱) مقدمه اگر تا به حال به مسائل Tuning و Performance در زمینه ی Queryها پرداخته باشید احتمالا با Execution Plan سر و کله زده اید تا ببینید کدام قسمت از Queryتان مشکل دارد و باید آن قسمت را اصلاح کنید و یا دنبال راه حل Indexی باشید. همان طور که می بینید در این Query تقریبا چیزی حدود ۵۰ درصد هزینه صرف Key Lookup شده است ، حال می خواهیم ابتدا ضمن بررسی کردن دلیل به وجود آمدن Lookup راه حل برطرف نمودن آن را نیز ارائه دهیم. Lookup چیست؟ خوب Lookup به معنای گشتن دنبال چیزی می باشد ، در Lookup اتفاقی که می افتد این است که SQL Server برای پیدا کردن یک یا چند فیلد نمی تواند از Index تعریف شده استفاده کند و باید برای یافتن آن فیلد و یا فیلدها دوباره در خود جدول بگردد.همانطور که می دانید دو نوع Index داریم ، Clustered و Non Clustered. چون مبحث امروز در مورد Indexها نیست سعی می کنم خیلی خلاصه مفهوم این دو Index را بررسی کنم. در نوع Clustered اتفاقی که می افتد این است که SQL چینش فیزیکی رکوردها را مرتب می کند و چون بحث مرتب سازی می باشد رکوردها تنها می توانند بر اساس یک فیلد مرتب شوند ، در نتیجه Clustered Index روی همان فیلد تعریف می شود ، پس از تعریف Primary Key هم یک Clustered Index تعریف می شود که رکوردها را بر اساس آن Key مرتب می کند. در نو Non Clustered روی یک یا چند فیلد می توانیم Index بگذاریم و اتفاقی که می افتد این است که این فیلد ها توسط SQL در مکانی دیگر کپی می شوند و با توجه به ساختاری به نام B-Tree مرتب می شوند. خوب Non Clustered index می تواند روی جداول Heap (جدولی که هیچ نوعی از Index روی آن تعریف نشده است) و یا جداول Clustered تعریف شود. در هر دوی آن ها ما تعداد محدودی از فیلدها را در Index داریم و اگر در شرط SELECT QUERY به فیلدهای بیشتری نیاز باشد SQL مجبور می شود برای یافتن آن فیلدها به خود جدول مراجعه کند و به دنبال آن فیلدها “بگردد” که به این کار به اصطلاح Key Lookup می گویند. اگر این گشتن روی یک Clustered Table باشد چون به دنبال کلید Indexی که خود جزو Non Clustered نیز هست می گردد به آن Key Lookup گفته می شود و حال اگر این Non Clustered روی Heap Table تعریف شده باشد SQL مجبور است تمامی جدول را برای یافتن فیلد اضافی بگردد که به این عمل هم RID Lookup گفته می شود. چگونه می توان Lookup را برطرف کرد؟ ما می توانیم به Non Clustered بگوییم که به جز آن چند فیلدی که در جایی دیگر کپی شده اند یک یا چند فیلد دیگر را نیز در کنار آن ها نگهداری کند به نحوی که این فیلدها در نحوه مرتب سازی تاثیری ندارند و فقط به عنوان فیلدهای اضافی به مابقی فیلدها چسبیده اند ، با این روش SQL وقتی شرط SELECT را چک می کند دیگر نیازی ندارد به خود جدول سر بزند و هرآنچه نیاز دارد در کنار بقیه فیلدها پیدا می کند و هزینه صرف شده جهت Lookup صرفه جویی می شود به این نوع از Indexها Covered Index گفته می شود. حال به مثال زیر دقت کنید در این مثال از دیتابیس AdventureWorks2012 استفاده می کنیم ، می خواهیم دستور زیر را اجرا کنیم ، قبل آن STATISTICS IO را فعال می کنیم تا تعداد صفحات خوانده شده را بتوانیم بررسی کنیم ، SET STATISTICS IO ON; SELECT CustomerID, OrderDate FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] WHERE CustomerID = 11000; خوب حالا نتایج STATISTICS IO را ببینیم ، Table ‘SalesOrderHeader’. Scan count 1, logical reads 11, physical reads 0, read-ahead reads برای این واکشی ۱۱ صفحه خوانده شده است. حال نتیجه Execution Plan را با هم ببینیم ، مشاهده می شود که ۶۸ درصد هزینه صرف Key Lookup شده است و با اینکه Non Clustered Index تعریف کرده بودیم هنوز هزینه ی زیادی بابت Key Lookup پرداخت می شود. حال Index قبلی را Drop کرده و Index جدیدی با استفاده از مفهوم Covered Index ایجاد می کنیم ، حال دوباره نتیجه STATISTICS IO و Execution Plan را ببینیم ، Table ‘SalesOrderHeader’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID; CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_OrderDate ON Sales.SalesOrderHeader(CustomerID, OrderDate); مشاهده می شود که علاوه بر حذف شدن هزینه Key Lookup تعداد صفحات خوانده شده نیز به عدد ۲ کاهش یافت. چه رتبه ای می‌دهید؟ میانگین ۴ / ۵. از مجموع ۱ اولین نفر باش معرفی نویسنده مقالات 8 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده میلاد فیروزی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ yahyaee.ali ۱۷ / ۰۹ / ۹۷ - ۰۱:۲۳ با سلام مقاله تحسین برانگیزی بود یه سوال: به جای استفاده از الگوی Cover Index بهتر نبود الگوی Included Columns پیشنهاد می شد چون با این الگوی جدید تر می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند. برداشت من این بود که به طور ضمنی در اولین کامنتی که استاد عزیز جناب مهندس طاهری هم گذاشته اند به این مطللب اشاه شده بود با سپاس پاسخ به دیدگاه yahyaee.ali ۱۷ / ۰۹ / ۹۷ - ۰۱:۲۳ با سلام مقاله تحسین برانگیزی بود یه سوال: به جای استفاده از الگوی Cover Index بهتر نبود الگوی Included Columns پیشنهاد می شد چون با این الگوی جدید تر می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند. برداشت من این بود که به طور ضمنی در اولین کامنتی که استاد عزیز جناب مهندس طاهری هم گذاشته اند به این مطللب اشاه شده بود با سپاس پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۱۱ / ۰۳ / ۹۵ - ۰۹:۱۰ با سلام و خسته نباشید خدمت آقای میلاد فیروزی از بابت این مقاله خیلی خوب ممنونم لطف کردید. با تشکر از شما پاسخ به دیدگاه مصطفی عینی ۲۶ / ۱۱ / ۹۴ - ۰۹:۰۹ ممنون پاسخ به دیدگاه عاطفه حسن پور ۲۹ / ۱۰ / ۹۴ - ۰۷:۵۹ با سلام وتشکر از مقاله بسیار و عالی و جذابی که داشتید واقعا بعد از مدت ها که اومدم شگفت زده شدم بابت این همه مطلب عالی پاسخ به دیدگاه مسعود طاهری ۲۱ / ۱۰ / ۹۴ - ۰۶:۲۴ یکی از بزرگترین معایب جداول Heap مسئاله Forwarding Pointer است. یکی از دلیل استفاده از Forwarding Pointer جلوگیری از بروزرسانی ردیف‌های ایندکس‌های Non Clustered بهنگام انتقال row اصلی به data page دیگر پس از بروزرسانی می‌باشد. اما این مسئاله (Forwarding Pointer) یک ایراد بزرگ به سیستم تحمیل می کند زمانی که SQL Server از IAM Page برای استخراج Pageهای وابسته به یک Heap Table استفاده می کند ممکن است به ازای برخی از Pageها چندین بار عملیات خواندن تکرار شود و این یعنی تحمیل IO اضافی به سیستم. پاسخ به دیدگاه Hamid J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۷:۰۳ با سلام بعد از روزها دوری باز هم برگشتم. البته این نکته رو بگم که SQL Server یک ستون مخفی برای جداول Heap قرار می دهد به نام RID که این مقدار شامل FileID, PageID, SlotID است. عملیات RID Lookup تمامی جدول را اسکن نمی کند بلکه دقیقا برای یافتن آن داده به آدرس آن مراجعه می کند و هزینه ی RID Lookup و Key Lookup دقیقا یکی است. نکته دوم اینکه شما می توانستید ستون OrderDate را در یک ایندکس دیگر قرار دهید تا از قابلیت Index Intersection استفاده کنید تا اگر احیانا یک Query دیگر از این ستون خواست استفاده کنه عملیات Seek انجام بشه. در این مثال شما اگر ما بر اساس OrderDate فیلتر کنید احتمال استفاده نکردن از Index Seek به دلیل نداشتن Index Edge Key بسیار بالا است. پاسخ به دیدگاه میلاد فیروزی ۲۱ / ۱۰ / ۹۴ - ۰۲:۲۰ جناب فرد ممنون از اطلاعات ازشمندتون نکاتی که در مورد FileID, PageID, SlotID گفتید رو نمی دونستم تشکر می کنم پاسخ به دیدگاه Hami J. Fard ۲۱ / ۱۰ / ۹۴ - ۰۸:۴۴ خواهش می کنم. امیدوارم که استفاده کرده باشید. پاسخ به دیدگاه میلاد فیروزی ۲۰ / ۱۰ / ۹۴ - ۰۹:۴۱ ممنون از متذکر شدن نکته ارزشمندتون جناب تجویدی و آقای محمد قطعا که زمانی به فکر هذف هزینه Lookup می افتیم که ارزشش رو داشته باشه و هزینه ی زیادی برداشته باشه اگر دقت کرده باشید من گفتم اگر Non Clustered روی یک جدول Heap تعریف شده باشد ممکن است حالتی باشد که به دلیل تعریف نادرست Index این اتفاق بیوفتد و SQL کل جدول را جست و جو کندفرض کنید یک فیلد Non Clustered روی یک جدول Heap تعریف کرده باشیم , این اتفاق گاها به دلیل درک نادرست از مفهوم Index رخ می دهد پاسخ به دیدگاه محمد ۲۰ / ۱۰ / ۹۴ - ۱۲:۵۴ با عرض سلام خدمت همه دوستانآقای فیروزی عزیز تشکر از مقاله ای که ارائه کردید. ۲ نکته وجود داشت که لازم میدونم به اون اشاره ای داشته باشم. ۱-همونطور که دوست خوبمون آقای تجویدی به اون اشاره داشتن SQL Optimizer مجبور نیست کل جدول را جستجو کند بلکه فقط اون ستونی رو که توسط Clustered یا NonClulustered پوشش داده نشده را در سطح Data Page جستجو میکند. ۲-آقای تجویدی عزیز صحبت شما در مواقعی که تعداد رکوردهای جداول کم باشد صحت دارد و در تعداد رکورد های میلیونی نتیجه عکس خواهید گرفت.برای نمونه اگر شما تست هایی را انجام داده باشید زمان کامپایل جداول heap در تعداد رکورد کم (در حد ۱۰۰ تا ۲۰۰ هزار تا)به نسب جداول غیر Heap کمتر است ولی read Ahead از جداولی که دارای key Lookup هستند بیشتر است.و این عمل موجب می شود که در تعداد رکوردهایی گفته شده اختلاف ۲ تا ۳ درصدی بین RID یا Key Lookup بوجود آید.با تشکر پاسخ به دیدگاه علی تجویدی ۲۰ / ۱۰ / ۹۴ - ۰۹:۳۳ با سلامممنون از مقاله مفیدجمله شما که در زیر آورده شده صحیح نیست“حال اگر این Non Clustered روی Heap Table تعریف شده باشد SQL مجبور است تمامی جدول را برای یافتن فیلد اضافی بگردد که به این عمل هم RID Lookup گفته می شود. “تمامی جدول را نمی گردد و در خیلی موارد RID Lookup هزینه کمتری نسبت به Key Lookup دارد.با تشکر پاسخ به دیدگاه 1 2
