خانه SQL Server مشکل Overestimation در SQL Server SQL Server افزایش سرعت SQL Server نوشته شده توسط: مهدی قپانوری تاریخ انتشار: ۲۱ مهر ۱۴۰۰ آخرین بروزرسانی: 12 آذر 1403 زمان مطالعه: 5 دقیقه ۰ (۰) مشکل overestimatino در SQL Server، هنگامی که ایندکس به ازای شرط کوئری شما وجود دارد و Statistics در تخمین تعداد رکوردهای بازگشتی به SQL Server کمک می نماید، چگونه ممکن است SQL Server تعداد رکوردهای بازگشتی را به شکل عجیبی اشتباه تخمین بزند؟ Statistics بروز رسانی شده، اما مشکل مطرح شده وجود دارد! برای درک بهتر مفاهیم آموزش جامع SQL Server را مطالعه کنید. مشکل overestimatino در SQL Server جهت نمایش مشکل مطرح شده کوئری زیر را بر روی Stack Overflow Database اجرا می نماییم. SELECT * FROM dbo.Users WHERE CreationDate > '2018-05-01' AND Reputation > 100 ORDER BY DisplayName در کوئری فوق از SQL Server خواسته شده است، کاربرانی را نمایش دهد، که تاریخ ثبت نام آن ها، بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’ باشد و Reputation بیشتر از ۱۰۰ داشته باشند. SQL Server از طریق Statistics می داند که، کاربرانی زیادی بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’ ثبت نام نموده اند و نیز می داند که کاربرانی زیادی در کل جدول کاربران Reputation بیشتر از ۱۰۰ دارند. SQL Server این اطلاعات را به صورت جدا از هم دارد و در واقع Overlap بین دو شرط کوئری را نمی تواند تشخیص دهد. یعنی SQL Server نمی داند که چه تعدادی از کاربران Reputation بیشتر از ۱۰۰ دارند و بعد از تاریخ ‘۲۰۱۸-۰۵-۰۱’ ثبت نام نموده اند. در ادامه ایندکس هایی مناسب برای استفاده کوئری مورد بحث، بر روی جدول Users ایجاد می نمائیم: CREATE INDEX CreationDate_Reputation ON dbo.Users(CreationDate, Reputation); CREATE INDEX Reputation_CreationDate ON dbo.Users(Reputation, CreationDate); GO ما ایندکس های ترکیبی شامل هر دو ستونی که در شرط کوئری آمده اند، بر روی جدول Users ایجاد نمودیم. تصویر زیر Plan اجرای کوئری را نمایش می دهد:همان طور که در Plan اجرای کوئری مشاهده می شود SQL Server از ایندکس های ایجاد شده استفاده نکرد و عمل Clustered Index Scan را انجام داد. آیا SQL Server اشتباه نمود؟ بله، قطعا اشتباه نمود. SQL Server تخمین زد که کوئری ۴۲۴۲۵ رکورد را برمی گرداند در صورتی که تعداد رکورد های واقعی بازگشتی کوئری ۴۱۹ رکورد است. این همان مشکل Overestimation می باشد. برای اثبات بهینه نبودن Plan اجرایی که SQL Server انتخاب نمود، کوئری را Force می نمائیم که از ایندکس ایجاد شده استفاده نماید: SELECT * FROM dbo.Users WITH (INDEX = CreationDate_Reputation) WHERE CreationDate > '2018-05-01' AND Reputation > 100 ORDER BY DisplayName; تصویر زیر Plan اجرایی کوئری را نشان می دهدمشاهده می نمائید که Plan اجرای کوئری به صورت NonClustered Index Seek می باشد. تصویر زیر IO دو کوئری را مقایسه می نمایدکوئری اول ۱۴۲ هزار Logical IO و کوئری دوم هزار و هفتصد Logical IO دارد. استفاده از Hint Index در کوئری راه حل مناسبی نمی باشد، همچنین در این مقاله ما قصد نداریم به بررسی Tipping Point بپردازیم. (این موضوع ها نیاز به مقاله های جداگانه دارند.) پیشنهاد میکنیم برای درک بهتر مفاهیم دوره کوئری نویسی پیشرفته را مطالعه کنید. در این مثال ما می خواهیم با استفاده از بازنویسی کوئری مشکل Overestimation را اصطلاحا دور بزنیم. زیرا که Overestimation همچنان وجود خواهد داشت اما کوئری IO بالایی را بر سیستم تحمیل نمی کند و Plan اجرای کوئری به صورت Index Seek خواهد بود. (در مثال مورد بحث ما Index Seek بسیار بهینه تر از Index Scan است). توجه داشته باشیم که کلید کلاستر ایندکس جدول Users بر روی ستون ID می باشد که Primary Key جدول نیز هست و کلید کلاستر ایندکس Built-In در دل NonClustered Index ها وجود دارد. کوئری مورد بحث را به شکل زیر بازنویسی می نمائیم: ; WITH CTE AS ( SELECT Id FROM dbo.Users WHERE CreationDate > '2018-05-01' AND Reputation > 100 ) SELECT u.* FROM dbo.Users u INNER JOIN CTE c ON c.Id = u.Id ORDER BY DisplayName تصویر زیر تعداد Logical IO مربوط به کوئری را بعد از بازنویسی نمایش می دهد که برابر با ۱۷۷۲ می باشد: همچنین Actual Execution Plan کوئری بعد از بازنویسی در تصویر زیر نمایش داده شده است سخن پایانی مشکل overestimatino در SQL Server، ایندکس هایی که بر روی جداول ایجاد می شوند لزوما توسط SQL Server مورد استفاده قرار نمی گیرند حتما باید آنها را مانیتور نمائیم. همچنین موارد زیادی وجود دارد که Performance کوئری ها از طریق بازنویسی کوئری افزایش می یابد. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش دانلود مقاله مشکل Overestimation در SQL Server فرمت PDF 5 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 15 مقاله توسط این نویسنده محصولات 1 دوره توسط این نویسنده مهدی قپانوری مهدی قپانوری بیش از 6 سال است که در زمینههای نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزههایPerformance Tuning، Database Administration، Database Development و طراحی سیستمهای OLTP میباشد. مهدی علاقهمند به R&D در حوزههای نوین SQL Server است. معرفی محصول مسعود طاهری آموزش ۳ در ۱ 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 چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ زینب کاشانی ۱۲ / ۰۸ / ۰۰ - ۰۹:۴۹ عالی و کاربردی پاسخ به دیدگاه ترکنژاد ۰۷ / ۰۸ / ۰۰ - ۰۹:۲۹ ممنون خیلی عالی بود پاسخ به دیدگاه ناشناس ۰۶ / ۰۸ / ۰۰ - ۰۱:۰۱ ممنون بابت مطلبی که گذاشتین نکته جالبی بود پاسخ به دیدگاه ناشناس ۰۶ / ۰۸ / ۰۰ - ۰۱:۰۱ ممنون بابت مطلبی که گذاشتین نکته جالبی بود پاسخ به دیدگاه صالحی ۲۱ / ۰۷ / ۰۰ - ۰۹:۱۴ ممنون از مطالب آموزنده و کاربردی پاسخ به دیدگاه