查詢所有DATABASE 的I/O統計資料

create table #t(
DbName sysname
,FileName SysName
,database_id smallint --資料庫的識別碼
,file_id smallint --檔案的識別碼
,sample_ms int --自電腦啟動之後的毫秒數。這個資料行可用來比較這個函數的不同輸出
,num_of_reads bigint --對檔案發出的讀取數
,num_of_bytes_read bigint --這個檔案讀取的總位元組數
,io_stall_read_ms bigint --使用者等候在檔案發出讀取的總時間 (以毫秒為單位)
,num_of_writes bigint --這個檔案所進行的寫入數
,num_of_bytes_written bigint --寫入檔案的總位元組數
,io_stall_write_ms bigint --使用者等候檔案完成寫入的總時間 (以毫秒為單位)
,io_stall bigint --使用者等候檔案完成 I/O 的總時間 (以毫秒為單位)
,size_on_disk_bytes bigint --該檔案在磁碟上所用的位元組數。如果是疏鬆檔案，這個數字就是資料庫快照集在磁碟上所用的實際位元組數
,file_handle varbinary(8) --這個檔案的 Windows 檔案控制代碼
);
exec sp_MSforeachdb @command1="use [?];"

INSERT #t SELECT * FROM (
SELECT DB_NAME(database_id) DBName,FILE_NAME(file_id) FileName,* FROM sys.dm_io_virtual_file_stats(DB_ID(N'?'), NULL)) t;
SELECT * FROM #t order by DbName;

drop table #t

1. #t是暫存表格


2. exec sp_MSforeachdb @command1="use [?];"
   此sp名稱很好猜意思，for each db，會去scan每個db，參數@command1帶入一個"use [?];"，即sp_MSforeachdb會幫你做use master；use model; use msdb;…依資料庫id進行切換。


3. Insert #t Select * From ()，將Select結果寫入暫存表，重點在sys.dm_io_virtual_file_stats()的參數DB_ID(N'?')，N是Unicode名命都能正常執行，'?'會依sp_MSforeachdb每一次執行的結果被替代。如果此次是use master，那此次的?就會被替代為DB_ID(N'master')。


4.  可以試著執行「SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'Northwind'), Null);
   GO」以了解此系統函數。


5. 最後，取出結果，刪除暫存表格#t。

Reference：

• http://byronhu.wordpress.com/2011/01/10/sql-server-instance-%E5%95%9F%E5%8B%95%E4%BB%A5%E4%BE%86%E5%90%84-db-%E7%9A%84-file-%E7%B4%AF%E7%A9%8D-io/ (原始出處)


• MSSQL