sys.dm_io_virtual_file_stats (Transact-SQL)sys.dm_io_virtual_file_stats (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

傳回資料和記錄檔的 I/O 統計資料。Returns I/O statistics for data and log files. 這個動態管理檢視會取代fn_virtualfilestats函式。This dynamic management view replaces the fn_virtualfilestats function.

注意

若要呼叫這個屬性從Azure SQL 資料倉儲Azure SQL Data Warehouse,使用名稱sys.dm_pdw_nodes_io_virtual_file_statsTo call this from Azure SQL 資料倉儲Azure SQL Data Warehouse, use the name sys.dm_pdw_nodes_io_virtual_file_stats.

語法Syntax

-- Syntax for SQL Server and Azure SQL Database

sys.dm_io_virtual_file_stats (   
    { database_id | NULL },  
    { file_id | NULL }  
)  
-- Syntax for Azure SQL Data Warehouse

sys.dm_pdw_nodes_io_virtual_file_stats

引數Arguments

database_id |NULLdatabase_id | NULL

適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

資料庫的識別碼。ID of the database. database_id是 int,沒有預設值。database_id is int, with no default. 資料庫的識別碼或 NULL 都是有效的輸入。Valid inputs are the ID number of a database or NULL. 如果指定 NULL,則會傳回 SQL ServerSQL Server 執行個體中所有的資料庫。When NULL is specified, all databases in the instance of SQL ServerSQL Server are returned.

內建函式DB_ID可以指定。The built-in function DB_ID can be specified.

file_id |NULLfile_id | NULL

適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

檔案識別碼。ID of the file. file_id是 int,沒有預設值。file_id is int, with no default. 有效輸入是檔案的識別碼或 NULL。Valid inputs are the ID number of a file or NULL. 如果指定 NULL,則會傳回資料庫中所有的檔案。When NULL is specified, all files on the database are returned.

內建函式FILE_IDEX指定,且參考目前資料庫中的檔案。The built-in function FILE_IDEX can be specified, and refers to a file in the current database.

傳回的資料表Table Returned

資料行名稱Column name 資料類型Data type 描述Description
database_namedatabase_name sysnamesysname 資料庫名稱。Database name.
針對 SQL 資料倉儲,這是儲存在由 pdw_node_id 的節點上的資料庫名稱。For SQL Data Warehouse, this is the name of the database stored on the node which is identified by pdw_node_id. 每個節點都有 13 個檔案的一個 tempdb 資料庫。Each node has one tempdb database that has 13 files. 每個節點也有一個資料庫,每個散發,而且每個散發資料庫中有 5 個檔案。Each node also has one database per distribution, and each distribution database has 5 files. 比方說,如果每個節點包含 4 個散發套件,結果會顯示 pdw_node_id 每 20 個散發資料庫檔案。For example, if each node contains 4 distributions, the results show 20 distribution database files per pdw_node_id.
database_iddatabase_id smallintsmallint 資料庫的識別碼。ID of database.
file_idfile_id smallintsmallint 檔案的識別碼。ID of file.
sample_mssample_ms bigintbigint 自電腦啟動之後的毫秒數。Number of milliseconds since the computer was started. 這個資料行可用來比較這個函數的不同輸出。This column can be used to compare different outputs from this function.
資料類型是intSQL Server 2008SQL Server 2008透過 SQL Server 2014 (12.x)SQL Server 2014 (12.x)The data type is int for SQL Server 2008SQL Server 2008 through SQL Server 2014 (12.x)SQL Server 2014 (12.x)
num_of_readsnum_of_reads bigintbigint 對檔案發出的讀取數。Number of reads issued on the file.
num_of_bytes_readnum_of_bytes_read bigintbigint 這個檔案讀取的總位元組數。Total number of bytes read on this file.
io_stall_read_msio_stall_read_ms bigintbigint 使用者等候在檔案發出讀取的總時間 (以毫秒為單位)。Total time, in milliseconds, that the users waited for reads issued on the file.
num_of_writesnum_of_writes bigintbigint 這個檔案所進行的寫入數。Number of writes made on this file.
num_of_bytes_writtennum_of_bytes_written bigintbigint 寫入檔案的總位元組數。Total number of bytes written to the file.
io_stall_write_msio_stall_write_ms bigintbigint 使用者等候檔案完成寫入的總時間 (以毫秒為單位)。Total time, in milliseconds, that users waited for writes to be completed on the file.
io_stallio_stall bigintbigint 使用者等候檔案完成 I/O 的總時間 (以毫秒為單位)。Total time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytessize_on_disk_bytes bigintbigint 該檔案在磁碟上所用的位元組數。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_handlefile_handle varbinaryvarbinary 這個檔案的 Windows 檔案控制代碼。Windows file handle for this file.
io_stall_queued_read_msio_stall_queued_read_ms bigintbigint 不適用於: :SQL Server 2008SQL Server 2008透過SQL Server 2012 (11.x)SQL Server 2012 (11.x)Does not apply to:: SQL Server 2008SQL Server 2008 through SQL Server 2012 (11.x)SQL Server 2012 (11.x).

IO 資源管理針對讀取導入的總 IO 延遲。Total IO latency introduced by IO resource governance for reads. 不可為 Null。Is not nullable. 如需詳細資訊,請參閱 < sys.dm_resource_governor_resource_pools (TRANSACT-SQL)For more information, see sys.dm_resource_governor_resource_pools (Transact-SQL).
io_stall_queued_write_msio_stall_queued_write_ms bigintbigint 不適用於: :SQL Server 2008SQL Server 2008透過SQL Server 2012 (11.x)SQL Server 2012 (11.x)Does not apply to:: SQL Server 2008SQL Server 2008 through SQL Server 2012 (11.x)SQL Server 2012 (11.x).

IO 資源管理針對寫入導入的總 IO 延遲。Total IO latency introduced by IO resource governance for writes. 不可為 Null。Is not nullable.
pdw_node_idpdw_node_id intint 適用於: SQL 資料倉儲SQL Data WarehouseApplies to: SQL 資料倉儲SQL Data Warehouse
發佈節點的識別碼。Identifier of the node for the distribution.

PermissionsPermissions

需要 VIEW SERVER STATE 權限。Requires VIEW SERVER STATE permission. 如需詳細資訊,請參閱 < 動態管理檢視和函式(TRANSACT-SQL)For more information, see Dynamic Management Views and Functions (Transact-SQL).

範例Examples

A.A. 傳回記錄檔的統計的資料Return statistics for a log file

適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseApplies to: SQL Server (starting with 2008), Azure SQL Database

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中記錄檔的統計資料。The following example returns statistics for the log file in the AdventureWorks2012AdventureWorks2012 database.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2);  
GO  

B.B. 在 tempdb 中傳回檔案的統計資料Return statistics for file in tempdb

適用於: Azure SQL 資料倉儲Applies to: Azure SQL Data Warehouse

SELECT * FROM sys.dm_pdw_nodes_io_virtual_file_stats 
WHERE database_name = 'tempdb' AND file_id = 2;

另請參閱See Also

動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
我 O 相關動態管理檢視和函數(Transact SQL) I O Related Dynamic Management Views and Functions (Transact-SQL)
sys.database_files (Transact-SQL) sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)sys.master_files (Transact-SQL)