sys.dm_io_virtual_file_stats ({ database_id | NULL }, { file_id | NULL })
SELECT sys.dm_io_virtual_file_stats.database_id,
SUBSTRING(name,1,20) AS 'Database',
CASE FILE_ID WHEN 1 THEN 'Data'
WHEN 2 THEN 'Log'
END AS 'Type',
num_of_reads,
num_of_bytes_read,
num_of_writes,
num_of_bytes_written,
size_on_disk_bytes,
io_stall_read_ms,
io_stall_write_ms,
io_stall
FROM sys.dm_io_virtual_file_stats(null,null),
sys.databases
WHERE sys.databases.database_id = sys.dm_io_virtual_file_stats.database_id
sys.dm_io_virtual_file_stats columns:
database_id ID of database.
file_id ID of file.
sample_ms Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.
num_of_reads Number of reads issued on the file.
num_of_bytes_read Total number of bytes read on this file.
io_stall_read_ms Total time, in milliseconds, that the users waited for reads issued on the file.
num_of_writes Number of writes made on this file.
num_of_bytes_written Total number of bytes written to the file.
io_stall_write_ms Total time, in milliseconds, that users waited for writes to be completed on the file.
io_stall Total time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytes Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.
file_handle Windows file handle for this file.
for MSSQL 2000:
SELECT * FROM FN_VIRTUALFILESTATS(DEFAULT, DEFAULT)
No comments:
Post a Comment