Optimizing Procedural Code Understanding Logging Recovery Locking and

Optimizing Procedural Code Understanding Logging, Recovery, Locking and Performance Presentation HIGHLIGHT The Anatomy of a Data Modification Kimberly L. Tripp President/Founder

The Anatomy of a Data Modification 1. User sends UPDATE l l l 2. 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 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 data pages Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

What it Looks Like – Data Log UPDATE… Server… Cache Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

The Anatomy of a Data Modification 3. SQL Server proceeds to lock the necessary data Locks are necessary to give us a consistent point FOR ALL rows from which to start l If any other transaction(s) have ANY of these rows locked we will wait until ALL locks have been acquired before we can proceed. l 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. l 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) l There a few locks that have already occurred – within indexes, etc. to read the data – but they are not significant here Enterprise Database Administration and Deployment SIGit’s not too bad… This sounds complex but l Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

What it Looks Like – Locks Update Lock Row Update Lock Page Row Update Lock Cache Row Update Lock Page Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm 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 l 2. 3. An update lock helps to allow better concurrency by being compatible with shared locks (readers). Readers can read the pre-modified data as it is still transactionally consistent and has not YET been modified The e. Xclusive lock is required in order to change the data because once modified no other reads will be allowed to see this transient/un-committed state of the data Make the modification to the data row (yes, in cache) Log the modification to the transaction log pages (also in cache) Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

What it Looks Like – Modifications x x Row x L Page x x Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm 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. this is an implicit transaction in “Autocommit” mode. NOTE: the session setting SET IMPLICIT_TRANSACTIONS ON cannot be turned on and rarely should be! Makes for sloppy developers!) Steps to ensure durability 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) Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

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… ~~~~~ ~~~~~ ~~ Sequential writes Change … Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm Cache

The Anatomy of a Data Modification l l l The transaction log portion of the database 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 it’s not the sole purpose of checkpoint to 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 that changed them! Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

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 Roll back System Failure Time Enterprise Database Administration and Deployment SIG Roll forward Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm

Key Points l l Data Portion mostly random reads – except at checkpoint Log Portion mostly sequential writes Separate physical disks minimizes contention at the drive level – first choice in tuning Log is critical! Ø Ø Ø Log is written AHEAD of the data portion Log is the ONLY place where transactional consistency is known (i. e. guaranteed) Once a checkpoint occurs SQL Server doesn’t need the information in the log – for committed (a. k. a. inactive) transactions (the log could even be cleared) However… without the transaction log a database cannot function (i. e. marked suspect) Then what? Enterprise Database Administration and Deployment SIG Pre-conference Workshop Tuesday, September 28 8: 30 am – 4: 30 pm