1 / 33

Transaction Log Performance Tuning

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. Agenda. Transaction Log Architecture Design Options for Performance

ronni
Download Presentation

Transaction Log Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 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

  2. Agenda • Transaction Log Architecture • Design Options for Performance • Hardware Options for Performance • Transaction Log Troubleshooting • Summary

  3. Transaction Log architecture

  4. Transaction Log Architecture Physical/Logical Architecture Logical Log File Virtual Log Files * http://msdn.microsoft.com/en-us/library/ms179355.aspx

  5. Transaction Log Architecture Lazy Writer Checkpoint Buffer Pool Plan Cache Dirty Page Transaction Log Full Recovery Mode Simple Recovery Mode Data Cache Data file

  6. Transaction Log Architecture 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

  7. Transaction Log Architecture Tools to Check T-LOG - • DBCC LOGINFO

  8. Transaction Log Architecture Tools to Check T-LOG - • DBCC SQLPERF(LOGSPACE)

  9. Transaction Log Architecture Tools to Check T-LOG - • Disk Usage Report • TRACE FLAG 3004

  10. Design Options for Performance

  11. Design options for performance 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

  12. Design options for performance VLF Design

  13. Design options for performance 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

  14. Design options for performance 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

  15. Design options for performance 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

  16. Design options for performance 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

  17. Design options for performance Considerations - • Log clearing can be affected by – • Recovery Model • Replication • Database Mirroring • Switch on Backup Compression in SQL 2008/R2

  18. Design options for performance 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

  19. Hardware Options for Performance

  20. Hardware Options 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

  21. Hardware Options 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

  22. Hardware Options 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

  23. Hardware Options 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

  24. Transaction Log troubleshooting

  25. hardware utilisation and performance 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)

  26. Dynamic management views sys.dm_os_waiting_tasks • Wait information • Task level • Very accurate • Transient data

  27. Dynamic management views sys.dm_os_wait_stats • Wait information • Cumulative by wait type • Persistent data • Transient data

  28. Dynamic management views 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

  29. Common wait types 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

  30. Demo

  31. summary

  32. SUMMARY • VLF Design • Switch of AutoShrink • Use Autogrow as last resort • Enable Compression in SQL 2008/R2 • Log files on Faster Dedicated Disks • significant resource waits • ASYNC_IO_COMPLETION • WRITELOG • LOGBUFFER

  33. THANK YOU & Questions

More Related