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

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

Hitesh Jain — Mon, 09 Feb 2009 18:51:01 GMT

Thank you, Linchi.

Very informative post on troubleshooting TLogs issues.

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

Chris Wood — Mon, 09 Feb 2009 18:57:18 GMT

Linchi,

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

Thanks

Chris

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

Adam Machanic — Mon, 09 Feb 2009 19:54:38 GMT

Chris:

dbcc loginfo('database_name')

... returns one row per VLF.

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

Kevin Boles — Mon, 09 Feb 2009 20:11:31 GMT

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?

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

Greg Linwood — Mon, 09 Feb 2009 20:22:08 GMT

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.

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

Chris Wood — Mon, 09 Feb 2009 21:29:44 GMT

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

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

LeoPasta — Mon, 09 Feb 2009 21:44:18 GMT

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.

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

Kevin Boles — Tue, 10 Feb 2009 01:02:01 GMT

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.

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

Linchi Shea — Tue, 10 Feb 2009 05:32:25 GMT

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

I'll cover that in the next post.

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

Mike Walsh — Tue, 10 Feb 2009 12:04:31 GMT

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.

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

ACALVETT — Tue, 10 Feb 2009 16:58:08 GMT

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

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

RangaSQL — Tue, 10 Feb 2009 17:54:21 GMT

Very informative...Thanks Linchi.

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

Glenn Berry — Tue, 10 Feb 2009 22:14:44 GMT

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

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

Dave — Tue, 10 Feb 2009 23:35:38 GMT

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.

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

Linchi Shea — Wed, 11 Feb 2009 01:30:59 GMT

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.

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

Linchi Shea — Wed, 11 Feb 2009 01:34:02 GMT

Dave;

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

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

Linchi Shea — Thu, 12 Feb 2009 05:46:42 GMT

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

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

Mario — Wed, 04 Mar 2009 17:24:03 GMT

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

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

altax — Tue, 29 Sep 2009 06:29:17 GMT

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.

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

ra,as — Sat, 22 May 2010 22:00:35 GMT

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

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

Sean Peters — Thu, 14 Oct 2010 16:07:46 GMT

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.

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

Sean Peters — Thu, 14 Oct 2010 16:19:01 GMT

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

Must-Have Resources - SQL Server Backup & Recovery

Kevin Kline — Thu, 08 Dec 2011 20:40:48 GMT

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

Follow-up Answers for my Australia Classes

Kalen Delaney — Wed, 04 Apr 2012 22:56:36 GMT

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

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

Mike Casey — Mon, 24 Sep 2012 10:56:54 GMT

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.