تعریف و کاربرد

مصرف Page و Extent فایل‌های Data را در Database جاری نشان می‌دهد و برای tempdb بسیار کاربردی است.

Page Countها را به MB تبدیل و فایل‌ها را با نام و مسیرشان تطبیق دهید.

Syntax و روش استفاده

نحو پایه

SELECT *
FROM sys.dm_db_file_space_usage;

پارامترها و Scope این شیء مدیریتی باید متناسب با نسخه SQL Server و سطح دسترسی کاربر بررسی شود. هنگام استفاده در اسکریپت عمومی، Context دیتابیس و نام Instance را صریح ثبت کنید تا خروجی بعداً قابل تفسیر باشد.

مثال‌های عملی

مثال 1: خواندن خروجی پایه

در این مثال از sys.dm_db_file_space_usage برای خواندن خروجی پایه استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT * FROM sys.dm_db_file_space_usage;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 2: محدود کردن دامنه بررسی

در این مثال از sys.dm_db_file_space_usage برای محدود کردن دامنه بررسی استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT file_id,total_page_count*8.0/1024 AS TotalMB,allocated_extent_page_count*8.0/1024 AS AllocatedMB,unallocated_extent_page_count*8.0/1024 AS FreeMB FROM sys.dm_db_file_space_usage;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 3: محاسبه یک شاخص قابل تفسیر

در این مثال از sys.dm_db_file_space_usage برای محاسبه یک شاخص قابل تفسیر استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT file_id,100.0*allocated_extent_page_count/NULLIF(total_page_count,0) AS AllocatedPct FROM sys.dm_db_file_space_usage ORDER BY AllocatedPct DESC;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 4: فیلتر کردن وضعیت مهم

در این مثال از sys.dm_db_file_space_usage برای فیلتر کردن وضعیت مهم استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT f.name,f.physical_name,u.total_page_count,u.unallocated_extent_page_count FROM sys.dm_db_file_space_usage u JOIN sys.database_files f ON f.file_id=u.file_id;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 5: ترکیب با Viewهای سیستمی

در این مثال از sys.dm_db_file_space_usage برای ترکیب با Viewهای سیستمی استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

USE tempdb; SELECT file_id,user_object_reserved_page_count*8.0/1024 AS UserObjectMB FROM sys.dm_db_file_space_usage;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 6: مدیریت NULL و مقادیر مرزی

در این مثال از sys.dm_db_file_space_usage برای مدیریت NULL و مقادیر مرزی استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

USE tempdb; SELECT file_id,internal_object_reserved_page_count*8.0/1024 AS InternalMB FROM sys.dm_db_file_space_usage;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 7: بررسی یک سناریوی عملیاتی

در این مثال از sys.dm_db_file_space_usage برای بررسی یک سناریوی عملیاتی استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

USE tempdb; SELECT file_id,version_store_reserved_page_count*8.0/1024 AS VersionStoreMB FROM sys.dm_db_file_space_usage;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 8: ساخت گزارش سازمانی

در این مثال از sys.dm_db_file_space_usage برای ساخت گزارش سازمانی استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT file_id,100.0*allocated_extent_page_count/NULLIF(total_page_count,0) AS UsedPct FROM sys.dm_db_file_space_usage WHERE 100.0*allocated_extent_page_count/NULLIF(total_page_count,0)>=85;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 9: اصلاح یک برداشت رایج

در این مثال از sys.dm_db_file_space_usage برای اصلاح یک برداشت رایج استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

SELECT f.name,f.size*8.0/1024 AS FileSizeMB,u.allocated_extent_page_count*8.0/1024 AS AllocatedMB FROM sys.database_files f JOIN sys.dm_db_file_space_usage u ON u.file_id=f.file_id;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

مثال 10: آماده‌سازی Snapshot برای مانیتورینگ

در این مثال از sys.dm_db_file_space_usage برای آماده‌سازی Snapshot برای مانیتورینگ استفاده می‌کنیم. هدف این است که Query فقط اجرا نشود، بلکه خروجی آن به یک تصمیم مانیتورینگ یا عیب‌یابی تبدیل شود.

USE tempdb; SELECT f.name,100.0*u.allocated_extent_page_count/NULLIF(u.total_page_count,0) AS AllocatedPct FROM sys.dm_db_file_space_usage u JOIN sys.database_files f ON f.file_id=u.file_id ORDER BY AllocatedPct DESC;

کاربرد واقعی این مثال زمانی است که نتیجه با Baseline و Snapshotهای دیگر مقایسه شود. از یک مقدار منفرد برای نتیجه‌گیری قطعی درباره Storage یا تنظیمات SQL Server استفاده نکنید.

درک Scope و واحد داده

هر DMV یا DMF یک محدوده مشخص دارد. ممکن است اطلاعات در سطح Instance، Database، File، Volume یا Request باشد. پیش از هر محاسبه باید بدانید هر ردیف دقیقاً نماینده چیست. همچنین واحد ستون‌ها را بررسی کنید؛ Byte، Page، Millisecond و Percentage را نباید با هم مخلوط کرد. تبدیل واحدها به MB، GB و میلی‌ثانیه خوانا باعث می‌شود گزارش برای DBA، تیم زیرساخت و مدیر فنی قابل فهم و قابل اقدام باشد.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

ساخت Baseline معتبر

Baseline به معنی ثبت رفتار عادی سیستم در شرایط مختلف است. ساعات اوج، اجرای Batch، Backup، ETL، Index Maintenance و ساعات کم‌بار الگوهای متفاوتی ایجاد می‌کنند. یک Threshold که در یک محیط درست است ممکن است در محیط دیگر هشدار کاذب بسازد. بنابراین Snapshotها را با SampleTime، نام Instance، Database و مشخصات Workload ذخیره کنید و بعد از چند روز یا چند هفته محدوده طبیعی را استخراج نمایید.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

همبستگی با سایر شواهد

هیچ DMV به‌تنهایی Root Cause را تضمین نمی‌کند. داده را با Wait Statistics، Query Store، Error Log، Windows Event Log، مانیتور Storage، تنظیمات Autogrowth، Backup History و تراکنش‌های طولانی مقایسه کنید. وقتی چند منبع مستقل یک جهت را نشان دهند، تشخیص قابل دفاع‌تر می‌شود و احتمال تغییر پرهزینه و اشتباه روی Storage یا تنظیمات SQL Server کمتر خواهد شد.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

طراحی Snapshot و History

برای داده‌های تجمعی، اختلاف دو Snapshot در یک بازه مشخص معمولاً از مقدار خام مفیدتر است. برای داده‌های لحظه‌ای، Sampling دوره‌ای کمک می‌کند رخدادهای کوتاه از دست نروند. Repository مانیتورینگ باید Retention مشخص داشته باشد و داده خام بسیار قدیمی را Aggregate کند. این طراحی حجم ذخیره‌سازی را کنترل می‌کند و در عین حال Trend بلندمدت را حفظ می‌نماید.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

تفسیر NULL و صفر

NULL همیشه خطا نیست و صفر همیشه به معنی سلامت نیست. ممکن است Counter هنوز داده کافی نداشته باشد، یک فایل استفاده نشده باشد یا View روی Instance غیرکلاستر عمداً خروجی خالی بدهد. در محاسبات از NULLIF برای جلوگیری از تقسیم بر صفر استفاده کنید و در Dashboard بین «داده وجود ندارد»، «مقدار صفر» و «عدم دسترسی» تفاوت بصری و معنایی ایجاد نمایید.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

Threshold و Alert

Threshold باید با SLA، نوع Storage، اندازه دیتابیس و الگوی کسب‌وکار هماهنگ باشد. بهتر است Alert چندمرحله‌ای باشد: Warning برای نزدیک شدن به محدوده خطر، Critical برای عبور پایدار، و Recovery برای بازگشت وضعیت. شرط پایدار در چند Snapshot معمولاً False Positive را کاهش می‌دهد. همچنین درصد را همراه مقدار مطلق ببینید؛ مثلاً 80 درصد مصرف در Log کوچک و Log چندصد گیگابایتی یک معنی عملی ندارد.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

کارایی و هزینه مانیتورینگ

بیشتر DMVهای این مجموعه سبک هستند، اما اجرای SELECT * پرتکرار روی چندصد دیتابیس و ذخیره همه ستون‌ها می‌تواند Repository را بی‌دلیل بزرگ کند. Query جمع‌آوری را هدفمند بنویسید، ستون‌های محاسباتی را فقط در صورت نیاز تولید کنید و Interval را بر اساس سرعت تغییر Metric انتخاب کنید. مانیتورینگ خوب نباید خودش به منبع فشار تبدیل شود.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

سناریوی Incident

در Incident واقعی ابتدا Timeline بسازید: زمان شروع کندی، Alertها، Deploy، Backup و تغییرات زیرساخت. سپس Snapshot مرتبط را بگیرید و آن را با Baseline همان ساعت مقایسه کنید. اگر شواهد I/O وجود دارد، مشخص کنید مشکل روی کدام Database، File، Volume یا Request متمرکز است. بعد از آن تیم مسئول Storage یا توسعه را با داده دقیق درگیر کنید، نه با عبارت کلی «دیسک کند است».

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

Best Practice تغییرات

هیچ تغییر Production را بدون معیار موفقیت انجام ندهید. قبل از تغییر Snapshot بگیرید، هدف کمی تعریف کنید، Change Window و Rollback Plan داشته باشید و بعد از تغییر همان Queryها را دوباره اجرا کنید. اگر Metric بهتر شد اما Latency کاربر تغییر نکرد، احتمالاً علت اصلی جای دیگری بوده است. این روش از موفقیت ظاهری و تشخیص اشتباه جلوگیری می‌کند.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

کاربرد تجاری و پروژه‌ای

داده‌های این DMVها می‌توانند پایه Health Check، گزارش ظرفیت، Dashboard مدیریتی، Alert خودکار و Runbook عملیات باشند. در پروژه‌های مشاوره SQL Server، ارزش واقعی زمانی ایجاد می‌شود که Metric خام به اقدام روشن تبدیل شود: توسعه ظرفیت، اصلاح Backup، بازطراحی Growth، بهبود Query، تغییر Storage Tier یا اصلاح مسیرهای Cluster. مستندسازی دلیل و نتیجه هر اقدام دانش سازمانی ایجاد می‌کند.

در ارتباط با sys.dm_db_file_space_usage، این اصل اهمیت بیشتری پیدا می‌کند چون نوع داده و Scope می‌تواند برداشت اولیه را تغییر دهد. نتیجه را با نام Database یا File، زمان نمونه، واحد و وضعیت سیستم همراه کنید تا شخص دیگری نیز بتواند تحلیل را بازتولید کند.

خطاهای رایج

• استفاده از یک Snapshot بدون Baseline.
• نادیده گرفتن Scope و Permission.
• اشتباه در تبدیل واحدها.
• اعمال Threshold عمومی بدون شناخت Workload.
• انجام تغییر Production قبل از Root Cause Analysis.

روش صحیح این است که فرضیه را با چند شاهد مستقل آزمایش کنید. اگر خروجی یک DMV غیرعادی است اما Waitها، OS و تجربه کاربر مشکلی نشان نمی‌دهند، ابتدا اعتبار داده و زمان نمونه‌برداری را بررسی کنید.

Performance Considerations

Page Countها را به MB تبدیل و فایل‌ها را با نام و مسیرشان تطبیق دهید.

Query مانیتورینگ را تا حد ممکن محدود نگه دارید. در Jobهای پرتکرار ستون‌های غیرضروری را حذف کنید و داده History را با Retention مناسب نگهداری نمایید. محاسبات تقسیم را با NULLIF امن کنید و از Data Type مناسب برای جلوگیری از Overflow استفاده کنید.

Best Practices

1. نسخه و Permission را قبل از Deploy کنترل کنید.
2. Scope و واحد را در نام ستون خروجی مشخص کنید.
3. Baseline عادی بسازید.
4. Snapshotها را با زمان دقیق ذخیره کنید.
5. Metric را با Wait Statistics و اطلاعات OS همبسته کنید.
6. قبل و بعد تغییر Production همان معیارها را دوباره اندازه‌گیری کنید.

سؤالات متداول

sys.dm_db_file_space_usage دقیقاً چه کاربردی در SQL Server دارد؟

sys.dm_db_file_space_usage برای مشاهده بخشی مشخص از وضعیت I/O، Storage، فایل یا Transaction Log استفاده می‌شود. ارزش اصلی آن در عیب‌یابی مبتنی بر شواهد و ساخت Health Checkهای قابل تکرار است.

برای اجرای sys.dm_db_file_space_usage چه مجوزی لازم است؟

مجوز دقیق به نسخه و پلتفرم بستگی دارد. در نسخه‌های جدید SQL Server برخی DMVهای سطح سرور به VIEW SERVER PERFORMANCE STATE و برخی اشیای سطح دیتابیس به مجوزهای Performance State نیاز دارند؛ اسکریپت را روی نسخه هدف بررسی کنید.

