Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  SQL Server Tips by Burleson

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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter