اسکریپت بدست آوردن میزان فضای تخصیص یافته LOB های یک دیتابیس
اسکریپت بدست آوردن میزان فضای تخصیص یافته LOB های یک دیتابیس در SQL Server و بررسی صفحات استفادهشده و رزروشده.
27 آبان 1405
لینک کوتاه
اسکریپت بدست آوردن میزان فضای تخصیص یافته LOB های یک دیتابیس
مدیریت فضای ذخیرهسازی در SQL Server یکی از موضوعات مهم در نگهداری و بهینهسازی دیتابیسها است.با افزایش حجم اطلاعات، بررسی میزان فضای اشغالشده توسط جدولها، ایندکسها و دادههای حجیم اهمیت بیشتری پیدا میکند.
یکی از بخشهایی که ممکن است سهم قابلتوجهی از فضای دیتابیس را به خود اختصاص دهد، ساختارهای LOB است.
این ساختارها برای نگهداری دادههایی استفاده میشوند که حجم آنها میتواند از ظرفیت معمول ذخیرهسازی درون ردیف بیشتر باشد.
با استفاده از اسکریپت بدست آوردن میزان فضای تخصیص یافته LOB های یک دیتابیس میتوان اطلاعات مناسبی درباره میزان فضای اختصاصیافته به این دادهها به دست آورد.
چنین گزارشی به مدیر دیتابیس کمک میکند تا جدولهایی را که بیشترین فضای ذخیرهسازی را مصرف میکنند شناسایی کند و برای مدیریت بهتر منابع تصمیم بگیرد.
🌟 آیا میخواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟با دوره آموزشی SQL Server ما، شما میتوانید به راحتی و با روشی عملی، تمام مهارتهای لازم را یاد بگیرید!این دوره به شما آموزش میدهد که چگونه دادهها را به بهترین شکل مدیریت کنید، گزارشهای قدرتمند بسازید و به تحلیلهای عمیق دست یابید.با محتوای جذاب و پروژههای واقعی، شما نه تنها تئوری را یاد میگیرید، بلکه تواناییهای عملی خود را نیز تقویت میکنید.پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشنتر بردارید!
LOB در SQL Server چیست؟
عبارت LOB مخفف Large Object است و به دادههایی اشاره دارد که برای ذخیرهسازی مقادیر بزرگ به کار میروند.در SQL Server، برخی انواع داده به دلیل اندازه یا ویژگیهای خود میتوانند از سازوکارهای مخصوص ذخیرهسازی دادههای حجیم استفاده کنند.
از جمله انواع داده مرتبط با این موضوع میتوان به موارد زیر اشاره کرد:
- VARCHAR(MAX) برای نگهداری رشتههای متنی با طول زیاد
- NVARCHAR(MAX) برای ذخیره متنهای Unicode با طول زیاد
- VARBINARY(MAX) برای نگهداری دادههای باینری حجیم
- XML برای ذخیره اطلاعات ساختاریافته در قالب XML
- انواع قدیمیتر مانند TEXT، NTEXT و IMAGE
برای مثال، دادههای یک ستون از نوع VARCHAR(MAX) لزوماً همیشه خارج از صفحه اصلی داده ذخیره نمیشوند.
SQL Server میتواند بسته به شرایط، داده را به شکل درونردیفی یا خارج از ردیف نگهداری کند.
بنابراین، هنگام بررسی فضای LOB باید بین نوع داده ستون و فضای واقعی تخصیصیافته به ساختارهای LOB تفاوت قائل شد.
منظور از فضای تخصیصیافته LOB چیست؟
در SQL Server، فضای تخصیصیافته به یک شیء با فضای استفادهشده توسط دادههای آن یکسان نیست.ممکن است بخشی از فضای تخصیصیافته هنوز برای نگهداری دادههای جدید یا عملیات داخلی در دسترس باشد، اما در حال حاضر بهطور کامل استفاده نشده باشد.
برای بررسی فضای مصرفی جدولها، معمولاً سه مفهوم اصلی مورد توجه قرار میگیرند:
-
فضای دادهها (Data Space)
فضایی که برای نگهداری صفحات داده جدول اختصاص یافته است. -
فضای ایندکسها (Index Space)
فضایی که برای ساختارهای ایندکس اختصاص داده شده است. -
فضای LOB (LOB Space)
فضایی که برای نگهداری دادههای حجیم خارج از ساختار معمول صفحات داده مورد استفاده قرار میگیرد.
بهویژه در جدولهایی که ستونهای حجیم دارند، بررسی جداگانه فضای LOB میتواند اطلاعات مفیدی درباره نحوه مصرف منابع ارائه دهد.
اسکریپت بدست آوردن فضای تخصیصیافته LOB
برای دریافت اطلاعات مربوط به فضای LOB در جدولهای دیتابیس فعلی میتوان از نمای مدیریتی sys.dm_db_partition_stats استفاده کرد.این نما اطلاعات مربوط به تعداد صفحات داده، صفحات ایندکس و صفحات LOB را در اختیار قرار میدهد.
اسکریپت زیر میزان فضای تخصیصیافته به صفحات LOB را برای جدولهای دیتابیس نمایش میدهد:
SELECT
s.name AS SchemaName,
t.name AS TableName,
SUM(ps.lob_used_page_count) AS LOBUsedPages,
SUM(ps.lob_reserved_page_count) AS LOBReservedPages,
CAST(
SUM(ps.lob_used_page_count) * 8.0 / 1024
AS DECIMAL(18, 2)
) AS LOBUsedMB,
CAST(
SUM(ps.lob_reserved_page_count) * 8.0 / 1024
AS DECIMAL(18, 2)
) AS LOBAllocatedMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON ps.object_id = t.object_id
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
GROUP BY
s.name,
t.name
HAVING SUM(ps.lob_reserved_page_count) > 0
ORDER BY
LOBReservedPages DESC;
این اسکریپت اطلاعات مربوط به جدولهایی را نمایش میدهد که برای صفحات LOB آنها فضایی تخصیص داده شده است.
نتیجه بر اساس فضای رزروشده مرتب میشود تا جدولهایی که بیشترین فضای LOB را در اختیار دارند، در ابتدای گزارش قرار بگیرند.
بررسی بخشهای مختلف اسکریپت
در این کوئری از نمای سیستمی sys.dm_db_partition_stats استفاده شده است.این نما آمار مربوط به پارتیشنهای جدولها و ایندکسها را ارائه میدهد و برای بررسی تعداد صفحات استفادهشده و رزروشده کاربرد دارد.
ستون lob_used_page_count تعداد صفحات LOB استفادهشده را نشان میدهد.
با جمعکردن این مقدار برای پارتیشنهای یک جدول، میتوان تعداد صفحات استفادهشده مرتبط با LOB را محاسبه کرد.
ستون lob_reserved_page_count تعداد صفحات رزروشده برای ساختارهای LOB را مشخص میکند.
این مقدار برای بررسی فضای تخصیصیافته اهمیت دارد، زیرا ممکن است تعداد صفحات رزروشده از تعداد صفحات استفادهشده بیشتر باشد.
در این گزارش، هر صفحه داده در SQL Server معمولاً ۸ کیلوبایت است.
به همین دلیل، برای تبدیل تعداد صفحات به مگابایت، تعداد صفحات در عدد ۸ ضرب و سپس بر ۱۰۲۴ تقسیم میشود.
برای مثال، اگر تعداد صفحات رزروشده LOB یک جدول برابر با ۱۲۸۰۰ باشد، فضای مربوط به آن به شکل زیر محاسبه میشود:
۱۲۸۰۰ × ۸ ÷ ۱۰۲۴ = ۱۰۰ مگابایت
در این مثال، مقدار محاسبهشده برابر با ۱۰۰ مگابایت است.
این عدد نشاندهنده فضای رزروشده گزارششده برای صفحات LOB است، نه لزوماً اندازه فایل دیتابیس روی دیسک.
مرتبسازی جدولها بر اساس فضای LOB
یکی از مزیتهای این اسکریپت، مرتبسازی خروجی بر اساس ستون LOBReservedPages است.این ترتیب باعث میشود جدولهایی که بیشترین تعداد صفحات رزروشده LOB را دارند، در بالای گزارش نمایش داده شوند.
برای مثال، در دیتابیسی که شامل جدولهای اسناد، تصاویر، فایلهای پیوست و اطلاعات متنی حجیم است، ممکن است یک یا چند جدول بخش بزرگی از فضای LOB را مصرف کنند.
با اجرای این کوئری میتوان جدولهای موردنظر را شناسایی کرد و در صورت نیاز، آنها را با جزئیات بیشتری بررسی کرد.
این اطلاعات برای تحلیل رشد دیتابیس و برنامهریزی ظرفیت ذخیرهسازی مفید هستند.
البته فضای LOB تنها بخشی از فضای کلی جدول را تشکیل میدهد.
برای محاسبه مجموع فضای استفادهشده توسط دادهها و ایندکسها باید ستونهای مربوط به سایر صفحات نیز بررسی شوند.




کاربران ما
شما هم نظرتون با ما دریاره “اسکریپت بدست آوردن میزان فضای تخصیص یافته LOB های یک دیتابیس” اشتراک بزارید
برای ارسال نظر لطفا ورود یا ثبت نام کنید