THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: a large number of virtual log files – Part I

It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad for triggers and log backups on SQL Server 2000.

 

This blog post explores two questions:

 

  1. Is a large number of VLFs in a transaction log still a significant performance factor in SQL Server 2008?
  2. Does a large number of VLFs have an adversely impact on application-related operations such as INSERT, UPDATE, and DELETE?

 

As I’ll show in the rest of this post, the answers to both questions are yes. Let me first describe the test, and then present the test results.

 

The test databases

I created two databases: dbVLF_small and dbVLF_large on the same SQL Server 2008 instance (Enterprise x64 Edition and the build number was 10.0.1600). The host was an HP DL585 with four single core 2.2GHz Opteron processors and 32GB of RAM (28GB of which was allocated to the buffer pool). The following table highlights the key properties of these two test databases:

 

Property

Property value

 

dbVLF_small

dbVLF_large

Data file size

20GB

20GB

Data file location

E: drive

E: drive

Log file size

10GB

10GB

Log file location

F: drive

F: drive

Recovery mode

Full

Full

Number of VLFs

16

20,000

 

As you see, the only masterial difference between these two databases was the number of virtual log files. The dbVLF_small database had 16 VLFs (a small number of VLFs), and the dbVLF_large database had 20,000 VLFs (a large number of VLFs). It’s probably a bit extreme for a database to have 20,000 virtual log files. But it’s not completely unreasonable if you let your database log file to grow in small increments. Note that my intention is to highlight the impact in case if your database does end up with a large number of VLFs.

 

The test table

An identical table, called customer, was created in both databases. The DDL for the customer table is as follows:

 

CREATE TABLE customer

(

    c_id                int,

    c_d_id              tinyint,

    c_w_id              int,

    c_first             char(16),

    c_middle            char(2),

    c_last              char(16),

    c_street_1          char(20),

    c_street_2          char(20),

    c_city              char(20),

    c_state             char(2),

    c_zip               char(9),

    c_phone             char(16),

    c_since             datetime,

    c_credit            char(2),

    c_credit_lim        numeric(12,2),

    c_discount          numeric(4,4),

    c_balance           numeric(12,2),

    c_ytd_payment       numeric(12,2),

    c_payment_cnt       smallint,

    c_delivery_cnt      smallint,

    c_data              char(500)

)

go

CREATE UNIQUE CLUSTERED INDEX ci_customer on customer(c_w_id, c_d_id, c_id)

go

 

This was the customer table used in the TPC-C benchmark.

 

The test data

3,000,000 rows—with data as specified for the TPC-C customer table— were bulk copied into the customer table in both databases. The index was then rebuilt, and sp_spaceused showed the table size to be about 2GB.

 

The tests

Three simple tests were run against the customer table in each database. The common characteristics of these three tests were that they were a single large transaction.

 

The insert test

SELECT * INTO customer_tmp
  FROM customer
 WHERE 1=2;
go
INSERT customer_tmp
SELECT * FROM customer;

 

The update test

UPDATE customer
   SET c_data = lower(c_data);

 

The delete test

DELETE FROM customer;

 

The test results

The following table summarizes the test results:

 

Test

Elapsed time (sec)
in database: dbVLF_small

Elapsed time (sec)
in database: dbVLF_large

The insert test

281

1069

The update test

174

1554

The delete test

193

936

 

The following chart gives a better visual representation of the same results:

 

In all the three cases, it was significantly (should I say dramatically) slower to perform these common SQL operations when the database had 20,000 VLFs than when the database had 16 VLFs. The insert was about four times slower. The update was about eight times slower, and the delete was about five times slower.

 

So the test results confirm that it is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes.

 

What does this all mean? First, pay attention to the often-repeated best practice advice: do not grow your database log files in small increments (because that is often how you introduce a large number of VLFs into your transaction log). For instance, if you know you’ll need 10GB for your log, it’s best to allocate 10GB in one fell swoop. Secondly, if you do find a large number of VLFs in your transaction log, it may be worth the maintenance effort to reduce the number. You can reduce the number of VLFs by first shrinking the log file to a minimum size, and then allocating the required space in one ALTER DATABASE statement. (SQL Server MVP Tibor Karaszi has a thorough discussion on shrinking log files at his site)

 

