Archive for February 21st, 2012

SQL Server 2012 – Too many VLF and Performance February 21st, 2012

Vinod Kumar

Personally, this topic is discussed over various blogs on the internet and I will try to refrain from talking about the VLF (Virtual Log Files) basics. I got into this topic as I started to prepare for my talk at TechEd India 2012. The talk fundamentally is about some internals of SQL Server and some great stuff are getting lined up into that session. One of the concept is to talk about VLF but more importantly, we will try to talk about how we can performance tune the database VLF specifically.

SQL Server 2012 Error Logs Help

Have you ever felt the SQL Server Error Logs can help you with this concept? Well, welcome SQL Server 2012 – there are some interesting insights you get as part of the Error Logs. Keep an eye for this behavior. If you see SQL Server database is taking a long time to come online from the “In Recovery” state, one of the direction to look at is the number of VLF’s. Anyways, if you try to access the database now – it would come with an error of.

Msg 922, Level 14, State 1, Line 1
Database ‘TestDB’ is being recovered. Waiting until recovery is finished.

The database eventually after 2-3 mins comes online (in my scenario) even after a proper shutdown. Now to investigate the same, just reading the SQL Server 2012 Error Logs gave away some interesting comments.

2012-02-21 21:21:13.530 spid29s      Database TestDB has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
2012-02-21 21:21:21.810 spid5s       Recovery is complete. This is an informational message only. No user action is required.


Performance Tips in Error Logs

This is awesome information which talks about the possible performance problem and the resolution steps too. To view the VLF’s feel free to use the DBCC LOGINFO() command. Pinal wrote a simple post around this. In my scenario, I had simulated with some 21000 VLF’s :). Also the error message tells you the exact steps required to solve the problem too. Shrink and then grow differently. This is exactly the steps you might do even today. Just to wrap up, when do these messages come up? As I tested, the messages come when –

  1. SQL Server starts/restarts and when the database initializes.
  2. Database attach process

With performance penalties from backup, replication, restore, startup etc – This is a great addition to SQL Server 2012 which I felt was worth talking. At TechEd India 2012, don’t miss to attend my session where I talk about many of these internal hidden gems as part of the talk.

Final words, do drop in your comments if you are planning to attend TechEd India 2012 (and my session :)) and don’t forget to tell me what area’s you want me to explore during the Internals Session (will try to squeeze them too).

Continue reading...