Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

  SQL Server Tuning
The Definitive Reference

Steve Callan

Retail Price $44.95 /  £37.95
 

Key Features About the Author Table of Contents
Index Reader Comments Errata
     

  Only $39.95 (30% off)

ISBN
ISBN
Library of Congress Number
600 pages
Perfect bind - 9x7
PD TBD
Shelving: Database/SQL Server IT in-Focus: Series # 4


SQL Server is growing up, and today's Microsoft professional needs to know expert tips for tuning a SQL Server database.

f you 're an Oracle DBA who wants to learn SQL Server fast, this is the book for you.

Written by one the world's most widely-read DBA experts, Steve Callan offers real-world advice on tuning mission-critical MS-SQL databases.

This is an advanced SQL Server tuning book, geared toward the full-time SQL Server DBA who needs to understand performance optimization for very large high volume systems.  This is not the SQL Server tuning book for the dilettante or neophyte.

 

Key Features

* Understand a proven approach to SQL Server tuning.

* See specialized scripts to find SQL Server bottlenecks.

* Learn secret techniques for identifying pending MS-SQL performance problems before they impact response time.

* See how SQL Server parameters effect performance.

* Develop a MSSQL tuning monitoring system that uses Windows scripts to alerts you to tuning issues.

 * Understand how to identify and fix fragmentation without impacting your end-users.

 

 

About the Author:

 


Steve Callan 
 

Steve Callan is an Oracle certified professional with over 20 years of progressive hands-on technical experience and nearly a decade managing mission-critical Oracle databases.  He is also a Microsoft Certified Professional (MCP), and is proficient in SQL Server 2005. 

 

Steve is a highly technical database administrator with skills in managing very large Oracle databases, including skills in using Oracle Real Application Clusters (RAC) for high availability.  He is also a quantitative expert, proficient at advanced modeling, simulation and statistical analysis techniques.  In his roles as a lead technical DBA, he has managed Oracle security (using virtual private databases and role-based security) and he has experience with Oracle data warehouse technology.

 

Steve is also an experienced UNIX Systems Administrator, performing critical UNIX system admin duties, knowledgeable in all areas of UNIX administration including installation and configuration, security and multi-tiered UNIX architectures. 

 

Mr. Callan is a multi-faceted expert with significant experience using Oracle Application Server technology, performing all of the duties of an Oracle Application Server DBA and leveraging his technical skills with application server extension technologies, including HTML, JSP, JavaScript, CSS, Apache and Tomcat.  He also has experience as a developer using Apex (HTML-DB), SQL*Forms and SQL*Reports.

 

Steve has a Masters degree in Software and Information Systems from Regis University, and a Masters degree in Industrial Engineering from New Mexico State University.  He has also completed all Ph.D. coursework for his pending doctorate in Mathematical & Computer Sciences at the Colorado School of Mines.

 

 

Table of Contents:
SQL Server optimizer statistics and SQL performance
SQL Server cursor cost analysis
SQL Server Index fragmentation
SQL Server database engine tuning advisor
Bookmark lookup analysis
 
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

 

 

   

 

 

 Index Topics:
 


Reviews:


Errata:

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks