1 / 6

Optimizing Procedural Code

Optimizing Procedural Code. Understanding Logging, Recovery, Locking and Performance. Presentation Addition Understanding/Optimizing VLFs. Kimberly L. Tripp President/Founder. How the Transaction Log Works. Virtual Log File 1. Virtual Log File 2. Virtual Log File 3. Virtual Log File 4.

elsa
Download Presentation

Optimizing Procedural Code

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. Optimizing Procedural Code Understanding Logging, Recovery,Locking and Performance Presentation AdditionUnderstanding/Optimizing VLFs Kimberly L. Tripp President/Founder

  2. How the Transaction Log Works Virtual Log File 1 Virtual Log File 2 Virtual Log File 3 Virtual Log File 4 Virtual Log File 5 • On commit, activity is written to the log – active and likely sequential • Activity moves through log sequentially, fills and then goes to a second file or autogrows • Excessive autogrowth causes: • Slight pause on autogrow • Windows call to extend a file (may cause file fragmentation) • Adds “VLFs” to the file on each autogrowth Inactive/UnusedLog File Inactive Virtual Log File Inactive Virtual Log File Active Virtual Log File Inactive Virtual Log File End Logical Log Start Logical Log Min LSN (first opentran)

  3. Transaction Log Optimization • Pre-allocate the log to the appropriate size • Minimize autogrowths • Log might NOT show 0 Percent Log Used after backup for example - the % Log Used will be ~6% • Disk Bound Systems might experience some performance degradation at log backup • Consider RAID 1+0 instead of RAID 1, RAID 5 generally is not recommended • Optimize existing file – minimize VLFs

  4. Minimize VLFs • Excessive VLFs add overhead to log related activities: • Transaction logging • Log Backups – may cause problems in clearing the log as well… • Logreader (replication) • After Triggers (inserted/deleted), etc.) • Should have less than 50 • BPA (Best Practices Analyzer only generates warning if over 200)

  5. Minimize VLFs • Execute DBCC LOGINFO • Number of rows = Number of VLFs • If excessive (> 50) then: • Free up log space by first clearing space from the transaction log (using BACKUP LOG) • If your Database Recovery Model is FULL or Bulk_Logged then perform a regular transaction log backup – this should remove the inactive portion of the log • If your Database Recovery Model is Simple then clear the log

  6. Minimize VLFs • Shrink the transaction log file DBCC SHRINKFILE(logfilename, TRUNCATEONLY) • Alter the Database and modify log file size to a more appropriate size (not requiring all of the autogrowth) in ONE increment ALTER DATABASE dbname MODIFY FILE ( NAME = name, SIZE = new_size )

More Related