Testing plays a critical part in the development of any software product. The more rigorous testing is, the better the final product will be. However, it is common for the database testing to be skipped or carried out on leftovers, while the software code testing is done meticulously. This can lead to performance issues and bottlenecks in the application.

In this blog post, we will discuss various aspects of database testing, including load testing and performance testing of SQL Server. We will focus on using tSQLt and SQLQueryStress to test the performance of our database.

Let’s consider an abstract task of developing an engine for online shops. To keep things simple, we will create a maximally simple database structure with tables for Customers, Products, Orders, and OrderDetails.

CREATE TABLE dbo.Customers (
      [CustomerID] INT IDENTITY PRIMARY KEY
    , [FullName] NVARCHAR(150)
    , [Email] VARCHAR(50) NOT NULL
    , [Phone] VARCHAR(50)
)

CREATE TABLE dbo.Products (
      [ProductID] INT IDENTITY PRIMARY KEY
    , [Name] NVARCHAR(150) NOT NULL
    , [Price] MONEY NOT NULL CHECK (Price > 0)
    , [Image] VARBINARY(MAX) NULL
    , [Description] NVARCHAR(MAX)
)

CREATE TABLE dbo.Orders (
      [OrderID] INT IDENTITY PRIMARY KEY
    , [CustomerID] INT NOT NULL
    , [OrderDate] DATETIME NOT NULL DEFAULT GETDATE()
    , [CustomerNotes] NVARCHAR(MAX)
    , [IsProcessed] BIT NOT NULL DEFAULT 0
)

CREATE TABLE dbo.OrderDetails
(
      [OrderID] INT NOT NULL
    , [ProductID] INT NOT NULL
    , [Quantity] INT NOT NULL CHECK (Quantity > 0)
    , PRIMARY KEY (OrderID, ProductID)
)

We also have stored procedures for inserting a new customer or getting the ID of an existing one, and for placing a new order. These procedures are simple and straightforward.

Now, let’s generate some test data for the Customers table. We can use a script to generate random test data or more realistic data. It is important to have some data to test the performance of our database.

Once we have the data, we can start testing the performance of our stored procedures. We can use SQLQueryStress to simulate multiple calls to the procedures and measure the execution time. By analyzing the execution plans and optimizing the queries, we can improve the performance of our database.

It is also important to regularly run performance tests and monitor the performance of our database. We can automate this process by using tools like tSQLt and SQLQueryStress. tSQLt is a free framework for unit testing in SQL Server, and SQLQueryStress is an open-source tool for stress testing SQL Server.

In addition to performance testing, we can also use unit tests to check for missing indexes, unused indexes, and unused tables. These tests can help us identify potential performance issues and optimize our database.

Overall, testing the performance of our database is crucial for ensuring the smooth operation of our application. By using tools like tSQLt and SQLQueryStress, we can identify and resolve performance issues, optimize our queries, and improve the overall performance of our database.

So, don’t skip the database testing and make it an integral part of your development process. Your application will thank you for it!