------

By the way, the 20,000 virtual log files were generated as follows. First, create the database in the full recovery mode with a 512KB initial log file size and 512KB file growth increment. Then, backup the database, and create a dummy table with a single char(8000) column. Finally, inserting data into this table in a loop until the log file size reaches 5000MB. This is essentially the same procedure used by the Microsoft support team in Stockholm in their post.

 

To prepare for the insert, update, and delete tests, backup the log, and expand the log file to 10GB.

 

Published Monday, February 09, 2009 1:13 PM by Linchi Shea

Attachment(s): VLFs.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hitesh Jain said:

Thank you, Linchi.

Very informative post on troubleshooting TLogs issues.

February 9, 2009 1:51 PM
 

Chris Wood said:

Linchi,

What's the best way to see how many VLF's a transaction log file has in SQL2005 SP2?

Thanks

Chris

February 9, 2009 1:57 PM
 

Adam Machanic said:

Chris:

dbcc loginfo('database_name')

... returns one row per VLF.

February 9, 2009 2:54 PM
 

Kevin Boles said:

dbcc loginfo.  search web for details on it since it isn't in BOL.

Linchi, I wonder if small transactions would see the same or similar reduction in throughput.  If you delete/insert/update 1-100 rows at a time in a loop (with begin/commit trans??) will there be a net reduction in executions per unit time with many VLFs?

February 9, 2009 3:11 PM
 

Greg Linwood said:

Its often important to pay attention to tempdb VLFs (rather than userdbs) as tempdb often does more TLogging than user dbs & is a central bottleneck, shared between dbs.

Its very common for tempdb to be left to grow automagically but its a good practise to pre-set TLog size on tempdb to avoid excessive VLFs.

February 9, 2009 3:22 PM
 

Chris Wood said:

That works great. Now when I see a number of rows coming back would this also indicate fragmentation of the database log? We have some servers database files on a SAN but currently most are on direct attached storage. From one of Linchi's previous blogs he had mentioned the potential performance issue with fragmented files. I would like to get some information from SQL server that can help me try an aleviate fragmentation and VLF's to improve performance.

Thanks

Chris

February 9, 2009 4:29 PM
 

LeoPasta said:

Hi Linchi,

 Great post. Some time ago, I wrote a procedure aiming at automating the process of reducing the number of VLF, I made it available at http://sqlblogcasts.com/blogs/leopasta/archive/2008/09/02/reducing-transaction-log-fragmentation.aspx

 I have not used it extensively, so it may contain a few bugs, but during my internal tests on SQL 2008 it did the job.

February 9, 2009 4:44 PM
 

Kevin Boles said:

Chris, you could have 2 forms of fragmentation problems.  First your OS files could be fragmented.  This is common when DBAs leave default settings for file size and growth.  It can have a significant affect on performance.  A windows file fragmentation checking tool is appropriate here.

The data structures inside the sql server files can also become fragmented - in 2 ways.  Pages can be in a less-than-optimally-full state and pages can be out of order.  These too can lead to performance problems.  Much has been written and is available online and in BOL to diagnose and correct these issues.

February 9, 2009 8:02 PM
 

Linchi Shea said:

> Linchi, I wonder if small transactions would see the same or similar reduction in throughput.  

I'll cover that in the next post.

February 10, 2009 12:32 AM
 

Mike Walsh said:

Great post. I have never taken the time to do a comparative analysis like this for this example. Now I'll have one more tool to use when showing people why it's best to "right size" a transaction log file instead of allow growths.

February 10, 2009 7:04 AM
 

ACALVETT said:

Great post! Looking forward to part II

Pulled the code below off a blog i wrote ages ago. Anyway, if you want to get the vlfs for all the databases on your server in one hit the code below works nicely.

CREATE  TABLE #VLFS (fileid int,filesize bigint,startoffset bigint,fseqno bigint,status int,parity int,createlsn varchar(1000))

CREATE TABLE #Results (SRV_Name nvarchar(500),Database_Name nvarchar(500),VLFS INT)

exec master.dbo.sp_msforeachdb

   @command1 = 'USE ? INSERT INTO #VLFS EXEC(''DBCC LOGINFO WITH TABLERESULTS'')',

   @command2 = 'insert into #Results SELECT @@SERVERNAME,''?'',count(*) from #vlfs',

   @command3 = 'truncate table #vlfs'

SELECT * FROM #Results

drop table #vlfs

drop table #Results

February 10, 2009 11:58 AM
 

RangaSQL said:

Very informative...Thanks Linchi.

February 10, 2009 12:54 PM
 

Glenn Berry said:

Linchi,

What kind of drive configuration do you have for the log file drive, i.e. how many spindles, what RAID level, etc.

Is LOGWRITE the top wait type if you query sys.dm_os_wait_stats during the tests ?

        -- Clear Wait Stats

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Isolate top waits for server instance

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',

 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

 'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits

SELECT W1.wait_type,

 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

February 10, 2009 5:14 PM
 

Dave said:

Awesome stuff, and much appreciated!

Where did you get the 3m rows from?  Or did you use a custom routine to generate the data?  I would like to leverage the same data within our environment to show what type of performance degredation we are experiencing by performing your test against our own db log files with varying #'s of VLFs.

February 10, 2009 6:35 PM
 

Linchi Shea said:

Glenn;

Logwrite was the top wait type. There was nothing else going on else INSERT, UPDATE, and DELETE, and these were expected to be bottlenecked on log writes.

To be honest, I didn't care about the the drive config because the only variable was the number of VLFs. But that could be a factor in determining the magnitude of the difference.

February 10, 2009 8:30 PM
 

Linchi Shea said:

Dave;

The three million rows were from a custom-written C# program that generates and loads TPC-C compliant data.

February 10, 2009 8:34 PM
 

Linchi Shea said:

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in

February 12, 2009 12:46 AM
 

Mario said:

Linchi, great research work again. Do you also know why this is happening? In other words: which mechanism is responsible for the performance reduction?

March 4, 2009 12:24 PM
 

altax said:

That was a great piece of information. I was exactly searching this regarding virtual log files. Great research. I don't know hot to greet you. Very very thanks for the information.

September 29, 2009 2:29 AM
 

ra,as said:

Could you tell me how i can create a database with a predefined set of VLF's?

May 22, 2010 6:00 PM
 

Sean Peters said:

Linchi, I may be late to the party here but I have one glaring question about your technique.  Did you reset the growth parameter on dbVLF_small to match dbVLF_large AFTER you created 20k vlfs but BEFORE you ran the queries?  My contention here is that the operations were impacted by the file growth operations themselves and not necessarily the sheer number of VLFs.  Although this doesn't explain the delete behavior.

October 14, 2010 12:07 PM
 

Sean Peters said:

. . . wait, actually it would explain the delete behavior. lol  So the question stands.  Did you make sure the growth parameters were the same before you ran the queries?

October 14, 2010 12:19 PM
 

Kevin Kline said:

The industry's best resources for maximizing Microsoft SQL Server backup and recovery. Don't get burned!

December 8, 2011 3:40 PM
 

Kalen Delaney said:

I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney,

April 4, 2012 6:56 PM
 

Mike Casey said:

In reply to Sean, if you read the article's paragraph starting with 'the 20,000 virtual log files were generated as follows' you'll see that all the growth was done in a loop up front of the timed test, so file growth time can not be a factor... but there is no mention of running a disk defrag after the repeated growth, and it might be interesting to see how much of the time difference was down to more VLFs and how much was down to disk fragmentation.

September 24, 2012 6:56 AM
 

Quanto os VLFs podem afetar o backup de log? | Comunidade SQL Server said:

June 11, 2014 4:45 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement