تاثیر 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 به عدم طراحی صحیح بر می گردد.