![قاتلین ایندکس‌ها در SQL Server [بخش پنجم]](https://nikamooz.com/wp-content/uploads/2017/11/index_killer5.jpg)
مقدمه:
در قسمت‌های قبلی (بخش اول، دوم، سوم و چهارم) مشاهده کردید که چگونه با انتخاب استراتژی نامناسب در نوشتن کوئری‌ها می‌توان زمینه‌های لازم برای عدم استفاده از ایندکس‌های موجود را فراهم کرد. در بخش پایانی به سراغ یکی دیگر از قاتلین ایندکس‌ها خواهیم رفت. توجه داشته باشید که موارد ذکر‌شده در این مجموعه مقاله، تنها بخشی از معضلاتی هستند که می‌توانند موجب عدم استفاده از قابلیت‌های ایندکس‌ها در حین اجرای کوئری‌ها شوند.
نکته مهم: تمامی کوئری‌های این مجموعه مقالات، بر روی دیتابیس AdventureWorks2016 و در محیط SQL Server Management Studio 2016 و 2017 اجرا شده است.
قاتل شماره 5 – (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 تعریف کنید.
[sql]
CREATE CLUSTERED INDEX IX_PersonPerson_ContactID ON PersonPerson (BusinessEntityID);
CREATE INDEX IX_PersonContact_FirstName ON PersonPerson(FirstName);
GO
[/sql]
اکنون کوئری‌های زیر را اجرا کنید و ببینید که چگونه عدم تطابق نوع‌داده یک فیلد می‌تواند اثرات منفی خود را بر روی کارآیی و نحوه استفاده از ایندکس‌ها نشان دهد.
[sql]
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
[/sql]
در کوئری اول، متغیری از نوع‌داده 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 آن را ارائه کرده است. پیشنهاد می‌کنم حتما این کتاب جذاب و فوق‌العاده را مطالعه کنید.
امیدوارم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. به‌زودی با مجموعه مقالات دیگری در حوزه ایندکس‌ها در خدمت‌تان خواهم بود.
13 دیدگاه
بهزاد عبداله زاده
با سلام خدمت آقای شیشه بری
من مجموعه مقاله هایتان را با عنوان قاتلین ایندکس پیگیری می کردم. باید بگم که مقاله های خوب و ارزشمندی اند. انتخاب عنوان خوب و متن ساده و روان. من این مطالب را در کتاب SQL Server Performance Tuning فصل یازدهمش خوانده بودم.
از زحمت بسیاری که کشیدید کمال تشکر را دارم و منتظر مقاله های خوب آینده تان هستم.
با احترام
جواد اسماعیلی
با سلام
ممنون از پیگیری و محبت شما
امیدوارم موفق و پیروز باشید
“از طرف مهندس مهدی شیشه بری”
jml.maryam
سپاس فراوان از شما
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/
amir
خیلی ممنون مهندس واقعا سایتتون خیلی مفیده
amir
سلام خسته نباشین آقای مهندس. بنده تو بعضی از سایت ها دیدم که نوشتن اگه تو SqlServer رایگان ، حجم پایگاه داده بیشتر از 8 گیگابایت شد. بمرور زمان پایگاه داده دچار مشکل میشود. آیا این حرف درست است یا نه؟
اگر درست باشد چه راهکاری وجود دارد؟
خیلی ممنون
آرزو محمدزاده
با سلام و عرض ادب
به نقل از مهندس مسعود طاهری
خیر دیدگاه اشتباهاتی است
اگر نسخه شما 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
چشم حتما خیلی ممونم از جوابتون.
حسن ضرابی
با سلام و خسته نباشید خدمت شما
از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم
متشکرم
امین خان
تو توضیحاتتون اشاره به این کردید که در بخش 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 تمامی این نکات به دقت بررسی شده است
Helia
واقعا مجموعه مقالات مفیدی بود استاد
خیلی از شما ممنونم