• Sql Server Stress Test Tool Free Online
• Free SQL Server Performance Testing Utilities – SQLServerCentral
• Sql Server Stress Test Tool Free Download
• Cached
• Sql Load Testing Tool

You Need To Test Your SQL Server!

Its like screaming “TEST THE BREAKS ON YOUR CAR!” If you don’t have the tools or know how it is just good advice you can’t follow. I’ve done a TON of benchmarks over the years using every tool imaginable including ones I’ve written myself. There are several barriers to generating a valid repeatable benchmark in your own environment. Lets take a look at the general criteria that I use when testing OLTP systems and the benchmark tools available.

I’ve Been Spoiled

Here are some free tools to Load/Stress Test web applications. Please note that this is just a listing and is in ‘no particular order’. The Grinder - The Grinder is a load testing framework that makes it easy to run a distributed test using many load injector machines. Test scripts are written in Jython, and HTTP scripts can be recorded easily from a browser session. SQL Server Stress Test tool. Eye on SQL - Load Test is a tool designed for volume tests against SQL Server. Once started, you can start a new test where you can define the amount of concurrent connections, if the load is all at once or by increments, the duration and if the test is Continuous (in which case the previous settings defaults to predefined values). Free online SQL Test tool for easy SQL query learning and testing. Oracle 11g MySQL 5.6 MS SQL Server 2019. FREE online SQL query test tool.

I’m going to come clean. I really like Quest Software’s Benchmark Factory. They have put years of work into the tool set. It is also one of the few database benchmark tools that runs against most major RDBMS platforms. This alone made it my go to tool for a very long time. Also, since it implements most of the TPC Council benchmark specifications when someone asks how I tested I can hand them reams of information on the benchmark in question. TPC also has a well defined metric for determining not just raw performance but a dollar amount to get that performance. The catch is that Benchmark Factory isn’t an apples to apples with the published TPC results. To assume so would be a horrible mistake on your part. If you have ever read through a full disclosure (I read them to my four year old to put him to bed) you know that it would be very difficult for your organization to implement the exact same test on the exact same software without spending some major bucks. I have worked a few projects that were willing to spend the money and bring in a team to implement the testing protocols. If you are talking a project that is going to run say ten million your boss may be willing to spend 100,000 of that to completely test the system before it goes into production. In most cases though, this isn’t the case. Even spending the money on Benchmark Factory may be outside of your budget. So, we turn to free or opensource solutions sometimes we just write our own.

Sql Server Stress Test Tool Free Online

SQLStress is a tool to stress test a Microsoft SQL Server installation. Its main purpose is to find infrastructure problems within an installation. It can also be used for hardware sizing, system tuning or benchmarking. The focus is on simplicity. Jul 12, 2012 SQLQueryStress by Adam Machanic (blog twitter) is one of my favorite tools for doing a quick stress test against a user object like a stored procedure. This tool can actually be used to do stress testing on T-SQL queries but I usually use it for compiled objects like stored procedures.

Other Options

There are several other options for testing database servers. I’ve use a few over the years. First, I’m limiting this list to those that run on Windows, they may run on other platforms but that isn’t important to me. Secondly, The test setup has to be repeatable. Lastly, there has to be a way to measure performance. In some cases this may be using perfmon(Windows Performance Monitor) or the tool may capture some meaningful metrics.

Database Hammer SQL Server Resource Kit
http://www.sqlmag.com/content1/topic/put-the-hammer-down/catpath/configuration

Not exactly free or cutting edge but was good a putting a load on SQL Server. Even though this is VERY old due to its simplicity it works pretty much on every version of SQL Server from 2000 on. Again, one of the downsides is capturing metrics. The other down side is finding the dang thing. If you have an MSDN subscription you can still download it or you can head over to your local used book store and hope the CD-Rom that came with the book isn’t toast.

Eye On SQL – Load Test
http://eyeonsqlloadtest.codeplex.com/

A basic tool to put a database under load. There are no predefined tests and it doesn’t gather more than the most basic metrics. It is simple to use and can be a replacement for something like Database Hammer

TPCBench
https://github.com/SQLServerIO/TPCCBench

