Transcript Designing for Performance - General Database

Designing for Performance
General Database Optimizations
Presentation Highlight:
The Anatomy of a Data Modification
Wednesday, 12 November 2003 – 16:30-18:00
S207
Kimberly L. Tripp
President, SYSolutions, Inc. – SQLSkills.com
Email: [email protected]
Principal Mentor, Solid Quality Learning – SolidQualityLearning.com
Email: [email protected]
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)
General Database Performance
12 November 2003  16:30-18:00
The Anatomy of a Data Modification
1.
User sends UPDATE



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


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
General Database Performance
12 November 2003  16:30-18:00
What it looks like - Data
Data
Log
UPDATE…
Server…
Cache
General Database Performance
12 November 2003  16:30-18:00
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…
General Database Performance
12 November 2003  16:30-18:00
What it looks like - Locks
Update Lock
Row
Update Lock
Page
Row
Update Lock
Row
Page
Update Lock
Cache
Row
Update Lock
Page
General Database Performance
12 November 2003  16:30-18:00
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 (eXclusive lock)


2.
3.
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 uncommitted change
Make the modification to the data row (yes, in
cache)
Log the modification to the transaction log pages
(also in cache)
General Database Performance
12 November 2003  16:30-18:00
What it looks like - Modifications
x
Exclusive
Update Lock
Lock
x
Row
x
L
Page
x
Row
x
x
Exclusive
Update Lock
Lock
Exclusive
Update Lock
Lock
Row
Page
x
Cache
x
x
Row
x
Page
General Database Performance
12 November 2003  16:30-18:00
Exclusive
Update Lock
Lock
Row
Exclusive
Update Lock
Lock
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)
General Database Performance
12 November 2003  16:30-18:00
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…
General Database Performance
12 November 2003  16:30-18:00
~~~~~
~~~~~
~~~~~
~~~~~
~~~~~
~~
Cache
Sequential writes
Change
Change
Change
Change
…
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!
General Database Performance
12 November 2003  16:30-18:00
Transaction Recovery and Checkpoints
Transactions…
1
L
2
Action Required
if restart recovery
None
D
L/D
L/D
Roll forward
L
Roll back
3
4
L
5
Checkpoint
Time
General Database Performance
12 November 2003  16:30-18:00
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 McBath
ISBN: 0-7356-1920-4
Check out the main page of
www.sqlskills.com for a
sample chapter to download!
General Database Performance
12 November 2003  16:30-18:00
Resources




From Books Online “Home Page” select
White Papers to get to msdn
For Tech Net articles use:
http://www.microsoft.com/technet/prodtec
hnol/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/defa
ult.asp
General Database Performance
12 November 2003  16:30-18:00
Resources



Whitepaper: Using Partitions in a Microsoft SQL
Server 2000 Data Warehouse,
http://msdn.microsoft.com/library/techart/partiti
onsindw.htm
Whitepaper: Index Tuning Wizard for Microsoft
SQL Server 2000,
http://msdn.microsoft.com/library/enus/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
General Database Performance
12 November 2003  16:30-18:00
Resources



Improving Performance with SQL Server 2000
Indexed Views
http://msdn.microsoft.com/library/enus/dnsql2k/html/indexedviews1.asp?frame=tr
ue
Microsoft SQL Server 2000 Index
Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechn
ol/sql/maintain/Optimize/SS2KIDBP.asp?fram
e=true
Support Resources listed:
http://www.microsoft.com/sql/support/default.
asp
General Database Performance
12 November 2003  16:30-18:00
Thank you!
Kimberly L. Tripp
President, SYSolutions, Inc.
Website: www.SQLSkills.com
Email: [email protected]
Principal Mentor, Solid Quality Learning
Website: www.SolidQualityLearning.com
Email: [email protected]
Please fill out your evaluation!
General Database Performance
12 November 2003  16:30-18:00