Transcript Transaction Log Performance Tuning

Chirag Roy – Senior SQL DBA
MCITP: Database Developer 2005/2008
MCITP: Database Administrator 2005/2008
http://sqlking.wordpress.com
http://www.twitter.com/chiragroy
Transaction Log Architecture
Design Options for Performance
Hardware Options for Performance
Transaction Log Troubleshooting
Summary
Physical/Logical Architecture
Logical Log File
VLF1
VLF2
VLF3
VLF4
Virtual Log Files
* http://msdn.microsoft.com/en-us/library/ms179355.aspx
VLF5
Lazy Writer
Checkpoint
Dirty Page
Transaction Log
Full Recovery
Mode
Simple
Recovery
Mode
Data file
Recovery Type Considerations Simple Recovery - Log file cleared on checkpoint
Full/Bulk Logged Recovery – Log file cleared on Log
Backup
Bulk Logged Recovery
 Potentially Larger Log Backups when running • ALTER INDEX REORGANIZE
• DBCC INDEXDEFRAG
Tools to Check T-LOG DBCC LOGINFO
Tools to Check T-LOG DBCC SQLPERF(LOGSPACE)
Tools to Check T-LOG Disk Usage Report
TRACE FLAG 3004
VLF Design
Too few Large VLF’s due to poor design
Too many Small VLF’s in case of Autogrow
Smallest Log File Size can be 512KB on creation
VLF Sizing should be carefully planned according to
environment needs
VLF Design
Chunk Size
Number of VLFs
<= 1MB
2
>=1MB and < 64MB
4
>=64MB and < 1GB
8
1GB and larger
16
VLF Design
If log file designed for VLDBs > 8GB, expand Log File
in Increments of 8GB on DB Creation to create
512MB VLFs
If log file designed < 8GB, size Log File as per
requirements
Considerations Autoshrink is Evil – Switch OFF
Autogrowth by % is Evil’er, causes VLF Fragmentation
VLF Fragmentation  Leads to I/O overhead
 Affects Redo/Undo phase performance
 Increases database recovery/restore time
 Cluster Failover Timing
Considerations Place Data and Log files on separate LUNS to
distribute I/O
 Data Files experience Random Read/Writes
 Log Files experience Sequential Read/Writes
SAN Admins need to provision LUNS optimized for
the type of load
Considerations Change Model Database Recovery Mode to Simple
 Full Recovery Database in Pseudo Simple Until First
Full Backup
 Runaway Log file if subsequently no Log backups are
taken
Instant File Initialization does not work with Log Files
 When Restoring Database create database first with
properly sized data and log files
Considerations Log clearing can be affected by –
 Recovery Model
 Replication
 Database Mirroring
Switch on Backup Compression in SQL 2008/R2
TempDB - Special Case
In Large OLTP Environment Size Tempdb data and log file
appropriately
 Test using Autogrow
 Size before going into production
Checkpoint occurs when Log File is 70% Full
Slow Disk I/O can cause delayed checkpoint
 Mitigate using Alerts to notify
 Manual Checkpoint precedes over System Checkpoint
RAID 1
Good Read,
Slower Write
Performance
Good
Redundancy
Data Availability
Expensive
*http://support.dell.com/support/edocs/software/svradmin/5.1/en/omss_ug/html/strcnpts.html
RAID 10
Good Read/Write
Performance
Good
Redundancy
Data Availability
More Expensive
*http://support.dell.com/support/edocs/software/svradmin/5.1/en/omss_ug/html/strcnpts.html
SSD
Extremely Good
Read + Good
Write
Performance
Good
Redundancy
Data Availability
Very Expensive
* http://www.fusionio.com/load/media-imagesMediakit/gsyhv/image6_orig.jpg?attach=1
Disk Sector Alignment
Still on Windows 2003 make sure to use disk sector
alignment
Read Jimmy May’s blogs or whitepaper
 http://blogs.msdn.com/jimmymay/archive/tags/Disk+Partition+Alignment
/default.aspx
 http://msdn.microsoft.com/en-us/library/dd758814.aspx
In Windows 2008, disk sectors are aligned to 1MB by
default for disks larger than 4GB
Storage
Check the file latency within SQL Server using
 sys.dm_io_virtual_file_stats (db_id,file_id)
Use this script to get the latency for each file:
select db_name(database_id),
io_stall_read_ms/num_of_reads AS 'Disk Read Transfer/ms',
io_stall_write_ms/num_of_writes AS 'Disk Write Transfer/ms'
from sys.dm_io_virtual_file_stats (2,1)
sys.dm_os_waiting_tasks
Wait information
Task level
Very accurate
Transient data
sys.dm_os_wait_stats
Wait information
Cumulative by wait type
Persistent data
Transient data
Log_reuse_wait_desc in sys.databases
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT
ASYNC_IO_COMPLETION
Can be for "zeroing" out a transaction log file during log
creation or growth
WRITELOG
Writing transaction log to disk
LOGBUFFER
Indicates worker thread is waiting for a log buffer to write log
blocks for a transaction
*http://blogs.msdn.com/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx