Gather IO statistics down to the SQL Server database file level

By:   |   Updated: 2013-06-10   |   Comments (9)   |   Related: > Performance Tuning


Problem

When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible.  SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O.  Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server.  What other options are available to look at I/O related information down to the file level for each database?

Solution

As mentioned above, SQL Server offers many great little functions and utility programs to help you gain an insight as to what is occurring on the server.  One of these tools is fn_virtualfilestats.

This function, fn_virtualfilestats allows you to get information for each physical file that is being used to hold your data including both the data and log files. The function returns read and write information as well as stall information, which is the time users had to wait for an I/O operation to complete.  Each time this function is called it returns the overall numbers that SQL Server has collected since the last time the database engine was started, so to use this effectively you need to gather data from two different points of time and then do a comparison.

To run this function to get data for all databases and all files this can be done as easily as this:

SQL 2005, 2008, 2008R2, 2012

SELECT * FROM fn_virtualfilestats(NULL,NULL);
--or you can use this DMV
SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL);

SQL 2000

SELECT * FROM :: fn_virtualfilestats(-1, -1)

The output for SQL 2000 and the other editions is pretty much the same, but some additional columns have been added after SQL Server 2000.

Column Name Notes Description
DbId   Database ID.
FileId   File ID.
TimeStamp   Database timestamp at which the data was taken.
NumberReads   Number of reads issued on the file.
BytesRead   Number of bytes read issued on the file.
IoStallReadMS not in 2000 Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.
NumberWrites   Number of writes made on the file.
BytesWritten   Number of bytes written made on the file.
IoStallWriteMS not in 2000 Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file.
IoStallMS   Sum of IoStallReadMS and IoStallWriteMS.
FileHandle   Value of the file handle.
BytesOnDisk not in 2000 Physical file size (count of bytes) on disk.

For database files, this is the same value as size in sys.database_files, but is expressed in bytes rather than pages.

For database snapshot spare files, this is the space the operating system is using for the file.

(Source SQL Server Books Online)

Sample Output

As you can see from the sample output, the Dbid and FileId columns are pretty cryptic.  The Dbid can be be translated to the database  name pretty easily by using the DB_NAME() function, but the fileId needs to be looked up from one of the system tables.

v1

To lookup the filename from the system tables you can use these queries.

SQL 2005, 2008, 2008R2, 2012

SELECT dbid, fileid, filename
FROM sys.sysaltfiles
WHERE dbid = 5 and fileid in (1,2)

SQL 2000

SELECT dbid, fileid, filename
FROM dbo.sysaltfiles
WHERE dbid = 5 and fileid in (1,2)

Here is sample output.

v2

From just using this function directly you can gather data from two different points in time and then do a comparison to determine the change that has occurred between these two periods of time.  Here is a sample query that gathers data, waits for a period of time and then gathers data again to show you a comparison.

This example is written for SQL Server 2005 and forward, but can easily be changed for SQL 2000.

USE master
GO

-- create table
IF NOT EXISTS (SELECT * 
       FROM sys.objects 
       WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]') 
       AND type IN (N'U'))
BEGIN
   CREATE TABLE filestats
    (dbname  VARCHAR(128),
    fName  VARCHAR(2048), 
    timeStart  datetime,
    timeEnd datetime,
    timeDiff bigint,
    readsNum1 bigint,
    readsNum2 bigint,
    readsBytes1 bigint,
    readsBytes2 bigint,
    readsIoStall1 bigint,
    readsIoStall2 bigint,
    writesNum1 bigint,
    writesNum2 bigint,
    writesBytes1 bigint,
    writesBytes2 bigint,
    writesIoStall1 bigint,
    writesIoStall2 bigint,
    ioStall1 bigint,
    ioStall2 bigint
    )
END

-- clear data
TRUNCATE TABLE dbo.filestats

-- insert first segment counters
INSERT INTO dbo.filestats
   (dbname,
   fName, 
   TimeStart,
   readsNum1,
   readsBytes1,
   readsIoStall1, 
   writesNum1,
   writesBytes1,
   writesIoStall1, 
   IoStall1
   )
SELECT 
   DB_NAME(a.dbid) AS Database_name,
   b.filename,
   GETDATE(),
   numberReads,
   BytesRead,
   IoStallReadMS,
   NumberWrites,
   BytesWritten,
   IoStallWriteMS,
   IoStallMS
FROM 
   fn_virtualfilestats(NULL,NULL) a INNER JOIN
   sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
ORDER BY 
   Database_Name

/*Delay second read */
WAITFOR DELAY '000:01:00'

-- add second segment counters
UPDATE dbo.filestats 
SET 
   timeEnd = GETDATE(),
   readsNum2 = a.numberReads,
   readsBytes2 = a.BytesRead,
   readsIoStall2 = a.IoStallReadMS ,
   writesNum2 = a.NumberWrites,
   writesBytes2 = a.BytesWritten,
   writesIoStall2 = a.IoStallWriteMS,
   IoStall2 = a.IoStallMS,
   timeDiff = DATEDIFF(s,timeStart,GETDATE())
FROM 
   fn_virtualfilestats(NULL,NULL) a INNER JOIN
   sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
WHERE   
   fName= b.filename AND dbname=DB_NAME(a.dbid)

-- select data
SELECT 
   dbname,
   fName,
   timeDiff,
   readsNum2 - readsNum1 AS readsNumDiff,
   readsBytes2 - readsBytes1 AS readsBytesDiff,
   readsIoStall2 - readsIOStall1 AS readsIOStallDiff,
   writesNum2 - writesNum1 AS writesNumDiff,
   writesBytes2 - writesBytes1 AS writesBytesDiff,
   writesIoStall2 - writesIOStall1 AS writesIOStallDiff,   
   ioStall2 - ioStall1 AS ioStallDiff
FROM dbo.filestats 
 

Summary

One problem that you may be faced with though is that not all files are stored on their own physical disks, so you may have a case where you want to look at things from a drive perspective vs. at an individual file level.  Here is a previous article written by Andy Novick that has the entire process broken down into functions, so you can aggregate things to a drive perspective.  The article can be found here, Examining SQL Server's I/O Statistics

Next Steps
  • When researching performance problems, don't forget to look at I/O stats as well.  This handy little function could give you big insight into some of your performance issues.
  • Stay tuned for more performance related tips, but for now check out these other tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2013-06-10

Comments For This Article




Friday, January 16, 2015 - 10:48:53 AM - John Grover Back To Top (35969)

We are preparing to migrate many of our SQL Servers to the cloud. I want to find a logical way to redistribute the databases on the new virtual servers. One factor I want to consider is how 'busy" the database is. My thought was to take the information from the dm_io_virtual_stats DMV as shown below to get a number I can compare across servers. Does it make sense to use the DMV in this way or am I misunderstanding how it works? Essentially it takes the sum of num_of_bytes_read and sum_of_bytes_written divided by the sample_ms to get an idea of the average I/O of each database since the last time the system was started. I'm running this code across several servers, so dividing by the sample_ms should make it a valid comparison against different machines, I hope!

BEGIN

declare@DatabaseIdasint

declare@FileIdasint

declare@FileAgeasbigint

declare@BytesInbigint

declare@BytesOutasbigint

 

createtable#FileInfo (

  ServerNamevarchar(128),

  DatabaseNamevarchar(128),

  DatabaseAgefloat,

  InputBytesfloat,

  OutputBytesfloat)

 

DECLAREFileCursorCURSORFOR

selectdatabase_id,file_id

fromsys.master_files

 

openFileCursor

FETCHNEXTFROMFileCursorinto@DatabaseId,@FileId

WHILE@@FETCH_STATUS= 0

   BEGIN

  select@FileAge=sample_ms,@BytesIn=num_of_bytes_read,@BytesOut=num_of_bytes_written

  fromsys.dm_io_virtual_file_stats(@DatabaseId,@FileId)

 

  insertinto#FileInfovalues (@@SERVERNAME,db_name(@DatabaseId),@FileAge,@Bytesin,@BytesOut)

  FETCHNEXTFROMFileCursorinto@DatabaseId,@FileId

   END;

CLOSEFileCursor

DEALLOCATEFileCursor

 

selectServerName,DatabaseName,SUM((InputBytes+OutputBytes)/DatabaseAge)[AvgBytesPerMS] 

from#FileInfo 

whereDatabaseAge> 0 

groupbyServerName,DatabaseName

Droptable#FileInfo

END

GO

 

 

Monday, October 6, 2014 - 7:07:35 PM - eric81 Back To Top (34852)

Does the information in the DMV represent the same lantency I see in windows perfmon?

Wednesday, December 18, 2013 - 8:02:27 AM - Przemek von Wielebny Back To Top (27827)

Hello Greg

I have real problem with understanding IoStallWriteMS column we have in fn_virtualfilestats view and also @@IO_BUSY.

Unfortunately both of them seems wrong… I know it’s a little bold statement, but please take a look at my test and try to point out errors.

So my tests are divided on 2 parts, first part create empty database , create table and create procedure which inserts 200000 rows to table. Second part – do snapshot of dm_io_virtual_file_stats and @@IO_BUSY before and after running procedure.

So main problem is :

without transaction timing on dm_io_virtual_file_stats seems correct, but with transaction it is totally strange – io_stall_write_ms is 2x bigger then elapsed time of whole test..- it could be possible with parallel processing but @@CPU_BUSY is just to low (Write time is much higher then CPU Usage – it’s impossible in my universe :-) ) on the other hand @@IO_BUSY is surprisingly low for example. Whole test take 30 seconds and difference in snapshot on @@IO_BUSY is just 1 second..

 

The script for test is as following:

---------------------------Prepare to test ----------------------------------------------------------\

USE [master]
GO
CREATE DATABASE [tester_1] 
GO
use [tester_1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tester](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[dat1] [datetime] NOT NULL,
[num1] [int] NULL,
[var1] varchar(1000) null,
[var2] varchar(4000) null,
[var3] varchar(4000) null
) ON [PRIMARY]

GO

----------------------------------------------

USE [tester_1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[run_insert] as
begin

DECLARE @RowCount INT 
DECLARE @Random1 INT 
DECLARE @Ranvar1 varchar(1000) 
DECLARE @Ranvar2 varchar(4000)
DECLARE @Ranvar3 varchar(4000) 
declare @dateDelete datetime
declare @sql varchar(max)
declare @alphabet varchar(1000) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789asdafsdaf;;lqwoeiqrnbzml/lx[09304985234$%&#$%$@#%@#$@#%$GFADFGABBVCXXZCDASFASERQW$%$YYTqaoiqasl;dkjl;qw3093945890jlkxzv,l;z;ldal;jkpapoeoipqwerijujdkgjh;bvjkz\hdfoadsfpqwyuer9384t-2u84t9uafuihadsjkvnbzcx.na;jkdfhyqpweyp9q3843ut890ujreijhaogfhjfdnjva;sjd;asjhjqajheworqupoyu90-8427u58423yu5tuioerjght;lknhjgbjk;dfbjknznn.av;sdfaq;ojfoqpwer9p83u483u6908itjuwqearfASGHYGRTUTYRUI*OUP{O{POP:":L"JKL>NM<>B<VMCVNXCVBVZXV|AASAEWQER!#!@#%$$#^$%#&^%*&&^*&^*(*&)(*_)(+_{PIO":UI:HKGHMCVBNMCVNBXCBSDGFADSGQWRETQ@@TRWRE#TWERYTERYTYRUTYURITYUOYUIPY***:LHK:HLGJGFNCBNDGHSDFGGAQWWERTWQERTWEYTRYHYUYUJM&I<*OLI>(OP:?)__{"{*(L*IK&^%H%^YH^%$YH$ERTGWejoiwetfoi3u423iu5toirewkjkge;***s;lkerlk;weroipu423uo235i4joiewtjkore;glsojirqew9,[0m4t9,0mvuoierrwwucehmow409mtohuoowqfoicwqe980cq98-95420349c98ncu89pwt9pemmu89ewu98u98mtu89m324u982um82umum8qcopoqpiocrqewcqwcrqecrqwecrq453456576nui785im768o676ioin887aowejaslkjopiufqwer734-05892304uaojfas;ljdasoidfqp[uwer8901u423';
SET @RowCount = 0

WHILE (@RowCount < 200000)
BEGIN 
set @Random1 = ROUND(((1200) * RAND() ), 0);

set @Ranvar1 = 
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500);

set @Ranvar2 = 
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500);

set @Ranvar3 = 
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500) +
substring(@alphabet, convert(int, rand()*500), 500);

INSERT INTO [dbo].[tester] with(rowlock,repeatableread)
  ([dat1],[num1],var1,var2,var3)
VALUES
  (DATEADD(DAY, -@Random1, GETDATE()) 
  ,@Random1
  ,@Ranvar1
  ,@Ranvar2
  ,@Ranvar3);

set @RowCount = @RowCount + 1;

end;

end;

GO

 

---------------------------Prepare to test ----------------------------------------------------------/

 

-------------------------------Test without transaction values seems ok ---------------------------\

use [tester_1]
GO
truncate table dbo.tester;
DECLARE @DateStart as datetime
DECLARE @DateEnd as datetime
 set @DateStart = GETDATE();
select @DateStart as dateStart;
SELECT 
    mf.type,
    mf.file_id,
    mf.physical_name as FileName,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms/1000.0 as io_stall_write_sec_before,
    vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
inner join sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
where DB_NAME(vfs.database_id) = 'tester_1';

 SELECT CAST(@@TOTAL_READ as bigint) as TotalRead
                          ,CAST(@@TOTAL_WRITE as bigint) as TotalWrite
                          ,(CAST(@@IO_BUSY as bigint) * @@TIMETICKS)/1000000 as IO_Busy_sec_before
                          ,(CAST(@@CPU_BUSY as bigint) * @@TIMETICKS)/1000000 as Cpu_Busy_sec
                          ,(CAST(@@IDLE as bigint) * @@TIMETICKS)/1000000 as Idle_sec;

--begin tran;
execute [dbo].[run_insert];
--commit tran;

SELECT 
    mf.type,
    mf.file_id,
    mf.physical_name as FileName,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms/1000.0 as io_stall_write_sec_after,
    vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
inner join sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
where DB_NAME(vfs.database_id) = 'tester_1';

 SELECT CAST(@@TOTAL_READ as bigint) as TotalRead
                          ,CAST(@@TOTAL_WRITE as bigint) as TotalWrite
                          ,(CAST(@@IO_BUSY as bigint) * @@TIMETICKS)/1000000 as IO_Busy_sec_after
                          ,(CAST(@@CPU_BUSY as bigint) * @@TIMETICKS)/1000000 as Cpu_Busy_sec
                          ,(CAST(@@IDLE as bigint) * @@TIMETICKS)/1000000 as Idle_sec;
set @DateEnd = GETDATE();                          
select @DateEnd as dateEnd , DATEDIFF(SECOND,@DateStart, @DateEnd) as ElapsedTime_sec ;

 

-------------------------------Test without transaction values seems ok ---------------------------/

 

 

-------------------------------Test with transaction values seems wrong ---------------------------\

use [tester_1]
GO
truncate table dbo.tester;
DECLARE @DateStart as datetime
DECLARE @DateEnd as datetime
 set @DateStart = GETDATE();
select @DateStart as dateStart;
SELECT 
    mf.type,
    mf.file_id,
    mf.physical_name as FileName,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms/1000.0 as io_stall_write_sec_before,
    vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
inner join sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
where DB_NAME(vfs.database_id) = 'tester_1';

 SELECT CAST(@@TOTAL_READ as bigint) as TotalRead
                          ,CAST(@@TOTAL_WRITE as bigint) as TotalWrite
                          ,(CAST(@@IO_BUSY as bigint) * @@TIMETICKS)/1000000 as IO_Busy_sec_before
                          ,(CAST(@@CPU_BUSY as bigint) * @@TIMETICKS)/1000000 as Cpu_Busy_sec
                          ,(CAST(@@IDLE as bigint) * @@TIMETICKS)/1000000 as Idle_sec;

begin tran;
execute [dbo].[run_insert];
commit tran;

SELECT 
    mf.type,
    mf.file_id,
    mf.physical_name as FileName,
    vfs.num_of_writes,
    vfs.num_of_bytes_written,
    vfs.io_stall_write_ms/1000.0 as io_stall_write_sec_after,
    vfs.io_stall
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
inner join sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
where DB_NAME(vfs.database_id) = 'tester_1';

 SELECT CAST(@@TOTAL_READ as bigint) as TotalRead
                          ,CAST(@@TOTAL_WRITE as bigint) as TotalWrite
                          ,(CAST(@@IO_BUSY as bigint) * @@TIMETICKS)/1000000 as IO_Busy_sec_after
                          ,(CAST(@@CPU_BUSY as bigint) * @@TIMETICKS)/1000000 as Cpu_Busy_sec
                          ,(CAST(@@IDLE as bigint) * @@TIMETICKS)/1000000 as Idle_sec;
set @DateEnd = GETDATE();                          
select @DateEnd as dateEnd , DATEDIFF(SECOND,@DateStart, @DateEnd) as ElapsedTime_sec ;

 

-------------------------------Test with transaction values seems wrong ---------------------------/

To summarize, my second test with transaction show result like: during 22 seconds of real time,   1 second of IO_BUSY was used , 76 seconds took write time on Rows Data file and 197 seconds took  write time on log file (please remember: all of this happend in 22 second of real time) - it doesn't make any sense 

Can you help me to find out how it really works?

Regards

Monday, October 7, 2013 - 7:09:06 PM - tom Back To Top (27070)

running the code at a busy time was difficult, but I could add calls to the beginning and ending of long running procedures to see how those procedures were performing.  To do that I came up with the code below.  Basically it just records the snapshot in the table and then, instead of doing the 'wait' thing, it is just makes a call later in your procedure-- connects the dots.  Does this make sense?

 

USE master 

GO 

 

-- create table 

IF NOT EXISTS (SELECT *  

       FROM sys.objects  

       WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]')  

       AND type IN (N'U')) 

BEGIN 

   CREATE TABLE dbo.filestats 

    (

    dbname  VARCHAR(128), 

    fName  VARCHAR(255),  

    timeStart  datetime, 

    timeEnd datetime, 

    timeDiff bigint, 

    readsNum1 bigint, 

    readsBytes1 bigint, 

    readsIoStall1 bigint, 

    writesNum1 bigint, 

    writesBytes1 bigint, 

    writesIoStall1 bigint, 

    ioStall1 bigint, 

    ) 

END 

 

go

create proc dbo.usp_fileStats1

as

-- insert first segment counters 

INSERT INTO dbo.filestats 

   (dbname, 

   fName,  

   TimeStart, 

   readsNum1, 

   readsBytes1, 

   readsIoStall1,  

   writesNum1, 

   writesBytes1, 

   writesIoStall1,  

   IoStall1 

   ) 

SELECT  

   DB_NAME(a.dbid) AS Database_name, 

   rIGHT(b.filename, CHARINDEX('\', REVERSE(b.filename)) - 1), 

   GETDATE(), 

   numberReads, 

   BytesRead, 

   IoStallReadMS, 

   NumberWrites, 

   BytesWritten, 

   IoStallWriteMS, 

   IoStallMS 

FROM  

   fn_virtualfilestats(NULL,NULL) a INNER JOIN 

   sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid 

ORDER BY  

   Database_Name 

go

 

--I use wait below, but in normal usage I would just call it at the top and bottom of a 

--  long running process or proc

exec dbo.usp_filestats1

WAITFOR DELAY '000:01:00' 

exec dbo.usp_filestats1

 

SELECT 

   f.dbname, 

   f.fName, 

   DATEDIFF(s,f.timeStart,e.timestart)  timeDiffSeconds, 

   e.readsNum1 - f.readsNum1 AS readsNumDiff, 

   e.readsBytes1 - f.readsBytes1 AS readsBytesDiff, 

   e.readsIoStall1 - f.readsIOStall1 AS readsIOStallDiff, 

   e.writesNum1 - f.writesNum1 AS writesNumDiff, 

   e.writesBytes1 - f.writesBytes1 AS writesBytesDiff, 

   e.writesIoStall1 - f.writesIOStall1 AS writesIOStallDiff,    

   e.ioStall1 - f.ioStall1 AS ioStallDiff    

--select *

FROM filestats  f

join (

select fstart.fname, fstart.timestart, min(fend.timestart) timeEndReal

from filestats fstart

join filestats fend

on fstart.fName = fend.fname

where fend.timestart > fstart.timestart

group by fstart.fname, fstart.timestart

) fxe

on f.timestart = fxe.timestart

and f.fname = fxe.fname

join filestats e

on fxe.timeEndReal = e.timestart

and fxe.fname = e.fname

--where f.fname = 'ga94_nba_cavs_06.mdf'

 

Thursday, July 25, 2013 - 2:21:27 PM - Ed - sqlscripter Back To Top (26012)

Sort by the read stalls:

Select DB_NAME(mf.database_id) as DBName,name as Logical_Name ,physical_name ,io_stall_read_ms , io_stall_write_ms,num_of_writes , io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) from sys.dm_io_virtual_file_stats(null,null) vfs left join sys.master_files mf on mf.database_id = vfs.database_id and mf.file_id = vfs.file_id order by io_stall_read_ms desc

 

Run it from a sqlps session, pipe to cvs for review and hand off, edit the path at the end and keep on 1 line in the .ps1 file.

invoke-sqlcmd -ServerInstance sqlshark -Database msdb –Query "Select DB_NAME(mf.database_id) as DBName,name as Logical_Name ,physical_name ,io_stall_read_ms , io_stall_write_ms,num_of_writes , io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) from sys.dm_io_virtual_file_stats(null,null) vfs left join sys.master_files mf on mf.database_id = vfs.database_id and mf.file_id = vfs.file_id order by io_stall_read_ms desc" | Export-Csv –path C:\powershell_scripting\dmv_pssss\output\DMV_results_IOReadStalls_$(get-date -f MM_dd_yyyy_HHmm).csv -notype

Monday, June 10, 2013 - 1:29:45 PM - Greg Robidoux Back To Top (25379)

Thanks Victor for pointing out the use of the DMV. I updated the tip to include this as well.

It looks like FILE_NAME() only returns the file name for the current database.

Monday, June 10, 2013 - 12:40:48 PM - Victor Girling Back To Top (25378)

Perhaps make this even tidier with the function FILE_NAME() to extract the file name.

SELECT FILE_NAME([file_id]),* 

FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Monday, June 10, 2013 - 9:50:03 AM - Greg Robidoux Back To Top (25373)

This tip has been updated.  Thanks Aleksei for pointing out that issue.

Greg

Monday, June 10, 2013 - 4:20:07 AM - Aleksei Back To Top (25363)

--mistyping in final select
SELECT 
   dbname,
   fName,
   timeDiff,
   readsNum2 - readsNum1 AS readsNumDiff,
   readsBytes2 - readsBytes2 AS readsBytesDiff,
   readsIoStall2 - readsIOStall1 AS readsIOStallDiff,
   writesNum2 - writesNum1 AS writesNumDiff,
   writesBytes2 - writesBytes2 AS writesBytesDiff,
   writesIoStall2 - writesIOStall1 AS writesIOStallDiff,   
   ioStall2 - ioStall1 AS ioStallDiff
FROM dbo.filestats

readsBytesDiff & writesBytesDiff always 0














get free sql tips
agree to terms