Shulou(Shulou.com)06/01 Report--

As far as DBA is concerned, we all do performance tests on the new server. I will start with the benchmark test of TPC and use HammerDB to evaluate the overall performance (formerly HammerOra) and compare it with vendor data. Then use DiskSpd to test disk IO performance metrics (formerly known as SQLIO), then SQLIOSIM to test storage integrity, and then to ostress concurrent stress testing. For database server migration, we will also collect and play back Profiler Trace, and compare key performance counters during the collection.

Let me focus on using HammerDB's TPC-C for SQL Server benchmarking.

It is difficult to write your own load test code

In order to simulate the load of the database, you want to have multiple application users and mixed data read and write statements. You don't want to always update the same value for a single row, or just repeat to insert false values.

It's not impossible to write load test scripts in Powershell, C # and other languages, but it's too time-consuming, so you need to create or restore the database and test it accordingly.

Free and simple stress test SQL Server: use HammerDB to simulate OLTP database load

HammerDB is a free, open source tool that allows you to run TPC-C and TPC-H benchmarks against SQL Server, Oracle, MySQL, and PostgreSQL. You can use HammerDB to generate scripts against a database and import tests. HammerDB also allows you to configure the length of a test run, define the warm-up phase, and the number of virtual users per run.

First of all, HammerDB has an automated queue that allows you to integrate multiple virtual users running at different levels into one queue-you can get a smooth result curve of virtual user performance at what level. You can also use it to simulate different loads for demonstration or research purposes.

Advantages and disadvantages of HammerDB on SQL Server

HammerDB is a free tool that is also extremely easy to access and a quick way to start benchmarking and load simulation. Its automatic program feature is that the running workload is quite automatic.

The main disadvantage is that it has a learning curve. The user interface is not very intuitive and takes time to get used to. After you use this tool for a while, it will be easier.

HammerDB does not run every benchmark. It does not run the TPC-E benchmark, for example, SQL Server is more keen on the current more developed OLTP benchmark TPC-E. If you run a TPC-C benchmark with HammerDB, you should understand that it cannot be directly compared to the TPC-C benchmark results provided by the vendor. However, it is free, fast and easy to use.

Benchmark use case

The benchmark load does not accurately simulate the characteristics of your application. Each load is unique and has different bottlenecks in different systems. For many use cases, using predefined benchmarks is still very effective, including the following performance comparisons:

Multiple environments (e.g. old physical servers, new virtual environments)

Use different just-in-time points of various factors (for example, performance of virtual machines using shared storage and shared host resources)

Points before and after configuration changes

Of course, running a benchmark against a database server can affect the performance of other SQL Server databases or other virtual machines on the same host, and you make sure you have a good test plan in production.

For self-study and research, having a preconfigured load is great.

Start using benchmarking

You can start by reading the "SQL Server OLTP Load Testing Guide" of the official HammerDB documentation.

Read in depth:

HammerDB

Http://www.hammerdb.com/document.html

DiskSpd

Https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

SQLIOSIM

Https://www.simple-talk.com/sql/database-administration/the-sql-server-sqliosim-utility/

Ostress

Https://msdn.microsoft.com/zh-cn/library/dn511655.aspx

Free Tools for Testing SQL Sever

Http://sqlserverio.com/2012/08/16/free-tools-for-testing-sql-sever/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.