Raw storage performance testing scripts: Discusses the usage of SQLIO. Demonstrates how to automate the calculation of waits during a Snapshot wait stats script: time interval. Provides a view of requests executing on SQL Server Requests executing on the system script: sorted by total elapsed time, includes the corresponding plan for each statement. Provides information from Query Stats Top query statements and plan by total CPU time script: on the top overall resource consumers. Uses a temporary table to provide the delta of spinlock Snapshot spinlock stats script: information since the last execution of this script. Uses a temporary table to provide the delta of I/O information since Snapshot I/O stats script: the last execution of this script. Raw storage performance testing scripts You can run the following 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 option to specify the I/O operation type (read or -k The -s option to specify the test duration in seconds The option to specify the type of I/O access (sequential or random) –f The option to specify the number of outstanding requests –o The option to specify the size of the I/O request in bytesblock size –b The option to capture the disk latency option –LS The option to specify the name of the file which contain the test files to run SQLIO against –F Copy and save the following script in a file called exectests.bat. ::Test random 8K 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) -kW -s300 -frandom -o32 -b32 -LS -Fparamnew.txt
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 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 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 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) -kW -s300 -frandom -o32 -b32 -LS -Fparamnew.txt
Performance%20Guidance%20for%20SQL%20Server%20in%20Windows%20Azure%20Virtual%20Machines.docx