Authors: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan Technical Reviewers

Appendix

The scripts and guidance covered in this section can be used to facilitate the information given in this article:

• 
  Guidance on defining key performance indicators (KPIs): Provides information on KPIs that you can use to monitor application tier performance and user characteristics while running SQL Server in Azure.
  
• 
  Raw storage performance testing scripts: Discusses the usage of SQLIO.
  
• 
  Snapshot wait stats script: Demonstrates how to automate the calculation of waits during a time interval.
  
• 
  Requests executing on the system script: Provides a view of requests executing on SQL Server sorted by total elapsed time, includes the corresponding plan for each statement.
  
• 
  Top query statements and plan by total CPU time script: Provides information from Query Stats on the top overall resource consumers.
  
• 
  Snapshot spinlock stats script: Uses a temporary table to provide the delta of spinlock information since the last execution of this script.
  
• 
  Snapshot I/O stats script: Uses a temporary table to provide the delta of I/O information since the last execution of this script.
  

Raw storage performance testing scripts

You can run the following SQLIO scripts in Azure Virtual Machine to generate some of the most common I/O patterns that SQL Server utilizes and measure related performance results on different storage configurations.

The following SQLIO test scripts demonstrate testing random 8K reads/writes (a typical OLTP I/O pattern), sequential writes for log files, and large sequential reads and writes for table scans and OLAP workloads.

The script uses the following options:

• 
  The -k option to specify the I/O operation type (read or write)
  
• 
  The -s option to specify the test duration in seconds
  
• 
  The –f option to specify the type of I/O access (sequential or random)
  
• 
  The –o option to specify the number of outstanding requests
  
• 
  The –b option to specify the size of the I/O request in bytesblock size
  
• 
  The –LS option to capture the disk latency option
  
• 
  The –F option to specify the name of the file which contain the test files to run SQLIO against
  

Copy and save the following script in a file called exectests.bat.

::Test random 8K reads/writes

sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam.txt

sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam.txt

::Test random 32K reads/writes (for example, SQL Server Analysis Services I/O pattern) sqlio -kW -s300 -frandom -o32 -b32 -LS -Fparamnew.txt sqlio -kR -s300 -frandom -o32 -b32 -LS -Fparamnew.txt

::Test small sequential writes

sqlio -kW -s180 -fsequential -o1 -b4 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o1 -b8 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o1 -b16 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o1 -b32 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o1 -b64 -LS -Fparam.txt

::Test large sequential reads/writes

sqlio -kR -s180 -fsequential -o8 –b8 -LS -Fparam.txt

sqlio -kR -s180 -fsequential -o8 -b64 -LS -Fparam.txt

sqlio -kR -s180 -fsequential -o8 -b128 -LS -Fparam.txt

sqlio -kR -s180 -fsequential -o8 -b256 -LS -Fparam.txt

sqlio -kR -s180 -fsequential -o8 –b512 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o8 –b8 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o8 -b64 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o8 -b128 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o8 -b256 -LS -Fparam.txt

sqlio -kW -s180 -fsequential -o8 –b512 -LS -Fparam.txt

The following is a copy of the param.txt configuration file that we referenced in our test scripts. Basically, the test scripts run a series of tests against the drive or drives specified in the param.txt file. This file should reside in the same directory as SQLIO.exe. The options on each line of the param.txt file are as follows, where 0x0 is a mask value:

PathToTestFile NumberofThreadsPerTestFile 0x0 TestFileSizeinMegaBytes

This param.txt file skips operating system and temporary disks (C: and D:) and tests a 16 data disks volume (F:) (using 1 thread per disk), with a file size of 50 GB.

#c:\testfile.dat 2 0x0 100

#d:\testfile.dat 1 0x0 1000

f:\testfile.dat 16 0x0 50000

Open a Command Prompt window, then run the test batch file as follows.

exectests.bat > results.txt

This operation captures all test results in a text file that can be processed manually or automatically to extract relevant disk performance figures later.