Wednesday, July 3, 2013

0 Tempdb log

This post is primary base on the Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011 .

  • tempdb log does not support instance file initialization . The log file MUST be zero out. If the log file already exist, the sql server engine only needs zero out the first vlog during the restart . If the log file use the new disk page ( like in the event of increase the log file size, the log file must be zero out).
  • Minimal logging. Only apply for heap ( no index) temp table. Only record the after image for insert and update .
  • The log is needed for user object to rollback.
  • checkpoint process will skip tempdb data file because the check point is to write the dirty page to the disk to support faster crash recovery, since there is no need to do crash recovery, the normal check point process will skip tempdb. The explicitly checkpoint will write the data page to the data file and truncate the log.
  • Lazy commits: normal commit will write the log to disk first before write the data page to the disk ( write ahead log).  Lazy commit does not.
  • internal object may do allocation logging only
  • Only log the undo portion of the data . No redo.

Below steps are mean to compare the log operation difference between tempdb, simple recovery db and full recovery db.

 

Create the testing database

CREATE DATABASE [summer_full] ON  PRIMARY 
( NAME = N'summer_fulldata', FILENAME = N'C:\DATA\summer_fulldata.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'summer__fulllog', FILENAME = N'C:\DATA\summer_fulllog.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);
 
 
CREATE DATABASE [summer_simple] ON  PRIMARY 
( NAME = N'summer_simpledata', FILENAME = N'C:\DATA\summer_simpledata.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'summer_simplelog', FILENAME = N'C:\DATA\summer_simplelog.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);
ALTER DATABASE [summer_simple] SET RECOVERY SIMPLE WITH NO_WAIT;
 
USE [master]; 
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 100Mb, MAXSIZE = UNLIMITED, FILEGROWTH = 10%);

Create the perfmon counter to monitor log file

  • Log Files size
  • Log Files used
  • Log Flush Wait time
  • Log Truncations

image

Run the create table script

Run the below script to generate the transaction

 

--- 1 user db table insert
use summer_full;
checkpoint;
create table t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
begin
insert into t1 values (1, '1')
set @x = @x + 1
end;
 
--- 2 user db table insert
use summer_simple;
checkpoint;
create table t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
begin
insert into t1 values (1, '1')
set @x = @x + 1
end;
 
--- 3 user temp table insert
use tempdb;
checkpoint;
create table #t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
begin
insert into #t1 values (1, '1')
set @x = @x + 1
end;

 

The same insert took 3 minutes in the full recovery mode db. It took 5 minutes in simple recovery mode db. It took only 33  secs in tempdb. 

 

image

image

image

Simple recovery database also have many checkpoint happen during the transaction .

image

 

Check the log size

 

  Time to execute the query log size log used Log record length Number of the log
Tempdb 0:33 102392 49756 47854792 637921
Tempdb with user object 0:34 102392 68852 66650480 837900
Summer_full 3:28 625784 604429 577728176 953624
Summer_simple 3:43 2040 730 117304 220

 

Run below scripts in each database

select sum([log record length]) as log_record_length from sys.fn_dblog(null, null);
select Operation, Context, [Transaction ID], [Log Record Length], AllocUnitName, 
[Transaction Name], [RowLog Contents 0], [RowLog Contents 1], 
[RowLog Contents 2], [RowLog Contents 3],[Number of Locks],[Lock Information]
from sys.fn_dblog(null, null);

 

tempdb:

image

 

summer_full

image

summer_simple

image

Lets do the same test for creating user object in the tempdb.

 

use tempdb;
create table t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
begin
insert into t1 values (1, '1')
set @x = @x + 1
end;

 

User object would have more logging than the temp table, so it has more log and take a little longer than temp table.

image

image

image

 

Conclusion

Transaction logging in tempdb is much faster than regular database because it is not write ahead and does not only log the undo portion of the log to support roll back.

 

Clean the tempdb log

hard checkpoint can clean the tempdb log. However, the checkpoint in tempdb could be more resource intensive than regular db because system checkpoint skip the tempdb so it maybe needs to write a lot of pages .

Once the checkpoint complete, the log is being truncate so it only show 2 records.

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates