اندازه‌های متغیر IO در SQL Server

مقدمه

این مقاله به شما نشان می‌دهد که چگونه NTFS داده‌ها را ذخیره می‌کند، معنای واحد تخصیص NTFS چیست و چگونهSQL Server
IO هایی با اندازه متغیر را انجام می‌دهد.

چگونه NTFS داده‌ها را روی دیسک ذخیره می‌کند؟

جدول فایل اصلی (MFT) ساختار داده‌ای است که فایل‌ها و دایرکتوری‌ها را در NTFS توصیف می‌کند. در شکل۱، می‌توانید ببینید که یک رکورد MFT دارای بخش‌های متعددی است که متادیتا‌های مربوط به فایل و نشانگرهای بلوک‌هایی را که فایل را تشکیل می‌دهند را توصیف می‌کند. یک بلوک که در ویندوز به آن خوشه نیز می‌گویند، انتزاعی است از یک یا چند ساختار فیزیکی (سکتور‌ها یا صفحات، بسته به رسانه) که توسط دیسک ارائه می‌شود.

یک بلوک یا خوشه، واحد تخصیص غیر قابل تقسیم از یک سیستم فایل است و اندازه آن قابل تنظیم است. در NTFS، به نام اندازه واحد تخصیص (AUS) شناخته می‌شود و یک ویژگی قابل تنظیم از سیستم فایل است. به طور پیش‌فرض، ۴ کیلوبایت است و می تواند به اندازه ۲ مگابایت نیز باشد. از آنجایی که یک بلوک یک واحد تخصیص است، اگر یک فایل ۱ بایت و اندازه واحد تخصیص سیستم فایل هم ۱ بایت باشد، دقیقاً یک بلوک یا خوشه در سیستم فایل را اشغال می‌کند. با رشد حجم فایل، بلوک‌ها یا خوشه‌های بیشتری برای نمایش فایل اختصاص داده می‌شود. ساختار داده MFT ردیابی می‌کند که کدام بلوک‌ها یک فایل را می‌سازند. تخصیص دهنده بلاک سیستم فایل سعی می‌کند اطمینان حاصل کند که بلوک‌ها از نظر فیزیکی روی دیسک در مجاورت هم هستند و آن‌ها را در اجراها با هم گروه‌بندی می‌کند.
بهترین حالت برای اندازه واحدهای تخصیص NTFS در سیستم‌های فایل همه منظوره، ۴ کیلوبایت در نظر گرفته می‌شود. همچنین برای SQL Server بهترین حالت ۶۴ کیلوبایت در نظر گرفته می‌شود. شاید از خود بپرسید چرا برای SQL Server، سایز متفاوتی در نظر گرفته می‌شود. در ادامه مقاله به این سوال خواهیم پرداخت.

چرا SQL Server به اندازه واحد تخصیص NTFS اهمیت می‌دهد؟

در داخل ساختار SQL Server، ساختار داده‌ای وجود دارد که برای تخصیص استفاده می‌شود. این ساختار داده Extent نام دارد. Extent یک ساختار داده ۶۴ کیلوبایتی است که دنباله‌ای متشکل از هشت صفحه ۸ کیلوبایتی را ارائه می‌دهد.
اغلب تصور می‌شود که SQL Server تمام IO های دیسک را در عملیات به سایز Extent انجام می‌دهد و به همین دلیل است که بهترین حالت واحدهای تخصیصNTFS ، ۶۴ کیلوبایتی در نظر گرفته می‌شود. SQL Server همه IO ها را در IO های سایز Extent انجام نمی‌دهد. واحد تخصیص NTFS واحد تخصیص بلوک‌ها یا خوشه‌ها در سیستم فایل است و از محدوده صفحات یا بخش‌های روی دیسک تشکیل شده است.
دلیل اصلی اهمیت دادن SQL Server به اندازه واحد تخصیص NTFS زمانی است که یک Extent به سایز ۶۴ کیلوبایت ایجاد و روی دیسک نوشته می‌شود، یک واحد تخصیص NTFS به سایز ۶۴ کیلوبایت، تضمین می‌کند که تمام هشت صفحه داخل Extent به صورت فیزیکی در یک واحد تخصیص NTFS نوشته شده و از نظر فیزیکی روی دیسک در مجاورت هم قرار گیرند. زمانی که دیسک‌ها می‌گردند، این موضوع بسیار مهم است، زیرا هِد باید حرکت کرده و پلاتر باید روی بخش مناسب دیسک بچرخد و سپس طیف وسیعی از صفحات را در یک تراکنش دیسک بخواند. همچنان مفاهیم بیشتری در این رابطه وجود دارد که در ادامه مقاله به آن‌ها خواهیم پرداخت.

اندازه‌های متغیر IOدر SQL Server

اندازه IO های صادر شده توسط SQL Server بر اساس عملیات انجام شده متغیر است، IO ها می‌توانند از ۵۱۲ بایت تا ۸ مگابایت باشند. برای جزئیات بیشتر در مورد اندازه‌های مختلفIO در SQL Server می‌توانید تحقیق کنید چون از حد این مقاله فراتر است و در این مقاله ما بر روی خواندن read-ahead تمرکز خواهیم کرد.
خواندن read-ahead در SQL Server به این صورت عمل می‌کند که بر اساس منطق داخل SQL Server یک سری صفحات را از یک فایل داده در یک تراکنش دیسک می‌خواند، تا زمانی که صفحات خوانده شده از نظر فیزیکی روی فایل داده به هم پیوسته باشند. اگر از نظر فیزیکی با هم پیوسته نباشند، داده‌ها برگردانده می‌شوند و تراکنش دیسک دوم صادر می‌شود. read-ahead اگر در حال اسکن جدول باشید عالی عمل می‌کند، SQL Server سعی می‌کند تا ۵۱۲ کیلوبایت را در یک تراکنش دیسک بخواند و با رفت و برگشت کمتر به دیسک، با سریع بالاتر، حجم داده‌های بیشتری را انتقال می‌دهد.

پیش‌نیازهای آزمایش

بسیار خوب، بیایید آزمایش را شروع کنیم. Perfmon را روی سرور باز کنید (می‌توانید این کار را به صورت محلی یا به صورت ریموت انجام دهید). شمارنده‌ها را اضافه کنید، میانگین بایت بر انتقال (Bytes/Transfer) دیسک که اندازه IO است و انتقال بر ثانیه (Transfers/sec) دیسک که IOP است. آن‌ها را در قسمت PhysicalDisk Object پیدا خواهید کرد. برای بازتولید دقیق نسخه نمایشی زیر، این کار را با یک پایگاه داده با یک فایل در یک حجم واحد امتحان کنید. همچنین به فاکتور مقیاس در نمودارهای زیر توجه کنید. برای بازتولید این نمودارها، برای میانگین بایت بر انتقال دیسک مقیاس ۰۰۰۱/۰ و برای انتقال بر ثانیه دیسک ۰۱/۰ خواهد بود.

خواندن اطلاعات از دیسک با غیرفعال شدن Read-Ahead

با پیکربندی Perfmon، به نمونه‌ای از انواع IO در SQL Server نگاهی می‌اندازیم، اندازه آن‌ها را بررسی می‌کنیم و تأثیر اندازه IO بر تعداد IO های صادر شده را اندازه‌گیری کنیم. در کد زیر، ما در حال انجام اسکن جدول هستیم. در اسکن جدول معمولاً read-ahead خواندن را آغاز می‌کند. اندازه خواندن read-ahead از ۱۲۸ کیلوبایت تا ۵۱۲ کیلوبایت بر اساس نحوه چیدمان داده‌ها بر روی دیسک متغیر است. برای اولین آزمایش، خواندن read-ahead را با Trace Flag 652 غیرفعال می‌کنیم. این کار به SQL Server می‌گوید که داده‌ها را به اندازه صفحه ۸ کیلوبایتی در هر تراکنش دیسک بخواند. این روال به لحاظ کارایی اصلا مطلوب نیست زیرا SQL Server باید برای هر صفحه در جدول، یک بار رفت و برگشت به فضای ذخیره‌سازی را انجام دهد. کد را بررسی می‌کنیم

DBCC DROPCLEANBUFFERS
GO
USE TPCH100 --change this to your DB
GO
DBCC TRACEON(652,-1)
GO
SELECT * FROM CUSTOMER OPTION (MAXDOP 1) --change this to a large table
GO
DBCC TRACEOFF(652,-1) --be sure to run this after your test to turn read ahead back on
GO

تذکر: لطفاً این کار را روی سرور تولیدی انجام ندهید زیرا ما Dropcleanbuffers را فراخوانی می‌کنیم.

هنگام اجرای این کد باید نتایجی مانند زیر را برای این تست مشاهده کنید. به مقادیر Last، Average، Minimum و Maximum توجه کنید، همه روی ۸،۱۹۲،۰۰۰ است که ۸ کیلوبایت است. SQL Server دقیقاً یک صفحه را در یک زمان می‌خواند. SQL Server به محل صفحه داده در فایل داده می‌رود، آن محدوده از داده‌ها را که فقط ۸ کیلوبایت است می‌خواند و آن داده‌ها را در یک تراکنش دیسک بر می‌گرداند. همچنین توجه داشته باشید که تعداد IO ها در طول مدت آزمایش حدود ۱۸۰۰ بار در هر ثانیه است.

خواندن داده‌ها از دیسک با فعال کردن Read-Ahead

الگوی IO دیگری را بررسی می‌کنیم. اسکن جدولی را اجرا می‌کنیم که read-ahead خواندن را آغاز می‌کند. (مطمئن می‌شویم که Trace Flag 652 برای این تست، خاموش است) همان طور که قبلاً اشاره شد، اندازه خواندن read-ahead بر اساس نحوه چیدمان داده‌ها بر روی دیسک، از ۱۲۸ کیلوبایت تا ۵۱۲ کیلوبایت متغیر است. کد این تست به صورت زیر است:

DBCC DROPCLEANBUFFERSGOUSE TPCH100 –change this to your DBGOSELECT * FROM CUSTOMER OPTION (MAXDOP 1) –change this to a large table GO

DBCC DROPCLEANBUFFERS
GO
USE TPCH100 --change this to your DB
GO
SELECT * FROM CUSTOMER OPTION (MAXDOP 1) --change this to a large table
GO

در نمودار زیر، خط سیاه میانگین سایز IO را نشان می‌دهد (Disk Bytes/Transfer). هر IO بسیار بزرگتر از آزمایش قبلی است و واریانس در سایز IO وجود دارد. read-ahead برای خواندن داده‌ها از روی دیسک، شروع به اجرا می‌کند، و وقتی IO در حال خواندن داده‌ها از روی دیسک با یک فرگمنت (صفحه غیر پیوسته) مواجه شود یا سایز داده‌ها به ۵۱۲ کیلوبایت می‌رسد، IO داده‌ها را از دیسک برمی‌گرداند. همچنین مقادیر زیر

Last ~= 279KB، Avgerage ~= 254KB، Minimum ~= 223KB، Maximum = 295KB

اندازه‌های واقعی تراکنش‌های دیسک هستند که توسط Read انجام می‌شوند. نکته دیگری که متوجه خواهید شد این است که میانگین بایت بر انتقال دیسک بسیار پایین می‌آید زیرا SQL Server داده‌ها را در بخش‌های بزرگ‌تر می‌خواند و بنابراین تعداد رفت و برگشت‌ها به دیسک را کاهش می‌دهد.

بیایید از زاویه دیگری به این موضوع نگاه کنیم. در نمودار زیر، یک حجم کاری را با Trace Flag 652 فعال کرده‌ایم، که خواندنread-ahead را غیرفعال می‌کند و باعث می‌شود SQL Server فرمان خواندن ۸ کیلوبایت در هر تراکنش دیسک را صادر کند. سپس در ساعت ۷:۳۷:۴۲ صبح، Trace Flag 652 را غیرفعال کردایم و در نتیجه خواندن read-ahead فعال می شود. نمودار کاملا گویا است. خط سیاه میانگین است. میزان بایت بر انتقال (Bytes/Transfer) دیسک هنگامی که خواندنread-ahead در SQL Server شروع به اجرای IOهای بزرگتر می‌کند، تراکنش‌های دیسک بسیار کمتری (Bytes/Transfer) انجام می‌دهد. زیرا هر IO، داده‌های بیشتری را از دیسک می‌خواند و تعداد رفت و برگشت‌ها به دیسک را کاهش می‌دهد. به همین دلیل است که read-ahead مهم است، داده‌های بیشتر، با سرعت بالاتر از دیسک به SQL Server انتقال می‌یابد و تعداد IO های کمتری نیز خواهیم داشت.

Backups

دوباره این آزمایش را با یک نوع IO دیگری امتحان کنیم. این بار ازBackup IO استفاده می‌کنیم. Backup IO ها، هنگام استفاده از تنظیمات پیش‌فرض، ۱ مگابایت در هر IO می‌خوانند. نمودار زیر نشان می‌دهد که اندازه IO برای Last، Average، Minimum و Maximum همگی ۱,۰۴۸,۵۷۶ است که به معنای ۱ مگابایت است. توجه داشته باشید که ضریب مقیاس در این نمودار ۰۰۰۰۱/۰ است.

خلاصه

در این مقاله به شما نشان داده شد کهSQL Server ، IO ها را در اندازه‌های متغیر بر اساس نوع IO تعیین شده انجام می‌دهد و اندازه IO مستقل از اندازه واحد تخصیص NTFS است. بنابراین هنگامی که با تیم ذخیره‌سازی یا فروشنده فضای ذخیره‌سازی خود صحبت می‌کنید، در مورد میانگین بایت بر انتقال دیسک و تعداد انتقال بر ثانیه دیسک مطمئن شوید، زیرا این اعداد با هم مهم هستند.

منابع

https://www.nocentino.com/posts/2021-12-10-sqlserver-io-size/