SQL Server Database File I/O Report

http://www.databasejournal.com/features/mssql/article.php/3796551/article.htm 를 보면,. 각 DB 별 I/O 사용량 통계 쿼리 예제 입니다. 물론,. 제공되는 시스템뷰나 함수를 좀더 보기쉽고 직관적으로 알수 있도록 만들어 놓은 쿼리 입니다.

SQL Server 2005 및 SQL Server 2008

select db_name(mf.database_id) as database_name, mf.physical_name,
left(mf.physical_name, 1) as drive_letter,
vfs.num_of_writes, vfs.num_of_bytes_written, vfs.io_stall_write_ms,
mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms,
vfs.io_stall, vfs.size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
order by vfs.num_of_bytes_written desc

SQL Server 2000 에서는 2005 나 2008 처럼 다양한 시스템뷰 나 함수를 지원하지 않기 때문에 다음처럼 커서를 이용해서 임시테이블에 넣는 방법을 이용합니다.

SQL Server 2000
DECLARE @DBID smallint
DECLARE @FILEID smallint
DECLARE @DBNAME sysname
DECLARE @FILENAME nvarchar(260)
 
CREATE TABLE #FileIOStats
(name sysname,
filename nvarchar(260),
drive_letter char(1),
NumberReads bigint,
NumberWrites bigint,
BytesRead bigint,
BytesWritten bigint,
IoStallMS bigint)
 
DECLARE ALLFILES CURSOR FOR
SELECT dbid, fileid, [name], [filename] FROM [master].[dbo].[sysaltfiles]
 
OPEN ALLFILES
FETCH NEXT FROM ALLFILES INTO @DBID, @FILEID, @DBNAME, @FILENAME
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
                INSERT INTO #FileIOStats
                SELECT @DBNAME, @FILENAME, left(@FILENAME, 1), NumberReads, NumberWrites, BytesRead, BytesWritten, IoStallMS
                FROM ::fn_virtualfilestats(@DBID, @FILEID)
 
                FETCH NEXT FROM ALLFILES INTO @DBID, @FILEID, @DBNAME, @FILENAME
END
 
CLOSE ALLFILES
DEALLOCATE ALLFILES
 
SELECT * FROM #FileIOStats
DROP TABLE #FileIOStats

좀더 자세한 사항은 위 포스트를 참고해 보시기 바랍니다.