خانه SQL Server قاتلین ایندکسها در SQL Server [بخش پنجم] SQL Server نوشته شده توسط: مهدی شیشه بری ۲۰ آبان ۱۳۹۶ زمان مطالعه: دقیقه ۵ (۱) مقدمه در قسمتهای قبلی (بخش اول، دوم، سوم و چهارم) مشاهده کردید که چگونه با انتخاب استراتژی نامناسب در نوشتن کوئریها میتوان زمینههای لازم برای عدم استفاده از ایندکسهای موجود را فراهم کرد. در بخش پایانی به سراغ یکی دیگر از قاتلین ایندکسها خواهیم رفت. توجه داشته باشید که موارد ذکرشده در این مجموعه مقاله، تنها بخشی از معضلاتی هستند که میتوانند موجب عدم استفاده از قابلیتهای ایندکسها در حین اجرای کوئریها شوند. نکته مهم: تمامی کوئریهای این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و ۲۰۱۷ اجرا شده است. قاتل شماره ۵ – (Data Conversion) یکی از مواردیکه میتواند تاثیری منفی در عملکرد ایندکسها داشته باشد زمانی است که نوعداده یا Data Type ستونها در حین عملیاتهای JOIN و یا در بخش WHERE مورد تغییر واقع شود. در این شرایط SQL Server مجبور است تا با تبدیل انواعداده، انطباق و تشابه میان نوعدادهی ستونهای شرکتکننده در کوئریها را برقرار سازد. اگر چنین تبدیلاتی بهصورت صریح (Explicit) در متن کوئری قید نشده باشد آنگاه SQL Server مجبور است تا چنین تبدیلاتی را در پسزمینهی فرایند اجرای کوئری و بهصورت ضمنی (Implicit) انجام دهد. این تبدیلات (منظور، انواع تبدیلات مجاز در SQL Server است) بر اساس اولویت انواعداده انجام خواهد شد. شما میتوانید این موضوع را در لینک زیر دنبال کنید: https://technet.microsoft.com/en-us/library/ms190309(v=sql.110).aspx اما این عملیات چگونه تاثیری منفی در روند اجرای یک کوئری خواهد داشت؟ پاسخ این سوال دقیقا همانند اتفاقی است که در بخش چهارم این مجموعه مقاله مورد بررسی قرار گرفت. بهعنوان مثال اگر قرار باشد ستونی را که دارای نوعداده VARCHAR بوده و از قبل بر روی آن ایندکسی پیادهسازی شده، در حین اجرای کوئری به نوع NVARCHAR تبدیل کنیم آنگاه در هنگام ساخت Plan کوئری بههیچ عنوان اطلاعات و آمار مربوط به ایندکسِ مقادیر آن ستون، در تصمیمگیری و تعیین میزان فراوانی و موقعیت مقادیر آن ستون، مفید واقع نخواهد شد. برای نشان دادن این موضوع، سناریو زیر را برایتان تشریح خواهم کرد: ابتدا بهکمک دستور Make Table Query، برخی از ستونهای جدول Person را در جدولی با عنوان PersonPerson کپی کنید. USE AdventureWorks2014 GO SELECT BusinessEntityID ,CAST(FirstName as varchar(50)) as FirstName ,CAST(MiddleName as varchar(50)) as MiddleName ,CAST(LastName as varchar(50)) as LastName INTO PersonPerson FROM Person.Person; GO سپس در جدول PersonPerson یک CLUSTEREDINDEX بر روی فیلد BusinessEntityID و یک Non-CLUSTEREDINDEX بر روی فیلد FirstName تعریف کنید. CREATE CLUSTERED INDEX IX_PersonPerson_ContactID ON PersonPerson (BusinessEntityID); CREATE INDEX IX_PersonContact_FirstName ON PersonPerson(FirstName); GO اکنون کوئریهای زیر را اجرا کنید و ببینید که چگونه عدم تطابق نوعداده یک فیلد میتواند اثرات منفی خود را بر روی کارآیی و نحوه استفاده از ایندکسها نشان دهد. SET STATISTICS IO ON; -- Query1 DECLARE @FirstName NVARCHAR(100) SET @FirstName = 'Gail'; SELECT FirstName, LastName FROM PersonPerson WHERE FirstName = @FirstName OPTION (RECOMPILE); GO -- Query2 DECLARE @FirstName VARCHAR(100) SET @FirstName = 'Gail'; SELECT FirstName, LastName FROM PersonPerson WHERE FirstName = @FirstName OPTION (RECOMPILE); GO در کوئری اول، متغیری از نوعداده NVARCHAR تعریف شده است. از طرفی نوعداده فیلد FirstName در جدول PersonPerson برابر با VARCHAR است. بنابراین این دو نوعداده همانند یکدیگر نبوده و میبایست این فیلد در حین پردازش کوئری از VARCHAR به NVARCHAR تبدیل شود. (این اتفاق با توجه به قواعدی انجام میشود که لینک آن را در ابتدای این مقاله برایتان گذاشتهام. در حقیقت اولویت نوعداده NVARCHAR بالاتر بوده؛ بنابراین در بخش WHERE تبدیل ضمنی صورت گرفته و فیلد FirstName به NVARCHAR تغییر مییابد) شما میتوانید این موضوع را در Plan اجرایی این کوئری مشاهده کنید که چگونه برای بازیابی رکوردها، عملیات تبدیلِ نوعداده بهصورت ضمنی انجام شده است.مجددا به Plan اجرایی کوئری اول نگاه کنید. در بخش SELECT این Plan، هشداری با این مضمون که تبدیل نوعداده از VARCHAR به NVARCHAR میتواند بر روی انتخاب اجرای کوئری از طریق عملیات Index seek تاثیرگذار باشد، بهچشم میخورد.اما در کوئری دوم، انطباق نوعداده میان متغیر تعریفشده و فیلد FirstName از جدول PersonPerson برقرار است و همین موضوع باعث میشود تا در هنگام ساخت Plan کوئری، از ایندکسِ تعریفشده (منظور IX_PersonContact_FirstName) بر روی فیلد FirstName استفاده شود.در شکل زیر، مقایسه تعداد Pageهای خواندهشده توسط این دو کوئری را مشاهده میکنید. کوئری اول صرفا به دلیل عدم تطابق میان نوعداده فیلد FirstName و متغیر تعریفشده، نتوانسته است از قابلیتهای ایندکس استفاده کند و در نتیجه برای اجرای کوئری، تعداد Pageهای بیشتری را خوانده است. پینوشت: این مجموعه مقاله، برداشت آزادی بود از فصل دوازدهم کتاب Expert Performance Indexing in SQL Server که انتشارات Apress آن را ارائه کرده است. پیشنهاد میکنم حتما این کتاب جذاب و فوقالعاده را مطالعه کنید. امیدوارم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. بهزودی با مجموعه مقالات دیگری در حوزه ایندکسها در خدمتتان خواهم بود. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش برچسب ها # create index sql server# Index در SQL Server# آموزش SQL Server# افزایش سرعت در Sql Server# انواع ایندکس در پایگاه داده# مفهوم Index در SQL Server دانلود مقاله قاتلین ایندکسها در SQL Server [بخش پنجم] فرمت PDF 7 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 1.790.000 تومان مقالات مرتبط ۰۹ خرداد مهندسی نرم افزار روش های مدرن سازی معماری نرم افزار علیرضا ارومند ۰۳ خرداد مهندسی نرم افزار چالش های مدرن سازی معماری نرم افزار و راهکارهای آن علیرضا ارومند ۲۴ اردیبهشت مهندسی نرم افزار مدرن سازی معماری نرم افزار علیرضا ارومند ۱۹ اردیبهشت هوش تجاری بهبود عملکرد Tabular Model در SQL Server با سه تکنیک کاربردی مسعود طاهری دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ Helia ۲۶ / ۱۲ / ۹۷ - ۰۲:۱۴ واقعا مجموعه مقالات مفیدی بود استاد خیلی از شما ممنونم پاسخ به دیدگاه Helia ۲۶ / ۱۲ / ۹۷ - ۰۲:۱۴ واقعا مجموعه مقالات مفیدی بود استاد خیلی از شما ممنونم پاسخ به دیدگاه امین خان ۰۲ / ۰۳ / ۹۷ - ۰۶:۱۳ تو توضیحاتتون اشاره به این کردید که در بخش where تبدیل ضمنی صورت گرفته و همین موجب افزایش IO میشه حالا اگه بیاییم برروی فیلدی از جنس DateTime این کوئری رو بزنیم باز هم با مشکل مواجه میشیم؟ DECLARE @NOW DATE=(SELECT GETDATE()) SELECT Id FROM dbo.tbSubmittedRank A WHERE CAST(A.DateResult AS DATE)=@NOW پاسخ به دیدگاه مسعود طاهری ۰۲ / ۰۳ / ۹۷ - ۰۷:۲۲ کوئری که شما نوشتید نمی تواند به سمت Index Seek هدایت شود از Function در قسمت Where Condition استفاده کرده اید. در دوره Performance & Tuning تمامی این نکات به دقت بررسی شده است پاسخ به دیدگاه امین خان ۰۲ / ۰۳ / ۹۷ - ۰۶:۱۳ تو توضیحاتتون اشاره به این کردید که در بخش where تبدیل ضمنی صورت گرفته و همین موجب افزایش IO میشه حالا اگه بیاییم برروی فیلدی از جنس DateTime این کوئری رو بزنیم باز هم با مشکل مواجه میشیم؟ DECLARE @NOW DATE=(SELECT GETDATE()) SELECT Id FROM dbo.tbSubmittedRank A WHERE CAST(A.DateResult AS DATE)=@NOW پاسخ به دیدگاه مسعود طاهری ۰۲ / ۰۳ / ۹۷ - ۰۷:۲۲ کوئری که شما نوشتید نمی تواند به سمت Index Seek هدایت شود از Function در قسمت Where Condition استفاده کرده اید. در دوره Performance & Tuning تمامی این نکات به دقت بررسی شده است پاسخ به دیدگاه حسن ضرابی ۱۰ / ۰۲ / ۹۷ - ۰۵:۵۹ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم متشکرم پاسخ به دیدگاه حسن ضرابی ۱۰ / ۰۲ / ۹۷ - ۰۵:۵۹ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم متشکرم پاسخ به دیدگاه amir ۱۹ / ۰۱ / ۹۷ - ۰۲:۴۰ سلام خسته نباشین آقای مهندس. بنده تو بعضی از سایت ها دیدم که نوشتن اگه تو SqlServer رایگان ، حجم پایگاه داده بیشتر از ۸ گیگابایت شد. بمرور زمان پایگاه داده دچار مشکل میشود. آیا این حرف درست است یا نه؟ اگر درست باشد چه راهکاری وجود دارد؟ خیلی ممنون پاسخ به دیدگاه آرزو محمدزاده ۱۹ / ۰۱ / ۹۷ - ۱۰:۳۲ با سلام و عرض ادب به نقل از مهندس مسعود طاهری خیر دیدگاه اشتباهاتی است اگر نسخه شما SQL Server Express Edition باشد بلی یه سری محدودیت ها مثل تعداد CPU Core , RAM و… دارید اگر نسخه SQL Server Express 2016 SP1 باشه اواضاع کمی بهتر است البته توجه داشته باشید ما در ایران بحث کپی رایت و… فعلا نداریم پیشنهاد من برای شما این است که در صورت امکان Enterprise Edition را استفاده کنید تا بتوانید از حداکثر امکانات سخت افزاری بهره بگیرید ….. کندی یک بانک اطلاعاتی عوامل زیادی دارد – مثل طراحی بد جداول – عدم وجود ایندکس – بلاکینگ طولانی – وجود deadlock – عدم وجود کویری های بهینه و… شما باید عوامل کندی را در بانک اطلاعاتی با استفاده از ابزارهایی مثل profiler و extended event پیدا کنید و مشکلات را رفع و رجوع کنید نیک آموز برای این کمظور دوره ای به نام performance tuning در SQL server دارد که این موارد در اون دوره به صورت حرفهای بررسی شده است برای کسب اطلاعات بیشتر درباره این دوره بر روی لینک زیر کلیک کنید https://nikamooz.com/product/course-performance-tuning-sql-server/ پاسخ به دیدگاه amir ۱۹ / ۰۱ / ۹۷ - ۱۱:۵۳ چشم حتما خیلی ممونم از جوابتون. پاسخ به دیدگاه amir ۱۹ / ۰۱ / ۹۷ - ۰۲:۴۰ سلام خسته نباشین آقای مهندس. بنده تو بعضی از سایت ها دیدم که نوشتن اگه تو SqlServer رایگان ، حجم پایگاه داده بیشتر از ۸ گیگابایت شد. بمرور زمان پایگاه داده دچار مشکل میشود. آیا این حرف درست است یا نه؟ اگر درست باشد چه راهکاری وجود دارد؟ خیلی ممنون پاسخ به دیدگاه آرزو محمدزاده ۱۹ / ۰۱ / ۹۷ - ۱۰:۳۲ با سلام و عرض ادب به نقل از مهندس مسعود طاهری خیر دیدگاه اشتباهاتی است اگر نسخه شما SQL Server Express Edition باشد بلی یه سری محدودیت ها مثل تعداد CPU Core , RAM و… دارید اگر نسخه SQL Server Express 2016 SP1 باشه اواضاع کمی بهتر است البته توجه داشته باشید ما در ایران بحث کپی رایت و… فعلا نداریم پیشنهاد من برای شما این است که در صورت امکان Enterprise Edition را استفاده کنید تا بتوانید از حداکثر امکانات سخت افزاری بهره بگیرید ….. کندی یک بانک اطلاعاتی عوامل زیادی دارد – مثل طراحی بد جداول – عدم وجود ایندکس – بلاکینگ طولانی – وجود deadlock – عدم وجود کویری های بهینه و… شما باید عوامل کندی را در بانک اطلاعاتی با استفاده از ابزارهایی مثل profiler و extended event پیدا کنید و مشکلات را رفع و رجوع کنید نیک آموز برای این کمظور دوره ای به نام performance tuning در SQL server دارد که این موارد در اون دوره به صورت حرفهای بررسی شده است برای کسب اطلاعات بیشتر درباره این دوره بر روی لینک زیر کلیک کنید https://nikamooz.com/product/course-performance-tuning-sql-server/ پاسخ به دیدگاه amir ۱۹ / ۰۱ / ۹۷ - ۱۱:۵۳ چشم حتما خیلی ممونم از جوابتون. پاسخ به دیدگاه amir ۰۹ / ۱۱ / ۹۶ - ۰۲:۳۶ خیلی ممنون مهندس واقعا سایتتون خیلی مفیده پاسخ به دیدگاه amir ۰۹ / ۱۱ / ۹۶ - ۰۲:۳۶ خیلی ممنون مهندس واقعا سایتتون خیلی مفیده پاسخ به دیدگاه 1 2