Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

Getting Database I/O Details

If I/O activity appears to be high, more information regarding I/O specifics for SQL Server 2000 and higher can be obtained. For SQL Server 2000 and 2005, I/O details for databases and files can be obtained, and for SQL Server 2005, the DBA can drill down a little further and get I/O at the object level.

To get I/O statistics for files and databases in SQL Server 2000, the following two procedures, which presents I/O metrics for all files and databases on a SQL Server can be used:

* up_wl_file_io

This script is combined with the following script
-- Script is available in the Online Code Depot

* up_wl_file_stats

-- Script is available in the Online Code Depot

Executing the up_wl_file_io procedure will provide statistics.

From an activity interest standpoint, the DBA should certainly want to pay attention to the reads and writes columns as those columns will reveal which database is under the most I/O duress. From a throughput viewpoint, the iostall column should be zeroed in on as it will reveal which files are experiencing the most delays in terms of accomplishing I/O goals. The higher the iostall, the worse the files are doing. These files are likely candidates for physical disk relocation or other such actions.

With SQL Server 2005, more detail on the iostall issue can be obtained. The iostall column returned from the fn_virtualfilestats function is broken out into two columns: IoStallReadMS and IoStallWriteMS. These columns give details, in milliseconds, regarding read and write throughput problems for each file. With this new granular information, it can be determined if read or write problems are causing the issues instead of just I/O in general.

This SQL Server 2005 enhancement means the procedures above need to be altered to account for the iostall column being broken up into two columns. The temporary table holding the I/O statistical data must be changed, as does the INSERT statement that inserts file metric info into it and the final SELECT, which presents the information.

SQL Server 2005 presents an easy way to drill deeper in order to retrieve actual object I/O statistical data. The sys.dm_db_index_operational_stats function provides good data that helps pinpoint hot objects in various databases. This function can be used to get data back on: an entire SQL Server but is not recommended if there are many of databases and objects; a particular database; or a specific object. There are many columns returned by the function, but the object_io.sql query below will give some of the most interesting statistics that will help reveal the objects under heavy I/O pressure.


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter