Transcript DBA324

DBA 324
Designing for Performance
Kimberly L. Tripp
Solid Quality Learning – SolidQualityLearning.com
Email: [email protected]
SYSolutions, Inc. – SQLSkills.com
Email: [email protected]
Introduction
Kimberly L. Tripp, SQL Server MVP
Principal Mentor, Solid Quality Learning
* In-depth, high quality training around the world!
www.SolidQualityLearning.com
Content Manager for www.SQLSkills.com
Writer/Editor for TSQL Solutions/SQL Mag
www.tsqlsolutions.com and www.sqlmag.com
Consultant/Trainer/Speaker
Coauthor for MSPress title: SQL Server 2000
High Availability
Presenter/Technical Manager for SQL Server
2000 High Availability Overview DVD
Very approachable. Please ask me questions!
Overview
Database Structure
Data v. Log
Logging and VLFs
Design Techniques
Table Design
Partitioning
Constraints
Redundant Keys
Intro: Indexing for Performance
Database Structure
Up to 32,767 Databases per Instance
Database
Up to 32,767 Files, per Database
(Total is for both Data and Log files)
Data (file)
.mdf (1)
and .ndf (0-n)
Log (file)
.ldf (1-n)
MDF = Primary (or MAIN) Data File
LDF = Log Data File
NDF = Non-Primary Data File
Should have only 1 log file
Data files v. Log files (1 of 2)
Data files
Generally READ activity
Lots of users = more random reads (some
sequential)
Optimize for read performance
RAID 1+0 or RAID 0+1, RAID 5 is OK for
data but not great on write intensive
operations –
BACKUP/RESTORE/CHECKPOINT
Data files v. Log files (2 of 2)
Log files
In OLTP DB Log file should be REDUNDANT
most critical portion of the database if failure
NO data loss if log is accessible
= Up-to-the-minute recovery (NO_TRUNCATE)
Generally write activity (transactions)
Usually, sequential write activity
Limited read activity (replication, rollback, triggers)
Optimize for write activity (RAID 1, RAID 1+0, NOT
RAID 5)
Optimize for log activity (minimize VLFs)
How the Transaction Log Works
Virtual Log File 1
Virtual Log File 2
Virtual Log File 3
Virtual Log File 4
Virtual Log File 5
Inactive Virtual
Log File
Inactive Virtual
Log File
Inactive Virtual
Log File
Active Virtual
Log File
Inactive/Unused
Log File
Start
Logical Log
Min LSN
(first open
tran)
End
Logical Log
Log might NOT show 0 Percent Log Used after
backup for example - the % Log Used will be ~6%
Disk Bound Systems might experience some
performance degradation at log backup
Consider RAID 1+0 instead of RAID 1, RAID 5
generally is not recommended
Minimize VLFs
Excessive VLFs add overhead to log related activities
(transaction logging, log backups, logreader, triggers
(inserted/deleted), etc.)
Execute DBCC LOGINFO
Number of rows = Number of VLFs
If excessive then:
Free up log space using BACKUP LOG…
Shrink the transaction log file
DBCC SHRINKFILE(logfilename, TRUNCATEONLY)
Alter the Database and modify log file size
ALTER DATABASE dbname
MODIFY FILE ( NAME = file_name, SIZE = new_size)
Design Techniques
Table Width for Optimal Cache Utilization
The Data Row
Rows on a Page
Optimal Row Width
Vertical Partitioning
Constraints
Check Constraints
Horizontal Partitioning
The Data Row
Data
Header
4 bytes
Fixed Data
NB
VB
Null
Block
Variable
Block
Variable Data
Rows on a Page
96
bytes
Page Header
Row A
Row C
8,096
bytes
Data rows
Row B
C
B
A
Row Offset Table
2 bytes each
Optimal Row Width
Consider Table Usage above all else
Estimate Average Row Length
Overhead
Fixed Width Columns
Estimate Average from realistic sample data
SELECT avg(datalength(column)) FROM tname
Calculate Rows/Page
8096 Bytes/Page
= Rows/Page
??? Bytes/Row
Calculate Wasted Bytes – on Disk  in
Memory
Vertical Partitioning
Optimizing row size for…
Caching
More rows on a page, more rows in memory
Locking
Only locking the columns that are of interest.
Minimize row based conflicts
Usage Defines Vertical Partitions
Logically Group Columns to minimize joins
Consider Read Only vs. OLTP Columns
Consider Columns often used together
Consider Customer Table with
1,600,000 Rows
Customer
CustomerPersonal
14 Columns
1000 Bytes/Row
8 Rows/Page
200,000 Pages
1.6GB Table
47 Columns
4600 Bytes/Row
Only 1 Row/Page
3400+ Bytes Wasted
1.6 Million Pages
12.8 GB Table
CustomerProfessional
CustomerMisc
18 Columns*
1600 Bytes/Row
5 Rows/Page
320,000 Pages
2.5GB Table
17 Columns*
2000 Bytes/Row
4 Rows/Page
400,000 Pages
3.2 GB Table
*The Primary key column must be made
redundant for these two additional tables.
47 Columns in Employee. 49 Columns total
between 3 tables.
Customer
= 12.8 GB
Partitioned Tables
= 7.3 GB
•Savings in Overall Disk
Space (5.5 GB Saved)
•Not reading data into
cache when not
necessary
•Locks are table specific
therefore less contention
at the row level
Check Constraints (optimizer)
All data will be checked on INSERT/UPDATE
Pattern
CHECK (Phone LIKE ‘(___) ___-____’)
*Range*
CHECK (Salary < 750000)
The only operators supported for increased performance gains are:
BETWEEN, AND, OR, <, <=, >, >=, =. Others are supported for data
integrity purposes only.
List
CHECK (Country Code IN (‘US’, ‘AU’, ‘GB’, ‘FR’))
Added during CREATE TABLE
Add later using ALTER TABLE
But what about the existing data?
DO NOT USE WITH NOCHECK
ALTER TABLE with CHECK – all existing data is
checked and verified
ALTER TABLE with NOCHECK – speeds up the
creation BUT there are no performance gains for later
use of the constraint
Scenario
Charge Table 1.6 Million Rows
Constraint added with NOCHECK (ChargeAmt < 5000)
SELECT * FROM Charge WHERE ChargeAmt > 6000
-- yields 9305 I/O
Constraint added with CHECK (ChargeAmt < 5000)
SELECT * FROM Charge WHERE ChargeAmt > 6000
-- yields 0 I/O
Horizonal Partitioning
A Single Large Table…
Can be Hard to Manage
May have different access patterns
Does NOT have to be one table!
Minimize the impact of maintenance
Index Maintenance (Smaller/Less Frequent)
Backup/Restore (File/Filegroup strategies to
minimize backup frequency of predominantly
ReadOnly tables – more options for restore when
isolated disk/RAID array corruption)
Lock Escalation (Modifications to one partition do
not cause escalation against the others – as they
likely would if everything were in one table!)
Horizontal Partitioning w/Constraints
Sales2000_01
Sales
Sales2000_02
Sales2000_03
Sales2000_04
.
.
.
Sales2000
Table or View
Sales2000_12
Sales2001_01
Sales2001_02
Sales2001_03
Sales2001_04
.
.
.
Sales2001_12
874 Million Rows
All Sales Since January 2000
•Current Sales (INSERTs) often
blocked by DSS queries.
•Index Rebuilds take quite a
bit of time
•Weekly FULL Database Backups
are backing up the same sales
(2000 & 2001) repeatedly
Sales2002_01
Sales2001
Sales2002_02
Table or View
2002Sales
Table or View
Each table has a CHECK Constraint
CHECK (SalesDate BETWEEN ’01/01/2001’ AND ’01/31/2001’)
Sales2002_03
Sales2002_04
.
.
.
Sales2002_12
Duplicating Keys
Mathematically – One to Many Relationship
For Every Sale there is Only one Title
For Each Title there can be Many Sales
The Relationship between Sales and Titles is One to Many
For Every Title there is Only one publisher
For Each Publisher there can be Many Titles
The Relationship between Titles and Publishers is One to Many
Directionally
Sales to Titles = One Sale to One Title
Titles to Publishers = One Title to One Publisher
Can we describe Sales to Publishers?
Duplicating Keys
BEFORE adding redundant pub_id
AFTER adding redundant pub_id
Design Techniques Summary
Rows cannot span pages therefore record size can
impact disk space, cache utilization and concurrency
The lowest granularity of locking holds an entire row –
partitioning a table vertically or horizontally can improve
concurrency as well as cache utilization
Optimizer is aware of constraints – efficient horizontal
partitioning requires constraints
Duplicating Keys which define relationships can
improve join performance by giving the optimizer more
options for performing a join
Indexing for Performance
Simple Strategy - ITW
Selectivity (Highly Selective...easy)
How to Improve Queries with Varying
Search Arguments (SARGs) and Low
Selectivity
Indexing for AND
Indexing for OR
Indexing for Joins
Indexing for Aggregations
Indexed Views
Index Strategies
Determine Primary Usage of Table – OLTP vs.
OLAP vs. Combo? This can help determine the
Clustered Index
Create Constraints – Primary Key and
Alternate/Candidate Keys
Manually Add Indexes to Foreign Key Constraints
Capture a Workload and Run through Index Tuning
Wizard
Continue to test, tune and troubleshoot and Add
Additional Indexes using these strategies
Index Tuning Wizard
Create a TRACE of your
Production Server from your
main workstation (use SQLProfilerTuning
template)
Production
Development
Realistic Sample Workload
Backup/Restore Production
Data to Development
Environment
Real Data/Real Statistics for ITW
Point Profiler at Development
Server in order to perform
Index Tuning Wizard
Your Workstation
Does not Impact Production Server
Review
Database Structure
Data v. Log
Logging and VLFs
Design Techniques
Table Design
Partitioning
Constraints
Redundant Keys
Intro: Indexing for Performance
Other Sessions…
DAT 335 – SQL Server Tips and Tricks for DBAs
and Developers
Tuesday, 1 July 2003, 15:15-16:30
DBA 324 – Designing for Performance: Structures,
Partitioning, Views and Constraints
Wednesday, 2 July 2003, 08:30-09:45
DBA 328 – Designing for Performance: Optimization
with Indexes
Wednesday, 2 July 2003, 16:45-18:00
DBA 322 – Optimizing Stored Procedure
Performance in SQL Server 2000
Thursday, 3 July 2003, 08:30-09:45
Articles…
Articles in TSQLSolutions at www.tsqlsolutions.com (FREE,
just register)
All About Raiserror, InstantDoc ID#22980
Saving Production Data from Production DBAs, InstantDoc ID#22073
Articles in SQL Server Magazine, Sept 2002:
Before Disaster Strikes, InstantDoc ID#25915
Log Backups Paused for Good Reason, InstantDoc ID#26032
Restoring After Isolated Disk Failure, InstantDoc #26067
Filegroup Usage for VLDBs, InstantDoc ID#26031
Search www.sqlmag.com and www.tsqlsolutions.com for
additional articles
Resources…
Whitepaper: Query Recompilation in SQL
Server 2000
http://msdn.microsoft.com/library/default.asp?
url=/nhp/Default.asp?contentid=28000409
Resources…
Check out www.SQLSkills.com for scripts, demos, links
and new resources!
Inside Microsoft SQL Server 2000,
Kalen Delaney, MS Press, ISBN: 0735609985
http://www.insidesqlserver.com/
Whitepaper: Database Architecture: The Storage
Engine, http://msdn.microsoft.com/library/enus/dnsql2k/html/thestorageengine.asp?
Register on www.tsqlsolutions.com to get free access to
technical TSQL articles from SQL Server Magazine.
Check out Instant Doc ID#23733 for the “n-Table Joins”
article where I discuss adding redundant keys.
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/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=%2Fservicede
sks%2Fwebcasts%2Fwc111400%2Fwcblurb111400%2Easp
Community Resources
Community Resources
http://www.microsoft.com/communities/default.mspx
Most Valuable Professional (MVP)
http://www.mvp.support.microsoft.com/
Newsgroups
Converse online with Microsoft Newsgroups, including Worldwide
http://www.microsoft.com/communities/newsgroups/default.mspx
User Groups
Meet and learn with your peers
http://www.microsoft.com/communities/usergroups/default.mspx
Ask The Experts
Get Your Questions Answered
I will be available in the ATE
area after most of my sessions!
Thank You!
Kimberly L. Tripp
Principal Mentor, Solid Quality Learning
Website: www.SolidQualityLearning.com
Email: [email protected]
President, SYSolutions, Inc.
Website: www.SQLSkills.com
Email: [email protected]
Suggested Reading And Resources
The tools you need to put technology to work!
TITLE
Available
Microsoft® SQL Server™ 2000
High Availability: 0-7356-1920-4
7/9/03
Microsoft® SQL Server™ 2000
Administrator's Companion:07356-1051-7
Today
Microsoft Press books are 20% off at the TechEd Bookstore
Also buy any TWO Microsoft Press books and get a FREE T-Shirt
evaluations
© 2003 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.