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

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://darkslategrey-bat-805937.hostingersite.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

Leave a Reply