SQL SERVER – Wait Statistics Generated by oStress – Insert Workload

When I write blog posts, I am never sure how popular they will get and who will read them. It seems my recent blog post about oStress has got lots of popularity. Recently I got the question about Wait Statistics Generated by oStress during Insert Workload. Well, let us check that in today’s blog post.

However, to understand the context of today’s blog post, I suggest you read the following blog post first.

• 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

Once you complete reading all the above blog post, let us discuss today’s topic. In the earlier blog posts, we discussed that oStress is not a tool to run your routine queries, it is just a stress testing tool.

One of the reasons, I like to use oStress at my client’s place is that it is incredibly easy to use as there are only a few commands we have to run to generate a multi-threaded workload at the client’s place. Let us today run a very simple query and observe what kind of wait statistics oStress generates.

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

Now that we have created a table with the identity key in it. Let us move to the next step where we will run our queries with the stress.

Now rest the wait statistics on your development or test server where you are going to run the query with the following command.

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
GO

Insert Workload with oStress

Now let us run the insert query in oStress and measure the time taken to run the query. First, go to the following folder where RML Utilities are installed. In the most system, it will be here: C:\Program Files\Microsoft Corporation\RMLUtils

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

As you can see in the code above, I am running the test of inserting a single row 10000 times (one at a time) in 100 parallel threads in parallel.

Here is a screenshot of when the oStress started and when the workload ended.

You can see the entire process took around 1 min and 31 seconds. Now before I ran the oStress workload, I had reset my wait statistics.

Wait Statistics for Insert Workload

Now let us run the SQL Server Wait Statistics collection script and identify the top Wait Statistics for the query.

Here is the screenshot of wait statistics right after I reset them.

Now after running a workload for insert where I have inserted 1 row at a time 10000 times in 100 parallel, I can see my wait statistics as displayed in the image below.

It is very clear from the image that I have two primary wait stat issues. 1) WRITELOG which is approx 59 % of the wait time and 2) PAGELATCH_EX which is around 39% of the wait time.

Here is how you can resolve each of the work statistics.

• Fix WRITELOG Wait Statistics
  A quick suggestion – Have a faster drive for log files
• Fix PAGELATCH_EX Wait Statistics
  A Quick suggestion – See if you can reduce the congestion while Inserting a Primary Key.

Well, I hope it is clear from this example, how Insert Workload impacts the Wait Statistics and how you can improve them. One of my client Comprehensive Database Performance Health Check, recently also faced a similar situation. I will blog about the resolution in future blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)