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.

SQL SERVER - Wait Statistics Generated by oStress - Insert Workload ostressworkload0-800x393

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

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]
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](500) NOT NULL DEFAULT ('I hope you have a great day!')

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);

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.

SQL SERVER - Wait Statistics Generated by oStress - Insert Workload ostressworkload

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.

SQL SERVER - Wait Statistics Generated by oStress - Insert Workload ostressworkload1

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.

SQL SERVER - Wait Statistics Generated by oStress - Insert Workload ostressworkload2

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.

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)

Best Practices, Load Testing, oStress, SQL Download, SQL Log, SQL Scripts, SQL Server, SQL Wait Stats, Stress Testing
Previous Post
SQL SERVER – Performance Test – sqlcmd vs SSMS
Next Post
SQL SERVER – Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics

Related Posts

2 Comments. Leave new

  • Brahmanand Shukla
    May 2, 2020 11:23 am

    Wonderfully explained Pinal! Very useful article!
    However, I have a quick question on Fixing PAGELATCH_EX wait type. The table you have referred in the test do not have a Primary Key specified on it, but it has an Identity column. In this case and in other cases how we can reduce the congestion while inserting a Primary Key?

    • Hi Brahmanand,

      Very good question. The reason, I did not create PK on it because if I would have created it, the common assumption would have been that is the cause of the PAGELATCH_EX wait type. In our case, we are getting that even without Primary Key.

      The solution which you will apply for the PAGELATCH_EX wait type without Primary Key will also help if you have a Primary Key on the table as well. If you are using SQL Server 2019 there is one more way you can help improve Identity Key Column and I will blog about them in the future.


Leave a Reply