Tuesday, April 23, 2013

TSQL to identify databases with high number of VLFs

If you aren't aware there can be significant impact to performance when a database has a high number of VLFs within it's Transaction Log. SQL Server divides each physical transaction log file internally into a number of virtual log files (VLFs) and it is these that contain the log records.  There is no fixed size for a VLF, and there is no fixed number which would be created within a physical log file. These values are all determined dynamically by SQL Server when creating or extending physical log files.  While SQL Server tries to maintain a small number of VLFs for the best performance, file growth can result in a database having a large number of VLFs because the following equation will be used to determine the number of files to create within the new space.

- Less than 64MB = 4 VLFs
- Greater than 64MB and less than 1GB = 8 VLFs
- Greater than or equal to 1GB = 16 VLFs

So for example if I initially create a 1GB transaction log file it will be divided into 16 64MB VLFs, then if it grew a few times by 10 Mb before being switched to 10% the following trend would be experienced and result in a quickly increasing number of VLFs when then compared to manually growing the file:

ActionSpace AddedVLFs CreatedTotal SizeTotal VLFs
Initial Size1024 Mb161024 Mb16
Auto Grow10 Mb41034 Mb20
Auto Grow10 Mb41044 Mb24 
Auto Grow10 Mb41054 Mb28
Auto Grow10 Mb41064 Mb32
Auto Grow106 Mb81170 Mb40
Auto Grow117 Mb81287 Mb48
 Auto Grow128 Mb81415 Mb56
 Auto Grow141 Mb81556 Mb64
 Auto Grow155 Mb81711 Mb72
 Auto Grow171 Mb81882 Mb80
 Auto Grow188 Mb82070 Mb88

The only way to reduce the number of VLFs is to then Shrink the file and manually grow the file by a set size such as:

ActionSpace AddedVLFs CreatedTotal SizeTotal VLFs
Initial Size1024 Mb161024 Mb16
Manual Grow1024 Mb162048 Mb32
Auto Grow204 Mb82252 Mb 40

Ideally you should keep the number of VLFs to as small as possible. In SQL 2012 there is now a warning raised when a database has >10,000 VLFs, although there is no warning available in earlier versions of SQL Server. You can use the following TSQL though to report on the number of VLFs per database within your SQL Instance. Then if you compare this with your auto-grow settings for the database you can determine the reason for why the count is the way it is.

SET NOCOUNT ON;
/* declare variables required */DECLARE @DatabaseId INT;
DECLARE @TSQL varchar(MAX);
DECLARE cur_DBs CURSOR FOR
SELECT database_id FROM sys.databases;
OPEN cur_DBs;
FETCH NEXT FROM cur_DBs INTO @DatabaseId

