SQL SERVER – Performance Test – oStress vs SSMS

Earlier on this blog post, I wrote about how one can do the stress testing with the help of oStress in this blog post over here: SQL SERVER – Stress Testing with oStress – Load Testing. As soon as I wrote the blog post the very first question I have received was from one of my clients of Comprehensive Database Performance Health Check. The senior DBA there wanted to know what would be the performance improvement if they use oStress vs SSMS.

The very first thing I wanted to discuss is that oStress is not a replacement of SSMS or vice a versa. They are build to accomplish absolutely different goals and they should not be used in place of each other. If you are looking to use a command prompt style tool for your daily tasks with SQL Server, I suggest you consider PowerShell.

oStress vs SSMS

SSMS is built for developers to write queries and DBAs to manage the database, whereas oStress is built to run the stress tests on the server. They are built for absolutely different purposes and visions. Now let us run a simple test.

I will first create a test table in the database and will insert some data into the table. Right after that, I will run the same test in oStress. We will compare various scenarios and compare the performance.

Set up

First, create a simple table in the database.

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]
GO

After every test we will truncate the table by running the following command:

TRUNCATE TABLE [SQLAuthority].[dbo].[TestTable]

SSMS Test

Now let us run the insert query in SSMS and measure the time taken to run the query.

SET NOCOUNT ON
INSERT INTO [SQLAuthority].[dbo].[TestTable]
VALUES(DEFAULT)
GO 10000

The query above will run for 7 seconds on my machine.

oStress Test

Now let us run the insert query in oStress and measure the time taken to run the query.

ostress -S"Quick\SQL19" -E -Q"INSERT INTO [SQLAuthority].[dbo].[TestTable] VALUES(DEFAULT);" -n1 -r10000 -q -dSQLAuthority

when we ran above oStress query it actually took around 20 seconds of the time.

Summary – oStress vs SSMS

It is very clear that SSMS is executing the insert queries much faster than oStress. Please understand that the real use of the oStress is not for running your routine queries. It is used to simulate your workload on SQL Server. oStress really does wonders when you want to run simultaneously multiple threads on SQL Server with little efforts.

One should never compare oStress vs SSMS with each other, it is not a fair fight.

Reference: Pinal Dave (http://blog.SQLAuthority.com)