When developer or DBA is working on their local machine and building the application on the development system, everything runs perfectly fine. The real challenge shows up when the same architecture is deployed on the production system with a real workload. One of the things which we cover in my Comprehensive Database Performance Health Check is how to do Load Testing or Stress Testing with oStress.
Stress Testing with oStress
Let us see how we can do Stress Testing with oStress in the following video.
Resources in Video
Here are a few of the important resources which are used in the video:
SQL SERVER – Stress Testing with oStress – Load Testing
This blog explains where you can download RML Utility and how you can install it with the graphics. Additionally, it also contains all the necessary commands ran in the video.
oStress is available as part of the RML utility from Microsoft. Once you install RML utility, oStress automatically is installed in your machine. I have blogged over here where you can Download RML Utilities.
Here are the commands ran in the video:
Command to change the directory:
cd C:\Program Files\Microsoft Corporation\RMLUtils
Run oStress:
ostress -S"Quick\SQL19" -E -Q"INSERT INTO [SQLAuthority].[dbo].[TestTable] VALUES (DEFAULT);" -n5 -r20 -q
Script to create a table:
USE [SQLAuthority]
GO
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](500) NOT NULL DEFAULT ('I hope you have a great day!')
) ON [PRIMARY]
GOInsert script used inside oStress:
INSERT INTO [SQLAuthority].[dbo].[TestTable] VALUES (DEFAULT);
Script to count the inserted row:
SELECT COUNT(*) TotalRows FROM SQLAuthority.dbo.TestTable WITH (NOLOCK);
Script to truncate table:
TRUNCATE TABLE SQLAuthority.dbo.TestTable;
Related Blog Posts about oStress
If you want to read more about how oStress can help to do various performance tuning and optimization tests, here are a list of the blog posts.
- SQL SERVER – Performance Test – sqlcmd vs SSMS
- SQL SERVER – Performance Test – oStress vs SSMS
- SQL SERVER – Stress Testing with oStress – Load Testing
- SQL SERVER Management Studio and SQLCMD Mode
- SQL SERVER – Wait Statistics Generated by oStress – Insert Workload
- SQL SERVER – Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics
- SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
Reference: Pinal Dave (https://blog.sqlauthority.com)
