خانه SQL Server تاثیر Data Type Size بر سرعت اجرای کوئری ها SQL Server افزایش سرعت SQL Server نوشته شده توسط: مهدی قپانوری تاریخ انتشار: ۲۳ مرداد ۱۴۰۰ آخرین بروزرسانی: ۲۲ شهریور ۱۴۰۲ زمان مطالعه: ۲۰ دقیقه ۳.۳ (۶) مقدمه از مهم ترین دلایلی که نباید 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 به عدم طراحی صحیح بر می گردد. چه رتبه ای میدهید؟ میانگین ۳.۳ / ۵. از مجموع ۶ اولین نفر باش دانلود مقاله تاثیر Data Type Size بر سرعت اجرای کوئری ها فرمت PDF ۶ صفحه صفحه حجم ۱مگابایت مگابایت دانلود مقاله معرفی نویسنده مقالات 15 مقاله توسط این نویسنده محصولات 1 دوره توسط این نویسنده مهدی قپانوری مهدی قپانوری بیش از 6 سال است که در زمینههای نرم افزار و بانک اطلاعاتی فعالیت مینماید. تخصص اصلی او در بانک اطلاعاتی SQL Server بوده و دارای تجربه در حوزههایPerformance Tuning، Database Administration، Database Development و طراحی سیستمهای OLTP میباشد. مهدی علاقهمند به R&D در حوزههای نوین SQL Server است. معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ صالحی ۲۷ / ۰۵ / ۰۰ - ۰۸:۴۲ با تشکر از مطالب آموزنده و مفیدتان پاسخ به دیدگاه محمدرضا خلیل زاده ۲۷ / ۰۵ / ۰۰ - ۰۰:۴۵ سلام همکار عزیز تاثیر Data Type Size بر سرعت اجرای کوئری ها واقعا بارها به شخصه دیدم و خودم در طراحی جداولم روی این مسئله حساسیت زیادی دارم. تشکر فراوان از زحمات شما پاسخ به دیدگاه محمدرضا خلیل زاده ۲۷ / ۰۵ / ۰۰ - ۰۰:۴۵ سلام همکار عزیز تاثیر Data Type Size بر سرعت اجرای کوئری ها واقعا بارها به شخصه دیدم و خودم در طراحی جداولم روی این مسئله حساسیت زیادی دارم. تشکر فراوان از زحمات شما پاسخ به دیدگاه ناشناس ۲۴ / ۰۵ / ۰۰ - ۰۳:۵۱ سلام مهندس جان ، مقاله ی خوبی بود ، ممنون از زحمات شما. پاسخ به دیدگاه