www.wssplex.net
http://www.databasejournal.com/features/mssql/article.php/3796551/article.htm 를 보면,. 각 DB 별 I/O 사용량 통계 쿼리 예제 입니다. 물론,. 제공되는 시스템뷰나 함수를 좀더 보기쉽고 직관적으로 알수 있도록 만들어 놓은 쿼리 입니다.
SQL Server 2000 에서는 2005 나 2008 처럼 다양한 시스템뷰 나 함수를 지원하지 않기 때문에 다음처럼 커서를 이용해서 임시테이블에 넣는 방법을 이용합니다.
좀더 자세한 사항은 위 포스트를 참고해 보시기 바랍니다.
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
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
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
좀더 자세한 사항은 위 포스트를 참고해 보시기 바랍니다.