Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Grant Fritchey
SQL Server 2012 Query Performance Tuning, Third Edition
Title Page
Contents at a glance
Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
CHAPTER 1: SQL Query Performance Tuning
The Performance Tuning Process
Performance vs. Price
Performance Baseline
Where to Focus Efforts
SQL Server Performance Killers
Summary
CHAPTER 2: System Performance Analysis
Performance Monitor Tool
Dynamic Management Objects
Hardware Resource Bottlenecks
Memory Bottleneck Analysis
Additional Memory Monitoring Tools
Memory Bottleneck Resolutions
Disk Bottleneck Analysis
Additional I/O Monitoring Tools
Disk Bottleneck Resolutions
Processor Bottleneck Analysis
Other Tools for Measuring CPU Performance
Processor Bottleneck Resolutions
Network Bottleneck Analysis
Network Bottleneck Resolutions
SQL Server Overall Performance
Considerations for Monitoring Virtual Machines
Creating a Baseline
Summary
CHAPTER 3: SQL Query Performance Analysis
Extended Events Wizard
Extended Events Automation
Extended Events Recommendations
Other Methods for Query Performance Metrics
Costly Queries
Execution Plans
Summary
CHAPTER 4: Index Analysis
What Is an Index?
Index Design Recommendations
Clustered Indexes
Nonclustered Indexes
Clustered vs. Nonclustered Indexes
Advanced Indexing Techniques
Special Index Types
Additional Characteristics of Indexes
Summary
CHAPTER 5: Database Engine Tuning Advisor
Database Engine Tuning Advisor Mechanisms
Database Engine Tuning Advisor Examples
Database Engine Tuning Advisor Limitations
Summary
CHAPTER 6: Lookup Analysis
Purpose of Lookups
Drawbacks of Lookups
Analyzing the Cause of a Lookup
Resolving Lookups
Summary
CHAPTER 7: Statistics Analysis
The Role of Statistics in Query Optimization
Statistics on a Nonindexed Column
Analyzing Statistics
Statistics Maintenance
Statistics Maintenance Status
Analyzing the Effectiveness of Statistics for a Query
Recommendations
Summary
CHAPTER 8: Fragmentation Analysis
Causes of Fragmentation
Fragmentation Overhead
Analyzing the Amount of Fragmentation
Analyzing the Fragmentation of a Small Table
Fragmentation Resolutions
Significance of the Fill Factor
Automatic Maintenance
Summary
CHAPTER 9: Execution Plan Cache Analysis
Execution Plan Generation
Components of the Execution Plan
Aging of the Execution Plan
Analyzing the Execution Plan Cache
Execution Plan Reuse
Ad Hoc Workload
Query Plan Hash and Query Hash
Execution Plan Cache Recommendations
Summary
CHAPTER 10: Query Recompilation
Benefits and Drawbacks of Recompilation
Identifying the Statement Causing Recompilation
Analyzing Causes of Recompilation
Avoiding Recompilations
Summary
CHAPTER 11: Query Design Analysis
Query Design Recommendations
Operating on Small Result Sets
Limit the Number of Columns in select_list
Avoiding Optimizer Hints
Using Domain and Referential Integrity
Avoiding Resource-Intensive Queries
Reducing the Number of Network Round-Trips
Reducing the Transaction Cost
Summary
CHAPTER 12: Blocking Analysis
Blocking Fundamentals
Understanding Blocking
Locks
Lock Operations and Modes
Isolation Levels
Effect of Indexes on Locking
Capturing Blocking Information
Blocking Resolutions
Partition the Contended Data
Recommendations to Reduce Blocking
Automation to Detect and Collect Blocking Information
Summary
CHAPTER 13: Deadlock Analysis
Deadlock Fundamentals
Deadlock Analysis
Avoiding Deadlocks
Summary
CHAPTER 14: Cursor Cost Analysis
Cursor Fundamentals
Default Result Set
Cursor Overhead
Summary
CHAPTER 15: Database Performance Testing
Database Performance Testing
Capturing Data with the Server Side Trace
Distributed Replay for Database Testing
Conclusion
CHAPTER 16: Database Workload Optimization
Workload Optimization Fundamentals
Capturing the Workload
Analyzing the Workload
Identifying the Costliest Query
Analyzing and Optimizing External Factors
Analyzing the Effect on Database Workload
Iterating Through Optimization Phases
Summary
CHAPTER 17: SQL Server Optimization Checklist
Database Design
Query Design
Configuration Settings
Database Administration
Database Backup
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title Page
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset