Introduction to SQL Server Tuning
SQL Server Performance Lifecycle Management
Database performance isn’t something
to be managed haphazardly, but instead should be approached in a
methodical and organized manner. This is precisely what
Performance Lifecycle Management or PLM is designed to provide.
This chapter provides an introduction to PLM and covers the
basics areas involved in a successful PLM implementation.
SQL Server proactive Tuning
The foundation of SQL Server
performance isn’t found in SQL tuning or other such tasks.
Instead, it’s established during the initial physical design and
performance testing phases of the PLM cycle. This chapter shows
how to create a winning SQL Server database design and discusses
how to perform one of the most neglected activities of
performance management, which is proactive testing.
SQL Server performance
monitoring
Determining the overall health of a
SQL Server system can be confusing task, but it doesn’t have to
be. Accurately measuring database performance can easily be
accomplished once you understand the basic components of
performance and how they relate to one another. This chapter
takes a brief look at performance modeling and how it can be
applied to any SQL Server installation.
SQL Server monitoring
architecture
What are the overall goals of a
smart SQL Server monitoring plan? Do you know how to accurately
use the most important performance analysis methods for SQL
Server? This chapter provides an overview of the building blocks
of a solid SQL Server monitoring plan and introduces the key
performance analysis methods that every DBA needs to understand
and practice.
SQL Server Storage Bottleneck
Analysis
Bottleneck analysis is the primary
performance analysis method DBAs should use in diagnosing and
tuning SQL Server response time problems. This chapter focuses
on how to recognize and correct bottlenecks that occur in the
storage and overall SQL Server system layers.
Server and
Windows performance tuning
SQL Server does not
run in a vacuum, and no SQL Server database is going to
perform well with a Windows or server bottleneck.
Diagnosing SQL
Server session performance
If DBAs cannot locate any obvious
SQL Server issues at the storage or system layers, the next step
is to drill down into session and operating system metrics to
determine if any bottlenecks exist in these layers. This chapter
contains information on how to identify and correct bottlenecks
with SQL Server processes and the Windows server.
SQL Server
workload Analysis
After bottleneck analysis, the
next most important performance methodology is workload
analysis, which focuses on the overall workload generated by
system, session, and SQL activity. This chapter contains details
on how to understand the overall workload on a SQL Server and
recognize where improvements can be made.
SQL Server
Performance Analysis
- A SQL Server database health check
script
- The SQL Server database engine tuning
advisor
- Bookmark lookup analysis
Key performance ratios can
quickly help a SQL Server DBA understand how well their overall
system is performing. This chapter provides details on what
ratios a DBA needs to monitor, along with recommendations for
what to do when key performance metrics are out of line.
SQL
Server Table Tuning
- SQL Server statistics
- SQL Server table fragmentation
analysis
SQL
Server Index Tuning
- SQL Server index
fragmentation
- When to rebuild SQL Server Indexes
SQL
Server SQL Tuning
- SQL Server SQL Tuning goals
- SQL Server optimizer parameters
- SQL Server optimizer statistics
- Finding high-impact SQL statements
- Removing
unnecessary large-table full-table scans
- Displaying SQL Server execution
plans
- Altering SQL Server execution plans
- SQL Sever cursor cost analysis
SQL Server
workload Analysis
SQL Server
predictive tuning
Many SQL Server DBAs work in
a reactive mode, which means they do little to plan for the
future needs of the databases they oversee. This chapter
discusses the importance of historical trend analysis, what
key metrics should be tracked, and how to use historical
data to forecast future needs.
SQL Server Data
Warehousing performance
<From Sam Afyouni
book>
SQL Server
tuning secrets
Many SQL Server DBAs spend
time tuning things that make little or no impact in overall
performance. This chapter focuses on what to pay attention
to and how to form an overall PLM tuning plan that can be
effectively used in large SQL Server installations to
dramatically increase performance.
Gamma
1. Introduction to
Extended Stored Procedures
SQL Server 2008 and extended stored procedures
Using Query
Analyzer
Execution of Extended Stored Procedures
XP
Samples
13. Retrieving free disk space (XP_DISKSPACE)
Implementation
Testing the Code
14. Retrieving CPU usage (XP_CPUUSAGE)
Implementation
Testing the Code
Applications of
XP_CPUUSAGE
26. SQL Server database tutorial
Data
Integrity
Referential Integrity
Constraints
Rules,
Defaults, and User-Defined Data Types
Views
Joins
Temporary tables
Subqueries
Derived table
Cursors
Indexes
SQL Server Collation
Triggers
Referential
Integrity with triggers
Schumacher Chapter 1: Data
Lifecycle Management 15
Introduction
15
What is Data Lifecycle Management? 16
Data Management
18
Database Management
21
Performance Management
21
What about Self-Managing Databases? 25
Conclusion
33
Chapter 2: Accurately Assessing
Database Performance
34
Introduction
34
Modeling Peak Efficiency
35
Modeling Availability
36
Database Server Accessibility 37
Resource Availability
37
Modeling Speed
38
Access Efficiency
39
Code Efficiency
41
Design Success
42
Model Dependencies
43
The Impact of Availability
46
The Impact of Speed
49
Conclusion
50
Chapter 3: Performance Lifecycle
Management: Step One
53
Proactive Actions that Ensure Optimized
Performance
53
Laying a Strong Foundation
53
How to Build an ‘Unbreakable’ Database
67
The Link between Change Control and
Performance
75
Conclusion
78
Chapter 4: Performance Lifecycle
Management - Step Two
80
Establishing a Smart Monitoring Plan 80
General Monitoring Requirements 82
Cursory Monitoring
84
Analytic Real Time Monitoring 86
24 x 7 Monitoring
87
Build versus Buy
89
Performance Methodologies
91
Conclusion
93
Chapter 5: Bottleneck Analysis:
Part One
94
Diagnosing Storage and System Problems
94
What is Bottleneck Analysis?
95
Storage Bottlenecks
97
A Quick SQL Server Storage Primer 98
A Coming Physical I/O Crisis? 99
Storage Monitoring Basics
100
More Space Demographic Diagnostics 107
Object Fragmentation
109
Environments That Benefit From
Fragmentation Elimination
110
Storage Bottleneck Wrap Up
123
System Bottlenecks
124
SQL Server’s Wait Interface
125
Wait Statistics in SQL Server 2005
131
Miscellaneous System Bottlenecks 132
Error Log Analysis
134
Conclusion
135
Chapter 6: Bottleneck Analysis: Part Two
136
Diagnosing Session and O/S Issues 136
Session Bottleneck Analysis
137
Operating System Bottleneck Analysis 147
Conclusion
150
Chapter 7: Workload Analysis
151
Unlocking the Who, What, and Why of
Performance Problems
151
Server Workload Analysis
151
Examining CPU Activity
152
Server I/O
154
Database Workload Analysis
158
Getting Database I/O Details
160
Reducing Backup and Recovery Impact 163
Proper Database and Log File Placement
166
Session Workload Analysis
167
“I See You!”
169
SQL Workload Analysis
173
What is Bad SQL?
175
System Waste
176
Global SQL Metrics
177
SQL Statement Analysis
179
Conclusion
184
Chapter 8: Ratio Analysis
185
Techniques for Quickly Getting a Bird’s
Eye View of Performance
185
What is Ratio Analysis?
185
Deficiencies of Only Using a Bottleneck
Approach
186
Accurate Ratio Analysis Techniques 186
Memory Ratios
189
Looking at Buffer Cache Performance 191
Examining the Plan Cache
193
Total vs. Target Memory
195
Storage Ratios
196
Session Ratios
196
Conclusion
198
Performance Lifecycle Management:
Steps 3 and 4
199
Using History to Prepare for the Future
199
Why Perform Capacity Planning?
200
Proper Capacity Planning Techniques 204
Collecting Statistics
206
Scheduling Statistical Collections
211
Setting up Trend Analysis
212
Predicting the Future
215
A Simple Example
215
Conclusion
223
Chapter 10: Performance Lifecycle
Management: Step 5
225
Tuning that Makes a Difference
225
The Simplified Performance Tuning Model
226
Making Configuration Changes
227
When to Consider More Memory
228
Avoiding Maximum Limit Problems 229
Other Parameters that Deserve a Look 229
Miscellaneous Hardware Configuration
Notes
230
Honest Talk about Optimizing SQL 231
The SQL Optimization Toolbox
233
Validate the SQL
235
Obtain a Baseline
236
EXPLAIN and Understand
237
Update and Review Object Statistics 242
Look for Object-Based Solutions
245
Rewrite and Benchmark
246
Statistically Compare Cases and Select a
Winner
247
When to Revisit
247
Physical Design Revisions
248
Using Microsoft Supplied Aids 248
Intelligent Indexing
258
Smart File and Object Placement
266
Denormalization Techniques
275
Conclusion
281
Index
283
About the Author
290
Burleson
Chapter
1: Introduction to Oracle Tuning
An Introduction to Tuning
Throughput vs. Response Time
Top-down Tuning vs.
Bottom-up Tuning
Proactive Tuning vs. Reactive Tuning
Reactive Tuning
Proactive Tuning
Know the Limits: Things that We Can Not Tune
Application-level Tuning
A Historical Review of DBA Job
Duties
Tuning and Server Consolidation
Hardware
Technology Drives Database Technology
The Changing Role
of an Oracle Tuning Professional
The Causes of Poor
Oracle Performance
The Tuning Hierarchy
External Hardware Performance Review
Finding Database
Bottlenecks
Chapter
2: Time-Series Oracle Tuning
The Pros and Cons of Time Series Tuning
Signatures,
Workloads and Exceptions
Workloads and Predictive Analysis
Using Adaptive
Thresholds for Predictive Modeling
Time Series Tuning Guidelines
Trend-based Reconfiguration
When to Trigger a Dynamic
Reconfiguration
Approaches to Self-tuning Databases
Tuning a Constantly Changing Database
Capturing Time Series
Metrics
Customized AWR Tuning Reports
Chapter 4:
Predictive Modeling
Predicting the Future
Data Mining and Predictive Analytics
The Evolution toward Data Mining
Data Mining and Predictive
Analytics
Predictive Models Made Easy
Exception
Reporting
General Trend Identification
Correlation Analysis
Chapter
12: Server & Network Tuning
Oracle Server Tuning
Outside the Oracle Instance
Oracle Server Bottlenecks
Oracle Server Monitoring
Capturing Server-side Metrics
OS Statistics for the
Cost-based Optimizer
OS data inside Oracle views
The Oracle OS Watcher utility
Starting Oracle OS Watcher
Oracle CPU Tuning
Viewing CPU Utilization for Oracle
Identifying High CPU
Usage with vmstat
Storing Information from vmstat
Disk I/O and Oracle
Moore’s Law and Disk Speed
Server RAM and Oracle
Oracle
and the 64-bit Server Technology
The New Age of Oracle
Server Consolidation
Oracle Enterprise Manager and Server
Metrics
Server Metrics and SQL Execution
Oracle Network Tuning
Conclusion
Chapter
13:
Tuning the I/O Subsystem
Inside Oracle Disk Architecture
The Plague of Large Oracle
Disks
Disk Architectures of the 21st Century
RAID Technology and Oracle
RAID 5 is Not for Every Database
Oracle and Direct I/O
Enabling Oracle Direct I/O
Calibrating Disk I/O
Monitoring External Disk I/O
Capturing External iostat Information
Generating iostat
Reports
Solutions to Physical Read Waits
Choosing a default blocksize
Using Oracle Multiple Blocksizes
Reducing Data Buffer Waste with multiple blocksizes
Reducing Logical I/O with Multiple Blocksizes
Improving
Buffer Efficiency with Multiple Blocksizes
Improving SQL
Execution with Multiple Blocksizes
Real World Applications
of Multiple Blocksizes
The
db_file_multiblock_read_count Parameter
Oracle Blocksize & Index I/O
The Latest Consensus on Using Multiple Blocksizes
Vendor
Notes on Oracle Multiple Blocksizes
Reducing Disk I/O with SSD
2010 Market Survey of SSD Vendors for Oracle
Oracle Disk Monitoring
Examining Real-time Disk Statistics
Examining Global I/O
Tracking I/O for Specific Tables
Analyzing Real Time I/O Waits
Collecting Real-Time Disk Wait Events
Find the Current Disk I/O Session Bandits
Measuring Disk
I/O Speed
Time Series I/O Wait Analysis
Time Series
Monitoring of the Data Buffers
Monitoring Disk I/O with AWR
Conclusion
SQL Tuning
The Origin of SQL
Understanding SQL Tuning
Holistic
Oracle SQL Tuning
Dealing with Time Constraints
Best Practices for SQL Optimization
Proper Development Environment
Maintaining A SQL
Infrastructure
What is the Best Optimizer Philosophy?
The Persistent SQL Philosophy
The Dynamic SQL
Philosophy
Goals of SQL Tuning
Determine Optimal Table Join Order
Remove Unnecessary
Large-table Full-table Scans
Locating full-scan operations
Tuning large-table full-table scans
Tuning small-table
full-table scans
Optimizing SQL RAM Resources
Cache
Small-table Full-table Scans
Verify Optimal Index Usage
Verify Optimal Join Techniques
Tuning by Simplifying SQL
Syntax
Roadblocks to SQL Tuning
SQL Profiles
Tracing SQL Execution History
SQL as a Database Access
Method
Outer Join
Syntax
Tuning Distributed SQL Queries
Subqueries and SQL
Basic SQL Subqueries
Scalar
Subqueries
In-line Views (Subqueries in the from Clause)
Inside Views
Benefits of Views
The Downside to Using Views
Combining
Hints and Views
Parsing SQL Syntax
Create Executable
Interrogating SQL Execution Plans
SQL Optimizer Statistics
Column Skew and histograms
External Costing with the
Optimizer
Tuning SQL with Histograms
Determining the
Optimal Table Join Order
How is Join Cardinality Estimated?
SQL Tuning with Hints
When hints appear to be ignored
Oracle Indexes – Is
Maintenance Required?
Identifying Problem SQL
Find the Problem Sessions
The Goals of Holistic SQL Tuning
The SQL Tuning Advisor
Using SQL Tuning Advisor Session
Optimizing SQL Insert
Performance
Blocksize and Insert Performance
Oracle Delete Tuning
Using Bulking for Delete Performance
Update Tuning
SQL Tuning with Indexes
The types of Oracle indexes
Forcing index usage
Tuning SQL with Function-based Indexes (FBI)
SQL tuning with regular expression indexes
Indexing on regular expressions
Doing case sensitive
searches with indexes
Monitoring index usage
Monitoring for Index Range Scans
Monitoring SQL workload activity
Verifying optimal index usage
Finding indexing
opportunities
Find SQL that uses sub-optimal indexes
Finding SQL with excessive I/O
Finding sub-optimal SQL in
the library cache
Locating un-used indexes
Dropping un-used indexes
Locating infrequently used
indexes
The problem of too many indexes
Determining which index to
delete
Large Multi-column Indexes
Row clustering and SQL Performance
Index reorganization and
SQL Performance
When rebuilding indexes may help SQL performance
When
rebuilding indexes will hurt performance
Choosing
candidates for index maintenance
Chapter 17:
Data Warehouse Tuning
Get text from Afyouni
|
Only $39.95
(30% off)
