About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Saturday, December 15, 2012

Virtual Log Files – a sometimes forgotten performance bottleneck

If the number of virtual log files (VLFs) for a given database gets too high, say more than 50 or so, then there will be a performance hit due to fragmentation.  Whenever the "auto-grow" for a log file kicks in, more VLFs are created.  The best prevention is to ensure that the auto-grow value is not set too low.  The default value is 10% in SQL 2005, 2008, 2008R2, and 2012.  Depending on how often you add data to your database, this value may be appropriate or way too low.  Also be careful of the initial size.  The default value is 1 Mbyte, which could be disastrous if it is not increased at database creation.


          What is a log file?
        guarantees data integrity of the database and for data recovery
        it is what makes ACID, in part, possible
          atomicity
          concurrency
          isolation level
          durability

          A database has at least one transaction log file
          Each transaction log file is comprised of VLFs
        no fixed size
        no fixed number of files

          Make sure initial size and auto-grow is set correctly
        Try to minimize the number of times a files grows
          Some growth is of course ok

          To determine number of VLFs, run

DBCC LogInfo(DatabaseName)

          50 or less is ok
          over 50:  use SHRINKFILE to reduce the number of virtual log files.


BACKUP LOG databasename TO devicename
DBCC SHRINKFILE(TransactionLogLogicalFilename, TRUNCATEONLY)
ALTER DATABASE databasename
MODIFY FILE
(
      NAME = TransactionLogLogicalFilename
    , SIZE = NewTotalSize
)

Now, for the real power of using code to help with database administration.  Create a procedure that will store the VLF info for all databases.  You can schedule this to run daily, and even set up alerts to be notified if the number of VLFs exceeds a threshold value, say 50 or 100.

See next post for a handy view which will show how many VLFs per database.











USE Admin
GO

CREATE PROC dbo.PopLogInfo
      @ShowSql bit= 0
AS
/*    DATE        AUTHOR            REMARKS
      10/16/12    PPaiva            Initial creation.
     
      DESCRIPTION
            Truncates and populates LogInfo using DBCC LogInfo.
           
      DEBUG
            SELECT *
            FROM LogInfo

            -- Number of VLFs per database
            SELECT Server, DB, COUNT(*) Qty
            FROM LogInfo
            GROUP BY Server, DB
            ORDER BY 3 desc, 1, 2

            SELECT status, COUNT(*)
            FROM LogInfo
            GROUP BY status;


            SELECT TOP 5 *
            FROM vwServerDBFile

            DBCC LogInfo ('AmRept')
     
      USAGE
            PopLogInfo 1
            PopLogInfo


*/   
SET NOCOUNT ON

-- DROP TABLE dbo.LogInfo
IF OBJECT_ID('dbo.LogInfo') Is Null
      CREATE TABLE LogInfo(
            ID int NOT NULL IDENTITY(1, 1) CONSTRAINT pk_LogInfo PRIMARY KEY CLUSTERED,
            Server varchar(30) NULL,
            DB sysname NULL,
            FileID int NOT NULL ,
            MBytes As Convert(decimal(8, 2), (SizeBytes/1000000.)),
            StartOffsetBytes bigint NOT NULL,
            FSeqNo bigint NOT NULL,
            Status int NOT NULL,    -- can be 0 or 2.  0 = reusable, 2 = not reusable
            StatusDesc  AS (case when Status=(0) then 'Reusable' when Status=(2) then 'Not Reusable' else '(undefined)' end),
            Parity int NOT NULL,    -- can be 64 or 128.  Value switches upon reuse.
            CreateLSN Numeric(25, 0) NOT NULL,  -- 0 indicates an original file, when database was created.
            SizeBytes bigint NOT NULL,
            dbID int NULL,
            InsertDate datetime CONSTRAINT def_LogInfoInsertDate DEFAULT GetDate()
            )




SELECT name, database_id
INTO #DBs
FROM sys.databases

DECLARE @MaxDB sysname,
            @DB sysname,
            @dbID int,
            @s varchar(500)
           
SELECT  @MaxDB = MAX(Name),
            @DB = ''
FROM #DBs        

           
IF @ShowSql = 0
      TRUNCATE TABLE LogInfo       
     
     
WHILE @DB < @MaxDB
      BEGIN
            -- Get next DB
            SELECT @DB = Min(Name)
            FROM #DBs
            WHERE Name > @DB       

            -- Get corresponding dbID
            SELECT @dbID = database_id
            FROM #DBS
            WHERE Name = @DB

            SET @s = 'INSERT INTO LogInfo (FileID, SizeBytes, StartOffsetBytes, FSeqNo, Status, Parity, CreateLSN)
      Exec (''DBCC LogInfo(''''' + @DB + ''''')'')
UPDATE LogInfo
SET DB = ''' + @DB + ''',
      dbID = ' + Convert(varchar, @dbID) + '
WHERE DB Is Null

'

            IF @ShowSql = 1
                  Print @s
            ELSE
                  Exec(@s)         
     
     
      END

UPDATE LogInfo
SET Server = Convert(varchar(30), SERVERPROPERTY('ServerName'))
WHERE Server Is Null




                                                        

1 comment: