SQL SERVER – Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics

Every single day, when I write blog posts, I am not sure what I will write tomorrow but I must express my gratitude to all of you, who keep on asking questions new interesting questions which made me write more. Today we will talk about the Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics. Today’s question was sent to me by one of my clients who works at a large technology firm that deals with the stock market. I had earlier consulted for the last month while engaging in the Comprehensive Database Performance Health Check.

Note: This blog post is not discussing the bulk-logged recovery model, so please do not post a comment about it.

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload0-800x293

Recovery Model – Simple or Full

Earlier on this blog, I wrote a blog post about SQL SERVER – Wait Statistics Generated by oStress – Insert Workload.  I got lots of emails and tweets about this blog post. In the blog post, I had created a test table and used oStress Utility to insert the data in parallel, during the insert, there was a huge amount of the WRITELOG Wait Statistics. Lots of people wrote back to me asking what was the Recovery Model during the insert operation.

The most common argument as following –

You are seeing a lot of WRITELOG wait statistics because you might have a recovery model set to FULL recovery and if you change it to simple, you will not see thew WRITELOG wait statistics as well the query will run faster.

Very interesting argument indeed. I often get this argument as well and I explain to them the reality behind it. However, yesterday when one of my clients reached out to me, I also explained the science behind the reality but he said his CTO would not believe the theory and would like to see the demonstration. Finally, we went on GoToMeeting call and looked at the demonstration.

As many you had the same concern, I decided to also blog about it. Here is the few blog posts, I suggest you read it before continuing today’s blog post as they are all related, and reading earlier blog post will give you the context behind the whole setup.

Now I will be running two different tests on my server and after both the tests, I will be measuring the time to complete the execution and also the wait statistics of the single operation. Between both the execution, I will truncate the table and reset the wait statistics.

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.

Before running each of the following tests, we reset the table with empty data by running the following truncate command.

TRUNCATE TABLE [SQLAuthority].[dbo].[TestTable];

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

Test 1: Full Recovery Model

First set the recovery model of the database to the full recovery model.

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET RECOVERY FULL WITH NO_WAIT
GO

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload1

Now after truncating the table and clearing the wait statistics, we will run the following command stress command.

To run the test make sure that you have installed RML Utility in your system. Next, you can run the oStress command RML Utility folder. In most systems, 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.

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload3

Now let us measure the time to execute the query and also the wait statistics. If you do not have a query to measure the wait statistics, you can get that from here: SQL Server Wait Statistics collection script.

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload5

The query took around 1 min and 18 seconds and wait statistics for write log is around 3090 seconds. 

Test 2: Simple Recovery Model

Now for this test, we will set the recovery model of the database to the full recovery model.

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET RECOVERY SIMPLE WITH NO_WAIT
GO

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload2

As this is the second test, make sure to truncate the table and clear the wait statistics. Now after truncating the table and clearing the wait statistics, we will run the following command oStress command. The command is the same as what we had ran before.

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

I am running the test of inserting a single row 10000 times (one at a time) in 100 parallel threads in parallel, just like earlier time.

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload4

Now let us measure the time to execute the query and also the wait statistics.

SQL SERVER - Impact of Recovery Model on Insert Workload Stress Test and Wait Statistics InsertWorkload6

The query took around 1 min and 21 seconds and wait statistics for write log is around 3029 seconds. 

Honestly, there is not much difference in either duration of the query or even in the wait statistics.

Summary – Insert Workload Stress Test

I have run the test nearly 10 different times with 10 different workloads for each of the workloads. That means I have run 100 + 100 total of 200 tests for this scenario. Every single time I found that the results are very similar. The difference and variation between both the tests are absolutely negligible.

The performance of the queries are the same irrespective of your Recovery Model. 

The reality is that in simple as well as in the full recovery model, all the data is always written in the log file first. The only difference between the recovery model is that the simple recovery model does not keep the transaction once it has completed successfully (or failed successfully).

I hope this helps you to understand how the recovery model has very little or no impact on query performance for insert workload stress test.

Note: This blog post is not discussing the bulk-logged recovery model, so please do not post a comment about it.

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

, , , , , , , , , , , ,
Previous Post
SQL SERVER – Wait Statistics Generated by oStress – Insert Workload
Next Post
SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column

Related Posts

3 Comments. Leave new

  • Jerry Goldin
    May 4, 2020 4:15 pm

    In line 3 of the simple recovery test, you are still setting the recovery model to ‘FULL’. Thank you for your many interesting posts and helpful scripts.

    Reply
    • Thanks for catching the copy paste error :-)

      I have fixed it and thank you again for reading it.

      Reply
  • Your blog giving good knowledge.

    Thanks for your help

    Reply

Leave a Reply

Menu