خانه 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 2.190.000 تومان مقالات مرتبط ۰۲ اردیبهشت SQL Server تاثیر ایندکس های Computed-Column روی دستور DBCC CHECKDB تورج عزیزی ۰۱ اردیبهشت پایتون مقایسه پایتون با PHP | قدرت ۲ زبان برنامه نویسی محبوب در مقابل هم تیم فنی نیک آموز ۳۰ فروردین سایر هوش مصنوعی ChatGPT چیست؟ آشنایی با نحوه استفاده از چت جی پی تی تیم فنی نیک آموز ۲۸ فروردین سایر پایگاه داده چیست؟ آشنایی با انواع دیتابیس و کاربردهای آن ها تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ amir ۰۸ / ۱۱ / ۹۶ - ۰۸:۲۲ با عرض سلام و خسته نباشید خدمت همه دوستان و مدیران و استادان این سایت خیلی خوب و مفید. Sql server کلید اصلی جدول را به عنوان Clustered Index تعریف میکنه و من یه جدولی تو DataBase دارم که واکشی اطلاات از آن جدول را فقط از طریق کلید اصلی می خونم. آیامیتونم کلید اصلی جدول که Clustered Index است، به عنوان NonClustered Index هم تعریف کنم؟ اصولا اینکار درست هست یا نه؟ و در سرعت واکشی کوئری هاتاثیری خواهد داشت؟ پاسخ به دیدگاه مسعود طاهری ۰۸ / ۱۱ / ۹۶ - ۱۰:۱۶ بلی کلید اصلی را می توان به صورت Unique NonClustered تعریف کرد در این حالت باید حتما یک فیلد یا ترکیب یک فیلد را به عنوان Clustered Index تعریف کرد. در خصوص افزایش سرعت با تعریف مناسب کلاستر ایندکس به ازای فیلدها می توان به سرعت مناسبی دست یافت ما در دوره Performance & Tuning در SQL Server 2017 این موارد را به خوبی بررسی خواهیم کرد. برای کسب اطلاعات بیشتر می توانید به این لینک مراجعه کنید https://nikamooz.com/product/course-performance-tuning-sql-server/ پاسخ به دیدگاه jml.maryam ۲۷ / ۱۰ / ۹۶ - ۰۱:۰۸ سپاس فراوان از شما پاسخ به دیدگاه abdollahzadeh.b ۰۲ / ۰۹ / ۹۶ - ۰۱:۵۶ با سلام خدمت آقای شیشه بری من مجموعه مقاله هایتان را با عنوان قاتلین ایندکس پیگیری می کردم. باید بگم که مقاله های خوب و ارزشمندی اند. انتخاب عنوان خوب و متن ساده و روان. من این مطالب را در کتاب SQL Server Performance Tuning فصل یازدهمش خوانده بودم. از زحمت بسیاری که کشیدید کمال تشکر را دارم و منتظر مقاله های خوب آینده تان هستم. با احترام پاسخ به دیدگاه abdollahzadeh.b ۰۲ / ۰۹ / ۹۶ - ۰۱:۵۶ با سلام خدمت آقای شیشه بری من مجموعه مقاله هایتان را با عنوان قاتلین ایندکس پیگیری می کردم. باید بگم که مقاله های خوب و ارزشمندی اند. انتخاب عنوان خوب و متن ساده و روان. من این مطالب را در کتاب SQL Server Performance Tuning فصل یازدهمش خوانده بودم. از زحمت بسیاری که کشیدید کمال تشکر را دارم و منتظر مقاله های خوب آینده تان هستم. با احترام پاسخ به دیدگاه جواد اسماعیلی ۰۸ / ۰۶ / ۰۰ - ۰۵:۴۶ با سلام ممنون از پیگیری و محبت شما امیدوارم موفق و پیروز باشید “از طرف مهندس مهدی شیشه بری” پاسخ به دیدگاه 1 2