قاتلین ایندکس‌ ها در  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 آن را ارائه کرده است. پیشنهاد می‌کنم حتما این کتاب جذاب و فوق‌العاده را مطالعه کنید.
امیدوارم این مجموعه مقاله مورد استفاده شما قرار گرفته شده باشد. به‌زودی با مجموعه مقالات دیگری در حوزه ایندکس‌ها در خدمت‌تان خواهم بود.

چه رتبه ای می‌دهید؟

میانگین ۵ / ۵. از مجموع ۱

اولین نفر باش

title sign
دانلود مقاله
قاتلین ایندکس‌ ها در SQL Server [بخش پنجم]
فرمت PDF
7 صفحه
حجم 1 مگابایت
دانلود مقاله
title sign
معرفی نویسنده
title sign
دیدگاه کاربران

    • واقعا مجموعه مقالات مفیدی بود استاد
      خیلی از شما ممنونم

    • واقعا مجموعه مقالات مفیدی بود استاد
      خیلی از شما ممنونم

    • تو توضیحاتتون اشاره به این کردید که در بخش 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

    • با سلام و خسته نباشید خدمت شما
      از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم
      متشکرم

    • با سلام و خسته نباشید خدمت شما
      از مقاله بسیار عالیتون واقعا ممنونم این مقاله بسیار عالی بود دست شما درد نکنه واقعا مقاله های شما خیلی روان و ساده نوشته شده است من که لذت بردم
      متشکرم

    • سلام خسته نباشین آقای مهندس. بنده تو بعضی از سایت ها دیدم که نوشتن اگه تو 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/

    • سلام خسته نباشین آقای مهندس. بنده تو بعضی از سایت ها دیدم که نوشتن اگه تو 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/

    • خیلی ممنون مهندس واقعا سایتتون خیلی مفیده

    • با عرض سلام و خسته نباشید خدمت همه دوستان و مدیران و استادان این سایت خیلی خوب و مفید.
      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
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link
وبینار رایگان SQL Server؛ مسیری به سوی فرصت‌های شغلی بی‌شمار       پنج‌شنبه 30 فرودین ساعت 15
ثبت نام رایگان
close-image