This is a tool I wrote a while back that implements TPC-C. It is in C# so anyone that is familiar can work on the code and peek inside to see how I implemented my version. The down side is it only implements one test, TPC-C. and TPC-C has several limiting factors to it. The one I never liked was all the data generated was random, all of it. If it was a name it was a random string. This leads to a database that effectively has 100% carnality, and is also a very poor choice for testing database compression. My goal was to implement TPC-E, which overcomes all of these issues but is fairly complex and hard to get right. I fell back to Benchmark Factory and never moved forward with this tool.

Hammerora
http://hammerora.sourceforge.net/

This is a pretty simple to use tool an has a easy to understand GUI. It supports two benchmarks a TPC-C style OLTP benchmark and a TPC-H style OLTP benchmark. That is also one of the down sides, it only supports two benchmarks and both are a bit long in the tooth. It also supports various flavors of SQL Server, Oracle, MySQL and Postgres. This is a nice little bonus if you are testing like hardware and OS and want to vary the RDBMS. The last bonus for me personally is it is opensource. This means I can look at the code and see how each test is implemented.

DVD Store Database Benchmark
http://en.community.dell.com/techcenter/extras/w/wiki/dvd-store.aspx

This is a tool I’m currently working on to bridge the gap between TPC-C and TPC-E. It was written in house by Dell and now is developed and maintained by Dell and VMWare. It is more complex than TPC-C but not as complex as TPC-E. It is opensource which was a huge plus for me and allowed me to fork it over at https://github.com/SQLServerIO/DVD-Store-Database-Benchmark and tweak on it to meet my needs. I’ll have a full blog post on this soon explaining the benchmark in detail and what changes I have made to it. It is a staple for testing databases on ESX. It has a standard way for gathering metrics. The load generator is actually in C#. You can also do a full stack test since it will work against a website that is also included in the benchmark. The down side again, is it only implements one benchmark.

Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837

This is a tool set that was initially developed in house at Microsoft to test customer scenarios. This is different than the other tools since it is a replay tool. But you can capture metrics and vary your workloads. The big down side is it isn’t easy to use. Also, if you are testing new hardware and don’t have a trace to replay from production you are back to zero.

Apache JMeter
http://jmeter.apache.org/

Not a database stress tool in the strictest since of the word but you can use it that way. The upside for JMeter is it will test your whole stack, web servers, middle tiers and database servers. You can use it to drive pretty much anything. Since it is written if Java it runs on Windows. Since there is a JDBC driver for SQL Server you can use it directly to test your database as well. The down sides are also huge. It is written in Java for starters. It is also using the JDBC stack and that can be a limiting factor. It is a significant undertaking to setup as well. I’ve included some links to people that have set it up to test databases and a ASP.NET web site.

Now What?

Well, that is easy, start reading, building and testing. I personally use something like Eye On SQL or Hammerora to do an initial smoke test or break in. Then move over to the more complicated benchmark tools. I have pretty much settled on the DVD Store benchmark for now as my OLTP testing tool. Now I need to find something more up to date than the TPC-H test everyone else has implemented so I can round out with a solid OLAP benchmarking tool. Maybe I’ll write something again!

By: Haroon Ashraf | Updated: 2017-12-15 | Comments (8) | Related: More >Testing

Problem

As a database developer I would like to implement an advanced and cost-effectivedatabase unit testing framework that can help my team in early bug detection anddoes not require context switching from SQL to any other tool/language and is suitablefor the following scenario: when all my database development work is done throughSSDT (SQL Server Data Tools).

Solution

The solution is to get your databases equipped with tSQLt and start writing andrunning unit tests for your database(s) without even leaving the Visual Studio IDE providedthe preferred database development toolset in your organization is SSDT (SQL ServerData Tools).

Recap: Answering Some Basic Questions about tSQLt

Some of the basic things that may come to mind before tSQLt implementation havealready been answered in detail inmy previous tip, in case you missed it a quick recap is as follows:

• Why tSQLt for advanced database unit-testing?
• Writing tSQLt unit-tests is same as writing T-SQL scripts so no contextswitching is needed you stay within SQL.
• Other benefits include isolation, transactions, continuous integration andcross-database objects testing.
• How is tSQLt Testing Framework Cost-Effective?
• tSQLt is an open-source project so it is free to use commercially and itis also underlying unit-testing framework behind many third party visual databasetesting tools.
• Apart from the cost-effectiveness and support for advanced database unit-testing,tSQLt framework architecture has been optimized to use for TDD (Test-DrivenDevelopment).

Recap: Creating and Running tSQLt Tests Generally

