The Anatomy of a Data Modification Subset of: What everyone should know about Performance, Recovery & Logging Mond

1. The Anatomy of a Data Modification Subset of: What everyone should know about Performance, Recovery & LoggingMonday, 13 October 2003 – 08:00SDB 308 Kimberly L. Tripp President,SYSolutions, Inc. – SQLSkills.comEmail:Kimberly@SQLSkills.com Principal Mentor, Solid Quality Learning – SolidQualityLearning.comEmail:Kimberly@SolidQualityLearning.com

2. The Anatomy of a Data Modification • User sends UPDATE • Update is highly selective (only 5 rows) • Indexes exist to aid in finding these rows efficiently • The update is a SINGLE statement batch NOT enclosed in BEGIN TRAN…COMMIT TRAN block therefore this is IMPLICIT transaction • Server receives the request and locates the data in cache OR reads the data from disk into cache • Since this is highly selective only the necessary pages are read into cache (maybe a few extra but that’s not important here) • Let’s use an example where the 5 rows being modified are located on 3 different data pages

3. What it looks like - Data Data Log UPDATE… Server… Cache

4. The Anatomy of a Data Modification • SQL Server proceeds to lock the necessary data • Locks are necessary to give us a consistent point FOR ALL rows from which to start • If any other transaction(s) have ANY of these rows locked we will wait until ALL locks have been acquired before we can proceed. • In the case of this update (because it’s highly selective and because indexes exist to make this possible) SQL Server will use row level locking. • The rows are locked but there are also “intent” locks at higher levels to make sure other larger locks (like page or table level locks) are not attempted (and fail) • There are a few locks that have already occurred – within indexes, etc. to read the data – but they are not significant here This sounds complex but it’s not too bad…

5. What it looks like - Locks Update Lock Row Update Lock Row Page Update Lock Row Page Update Lock Cache Row Update Lock Row Page

6. The Anatomy of a Data Modification • SQL Server can now begin to make the modifications – for EVERY row the process will include: • Change the lock to a stricter lock (eXclusive lock) • An update lock helps to allow better concurrency by being compatible with other shared locks (readers). Readers can read the pre-modified data as it is transactionally consistent • The eXclusive lock is required to make the change because once modified no other reads should be able to see this un-committed change • Make the modification to the data row (yes, in cache) • Log the modification to the transaction log pages (also in cache)

7. What it looks like - Modifications x Exclusive Lock Update Lock x Row x x Exclusive Lock Update Lock Row Page L x Exclusive Lock Update Lock x Row Page x Exclusive Lock Update Lock x Cache x Row x Exclusive Lock Update Lock Row Page

8. The Anatomy of a Data Modification • Finally, the transaction is complete – this is the MOST critical step • All rows have been modified • There are no other statements in this transaction – i.e. Implicit transaction • Steps are: • Write all log pages to transaction log ON DISK • Release the locks • Send a message to the user: (5 Rows Affected)

9. What it looks like Write-Ahead Logging Data L Log 5 Rows Affected Server… Log Cache ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sequential writes Change Change Change Change … After the log entries are made and the locks are released…

10. So now what? • The transaction log ON DISK – is up to date • The data in CACHE – is up to date • But when does the data get written from cache to disk? CHECKPOINT It’s important to realize that the sole purpose of checkpoint is NOT just to write committed pages… Instead a checkpoint writes ALL pages which have changed since they were brought into cache – regardless of the state of the transaction which changed them!

11. Transaction Recovery and Checkpoints Transactions… Action Requiredif restart recovery L D 1 None L/D L 2 Roll forward L/D 3 Roll back L 4 Roll forward 5 Roll back Checkpoint System Failure Time

12. Resources • Check out www.sqlskills.com for information about upcoming events, useful downloads and excellent scripts! There are quite a few resources and/or links to use. • MSPress title: SQL Server 2000 High AvailabilityAuthors: Allan Hirt with Cathan Cook, Kimberly L. Tripp, Frank McBathISBN: 0-7356-1920-4 • Check out the main page of www.sqlskills.com for asample chapter to download!

13. Resources • From Books Online “Home Page” select White Papers to get to msdn • For Tech Net articles use: http://www.microsoft.com/technet/prodtechnol/sql/default.asp?frame=true • See www.microsoft.com/sql for all sorts of useful links, resources and whitepapers, etc. • Support Resources listed: http://www.microsoft.com/sql/support/default.asp

14. Resources • Articles in SQL Server Magazine, Aug/Sept 2003: • Recovering from Isolated Corruption, InstantDoc #39657 • The Best Place for Bulk_Logged, InstantDoc #39782 • Articles in SQL Server Magazine, Sept 2002: • Before Disaster Strikes, InstantDoc ID#25915 • Log Backups Paused for Good Reason, InstantDoc #26032 • Restoring After Isolated Disk Failure, InstantDoc #26067 • Filegroup Usage for VLDBs, InstantDoc #26031 • Articles in TSQLSolutions Journal, Oct 2001: • TSQLTutor Quick Tip: Saving Production Data from Production dBAs, InstantDoc ID#22073

15. BOL Favorites • Make sure to get the latest version of the BOL. See www.microsoft.com/sql, Technical Resources, Product Documentation • Functions: DATABASEPROPERTYEX, OBJECTPROPERTY, SERVERPROPERTY • “Using Recovery Models” • “Checkpoints and the Active Portion of the Log” • “Virtual Log Files” • “Shrinking the Transaction Log” • “Optimizing Transaction Log Performance” • “Switching Recovery Models” • “ALTER DATABASE”

16. Thank you! Kimberly L. Tripp President, SYSolutions, Inc.Website: www.SQLSkills.com Email: Kimberly@SQLSkills.com Principal Mentor, Solid Quality LearningWebsite: www.SolidQualityLearning.comEmail: Kimberly@SolidQualityLearning.com