SQL SERVER TIPS n°3 : How can I view I/O statistics on the physical database files in a database?
The system function fn_virtualfilestats, available in both SQL Server 2000 and SQL Server 2005, or sys.dm_io_virtual_file_stats (in SQL 2005 only) allows you to do exactly what you’re looking for. The function returns statistical information gathered since the last time the instance of SQL Server was started.
Sample Results are shown in Figure 1.
Figure 1
DbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS
20 1 250765718 381 0 3350528 0 951
20 2 250765718 12 8 409600 491520 0
20 3 250765718 5 0 40960 0 16
Understanding the I/O impact on the underlying data files can you help you better plan such things as the physical placement of files and filegroups on data volumes, detecting possible I/O bottlenecks, performing file-level database maintenance, and other tasks. This function is particularly useful for examining the I/O impact for large databases, where you may have multiple files and filegroups.
Figure 2 shows the query for displaying file I/O info for SQL Server 2000 and Figure 4 shows the SQL Server 2005 code.
Figure 2 Display File I/O Info for SQL Server 2000
SELECT * FROM ::fn_virtualfilestats(default,default)
GO
And to view a specific databaseID, pass the ID for the database…
SELECT * FROM ::fn_virtualfilestats(7,default)
GO
Figure 3 Display File I/O Info for SQL Server 2005
Example: Shows file statistics for ALL databases on the server
SELECT * FROM ::fn_virtualfilestats(NULL,NULL)
GO
Example: Shows file statistics for only the CURRENT database...
SELECT * FROM ::fn_virtualfilestats(NULL,NULL) WHERE DBID=db_id()
GO
For SQL 2005, there is also a new system function called sys.dm_io_virtual_file_stats, which is intended to eventually replace the legacy function fn_virtualfilestats.
sys.dm_io_virtual_file_stats(
{ database_id | NULL } , { file_id | NULL }
)
Example:
SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)
If you want to generate a more readable report that shows the actual database names and file names from the output, you can use the following code (compatible with both SQL 2000 and SQL 2005, but would need to be modified if you want to utilize the newer functions in SQL 2005).
------------------------------------
-- Create temp tables to hold info..
------------------------------------
CREATE TABLE #filespec
(
dbid smallint,
fileid smallint,
groupid tinyint,
size int,
maxsize int,
growth int,
status int,
name varchar(255),
filename varchar(255)
)
CREATE TABLE #fileIOStats
(
dbid int,
dbname sysname,
fileid int,
FileName varchar(255),
FilePath varchar(255),
FileSize int,
NumberReads bigint,
NumberWrites bigint,
BytesRead bigint,
BytesWritten bigint,
IOStallMS bigint
)
------------------------------------
-- Collect File Stat Information
------------------------------------
INSERT #fileIOStats
SELECT
dbid,
db_name(dbid) as DBName,
fileid,
NULL,
NULL,
NULL,
NumberReads,
NumberWrites,
BytesRead,
BytesWritten,
IOStallMS
FROM ::fn_virtualFilestats(default,default)
---------------------------------
-- Get list of databases @dblist from sysdatabases
---------------------------------
DECLARE @dblist table (dbname varchar(48),dbid int) DECLARE @curdbid int DECLARE @name varchar(48) DECLARE @exec_stmt varchar(5000)
INSERT INTO @dblist (dbname, dbid )
SELECT name, dbid from master.dbo.sysdatabases
-------------------------------
-- LOOP THROUGH @dblist TO SET VALUES
-------------------------------
SELECT @curdbid = min(dbid) from @dblist
SET @name = db_name(@curdbid)
WHILE @curdbid IS NOT NULL
BEGIN
IF (has_dbaccess(@name) = 1)
BEGIN
SELECT @exec_stmt = '
INSERT #filespec select '+cast(@curdbid as varchar(10))+',fileid,groupid,size,maxsize,growth,status,name,filename FROM '
+ quotename(@name, N'[') + N'.dbo.sysfiles '
EXECUTE (@exec_stmt)
END
SELECT @curdbid = min(dbid) from @dblist where dbid > @curdbid
SET @name = db_name(@curdbid)
END
---------------------------------
-- Update #fileIOStats Info
---------------------------------
UPDATE t1
SET t1.FileName=t2.name,
t1.FilePath=t2.filename,
t1.FileSize=t2.size
FROM #fileIOStats t1
INNER JOIN
#filespec t2
ON t1.dbid=t2.dbid AND t1.fileid=t2.fileid
---------------------------------
-- NOW: Select the final info...
---------------------------------
SELECT
DBName,
FileName,
FilePath,
FileSize,
NumberReads,
NumberWrites,
BytesRead,
BytesWritten,
CAST(CAST(NumberReads as float)/CAST((NumberReads+NumberWrites) as float)*100 as int) as PerCentReads, --shows the relative percent of reads vs. writes
CAST(CAST(NumberWrites as float)/CAST((NumberReads+NumberWrites) as float)*100 as int) as PerCentWrites, --shows the relative percent of reads vs. writes
IOStallMS,
CAST((IOStallMS/CASE WHEN(NumberReads+NumberWrites)>0 THEN NumberReads+NumberWrites ELSE 1 END) as float) as ISStallPCT --shows relative IO bottleneck by percentage
FROM #fileIOStats
GO
DROP TABLE #filespec
DROP TABLE #fileIOStats
GO