SQLBENCH - microsoft/CSS_SQL_Networking_Tools GitHub Wiki

SQL Benchmark Tool (SQLBENCH)

This is a command-line tool that will run a predefined set of benchmark tests on CPU and memory, the file system, and a SQL Server database. This allows you to get a comparative summary between different servers. CPU and memory tests are always run locally. File and SQL tests can be run locally or remotely (UNC path for file and remote SQL Server respectively).

Scenarios

Scenario 1:

Bill moved his SQL Server from a regular computer to a VM. His users complain their apps run slower.

  1. From a client machine, run the SQL test to the old SQL Server and the new SQL Server and compare.
  2. From a client machine, run the file test to both SQL Servers and compare. Maybe the virtual network is slower?
  3. Run the CPU test on both SQL Servers. Are the CPU stats significantly different?

Scenario 2:

Jane upgraded from SQL 2012 to SQL 2019 on a new machine. Users complain itโ€™s slower on the new machine.

  1. From a client machine, run the SQL test to the old SQL Server and the new SQL Server and compare.
  2. From a client machine, run the file test to both SQL Servers and compare. Maybe the network card is slower?
  3. Run the CPU test on both SQL Servers. Are the CPU stats significantly different?

Usage

SQLBENCH.EXE [-n timestorun] [-cpu] [[-file "folderpath"]...] [[-sql "connectionstring"]...] [-output "folderpath"]

-n      the number of times to run the test(s)
-cpu    starts the CPU test
-file   starts the I/O test
-sql    starts the SQL Server test
-output the output path where various output files are written (MathTests.csv, Database.csv, File.csv). The path must already exist.

Installation

  1. Download SQL_BENCH.ZIP v1.0.240.0
  2. Extract SQLBENCH.EXE to a folder of your choice. It is ready to use.

Software requirements

This is a C# application that requires the full .NET 4x framework on Windows.

Permissions

SQLBENCH does not require Administrative permissions, but you must have permission to the folder, file share, or SQL Server database that you are benchmarking.

Output files

  • All tests write to the Console Window and a file called TestLog.txt
  • CPU/memory test writes a file called MathTests.csv to the current folder or a folder of your choice
  • File test creates a file called test123.bin in the specified folder (-file switch) and reads and writes to it. It writes to a file called File.csv
  • The SQL test creates a TABLE called P1 in the server and database specified and reads and writes to it. It writes to a file called Database.csv (the specified database must already exist before the test)

Important: This tool does not communicate with any external systems other than a SQL Server or File Server as specified in the command line and to write the three log files to the current folder or the folder specified. It does not modify the system in any way other than as described above. The test table is removed at the end of the run.

Sample executions

Database Test - sample output

c:\> SQLBENCH -sql "server=sqlprod01.contoso.com;integrated security=sspi;database=northwind"

SQL Benchmark Tool - Version 1.0.229.0
Copyright (c) Microsoft Corporation.
Licensed under the MIT license.
Written by the CSS SQL Networking Team.

Machine Name: WIN10CLIENT
Processors: 12
OS Version: 6.2.9200.0
Version: 1809
.NET Version: 4.0.30319.42000
User Name: CONTOSO\johndoe
Current Directory: c:\temp

Database Test: server=sqlprod01.contoso.com;integrated security=sspi;database=northwind
Connection Open: 415 operations per second.
Connection Close: 7,901 operations per second.
Insert Row: 235 operations per second.
Write BLOB: 167 operations per second.
Read Rows: 958 operations per second.
Read BLOB: 211 operations per second.
Database Test duration: 249,755 ms.

End of Tests.

CPU Test - sample output

C:\Tools\SQLBench\SQL_BENCH> SQLBENCH.EXE -n 10 -cpu -output "c:\temp"

SQL Benchmark Tool - Version 1.0.0.237
Copyright (c) Microsoft Corporation.
Licensed under the MIT license.
Written by the CSS SQL Networking Team.

Machine Name: WINSERVER
Processors: 8
OS Version: 6.2.9200.0
Version: 2009
.NET Version: 4.0.30319.42000
User Name: CONTOSO\jondoe
Current Directory: C:\Tools\SQLBench\SQL_BENCH

Integer Math: 138,961,973 operations per second.
Float Math: 150,967,732 operations per second.
Decimal Math: 149,091,495 operations per second.
String Concatenation: 3,503,793 operations per second.
Create Tree: 2,931,692 operations per second.
Tree Span in order: 71,174,377 operations per second.
Tree Span Pre order: 94,696,970 operations per second.
Tree Span Post order: 111,856,823 operations per second.

Integer Math: 143,651,173 operations per second.
Float Math: 149,896,038 operations per second.
Decimal Math: 160,980,609 operations per second.
String Concatenation: 4,170,142 operations per second.
Create Tree: 4,627,487 operations per second.
Tree Span in order: 143,472,023 operations per second.
Tree Span Pre order: 223,713,647 operations per second.
Tree Span Post order: 187,969,925 operations per second.

Integer Math: 136,418,744 operations per second.
Float Math: 156,450,044 operations per second.
Decimal Math: 164,942,532 operations per second.
String Concatenation: 4,037,957 operations per second.
Create Tree: 4,128,819 operations per second.
Tree Span in order: 121,506,683 operations per second.
Tree Span Pre order: 110,497,238 operations per second.
Tree Span Post order: 77,579,519 operations per second.

Integer Math: 117,429,008 operations per second.
Float Math: 168,691,899 operations per second.
Decimal Math: 162,118,605 operations per second.
String Concatenation: 4,926,594 operations per second.
Create Tree: 4,345,937 operations per second.
Tree Span in order: 76,219,512 operations per second.
Tree Span Pre order: 94,517,958 operations per second.
Tree Span Post order: 74,460,164 operations per second.

Integer Math: 156,690,000 operations per second.
Float Math: 180,586,547 operations per second.
Decimal Math: 117,410,880 operations per second.
String Concatenation: 3,859,067 operations per second.
Create Tree: 2,776,235 operations per second.
Tree Span in order: 50,000,000 operations per second.
Tree Span Pre order: 63,775,510 operations per second.
Tree Span Post order: 47,036,689 operations per second.

Integer Math: 129,507,926 operations per second.
Float Math: 176,050,529 operations per second.
Decimal Math: 191,053,904 operations per second.
String Concatenation: 5,479,152 operations per second.
Create Tree: 3,993,610 operations per second.
Tree Span in order: 36,376,864 operations per second.
Tree Span Pre order: 58,754,407 operations per second.
Tree Span Post order: 42,844,901 operations per second.

Integer Math: 145,716,385 operations per second.
Float Math: 158,243,108 operations per second.
Decimal Math: 179,901,858 operations per second.
String Concatenation: 3,524,726 operations per second.
Create Tree: 2,605,524 operations per second.
Tree Span in order: 44,014,085 operations per second.
Tree Span Pre order: 40,700,041 operations per second.
Tree Span Post order: 52,465,897 operations per second.

Integer Math: 154,740,859 operations per second.
Float Math: 181,235,067 operations per second.
Decimal Math: 178,140,206 operations per second.
String Concatenation: 4,695,387 operations per second.
Create Tree: 2,764,722 operations per second.
Tree Span in order: 37,064,492 operations per second.
Tree Span Pre order: 38,343,558 operations per second.
Tree Span Post order: 35,498,758 operations per second.

Integer Math: 159,874,220 operations per second.
Float Math: 193,553,586 operations per second.
Decimal Math: 172,459,781 operations per second.
String Concatenation: 3,436,308 operations per second.
Create Tree: 2,254,283 operations per second.
Tree Span in order: 28,645,087 operations per second.
Tree Span Pre order: 38,299,502 operations per second.
Tree Span Post order: 42,052,145 operations per second.

Integer Math: 154,127,877 operations per second.
Float Math: 188,579,385 operations per second.
Decimal Math: 169,331,597 operations per second.
String Concatenation: 4,566,419 operations per second.
Create Tree: 2,292,526 operations per second.
Tree Span in order: 25,893,320 operations per second.
Tree Span Pre order: 18,308,312 operations per second.
Tree Span Post order: 38,124,285 operations per second.

End of Tests.

File I/O Test - Sample Output

C:\Tools\SQLBench\SQL_BENCH>SQLBENCH.EXE -n 10 -output "c:\temp" -file "c:\temp"

SQLBENCH.EXE -n 10 -output "c:\temp" -file "c:\temp"
SQL Benchmark Tool - Version 1.0.0.237
Copyright (c) Microsoft Corporation.
Licensed under the MIT license.
Written by the CSS SQL Networking Team.

Machine Name: WINSERVER
Processors: 8
OS Version: 6.2.9200.0
Version: 2009
.NET Version: 4.0.30319.42000
User Name: CONTOSO\johndoe
Current Directory: C:\Tools\SQLBench\SQL_BENCH

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 261,947 milli-seconds.
File Sequential Read: 366,533 operations per second.
File Sequential Write: 363,616 operations per second.
File Random Read: 188,249 operations per second.
File Random Write: 193,045 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 259,193 milli-seconds.
File Sequential Read: 333,989 operations per second.
File Sequential Write: 378,719 operations per second.
File Random Read: 188,482 operations per second.
File Random Write: 187,480 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 294,520 milli-seconds.
File Sequential Read: 383,011 operations per second.
File Sequential Write: 383,797 operations per second.
File Random Read: 188,179 operations per second.
File Random Write: 193,753 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 304,550 milli-seconds.
File Sequential Read: 351,583 operations per second.
File Sequential Write: 371,593 operations per second.
File Random Read: 188,386 operations per second.
File Random Write: 187,407 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 264,599 milli-seconds.
File Sequential Read: 373,698 operations per second.
File Sequential Write: 384,212 operations per second.
File Random Read: 189,002 operations per second.
File Random Write: 190,699 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 282,925 milli-seconds.
File Sequential Read: 384,288 operations per second.
File Sequential Write: 371,109 operations per second.
File Random Read: 190,837 operations per second.
File Random Write: 191,157 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 265,713 milli-seconds.
File Sequential Read: 365,451 operations per second.
File Sequential Write: 365,512 operations per second.
File Random Read: 162,361 operations per second.
File Random Write: 171,214 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 285,547 milli-seconds.
File Sequential Read: 345,103 operations per second.
File Sequential Write: 347,230 operations per second.
File Random Read: 160,851 operations per second.
File Random Write: 152,128 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 285,117 milli-seconds.
File Sequential Read: 358,287 operations per second.
File Sequential Write: 371,253 operations per second.
File Random Read: 162,813 operations per second.
File Random Write: 148,787 operations per second.

File Test: c:\temp\test123.bin
File Creation (100,000 * 512-byte records): 249,974 milli-seconds.
File Sequential Read: 374,377 operations per second.
File Sequential Write: 383,640 operations per second.
File Random Read: 191,419 operations per second.
File Random Write: 189,734 operations per second.

End of Tests.