sqlrx logo
SQLRx Tip of the Month


December 2010
Greetings!

Disk performance is a common issue on SQL Server systems.  Use this handy tip to find out how long data files are waiting with Filestats.  Also be sure to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks.
___________________________________________________________________
SQL 2008  SQL Server Administration:  It is very useful to know how much SQL Server is waiting for I/O and what files are handling the most work or experiencing the poorest performance. The virtual file stats DMF provides this information, and is ideal for periodic captures, e.g., at the beginning, middle, and end of a day.  This function returns one row per file and can return this information either for specific files or for every file attached to a SQL Server instance.
___________________________________________________________ 

To retrieve information for all files attached to a 2005 or 2008 instance, use the following syntax:

select * from sys.dm_io_virtual_file_stats (NULL, NULL)

The equivalent on SQL 2000 is as follows:

select * from ::fn_virtualfilestats(-1,-1)

Some of the most useful information is shown below:

�  Number of reads and writes issued against this file

�  Total number of bytes read from and written to this file

�  Total time, in milliseconds, users waited for I/O completions overall (also reads and writes specifically on 2005 and 2008)

�  Number of disk bytes used by this file

Since these values are cumulative from the last time the SQL Server instance was restarted, differences between samples must be computed.  Either SQL Server T-SQL or Excel can be used to perform these calculations.  In addition, the database and file IDs must be translated into meaningful names using the sys.databases  and sys.database_files DMVs on 2005 and 2008.  For those still using 2000, system tables must be used to decode these values.

Using this information, a DBA can prove whether the disk subsystem is performing adequately and possibly find alternative disk locations to which database files may be moved. 

 _________________________________________________________________

 
                            
SQLRx is a part of www.msdev.com, a Microsoft training site!  Check the site periodically to view new training videos.
 
If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month or msdev video, email it to us!  If you missed any of our previous tips, you can view them here.
 
For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].  

Sincerely,

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | [email protected] 
 
 
Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit
 
25 Years of Credibility & Trust
 

Join our email list          Check out our events          SQLRx Downloads