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 VLF5 * http://msdn.microsoft.com/en-us/library/ms179355.aspx
Virtual Log Files
Transaction Log Data file
Dirty Page Lazy Writer Checkpoint
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
<= 1MB >=1MB and < 64MB >=64MB and < 1GB 1GB and larger
Number of VLFs
2 4 8 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