1 / 17

Kimberly L. Tripp President, SYS olutions, Inc. – SQLSkills Email: Kimberly@SQLSkills

Understand the intricacies of database modifications and the performance implications. Learn about locks, caching, and transaction processing for efficient data operations.

csimon
Download Presentation

Kimberly L. Tripp President, SYS olutions, Inc. – SQLSkills Email: Kimberly@SQLSkills

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. Designing for PerformanceGeneral Database OptimizationsPresentation Highlight: The Anatomy of a Data ModificationWednesday, 12 November 2003 – 16:30-18:00S207 Kimberly L. Tripp President,SYSolutions, Inc. – SQLSkills.comEmail:Kimberly@SQLSkills.com Principal Mentor, Solid Quality Learning – SolidQualityLearning.comEmail:Kimberly@SolidQualityLearning.com

  2. Speaker – Kimberly L. Tripp • Independent Consultant/Trainer/Speaker/Writer • President, SYSolutions, Inc.www.SQLSkills.com • Principal Mentor, Solid Quality Learning * In-depth, high quality training around the world! www.SolidQualityLearning.com • SQL Server MVP (http://mvp.support.microsoft.com/) • Microsoft Regional Director (http://www.microsoftregionaldirectors.com/Public/) • Writer/Editor for TSQL Solutions/SQL Magazine www.tsqlsolutions.com and www.sqlmag.com • Coauthor for MSPress title: SQL Server 2000 High Availability • Presenter/Technical Manager for SQL Server 2000 High Availability Overview DVD (MS Part# 098-96661)

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

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

  5. 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…

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

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

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

  9. 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)

  10. 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…

  11. 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!

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

  13. 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!

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

  15. Resources • Whitepaper: Using Partitions in a Microsoft SQL Server 2000 Data Warehouse,http://msdn.microsoft.com/library/techart/partitionsindw.htm • Whitepaper: Index Tuning Wizard for Microsoft SQL Server 2000, http://msdn.microsoft.com/library/en-us/dnsql2k/html/itwforsql.asp?frame=true • Support WebCast: SQL Server 2000 Profiler: What's New and How to Effectively Use It http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fwc111400%2Fwcblurb111400%2Easp

  16. Resources • Improving Performance with SQL Server 2000 Indexed Views http://msdn.microsoft.com/library/en-us/dnsql2k/html/indexedviews1.asp?frame=true • Microsoft SQL Server 2000 Index Defragmentation Best Practices http://www.microsoft.com/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp?frame=true • Support Resources listed: http://www.microsoft.com/sql/support/default.asp

  17. 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 Please fill out your evaluation!

More Related