خانه SQL Server قاتلین ایندکس ها در 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 آن را ارائه کرده است. پیشنهاد میکنم حتما این کتاب جذاب و فوقالعاده را مطالعه کنید. امیدوارم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. بهزودی با مجموعه مقالات دیگری در حوزه ایندکسها در خدمتتان خواهم بود. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش دانلود مقاله قاتلین ایندکس ها در SQL Server [بخش پنجم] فرمت PDF 7 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۱۰ شهریور مهندسی داده NoSQL چیست؟ هر آن چیزی که باید بدانید! تیم فنی نیک آموز ۰۹ شهریور زبان های برنامه نویسی چگونه میتوان Blazor را برای ساخت برنامههای (PWA (App Web Progressive استفاده کرد؟ تیم فنی نیک آموز ۰۸ شهریور زبان های برنامه نویسی Eloquent در لاراول و جادوی کاربردیاش تیم فنی نیک آموز ۰۸ شهریور SQL Server اهمیت مانیتورینگ در 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 پاسخ به دیدگاه حسن ضرابی ۱۰ / ۰۲ / ۹۷ - ۰۵:۵۹ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم متشکرم پاسخ به دیدگاه حسن ضرابی ۱۰ / ۰۲ / ۹۷ - ۰۵:۵۹ با سلام و خسته نباشید خدمت شما از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم متشکرم پاسخ به دیدگاه 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 ۰۸ / ۱۱ / ۹۶ - ۰۸:۲۲ با عرض سلام و خسته نباشید خدمت همه دوستان و مدیران و استادان این سایت خیلی خوب و مفید. 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/ پاسخ به دیدگاه 1 2