Benchmark servers remotely with SQLIO and PowerShell

PowershellYou’ve got a zoo of SQL Server instances and have to establish a baseline of IO subsystem performance?  Perhaps, you need to run IO benchmarks on a scheduled basis on IaaS servers just to see a trend over the time? Or, simply, you tasked to configure yet another SQL instance and would like to figure out what the offered hardware is capable? For all those calls the PowerShell automation is a great point to start, that can evolve infinitely. In this post I going to reveal a story of mixing Powershell, PsExec, SQLIO and Invoke-Sqlcmd together to achieve the goals.


The benchmark in action

Firstly, couple of simple examples how the script can be used.

Example 1. Benchmark remote server and return information to the console

Following code will stress drive D of the remote server with default parameters values like duration, threads number,  type of access:

> .\RemoteSQLIO.ps1 -Server NL000REP03 -Drive D -ReadWrite W -workfolder "C:\Temp\SQLIO"

Result  displayed on the console, however it also can serve as the input for a pipeline:

01. The result displayed as an object
01. The result displayed as an object


Example 2. Benchmark remote server and save result to a database table

There are many possible scenarios exists when unattended , perhaps scheduled, benchmark is required. Following script brings extra parameters to fulfill that extra requirement:

$params = @{ 
             'Drive'= 'D';
             'TestFileSizeGB'= 4 ;
             'Duration'= 5 ;
             'Threads'= 8 ;
             'OutstandingRequests'= 16 ;
             'ReadWrite'= 'R' ;
             'RandomSequential' = 'S';             
             'workfolder' = 'C:\Temp\SQLIO';             

             #SQL Server logging:
             'logDatabase' = 1;
             'logAppendData' = 1;
             'SQLServerInstance' = '(local)';
             'Database' = 'tempdb';
             'TableName' = 'dbo.SQLIO';

.\RemoteSQLIO.ps1  @params

As the result of execution, IO metrics stored in the table, specified by script – dbo.SQLIO:

02. The result stored in a SQL Server table
02. The result stored in a SQL Server table

Such table will be created automatically if it not exists yet.

Example 3. Benchmark mount point on a remote server

SQL Server failover clustering often comes with LUNs attached as mount points rather than drives. For such scenarios, sample script will look like:

$params = @{ 
 'Drive'= 'E:\MountPoint';
 'TestFileSizeGB'= 4 ;
 'Duration'= 1 ;
 'Threads'= 8 ;
 'OutstandingRequests'= 16 ;
 'ReadWrite'= 'R' ;
 'RandomSequential' = 'S'; 
 'workfolder' = 'C:\Temp\SQLIO';  

.\RemoteSQLIO.ps1 @params


How does it work?

The solution built on top of PowerShell, PsExec, Invoke-SqlCmd and SQLIO, therefore all these components are in requirements to be available on the server-issuer of the benchmark calls. Following diagram is about the internal logic:

03. Remote SQLIO. Internal logic
03. RemoteSQLIO. Internal logic


Installation and configuration

The solution is consciously not distributed as packaged unit which includes all binaries and scripts. Mainly, because all used components are subjects for evolution and development, therefore, the most recent version can be obtained from the official website. Following steps are necessary to put components together:

01. Software to obtain and install:
  • Powershell 2.0 or higher. This is the base component since it delivered as the part of all modern versions of Windows.
  • PsExec. This sysinternals utility necessary for execution programs remotely. Link to official page for a download.
  • SQLIO. This tool developed by Microsoft for I/O benchmarking and worth to say – its usage is not limited by SQL Servers. Link to download.
  • Invoke-Sqlcmd. The Invoke-Sqlcmd cmdlet is a wrapper for sqlcmd.exe  in a Windows PowerShell environment. It required for scenarios, when results needs to be stored in SQL Server database. Invoke-Sqlcmd shipped with SQL Server client tools, starting SQL Server 2008. However, it also available as part of the SQL Server feature pack. Link to download.
  • RemoteSQLIO.ps1. This powershell script is a result of my journey and serves as the glue of the solution. Link to  download.
02. Combine things together:
  • Create the working folder, for instance: C:\Temp\SQLIO.
  • Download PsExec and copy executable psexec.exe to the working folder.
  • Download SQLIO and copy sqlio.exe to the working folder.
  • Download script RemoteSQLIO.ps1 and place it to the working folder.
03. Permissions:
  • Make sure that account you use to run a powershell script has local administrator permissions on a remote server, selected to be benchmarked.
  • For scenarios when database logging involved, make sure that account has enough permissions to create table on specified SQL Server instance/database.
  • Again, for database logging purposes, script require SQL Server snap-ins to be registered. In case of run-time errors related to “invoke-sqlcmd”, please follow instructions described in this post.
  • And last, but not least, PowerShell permissions to run the script: Set-ExecutionPolicy -ExecutionPolicy RemoteSigned


Script parameters

The RemoreSQLIO script has predefined set of parameters. Many of them are directly mapped to corresponding sqlio.exe arguments:

Parameter Required Default Description
Server Yes Server to benchmark
Drive Yes Drive or mount point to benchmark. For instance, E or E:\MountPoint
ReadWrite Yes Specify either R or W (read or write)
workfolder Yes Folder with required utilities and scripts. For instance, C:\Temp\SQLIO
Duration No 60 Duration of test (in seconds). For initial tests, running for 5-10 minutes per I/O size is recommended to get a good idea of I/O performance.
Threads No 8 Threads number, typically: 4-8
Outstandingrequests No 16

Number of outstanding I/O requests per thread. When attempting to determine the capacity of a given volume or set of volumes, start with a reasonable number for this and increase until disk saturation is reached (that is, latency starts to increase without an additional increase in throughput or IOPs). Common values for this are 8, 16, 32, 64, and 128.

Keep in mind that this setting is the number of outstanding I/Os per thread.

TestFileSizeGB No 4 Size of preallocated file in gigabytes
RandomSequential No R Type of I/O to issue. R or (random or sequential)
logDatabase No 0 Flag to enable logging to database. Values: 0 or 1
logAppendData No 0 Flag to append result to existing rows. Values: 0 or 1
SQLServerInstance No null Instance, database and table to store results of benchmark
Database No null Database to store results of benchmark
TableName No null Table to store results of benchmark


Final words

I hope that script can be useful and save someone a plenty of time by running benchmarks remotely and unified way. Looking forward for a feedback and ideas about usage and improvement.



2015-08-05: Added a support for mount points