تاثیر Data Type Size بر سرعت اجرای کوئری ها

تاثیر Data Type Size بر سرعت اجرای کوئری ها

نوشته شده توسط: مهدی قپانوری
۲۳ مرداد ۱۴۰۰
زمان مطالعه: ۲۰ دقیقه
۳
(۵)

مقدمه

از مهم ترین دلایلی که نباید Data Type Size مربوط به ستون ها را بیش از اندازه مورد نیاز در نظر گرفت، بحث تخمین منابع جهت اجرای کوئری ها می باشد.

SQL Server منابع مورد نیاز جهت اجرای کوئری ها را بر اساس Size ستون های شرکت کننده در کوئری و نیز تعداد رکوردهایی که پردازش می شوند در نظر می گیرد و محتوای فیلدها را برای عمل تخمین منابع در نظر نمی گیرد.

در این مقاله قصد داریم به بررسی تاثیر Data Type Size بر Performance کوئری ها بپردازیم.

آیا در نظر گرفتن Data Type Size بزرگ سرعت اجرای کوئری ها را کاهش می دهد؟

برای یافتن پاسخ این سوال یک سری تغییرات غیر معمول بر روی جدول Users در دیتابیس StackOverflow اعمال می نماییم.اسکریپت زیر Data Type Size ستون  های DisplayName، Location و WebsiteUrlرا به اندازه های بسیار بزرگ تغییر می دهد:

USE StackOverflow;
GO
ALTER TABLE dbo.Users
  ALTER COLUMN DisplayName NVARCHAR(400);
ALTER TABLE dbo.Users
  ALTER COLUMN Location NVARCHAR(1000);
ALTER TABLE dbo.Users
  ALTER COLUMN WebsiteUrl NVARCHAR(2000);
GO

 بعد از اعمال تغییرات فوق کوئری زیر را اجرا می نماییم:

Select Top (250)
Id,
Age,
CreationDate,
DisplayName,
DownVotes,
EmailHash,
LastAccessDate,
Location,
Reputation,
UpVotes,
Views,
WebsiteUrl,
AccountId
From dbo.Users
Order By Reputation Desc

این کوئری اطلاعات مربوط به ۲۵۰ کاربر را که بیشترین Reputation را دارند، نمایش می دهد.
تصویر زیر بخشی از Plan اجرای کوئری را نمایش می دهد:همان طور که در تصویر مشاهده می نمایید SQL Server برای اجرای کوئری از Clustered Index Scan استفاده نموده و میزان داده تخمین زده شده برابر با ۲۹ GB است!
یعنی SQL Server حدس زده است که میزان ۲۹ GB داده را برای اجرای کوئری باید پردازش نماید.
تصویر زیر فضای مورد استفاده توسط جدول Users را نمایش می دهد:حجم جدول تقریبا ۱ GB است اما SQL Server حدس زده است که میزان ۲۹ GB داده را برای اجرای کوئری باید پردازش نماید.
آیا این باگ SQL Server است؟ به هیچ عنوان، این مشکل از طراحی اشتباه ناشی می شود، زیرا SQL Server جهت اجرای یک کوئریSize ستون های شرکت کننده در کوئری را در نظر می گیرد، نه محتوای فیلدها را.
در ادامه میزان Memory مورد نیاز جهت اجرای کوئری را بررسی می نماییم:تصویر فوق نشان می دهد که Desired Memory برای اجرای کوئری تقریبا برابر یا ۳۸ GB است. توجه داشته باشید که میزان RAM تخصیص داده شده به SQL Server برابر با ۱۰ GB می باشد.
مجددا Plan اجرای کوئری را بررسی می کنیم:به علامت Warning که بر روی اپراتور Sort وجود دارد توجه نمائید، تصویر بعدی متن مربوط به این Warning را نمایش می دهد:تصویر نشان می دهد که تعداد Page 25592 (هر Page برابر با ۸ KB) در دیتابیس سیستمی TempDB نوشته شده و Spill To Disk اتفاق افتاده است که این عمل سرعت اجرای کوئری را به شدت کاهش می دهد.

راه حل ها

در نگاه اول ممکن است تصور شود که، باید میزان RAM مربوط به SQL Server را افزایش داد اما مشکل مورد بحث به دلایل دیگری رخ می دهد. در ادامه به بررسی دو راهکار خواهیم پرداخت.

۱- فقط ستون ها و ردیف های مورد نیاز را واکشی نمائیم

SELECT TOP 36 DisplayName, Location, Reputation, Id
FROM dbo.Users
ORDER BY Reputation DESC;

تصویر زیر پلن اجرای دو کوئری را با هم مقایسه می نماید:همان طور که در تصویر مشاهده می نمایید در کوئری دوم Spill To Disk حذف شده است. در ضمن هزینه اجرای کوئری اول ۷۱ درصد است نسبت به ۲۹ درصد هزینه اجرای کوئری دوم.

۲- استفاده از Data Type Size صحیح

اسکریپت زیر اندازه ستون های تغییر یافته را به حالت Normal برمی گرداند:

ALTER TABLE dbo.Users
  ALTER COLUMN DisplayName NVARCHAR(40);
ALTER TABLE dbo.Users
  ALTER COLUMN Location NVARCHAR(100);
ALTER TABLE dbo.Users
  ALTER COLUMN WebsiteUrl NVARCHAR(200);
GO

تصویر زیر Plan اجرایی کوئری اول را نشان می دهد و می  توانید مشاهده نمائید که علامت Warning وجود ندارد و Spill To Disk حذف شده است. تصویر زیر نیز نشان می دهد که Desired Memory بعد از تغییر اندازه ستون ها برابر با ۵ GB است:همچنین تصویر بعدی نشان می دهد که میزان تقریبا سه و نیم گیگا بایت داده جهت پردازش توسط SQL Server تخمین زده شده است:

نتیجه گیری

Size ستون های جداول را بیشتر از آنچه که مورد نیاز است تعریف ننمائیم. حتی در بسیاری از موارد باید از سمت Application فورس شود که، ستونی مثل آدرس نباید بیشتر از Nvarchar(100) باشد. رعایت موارد ذکر شده نیاز به همکاری تیم Develop دارد. بسیاری از مشکلات مربوط به Performance به عدم طراحی صحیح بر می گردد.

 

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

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

اولین نفر باش

title sign
معرفی نویسنده
مهدی قپانوری
مقالات
15 مقاله توسط این نویسنده
محصولات
1 دوره توسط این نویسنده
مهدی قپانوری

مهدی قپانوری بیش از 6 سال است که در زمینه‌های نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزه‌هایPerformance Tuning، Database Administration، Database Development و طراحی سیستم‌های OLTP می‌باشد. مهدی علاقه‌مند به R&D در حوزه‌های نوین SQL Server است.

پروفایل نویسنده
title sign
معرفی محصول
مسعود طاهری

دوره آموزشی Performance Tuning در SQL Server (گروه 15)

ثبت نام حضوری8.700.000 تومان
ثبت نام غیرحضوری5.700.000 تومان
title sign
دیدگاه کاربران

    • با تشکر از مطالب آموزنده و مفیدتان

    • با تشکر از مطالب آموزنده و مفیدتان

    • سلام همکار عزیز
      تاثیر Data Type Size بر سرعت اجرای کوئری ها واقعا بارها به شخصه دیدم و خودم در طراحی جداولم روی این مسئله حساسیت زیادی دارم.
      تشکر فراوان از زحمات شما

    • سلام همکار عزیز

      تاثیر Data Type Size بر سرعت اجرای کوئری ها واقعا بارها به شخصه دیدم و خودم در طراحی جداولم روی این مسئله حساسیت زیادی دارم.

      تشکر فراوان از زحمات شما

    • سلام مهندس جان ، مقاله ی خوبی بود ، ممنون از زحمات شما.

    • سلام مهندس جان ، مقاله ی خوبی بود ، ممنون از زحمات شما.