--These table variables will be used to store the data
DECLARE @tblAllDBs Table (DBName sysname
   , FileId INT
   , FileSize BIGINT
   , StartOffset BIGINT
   , FSeqNo INT
   , Status TinyInt
   , Parity INT
   , CreateLSN NUMERIC(25,0)
)IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
BEGIN   DECLARE @tblVLFs2012 Table (RecoveryUnitId BIGINT
      , FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
ELSE
BEGIN
   DECLARE @tblVLFs Table (
      FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
--loop through each database and get the info
WHILE @@FETCH_STATUS = 0
BEGIN   PRINT 'DB: ' + CONVERT(varchar(200), DB_NAME(@DatabaseId));
   SET @TSQL = 'dbcc loginfo('+CONVERT(varchar(12), @DatabaseId)+');';
   IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
   BEGIN
      DELETE FROM @tblVLFs2012;
      INSERT INTO @tblVLFs2012
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs2012;
   END
   ELSE
   BEGIN
      DELETE FROM @tblVLFs;
      INSERT INTO @tblVLFs
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs;
   END
   FETCH NEXT FROM cur_DBs INTO @DatabaseId
ENDCLOSE cur_DBs;
DEALLOCATE cur_DBs;

--just for formating if output to Text
PRINT '';
PRINT '';
PRINT '';

--Return the data based on what we have found
SELECT a.DBName
    , COUNT(a.FileId) AS [TotalVLFs]
    , MAX(b.[ActiveVLFs]) AS [ActiveVLFs]
    , (SUM(a.FileSize) / COUNT(a.FileId) / 1024) AS [AvgFileSizeKb]
FROM @tblAllDBs a
INNER JOIN (
    SELECT DBName
        , COUNT(FileId) [ActiveVLFs]
    FROM @tblAllDBs
    WHERE Status = 2
    GROUP BY DBName
) b
ON b.DBName = a.DBName
GROUP BY a.DBName
ORDER BY TotalVLFs DESC;

SET NOCOUNT OFF;


Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
 

12 comments:

  1. Le magasin a résolu des problèmes pour moi, le service est très patient,ugg bottes pas cher c’est une expérience de magasinage parfaite. Quand j'ai reçu les chaussures, j'ai pensé que c'était une bonne copie des chaussures.pas cher les ténèbres des ténèbres J'aime cette boutique en ligne, cette chaussure est simple et généreuse. Très satisfait de cet achat.

    ReplyDelete
  2. En el momento en que abrí la caja, me sorprendió que solo fuera una réplica, pero se veía exactamente igual a la original y la calidad era buena.Réplica de relojes Rolex Creo que este debería ser el mejor reloj de réplica que he comprado. Comparto este sitio web con mis amigos.Réplica de relojes submariner Son muy felices, el precio es barato, puedo comprar un reloj tan bueno.

    ReplyDelete
  3. Tips for buying a copy here.perfect swiss replica iwc watches uk People spend a lot of time searching on the Internet, where there are many replicas of famous designer brands on sale, and we can be sure that they are of high quality and quality. This is the first time I bought a copy watch. This watch is very surprising to me. perfect replica IWC portugieser watches The color is very low-key and the quality is very good. Good value for money

    ReplyDelete
  4. Tipps zum Kauf eines Exemplars hier. billigste uhren kaufen Die Leute verbringen viel Zeit mit der Suche im Internet, wo viele Repliken berühmter Designermarken zum Verkauf stehen, und wir können sicher sein, dass sie von hoher Qualität und Qualität sind. Dies ist das erste Mal, dass ich eine kopierte Uhr kaufte. Diese Uhr ist für mich sehr überraschend. replik rolex uhren Die Farbe ist sehr zurückhaltend und die Qualität ist sehr gut. Gutes Preis-Leistungs-Verhältnis

    ReplyDelete
  5. replica louboutin italyFondato nel 1993, repliche donne appartamentigli iconici tacchi a suola rossa di Christian Louboutin sono ancora i tacchi più alla moda nel mondo della moda. Le impareggiabili vendite di Christian Louboutin rendono la tua collezione di scarpe ancora più equilibrata, in cui troverai allettanti tacchi Louboutin e scarpe Louboutin uniche delle dimensioni perfette.

    ReplyDelete
  6. The quality of this replica gucci dress is perfect,cheap gucci shoes it is very comfortable to wear, and wear this dress when you travel. The quality of this replica gucci dress is perfect,cheap gucci shoes it is very comfortable to wear, and wear this dress when you travel. I recommended this website to my good friends. They are very happy to buy such cheap gucci clothes.I recommended this website to my good friends. They are very happy to buy such cheap gucci clothes.

    ReplyDelete
  7. I have always had a good impression of Ray-Ban.buy ray bans wayfarer mens sale I just happened to buy it at this store. I chose this classic and I'm beautiful. After that, I found that the price of the event is 50% cheaper than usual or other e-commerce.copy Ray-Ban sunglasses Very valuable

    ReplyDelete
  8. The store has been solving the problem for me and the service is very patient,cheap Soccer Jerseys this is a perfect shopping experience. When I received the jersey, I thought it was a good copy. I like this online store.cheap france national team Jerseys The clothes here are simple and elegant. Very satisfied with this purchase.

    ReplyDelete
  9. Der Laden hat Probleme für mich gelöst, der Service ist sehr geduldig,nike billig kaufen dies ist ein perfektes Einkaufserlebnis. Als ich die Schuhe erhielt, dachte ich, es sei eine gute Kopie der Schuhe.billig kaufen Zero Ich mag diesen Online-Shop, dieser Schuh ist einfach und großzügig. Sehr zufrieden mit diesem Kauf.

    ReplyDelete
  10. This replica jimmy choo shoe is perfect in quality and comfortable to wear.jimmy choo shoes uk You can wear this shoe when you travel, and you will not feel tired.replica jimmy choo flats I recommend this site to my good friends, they are very happy to be able to buy such cheap Jimmy Choo shoes

    ReplyDelete
  11. Tips for buying a copy here.swisss uk rolex watches People spend a lot of time searching on the Internet, and there are many copies of famous designer brands on sale here, and we can be sure they are of high quality and premium quality. This is my first time buying a replica watch.replica omega speedmaster watches This watch surprised me a lot. The color is very understated and the quality is very good. Cheap

    ReplyDelete
  12. Breitling watches have been at the pinnacle of the replica watches uk watch industry since entering the marketplace in 1884. replica breitling watches Today, the Breitling name is a true giant among timepiece manufacturers, renowned for inventing the chronograph and for exceptional quality control standards. When you buy a Breitling watch, you choose a product made with excellence in precision. Breitling is a standard in the aviation and driving industries, as well as a favorite of the adventurer. Some of the most iconic collections include the: Navitimer, Bentley, Superocean, Avenger, Galactic, Chronomat, and Transocean. we offer a variety of authentic Breitling watches for sale in men's and women's styles. Shop our site to find the best in discount Breitling watches online.

    ReplyDelete