PowerShell Scripting Disclaimer:
This script is provided "as is" for the purpose of illustrating how product tasks may be performed in conjunction with PowerShell. Support shall not be liable for any direct, indirect, incidental, consequential, or other damage alleged in connection with the furnishing or use of this script or of the principles it demonstrates. See PowerShell Scripting Support for more information.
SQLIO is a Microsoft provided utility for measuring disk IO and MB/s performance. The tool is highly configurable and will use a testfile to ascertain random and sequential read / write statistics. This solution will focus on first collecting the data and subsequently analysing the data.
Collecting SQLIO statistics
- On the Core server download and install SQLIO from SQLIO . The default install path is C:\Program Files (x86)\SQLIO (retain this path for compatibility with the attached Powershell scripts.
- Cancel all transfers and replication and stop the Core service.
- Download the SQLIOCollect powershell file below or copy below code into Notepad and save as SQLIOCollect with ps1 extension.
- Execute the SQLIOCollect.ps1 script.
- When prompted select the disk where the repository is located (eg. E:\). A testfile.dat 1GB in size will be created and the script will run for approximately 12 minutes.
- Once complete the disk performance report will be created in c:\SQLIOstats.txt.
Interpreting SQLIO statistics
- On a PC / Laptop with Excel installed download the SQLIOExcel.ps1 file at the bottom of this KB
- Run the SQLIOExcel.ps1 with Administrative permissions
- When prompted point to the c:\SQLIOstats.txt file collected in the previous steps
- This will output a file called C:\SLQIO_Pivot.xlsx
- Open the spreadsheet and note the 2 worksheets - i) Raw Data and ii) Pivot
- Select Pivot workdheet and click the PivotTabe graphic
- Under the PivotTable Field List tick Operation, IOType, IOPS, MBs/Sec and Caption
- In the Drag fields area drag the Caption field from Row Labels and move to Column Labels
- Click Sum of MBs/Sec and Choose Value Field Settings - change the Summarize value field by to Average. This will give a summary of the random / sequential read and write performance.
- To generate a graph click the table and from the PivotTable Tools toolbar click Options
- Click Pivotchart off the toolbar and click OK on the Insert Chart popup screen. An excel file with performance summary and chart is now available for review.
Is your Rapid Recovery solution working as efficiently as possible? Learn more about our Rapid Recovery Health Check