Home > SQL Server Tips > Database Management and Administration > Performance implications of transaction log autogrowth in SQL Server
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

Performance implications of transaction log autogrowth in SQL Server


Michelle Gutzait, Contributor
Rating: -4.33- (out of 5)

Part 1 | Part 2 | Part 3

Too often I see people create maintenance plans or customized jobs that shrink the database files (data and/or transaction log) periodically.

In my previous article on shrinking your database to death, I tested and proved that there are some performance implications when a database file grows during a transaction. I then provided evidence on the unpredicted behavior of the transaction log autogrowth while executing INSERT, UPDATE and DELETE operations.

In this article, I will test the performance implications when a transaction log grows during transactions. I'll be using t...


RELATED CONTENT
Microsoft SQL Server Performance Monitoring and Tuning
SQL sprawl: Why is SQL Server Express installed everywhere?
Top 10 SQL Server tips of 2009
Top 5 SQL Server DBA tasks that are a waste of time
SQL Server Mailbag: Asymmetric encryption, log shipping issues
Using traces in SQL Server Profiler
SQL Server Mailbag: CALs, witnesses and unwanted changes
SQL Server Mailbag: Data restoration and DB property management
Working with IntelliSense in SQL Server 2008 Management Studio
SQL Server Mailbag: Stored procedures, triggers and SSRS reports
Troubleshooting Distributed Transaction Coordinator errors in SQL Server

Database Management and Administration
Database encryption in SQL Server 2008: Improvements you finally need
Common oversights with SQL Server security audits
Top 5 SQL Server DBA tasks that are a waste of time
Password cracking tools for SQL Server
Using traces in SQL Server Profiler
Meet compliance requirements with improved database security practices
Hardening the network and OS for SQL Server security
Securing the server and database in SQL Server
How SQL Server 2008 components impact SharePoint implementations
Troubleshooting Distributed Transaction Coordinator errors in SQL Server

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
contiguity  (SearchSQLServer.com)
contiguous  (SearchSQLServer.com)
drilldown  (SearchSQLServer.com)
hashing  (SearchSQLServer.com)
hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


he same environment and configuration as the one described in my previous articles.

The database

The ShrinkDB database I used in this test is initially configured so that the database file is large enough that it will not grow during the transactions. The transaction logog file size is only 2MB, however. The goal is to let it grow with the transaction to measure its implications:

[IMAGE]

The recovery model of the ShrinkDB database is set to FULL.

So the following query (fileid = 2 → the T-Log file):

select size from sysfiles where fileid = 2

Returns: 256.
This is the size of the transaction log file (in 8 KB pages). 256*8K = 2MB.

The ExpandDB table described in my previous article is used yet again:

create table ExpandDB (a varchar(8000))

The tests

My goal is to test how performance is affected by INSERT, UPDATE and DELETE commands that autogrow the database transaction log.

Since updates, inserts and deletes may behave differently, this test includes three sections, one per operation:

  • UPDATES
  • INSERTS
  • DELETE

Note: In the previous article, I proved that only large transactions autogrow the transaction log.

Insert commands

In this test, I inserted 10,000 rows into the table in one transaction. The same code was executed in both steps, but the autogrowth of the transaction log was set differently:

  • In the first step, autogrowth was set to 1MB;
  • In the second step, autogrowth was set to 10MB.

The two steps were executed three times each and the execution statistics were captured by SQL Profiler. Here is the code:

