h

IO

mon

™

  The I/O Performance Monitor

Home  |  Products & Services  |  Contact  |  About hyperI/O^sm

Big Picture

hIOmon Fragmented File I/O Metrics Analysis Add-On
Microsoft® Office Excel Template/Macros Usage

Product Info

The hIOmon "Device and File I/O Performance Analysis Add-On" provides Microsoft® Office Excel template/macros that can also be used by the hIOmon "Process I/O Performance Analysis Add-On" and by the hIOmon "Fragmented File I/O Operation Metrics Analysis Add-On" (in which case the hIOmon Excel template can be used to directly import the "fragmented file I/O operation" performance metrics from the "hIOmon Manager Export File" that was created/activated through the use of the configuration script "Fragmented File I/O Performance Metrics" option).

In addition, the hIOmon Excel template includes a hIOmon Excel macro that lets you easily generate a variety of graphs/charts based upon the metrics saved within the hIOmon Manager Export File.  For example, you can graph upon a logical disk basis over a period of time the total number of file I/O operations that were associated with Physical Device "split I/O" operations. 

Features

Architecture

Screen Shots

Documentation

Download

NOTE:

The "fragmented file I/O operation" performance metrics that are collected, exported, and displayed through the use of the hIOmon "Fragmented File I/O Operation Metrics Analysis Add-On" and accompanying hIOmon Excel template/macros reflect the actual file I/O operations performed through the normal, everyday use of your very own applications and associated files!  And no file, application, or operating system changes are required!  

The hIOmon Excel template/macros along with the configuration script provide a quick and easy way to help you evaluate and understand the specific performance impact of file fragmentation, once again based upon empirical metrics directly related to the individual files that you are actually using.  Additional information about the hIOmon Excel template/macros is provided below (and full details provided within the "hIOmon Add-On User Guide" document).

hIOmon Excel Template to Import Fragmented File I/O Operation Metrics

The hIOmon Excel template that is used by the hIOmon "Fragmented File I/O Operation Metrics Analysis Add-On" is provided with the hIOmon "Device and File I/O Performance Analysis Add-On".  This hIOmon Excel template includes an Excel macro that can be used to generate Excel Pivot Table/Charts based upon the hIOmon Manager Export File that has been loaded into the worksheet template.

The hIOmon Manager Export File is a CSV export file containing I/O operation performance metrics that have been collected by the hIOmon I/O Monitor.  The "configuration" script can be used to easily and quickly configure the hIOmon software so that I/O operation performance metrics for the desired logical/physical disks are collected and exported to a hIOmon Manager Export File for subsequent use with the Excel template file and its associated macros.

Specific instructions for loading the hIOmon Manager Export File contents (i.e., the exported I/O operation performance metrics) into the Excel template file and for using the accompanying macro to create Pivot Table charts/graphs are described below.  An online tutorial is also available.

Please refer to the hIOmon Excel template/macros description page for the requirements associated with the hIOmon Excel template and its accompanying macros.

Please see the hIOmon Excel template/macros description page for the location information regarding the hIOmon Excel template.

The hIOmon Manager Export File to be imported can be found within the "\hIOmonGr\ExportFiles" folder within the "..\hIOmon\Support\" directory where the hIOmon software was installed.

The "configuration" script (which can be used to automatically configure the hIOmon software to collect "fragmented file I/O operation" performance metrics) will also automatically create a hIOmon Manager Export File (named "FragIOstatsExportFile" by default) that is to include the particular I/O operation performance metric types for which collected metrics are to be exported/saved.

The hIOmon Manager Export File is a standard "Comma Separated Values (CSV)" delimited file.  As such, it can be imported simply by opening the export file within the hIOmon Excel template.

First, simply double-click upon the hIOmon Excel template file.  Due to the current security settings in effect, the use of macros within Excel might be disabled or limited, in which case a message prompt can be displayed as shown below:

If the message above (or one similar) is displayed, then click on the "Enable Macros" button so that the hIOmon Excel macros included within the Excel template file can be used as an easy and quick way to automatically generate Pivot Table/Charts based upon metrics imported from hIOmon Manager Export File.  See requirements above for additional information about enabling/disabling Excel macros.

After the template file has been opened, click on the "Open" file folder icon upon the tool bar (or use the "File->Open" menu option, or alternatively shortcut key "Ctrl+O") to bring up the standard Windows® "Open File" dialogue box shown below.  Look within the "\hIOmonGr\ExportFiles" folder (within the "..\hIOmon\Support\" directory as mentioned above) for the "FragIOstatsExportFile.csv" file.  Note that you might need to change the "Files of types" to "Text Files (*.prn; *.txt; *.csv)" as shown below so that ".csv" files will be included within the displayed list of files.

Select the file and then click on the "Open" button in the lower right corner.

As shown below, the hIOmon Manager Export File selected within the Open Dialogue box (i.e., "FragIOstatsExportFile.csv" as shown above) will then automatically be imported into an Excel worksheet using the Excel template file.  Note that the name of this worksheet will be that of the imported hIOmon Manager Export File.

Also please note that:

• If the hIOmon Manager Export File was created using the "Specify devices" configuration option, then the Logical Disk names (e.g., "E:") as well as Physical Disk volume names (e.g., "\Device\HarddiskVolume2:") that appear will be listed under the "File Name" column heading.  These device names represent those particular physical device volumes for which "Device Summary" I/O operation performance metrics were collected in accordance with the Filter Selection that was created/activated by the configuration script.
  
  

• Only "Device Summary" metrics, which represent the aggregate for all of the monitored files associated with a particular disk, were written/saved by the hIOmon Manager to the hIOmon Manager Export File that is created using the "Specify devices" configuration option.  As set up by the configuration script, "File Summary" metrics (i.e., separate summary metrics upon an individual file basis for each of the files monitored) are not exported to the hIOmon Manager Export File.  Consequently, only "Device Summary" metrics will be displayed/available within the Excel worksheet.
  
  

• The "Device Summary" I/O operation performance metrics reflect cumulative values since the hIOmon I/O Monitor began collecting metrics for the respective device.  For instance as shown below, the "Read IOP Count" metric for the "E:" Logical Disk was a value of 70803 at hour 9 and a value of 130750 at hour 10 (i.e., 59947 read I/O operations for monitored files residing upon the "E:" drive where observed by the hIOmon I/O Monitor during the one-hour period).  Likewise, the average read I/O operation queue length for the first 70803 read I/O operations for the "E:" drive was 1.147, and 1.104 was the overall read average queue length at hour 10 (for all 130750 read I/O operation observed up to that time).
  
  Please see "Appendix A - Export Data Definitions" in the hIOmon User Guide for additional information about each of the metric types displayed.
  
  

• If you double-click upon the "FragIOstatsExportFile.csv" file directly (rather than "open" it into the Excel template), then the hIOmon Manager Export File file will immediately be imported into an Excel spreadsheet but the hIOmon Excel macro will not be available for use; consequently, you will need to manually generate Pivot Tables/Charts (typically by using the PivotTable and PivotChart Wizard provided by Excel).

Using the Excel template file and the procedure outlined above (i.e., double-click on the Excel template file, click on the "Enable Macros" button if necessary, and then open the hIOmon Manager Export File) allows you to quickly access the hIOmon Excel macro to easily generate a variety of Pivot Table/Charts as described here.

Back to Top

hyperI/O^sm -  improving the performance of storage I/O in computer systems