The Anatomy of a Data Modification Subset of

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

The Anatomy of a Data Modification 1. User sends UPDATE l l l 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 2. Server receives the request and locates the data in cache OR reads the data from disk into cache l l 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 Monday, 13 October 2003

What it looks like - Data Log UPDATE… Server… Cache Monday, 13 October 2003

The Anatomy of a Data Modification 3. SQL Server proceeds to lock the necessary data l l l 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 also “intent” locks at higher levels to make sure other larger locks (like page or table level locks) are not attempted (and fail) There 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… Monday, 13 October 2003

What it looks like - Locks Update Lock Row Update Lock Page Row Update Lock Cache Row Update Lock Page Monday, 13 October 2003 Row

The Anatomy of a Data Modification 4. SQL Server can now begin to make the modifications – for EVERY row the process will include: 1. Change the lock to a stricter lock (e. Xclusive lock) l 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 l The e. Xclusive lock is required to make the change because once modified no other reads should be able to see this un -committed change 2. 3. Make the modification to the data row (yes, in cache) Log the modification to the transaction log pages (also in cache) Monday, 13 October 2003

What it looks like - Modifications x x Row x L Page x x Monday, 13 October 2003 Exclusive Update Lock Row x Page Exclusive Update Lock Row x x Cache Exclusive Update Lock Row Exclusive Update Lock

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

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

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! Monday, 13 October 2003

Transaction Recovery and Checkpoints Transactions… 1 L 2 Action Required if restart recovery None D L/D Roll forward L Roll back 3 4 L 5 Checkpoint Time Monday, 13 October 2003 Roll forward Roll back System Failure

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 Availability Authors: Allan Hirt with Cathan Cook, Kimberly L. Tripp, Frank Mc. Bath ISBN: 0 -7356 -1920 -4 • Check out the main page of www. sqlskills. com for a sample chapter to download! Monday, 13 October 2003

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 Monday, 13 October 2003

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

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” Monday, 13 October 2003

Thank you! Kimberly L. Tripp President, SYSolutions, Inc. Website: www. sqlskills. com Email: Kimberly@SQLSkills. com Principal Mentor, Solid Quality Learning Website: www. solidqualitylearning. com Email: Kimberly@Solid. Quality. Learning. com Monday, 13 October 2003