Inmy previous tip the steps to create and run tSQLt tests steps are mentioned.In short a tSQLt unit-test is ultimately a test class which is actually a schemawhich helps us to group relevant tests together while calling a test class or allthe tests in all test classes is simply calling a stored procedure so nothing newto learn apart from T-SQL.

Implementing tSQLt Framework with SSDT vs SSMS

The steps to implement tSQLt unit-testing with SSDT are different than SSMS implementationdue to the following reasons:

tSQLt Implementation Scenario

In order to implement tSQLt consider the following scenario:

• You want to implement tSQLt as your database unit-testing framework whileall your database development, debugging, testing, deployment and refactoringis done through SSDT (SQL Server Data Tools).

Setup: Database Unit-Testing with tSQLt using SSDT

Now that your team has finally decided to use tSQLt for advanced database unit-testing you are going to start implementing the testing framework on your database(s) whichare managed through SSDT.

According totSQLt.ORG the tSQLt Test Framework is compatible with SQL 2005 (Service Pack) andall versions above it.

Using OfficeSuppliesSample Database Project (Mimicking Transactional DatabaseProject)

We are using OfficeSuppliesSample database project to mimic main transactionaldatabase which contains a simple version of customer-product-order scenario foroffice supplies as follows:

Apart from the above three objects (tables) there is a procedure to add customerand view to see the orders.

Note: The database (OfficeSuppliesSample) was originally created in previoustip to mimic traditional online sales systems in a simplest manner can also be referencedin upcoming tips, so please feel free to get familiar with it.

Pleasedownload and run the source code below to create and populate OfficeSuppliesSampledatabase:

The database is ready to be used now.

Writing tSQLt Unit-Tests against OfficeSuppliesSample_Data

The best way to implement tSQLt Testing Framework to write unit-tests againsttransactional database project (which results into OfficeSuppliesSample_Data database)is to have another database project containing unit-tests.

So we are looking forward to have the following database projects:

1. OfficeSuppliesSample_Data
2. OfficeSuppliesSample_Tests

Creating OfficeSuppliesSample Database Project (Mimicking Transactional DatabaseProject)

Since SSDT (SQL Server Data Tools) uses naturally declarative database development,we begin with a database project followed by a database in a sandboxed environment.

SSDT provides complete isolation from changes done by other developers by providinga fully functional version of the main database in the form of a Project with bothconnected and disconnected development flavors that takes database development,testing, refactoring and deployment to next phase.

So the first thing that needs to be done (keeping in mind SSDT is used for databasedevelopment) is to create OfficeSuppliesSample Database Project and we can easilysimulate it by creating the project from the database, however, please bear in mindeither you create a database with basic objects and then create project from itor begin with database project and start adding objects both ways are acceptable.

Start a new solution and create a new SQL Database Project asshown:

Now right click on OfficeSuppliesSample database project and click on Importand then click on Database…

Next Connect to OfficeSuppliesSample database and click Start and then clickFinish.

Next check the database project in the solution explorer it must have been populatedwith OfficeSuppliesSample database objects.

Scripting OfficeSuppliesSample Data in Database Project

Reference data can be added to the project in the form of scripts to be centrallymanaged by a Post Deployment Script.

If we debug the project to deploy changes the debug database (OfficeSuppliesSample_Data)will have no data.

In order to create and populate OfficeSuppliesSample_Data database please createa new folder “Reference Data”.

Unzip thescripts below and put them under “Reference Data” folder using windowsexplorer:

Now In the solution explorer right click on “Reference Data” folderselect “Add” and then select “Existing Item…”:

Free SQL Server Performance Testing Utilities – SQLServerCentral

Next select all the scripts in the “Reference Data” folder (you recentlyunzipped) and as a result they will become part of the project:

Before these scripts can run we have to make sure that SQLCMD Mode has been turnedon which can be achieved by clicking on the SQLCMD Mode in any script pane in thetop right:

Creating and Populating OfficeSuppliesSample_Data database from Project

You can set your Debug Database as Local Dev SQL database by checking Propertiesof your database project and then under “Debug Tab” pointing to yourdesired SQL instance:

Since the data scripts have been kept simple, we are checking the deploymentoption “Always re-create database” so that rerunning the data scriptsshould not result in any key violation.

It is important to set the Target Platform the same as your SQL Server Instanceversion as in our case it is SQL 2012:

