As a DBA, implementing a new database consists of a variety of aspects like correct table structure, tuning the stored procedures, creating right indexes, and many more. But all of these items, the stress testing and performance benchmarking play one of the most crucial roles. There are many tools available for stress testing, but they can be expensive and complicated.

In this tip, we are going to explore one of the best free stress testing tools – Replay Markup Language (RML) for Microsoft SQL Server. The RML tool consists of four utilities:

• ReadTrace
• Reporter
• OStress
• OStress Replay Control Agent (ORCA)

This blog is about stress testing so I would be only discussing the OStress utility here.

OStress

OStress is a scalable and straightforward command line tool which can stress or replay database commands. It is designed to assist with performance stress testing of T-SQL queries. It provides a great number of switches to simulate many connections and iterations for a given workload. In a nutshell, it also allows putting a serious load on your database.

How to Install and Use the tool

• Download 32-bit or 64-bit versions RML utilities for SQL Server.

• Click on the downloaded software to install, check the box “I Agree” to accept the term and condition and press next-next to install. It is straightforward to install.

• Once installation completed successfully,  search in start “rml cmd prompt” or open the file from below location, and open the command prompt.
  

       “C:\ProgramData\Microsoft\Windows\Start Menu\Programs\RML Utilities for SQL Server\”

Type “ostress” on the RML Utilities command prompt, and it shows a list of switches that you can use with the tool. For more details, you can follow the below snapshot.

• The tool is ready to generate the workload on the SQL Server instance.

Generate stress (workload)

There are three different ways you can generate the workload using the Ostress utility. 

• Feed Ostress .sql file(s)
• The best part about ostress is that it’s all common-line interface. So you can use SQL Server Agent Jobs, and feed it all sorts of cool options.
• Directly fire query as an ad-hoc query.

Now, I am going to create the below three stored procedures under the database WideWorldImporters to generate load on TempDB, one reads load, and one writes load. Let’s run the script.

----1 Create Stored Procedure to generate the load on TEMPDB
USE WideWorldImporters
GO
CREATE PROCEDURE dbo.sel_salesVsInvoice_Ostrees @OrderID INT
AS
SELECT s.* 
INTO #t
FROM sales.Orders s
INNER JOIN sales.Invoices i ON s.OrderID = i.OrderID AND s.CustomerID = i.CustomerID
WHERE s.OrderID <=@OrderID
DROP TABLE #t
GO
----2 Create Stored Procedure to generate the READ workload
CREATE PROC [dbo].[sel_ByCityName_Ostrees] @CityName NVARCHAR(100)
AS
BEGIN
SELECT [CityID]
      ,[CityName]
      ,[StateProvinceID]
      ,[Location]
      ,[LatestRecordedPopulation]
      ,[LastEditedBy]
FROM [WideWorldImporters].[Application].[Cities]
WHERE CityName = @CityName
END
----3 Create table and Stored Procedure to generate the INSERT workload
IF object_id('Cities') is null
   CREATE TABLE [DBO].[Cities](
[CityID] [int] NOT NULL,
[CityName] [varchar](100) NULL,
[StateProvinceID] [int] NOT NULL,
[Location] [geography] NULL,
[LatestRecordedPopulation] [bigint] NULL,
[LastEditedBy] [int] NOT NULL,
 CONSTRAINT [PK_Application_Cities] PRIMARY KEY CLUSTERED 
(
[CityID] ASC
))
GO
CREATE PROC Ins_dbo_cities_Ostrees
AS
INSERT INTO [DBO].[Cities]
SELECT top 5 [CityID]
      ,[CityName]
      ,[StateProvinceID]
      ,[Location]
      ,[LatestRecordedPopulation]
      ,[LastEditedBy]
  FROM [WideWorldImporters].[Application].[Cities]
  ORDER BY NEWID()

• Feed Ostress .sql file(s)

  Let’s feed the .sql file from specific location F:\Ostress_demo\ to generate the load.

Below are statements which you can feed to generate the read, tempdb, and write workload.

Ostress.exe -S"DELL\SQL2K16DEV01" -Usa –PP@ssw0rd –iF:\Ostress_demo\Read_Workload.sql –n20 –r5 
Ostress.exe -S"DELL\SQL2K16DEV01" -Usa –PP@ssw0rd –iF:\Ostress_demo\Tempdb_Workload.sql –n20 –r5 
Ostress.exe -S"DELL\SQL2K16DEV01" -Usa –PP@ssw0rd –iF:\Ostress_demo\Write_Workload.sql –n20 –r5 
 ostress.exe -- ostress executable file
-S"DELL\SQL2K16DEV01" --Server name
-Usa -- Username
-PP@ssw0rd -- Password
-n20 --How many concurrent sessions you want to run for your query
-r5 --How many iterations they should each perform
-iF:\Ostress_demo\Read_Workload.sql --Query you want to run

• Generate Stress using SQL Server Agent:

 – Create a SQL Agent job using the Operating system (CmdExec) type.

– Below are the statements which I will be using to create SQL Server Agent job for each statement.

"C:\Program Files\Microsoft Corporation\RMLUtils\Ostress.exe" -S”DELL\SQL2K16DEV01? -Usa –PP@ssw0rd –iF:\Ostress_demo\Read_Workload.sql –n20 –r5 -oF:\Ostress_demo\Read_Workload
"C:\Program Files\Microsoft Corporation\RMLUtils\Ostress.exe" -S”DELL\SQL2K16DEV01? -Usa –PP@ssw0rd –iF:\Ostress_demo\Tempdb_Workload.sql –n20 –r5 -oF:\Ostress_demo\Tempdb_Workload
"C:\Program Files\Microsoft Corporation\RMLUtils\Ostress.exe" -S"DELL\SQL2K16DEV01" -Usa –PP@ssw0rd –iF:\Ostress_demo\Write_Workload.sql –n20 –r5 -oF:\Ostress_demo\Write_Workload
"C:\Program Files\Microsoft Corporation\RMLUtils\Ostress.exe" -- ostress executable file path
-S"DELL\SQL2K16DEV01" --Server name
-Usa -- Username
-PP@ssw0rd -- Password
-n20 --How many concurrent sessions you want to run for your query
-r5 --How many iterations they should each perform
-iF:\Ostress_demo\Read_Workload.sql --Query you want to run
-oF:\Ostress_demo\XXXX_Workload -----Logging folder

– Setup three jobs to generates reads, writes, and the other generates some TempDB load.

– When the jobs run on the schedule, you will see the workload. You can use the blog to capture the workload.

• Directly fire query as an ad-hoc query:

 You can use “-Q” switch to fire ad-hoc query. In the below statement, I am running the ad-hoc query by passing the switch.

Ostress.exe -S"DELL\SQL2K16DEV01" -Usa –PP@ssw0rd –Q"SELECT [CityID], [CityName], [StateProvinceID], [Location], [LatestRecordedPopulation], [LastEditedBy] FROM [WideWorldImporters].[Application].[Cities]" –n20 –r5

Conclusion

OStress is a great place to start the simulation of possible workload. It might not be a perfect tool, but it’s a certainly the best free option which allows you to simulate many user sessions and activities.

Happy Learning!

The post SQL Server Stress Testing Using OStress – RML Utilities appeared first on .