Is your SQL Server running SLOW? Then you could have an "High Count VLF" problem! TIPS for the DBAs

Hi guys, welcome back!

Today we change the subject
I present you a problem happened to me last week!
So, if you are a DBA then i suggest to bookmark this post!

Introduction

Driiiiin...  

On the other end of the phone a customer has a big problem. 

For a few days it has been increasingly difficult to work! 

Its management is always slower with the passage of time ..

A few minute and we are already connected hunting for slowdowns!

What is a virtual log file (VLF)?

When slowdowns involve all areas of your application, I personally take a look also to the transaction log.

You should know that even if the physical transaction log (a file with the .ldf extension) is a single file it is divided logically in many parts that are called Virtual Log file aka VLF.

Actually having too many virtual log files could rapresent a problem for performances.

So how to determine how many VLF have your databases?

You can count number of VLF through the query below:

       
SELECT
   [name], s.database_id,
   COUNT(l.database_id) AS 'VLF Count',
   SUM(vlf_size_mb) AS 'VLF Size (MB)',
   SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
   SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
   COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
   SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
  CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESCGO

This query will return a table with the information you want.

Take a look to the column VLF Count.
If your database have thousands of VLF well this could be a problem!

Hey! we have found a problem!
He have more than 4500 virtual log files!

Why do we have so many VLFs?

Well, usually the problem is due to the autogrowth value set to a lower value.
This means that the transaction log will growth very often.

In the case below we have a transaction log of about 8 GB.
how many times did it have to grow 20 mb at a time to reach that size?
Really many!!

How to fix the problem?

How to solve the problem?
We need to shrink the transaction log.

How to do it?
You can do it by doing right click on you database, then choose Task item and then the Shrink item:

But execute this operation you must execute the T-SQL command below:

       
DBCC FREEPROCCACHE
 

Remember: Run this command while users are not working because it could cause heavy slowdowns!

Now at the end of the operation check again the number of VLFs.

Et voila! He have only 9 VLFs!
That's very good!

Now finally set a correct value for the autogrowth value.

If your transaction log could reach the size of about 8 GB i suggest to set the autogrowth equal to 128 / 256 MB.


That's all for today.
So listen and wait for the next post!

Luca Biondi @ SQLServerPerformance blog!

 

 

 

 

Previous post:

• SQL Server Optimizer and The partial aggregate, GenLGAgg e LocalAggBelowJoin rules  
• Inside the SQL Server Query Optimizer - part 5 The cost based optimization process and the Rules

Tune of the post: Fantasy by Oliver Onions