Press F5 to deploy changes to the debug database and see OfficeSuppliesSample_Data(debug database) getting populated.

Now using SQL Server Object Explorer view data navigate to OfficeSuppliesSample_Dataand right click on “dbo.OrdersView” under Views and then click on “ViewData” to see the tables populated by the post deployment script:

Creating OfficeSuppliesSample_Tests database Project based on TSQLT Objects

The first step to implement tSQLt is to download it fromtSQLt.ORG.

Please see the attachedtSQLt zip file download which was done quite a while ago, but still matchesthe latest version available on website:

Create a temporary database “TSQLT_Temp” to hold the tSQLtClass.sqlscript output that generates tSQLt objects:

Then run the tSQLtClass.SQL script in the TSQLT_Temp database to create tSQLtobjects:

Next create a new database project called “OfficeSuppliesSample_Tests”:

Set the Target Platform the same as that of main database:

Then right click on OfficeSupplieSample_Tests and click on the “Importfrom Database” option:

Then connect to the TSQLT_Temp database and get all tSQLt objects from this databaseinto the project (OfficeSuppliesSample_Tests):

Setting Target Connection for Test Project (OfficeSuppliesSample_Tests)

Adding Master Database and Main Database Reference

Since the test database is going to refer to the main database objects for testingwe have to add the following database references:

1. Master Database
2. OfficeSuppliesSample_Data

Right click on OfficeSuppliesSample_Tests project and select “Add DatabaseReference” then the master database as follows:

Next add the OfficeSuppliesSample_Data reference as follows:

Enabling CLR for tSQLt Test Runs

One of the requirements for tSQLt tests to run successfully is to make sure thatCLR is enabled at the Server Level which can be easily done by creating a Pre-Deploymentscript using the following code:

Automating tSQLt Test Runs for Debug Database

Sql Server Stress Test Tool Free Download

All the tSQLt tests can be run by the following script:

In order to automate the running of tests as soon as the project is debuggedwe need to put it into a Post-Deployment script.

Now Debug the OfficeSuppliesSample_Tests database project to see tSQLt blanktests run:

Creating Tests: Database Unit-Testing with tSQLt using SSDT

Now both main and test database projects along with their databases are readyand it’s time to write and run unit-tests.

Create a new folder “Tests” in OfficeSuppliesSample_Tests projectand then create “CustomerTests” sub-folder under the “Tests”folder.

Real Time Scenario for Adding New Customer Test

Now keeping in mind the main tables of the OfficeSuppliesSample, what if youhave been asked to test these basic requirements:

• In your system (database) an admin must be able to add a new Customer.
• At a very basic level we need to make sure the following things:
  • Customer object (table) exists
  • AddCustomer object (stored procedure) exists

Create a New Test Class (CustomerTests)

Let's create a Test Class first by creating a schema under the CustomerTestsfolder of the test project:

Then write the following code:

Create a test to check if Customer table exists

Create a unit-test by creating a new stored procedure under CustomerTests schemawith the following code:

Create a test to check if AddCustomer procedure exists

Next create another stored procedure in the test project to check that the AddCustomerprocedure exists as shown below:

The script to create a test to check if the Product table exists is as follows:

Running All Tests

Making sure the test project is selected and press F5 to start debugging:

We can see above that all tests have passed.

tSQLt Design-Time Test Automation Scenario

It is easier to achieve test automation at design time for the developer by simplylinking a database project with a test project that means debugging the databaseproject should result in a BUILD error if tests fail.

This can be achieved by choosing the “Multiple startup projects”option under Solution > Common Properties > Startup Project as shown below:

Let's change the procedure “AddCustomer” to “AddCustomer2”and then deploy the changes to the debug database by pressing F5:

There is no need to start the unit-tests separately as they run automaticallywhen the main database project is debugged and this results into a BUILD failureif the tests do not pass.

Now rename the stored procedure “AddCustomer2” to “AddCustomer”and debug the main project again to see all goes well:

Next Steps

• Create a fresh database project and test the project from scratch and thenadd objects to the main database project and simple tests to the test databaseproject and see how it goes.
• Create some solid unit tests to check functionality of the objects suchas adding a new customer, product or order.
• Explore the tSQLt officialwebsite to develop basic and advanced skills of creating and running tSQLt tests.
• Please keep watching MSSQLTips.com as more tips about tSQLt are on theirway.

Cached

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).
View all my tips

Sql Load Testing Tool