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.
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]
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.
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)