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