آیا sys.dm_db_file_space_usage برای مانیتورینگ Production مناسب است؟

بله، به شرط آنکه Scope محدود، Interval منطقی و Repository مناسبی برای History داشته باشید. مانیتورینگ حرفه‌ای باید علاوه بر Metric خام، زمان نمونه و Context سرور و دیتابیس را ذخیره کند.

آیا می‌توان خروجی sys.dm_db_file_space_usage را در Dashboard نمایش داد؟

بله. بهتر است Byte به MB یا GB، Page به MB و زمان انتظار به واحد خوانا تبدیل شود. KPIهای نهایی باید نام روشن، واحد مشخص و Threshold متناسب با Baseline محیط داشته باشند.

sys.dm_db_file_space_usage چه تفاوتی با Performance Monitor سیستم‌عامل دارد؟

DMVها Context داخلی SQL Server را می‌دهند، اما ابزار سیستم‌عامل دید Storage، CPU و OS را کامل می‌کند. برای Root Cause دقیق معمولاً هر دو دید باید کنار هم تحلیل شوند.

آیا اجرای sys.dm_db_file_space_usage داده را تغییر می‌دهد؟

SELECT روی این شیء مدیریتی برای مشاهده وضعیت است و ذاتاً عملیات تغییر داده کسب‌وکار نیست. با این حال اسکریپت‌های جانبی جمع‌آوری Snapshot یا اقدامات اصلاحی باید جداگانه کنترل شوند.

رایج‌ترین خطا در تفسیر sys.dm_db_file_space_usage چیست؟

نتیجه‌گیری از یک عدد منفرد بدون شناخت Scope، واحد و ماهیت تجمعی یا لحظه‌ای داده، خطای بسیار رایجی است. Baseline و مقایسه چند منبع داده ضروری است.

چطور Queryهای مبتنی بر sys.dm_db_file_space_usage را بهینه نگه داریم؟

فقط ستون‌های لازم را انتخاب کنید، Scope را محدود کنید، Sampling بیش از حد انجام ندهید و History را با Index و Retention مناسب نگهداری کنید.

Best Practice اصلی استفاده از sys.dm_db_file_space_usage چیست؟

ابتدا سؤال عملیاتی را مشخص کنید، سپس Query هدفمند بسازید، Baseline جمع کنید و قبل از هر تغییر Production شواهد مکمل و Rollback Plan داشته باشید.

سازگاری نسخه‌ای sys.dm_db_file_space_usage را چگونه کنترل کنیم؟

وجود Object، ستون‌ها و Permissionهای مورد نیاز را روی نسخه هدف تست کنید. تفاوت SQL Server نصب‌شده، Azure و نسخه‌های جدیدتر باید در Runbook مانیتورینگ ثبت شود.

سؤالات مصاحبه

1. Scope داده‌های sys.dm_db_file_space_usage چیست و چه خطای تفسیری ممکن است رخ دهد؟
2. چگونه برای sys.dm_db_file_space_usage Baseline می‌سازید؟
3. چگونه خروجی sys.dm_db_file_space_usage را با Wait Statistics همبسته می‌کنید؟
4. در چه شرایطی خروجی خالی یا NULL از sys.dm_db_file_space_usage طبیعی است؟
5. قبل از اقدام زیرساختی بر اساس sys.dm_db_file_space_usage چه شواهد دیگری جمع می‌کنید؟

چک‌لیست نهایی

• Permission بررسی شده است.
• Context صحیح است.
• واحدها تبدیل شده‌اند.
• Baseline وجود دارد.
• Threshold محیطی است.
• شواهد مکمل جمع شده‌اند.
• اقدام و Rollback Plan مستند شده‌اند.

جمع‌بندی

sys.dm_db_file_space_usage زمانی بیشترین ارزش را دارد که از یک Query نمایشی به بخشی از فرآیند منظم Monitoring و Root Cause Analysis تبدیل شود. با شناخت Scope، ساخت Baseline، ثبت History و همبستگی با شواهد دیگر می‌توان تصمیم‌های فنی دقیق‌تر و کم‌ریسک‌تری گرفت.

برای ادامه مسیر، به مقاله مادر DMVهای عملکرد I/O و Storage در SQL Server برگردید.