SQL Server Data Modifications by chenshu

VIEWS: 5 PAGES: 16

									The Anatomy of a Data
Modification
             everyone should know about
Subset of: What
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 – SolidQualityLearning.com
                    Email: Kimberly@SolidQualityLearning.com


    Monday, 13 October 2003
The Anatomy of a Data Modification
1. 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
2. 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
  Monday, 13 October 2003
What it looks like - Data




                             Data




                             Log

UPDATE…            Server…
                                    Cache




 Monday, 13 October 2003
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
       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…
 Monday, 13 October 2003
What it looks like - Locks

                                        Update Lock
                                  Row
                                        Update Lock
                                  Row
                           Page



                                        Update Lock
                                  Row
                           Page

                                        Update Lock
           Cache                  Row
                                        Update Lock
                                  Row
                           Page



 Monday, 13 October 2003
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 (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
   2.   Make the modification to the data row (yes, in cache)
   3.   Log the modification to the transaction log pages (also
        in cache)
 Monday, 13 October 2003
What it looks like - Modifications

                                           x    Update Lock
                                               ExclusiveLock
                                 x
                                         Row
                                     x
                                           x    Update Lock
                                               ExclusiveLock
                                         Row
                                Page
                            L


                                 x
                                           x    Update Lock
                                               ExclusiveLock
                                         Row
                                Page

                                     x     x    Update Lock
                                               ExclusiveLock
            Cache                x
                                         Row
                                           x    Update Lock
                                               ExclusiveLock
                                         Row
                                Page



  Monday, 13 October 2003
The Anatomy of a Data Modification
5. 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:
        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
                                            ~~~~~                        Cache
                                            ~~~~~    Sequential writes
                                            ~~~~~    Change
                After the log               ~~~~~    Change
              entries are made              ~~~~~    Change
              and the locks are             ~~       Change
                 released…                           …



 Monday, 13 October 2003
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…                                           Action Required
                                                               if restart recovery
                   1            L    D                              None

                            2       L/D       L                     Roll forward

                                    L/D   3                         Roll back

                                                  4       L         Roll forward

                                                      5             Roll back

                            Checkpoint                System Failure


Time
  Monday, 13 October 2003
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 McBath
  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, 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
  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@SolidQualityLearning.com

 Monday, 13 October 2003

								
To top