--  Truncate the table
truncate table ExpandDB
go
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB  (initial size):
DBCCSHRINKFILE (N'ShrinkDB_Log', 0, TRUNCATEONLY
Go
--  Insert 10000 rows
--  Big transaction
begin tran
declare @i  int
set @i = 1
while @i <= 10000
begin      insert into ExpandDB select replicate ('a',8000)
     set @i = @i + 1
end
commit
Go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace
go
select count(*) from ExpandDB
go

In the first step, the transaction log reaches around 110 MB. In the second it's around 102 MB.

Here is a comparison of the results (inserts only):

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 985 65152 10115 30479
File grows by 10MB 875 65111 10113 15416
% improvement 11.16751269 0.062929764 1.008403361 49.42091276
Difference 110 41 102 15063

2 File grows by 1MB 828 65155 10115 32109
File grows by 10MB 875 65104 10118 13240
% improvement -5.676328502      0.078274883 0.958971824 58.76545517
Difference -47 51 97 18869

3 File grows by 1MB 969 65218 10118 37298
File grows by 10MB 1062 65104 10012 15773
% improvement -9.59752322 0.174798369 1.047637873 57.71086922
Difference -93 114 106 21525
 
Average % improvement -1.36877968 0.105334339 1.005004353 55.29907905
Average difference -10 68.66666667 101.6666667 18485.66667

I conducted a third test where the transaction log file was set to 130 MB and did not grow during the transaction. This time I did not shrink the transaction log, and the code is as follows:

--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Insert 10000 rows
--  Big transaction
begin tran
declare @i int
set @i = 1
while @i <= 10000
begin
      insert into ExpandDB select replicate ('a',8000)
      set @i = @i + 1
end
commit
Go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go
select count(*)
from ExpandDB
go

Now we have a comparison of this last execution and the first execution (where the transaction log was set to grow by 1 MB), with insert commands only:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 985 65152 10115 30479
File did not grow 812 65097 10003 5651
% improvement 17.56345178 0.084417976 1.107266436 81.45936546
Difference 173 55 112 24828

2 File grows by 1MB 828 65155 10115 32109
File did not grow 719 65093 10007 8160
% improvement 13.16425121      0.095157701 1.067721206 74.58656451
Difference 109 62 108 23949

3 File grows by 1MB 969 65218 10118 37298
File did not grow 985 65103 10003 6741
% improvement -1.651186791 0.176331688 1.136588259 81.926644862
Difference -16 115 115 30557
 
Average % improvement 9.692172065 0.118635788 1.103858634 79.32419161
Average difference 88.66666667 77.33333333 111.6666667 26444.66667

Insert summary:
The average improvement metrics demonstrate clear performance enhancement related to the number of times a transaction log grows during a transaction. The less times an autogrow occurs the better, especially for the total duration (almost 80% improvement between 1 MB growth and no growth).

Update commands

In my previous article, it was revealed that updates grow transaction logs when many rows change at a time. For that reason, I executed the following:

--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Update 10000 rows at a time
update ExpandDB set a =   'aaaaaaaaaaaaaaaaaaaaaaaa'
go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go

I ran this twice to make sure that the transaction log grows by the same rate, and was puzzled to discover that on the first try the transaction log grew, but in the second it did not grow at all (staying at 2 MB)!

This might mean that if the values of the rows do not change, the transaction log is not growing. To prove my case, I added a select statement to print the number of rows affected by the update:

--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Update 10000 rows at a time
update ExpandDB set a =   'aaaaaaaaaaaaaaaaaaaaaaaa'
select @@rowcount go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go

The rowcount returned was always 10000.

When I modified the updated value (update ExpandDB set a = 'abcde'), the transaction log once again grew the first time, but not after subsequent executions of the same update.

Therefore, I proved my case that when the values in the rows are not changed, transaction log is not growing.

So I executed the following code, first when transaction log growth was set to 1 MB and then when it was set to 200 MB and did not grow. In the second execution, I removed all the DBCC SHRINKFILE commands so that the transaction log was not shrunk:

--   #1 ITERATION:
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go --  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Update 10000 rows at a time
update ExpandDB set a =   'aaaaaaaaaaaaaaaaaaabc'
go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go
--   #2 ITERATION:
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go --  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Update 10000 rows at a time
update ExpandDB set a =   'AAA'
go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go
--   #3 ITERATION:
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go --  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Update 10000 rows at a time
update ExpandDB set a =   'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'
go
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go

As expected, the transaction log ended up in different sizes when it was set to grow by 1 MB at a time.

Here is a comparison of the results (updates only):

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 203 8842 50 271
File did not grow 187 8840 94 192
% improvement 7.881773399 0.022619317 -88 29.15129151
Difference 16 2 -44 79

2 File grows by 1MB 156 13125 47 203
File did not grow 157 13125 3 267
% improvement -0.641025641      0 93.61702128 -31.5270936
Difference -1 0 44 -64

3 File grows by 1MB 781 17919 100 827
File did not grow 750 17918 31 804
% improvement 3.969270166 0.005580669 69 2.781136638
Difference 31 1 69 23
 
Average % improvement 3.736672641 0.009399995 24.87234043 0.135111518
Average difference 15.33333333 1 23 12.66666667

Update summary:
It seems that there is an improvement in performance when the transaction log does not grow, especially in the amount of writes.

Delete commands

My goal in this test was the same for deletes as it was with inserts and the updates.

I inserted 10,000 rows into the table and ran the following code to delete all rows at once:

--  Truncate the table
truncate table ExpandDB
go
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--  Delete 10,000:
declare @i int
set @i = 1
while @i <= 10000
begin
    insert into ExpandDB select replicate ('a',1000)
    set @i = @i + 1
end
go
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
go
--  Delete 10,000:
delete from ExpandDB
--  Check size and % free space in T-Log
dbcc sqlperf(logspace)
go

Again, in the first, the transaction log was shrunk and the autogrowth was set to 1 MB. In the second step, it was not shrunk (I commented the DBCC SHRINKFILE part) and the transaction log was set to be big enough to not grow.

Here is a comparison of the results (deletes only):

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 78 7224 1468 7890
File did not grow 79 7196 1440 495
% improvement -1.282051282 0.387596899 1.907356948 93.72623574
Difference -1 28 28 7395

2 File grows by 1MB 93 7220 1468 8372
File did not grow 62 7194 1441 435
% improvement 33.33333333      0.360110803 1.839237057 94.80410893
Difference 31 26 27 7937

3 File grows by 1MB 46 7220 1468 6657
File did not grow 94 7192 1440 355
% improvement -104.3478261 0.387811634 1.907356948 94.66726754
Difference -48 28 28 6302
 
Average % improvement -24.098848 0.378506446 1.884650318 94.39920407
Average difference -6 27.33333333 27.66666667 7211.333333

Delete summary:
Once again, performance is much better when the transaction log is not growing, especially in the total duration. CPU difference renders jagged results (probably because the numbers are very low and therefore not accurate).

More rows in the transaction

In order to test the relative performance of larger transactions in relation to the transaction log autogrowth, I repeated all the above tests with 50,000 rows (instead of 10,000) and here are the results:

Insert:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 2219 103797 7249 26237
File did not grow 2110 103713 7152 5666
% improvement 4.912122578 0.080927194 1.338115602 78.4045432
Difference 109 84 97 20571

2 File grows by 1MB 2250 103797 7250 24083
File did not grow 2063 103704 7157 3528
% improvement 8.311111111      0.089597965 1.282758621 85.35066229
Difference 187 93 93 20555

3 File grows by 1MB 2234 103804 7250 24880
File did not grow 2078 103711 7157 6010
% improvement 0.089591923 0.387811634 1.282758621 75.84405145
Difference 156 93 93 18870
 
Average % improvement 6.735407947 0.086705694 1.301210948 79.86641898
Average difference 150.6666667 90 94.33333333 19998.66667

Update:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 313 50055 50023 4868
File did not grow 250 50032 50000 386
% improvement 20.12779553 0.045949456 0.04597885 92.07066557
Difference 63 23 23 4482

2 File grows by 1MB 218 50048 50020 5273
File did not grow 265 50028 50000 254
% improvement -21.55963303      0.039961637 0.039984006 95.18300778
Difference -47 20 20 5019

3 File grows by 1MB 297 50048 50020 4418
File did not grow 266 50028 50000 282
% improvement 10.43771044 0.039961637 0.039984006 93.61702128
Difference 31 20 20 4136
 
Average % improvement 3.001957646 0.041957576 0.041982288 93.62356487
Average difference 15.66666667 21 21 4545.666667

Delete:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 375 43054 7299 35894
File did not grow 406 42911 7155 3138
% improvement -8.266666667 0.332141032 1.972872996 91.2575918
Difference -31 143 144 32756

2 File grows by 1MB 343 43053 7293 31672
File did not grow 312 42911 7155 4933
% improvement 9.037900875      0.329826028 1.892225422 84.42472847
Difference 31 142 138 26739

3 File grows by 1MB 359 43057 7269 33549
File did not grow 297 42907 7155 2990
% improvement 17.27019499 0.348375409 1.568303756 91.08766282
Difference 62 150 114 30559
 
Average % improvement 6.013809731 0.336780823 1.811134058 88.9233277
Average difference 20.66666667 145 132 30018

It seems that the larger the transaction, the more performance improvement was found by not growing the transaction log.

Larger autogrowth

In order to test the relative performance of larger autogrowth block size, I configured the autogrowth of the transaction log to be 50 MB and ran the following (both insert and delete) three times in a row:

--  Truncate the table
truncate table ExpandDB
go
--  Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
--  Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--   Insert 50,000 rows
begin tran
declare @i int
set @i = 1
while @i <= 50000
begin          insert into ExpandDB select replicate ('a',1000)
     set @i = @i + 1
end
commit
Go
--   Truncate the T-Log
backup transaction ShrinkDB with truncate_only
Go
--   Shrink T-Log back to 2MB: DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
--   Delete 50,000: delete from ExpandDB
--   Check size and % free space in T-Log
dbcc sqlperf(logspace)
Go

I then compared the results to the previous two (50,000 rows and different autogrowth):

Insert:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 2219 103797 7249 26237
File grows by 50MB 2281 103713 7158 8933
File did not grow 2110 103713 7152 5666
% improvement 50MB to no growth 7.496711968 0 0.083822297 36.57226016
Difference 171 0 6 3267

2 File grows by 1MB 2250 103797 7250 24083
File grows by 50MB 2203 103713 7158 7940
File did not grow 2063 103704 7157 3528
% improvement 50MB to no growth 6.354970495      0.008677794 0.013970383 55.56675063
Difference 140 9 1 4412

3 File grows by 1MB 2234 103804 7250 24880
File grows by 50MB 2312 103713 7159 12823
File did not grow 2078 103711 7157 6010
% improvement 50MB to no growth 10.12110727 0.001928399 0.027936863 53.13109257
Difference 234 2 2 6813
 
Average % improvement 7.99092991 0.003535397 0.041909847 48.42336779
Average difference 181.6666667 3.666666667 3 4830.666667

Delete:

ITERATION

Step

CPU

Reads

Writes

Duration (ms)

1 File grows by 1MB 375 43054 7299 35894
File grows by 50MB 375 42914 7158 10920
File did not grow 406 42911 7155 3138
% improvement 50MB to no growth -8.266666667 0.006990726 0.041911148 71.26373626
Difference -31 3 3 7782

2 File grows by 1MB 343 43053 7293 31672
File grows by 50MB 407 42910 7152 10854
File did not grow 312 42911 7155 4933
% improvement 50MB to no growth 23.34152334      -0.002330459 -0.041946309 54.55131749
Difference 95 -1 -3 5921

3 File grows by 1MB 359 43057 7269 33549
File grows by 50MB 391 42910 7126 11133
File did not grow 297 42907 7155 2990
% improvement 50MB to no growth 24.04092072 0.006991377 -0.406960427 73.142908477
Difference 94 3 -29 8143
 
Average % improvement 13.03859246 0.003883881 -0.1356652 66.31932074
Average difference 52.66666667 1.666666667 -9.66666667 7282

It seems that the less the transaction log grows – the better.

Summary of findings – Phase 2

I was able to prove that when a transaction log grows during a transaction, this affects the performance of the transaction negatively:

  • The larger the transaction, the more performance is affected
  • The wider the updated rows, the bigger the T-Log becomes
  • The smaller the autogrowth, the greater impact on performance

Note that my tests included a single running transaction. What would happen when the transaction log needs to grow in a multi-user environment? Stay tuned… more tests to come.

Additional considerations

On the same subject, it also has been proven that large number of Virtual Log Files (VLFs) in the transaction log affects overall performance of data modifications for some databases. In that case, it is recommended to grow the transaction log proactively rather than letting it autogrow. For other databases, a larger autogrow is recommended (by size, not by percentage).

[IMAGE]
[IMAGE]CAN YOU SHRINK YOUR DATABASE TO DEATH?
[IMAGE]
[IMAGE] Part 1: Examining data file growth
[IMAGE] Part 2: Transaction log autogrowth behavior
[IMAGE] Part 3: Performance implications of T-log autogrowth

ABOUT THE AUTHOR:   

[IMAGE]Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.


Rate this Tip
To rate tips, you must be a member of SearchSQLServer.com.
Register now to start rating these tips. Log in if you are already a member.




DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



SQL Server Development - .NET, C#, T-SQL, Visual Basic
HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Site Map




All Rights Reserved, Copyright 2005 - 2010, TechTarget | Read our Privacy Policy 		  TechTarget - The IT Media ROI Experts