SQL Server 2008 Query Performance Tuning Distilled

SQL Server 2008 Query Performance Tuning Distilled

by Grant Fritchey, Sajal Dam
Released March 2009
Publisher(s): Apress
ISBN: 9781430219026

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

SQL Server 2008 Query Performance Tuning Distilled presents a direct trouble–shooting methodology for identifying poorly-performing stored procedures and queries, isolating the causes of that poor performance, and fixing the underlying problems. Each chapter is dedicated to one of the top causes of poorly performing queries and shows methods for identifying and dealing with the problems in that chapter's domain. Emphasis is always put upon or placed upon practical methods that you can put to immediate use in your day-to-day work. SQL Server 2008 functionality, tips, and tricks are emphasized in each subject area.

  • Emphasizes the practical. Does not bury readers in theory.

  • Gives readers practical techniques to immediately apply in their daily work.

  • Dedicates a chapter to each of the most common, performance-related problem areas.

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright
  4. Contents at a Glance
  5. Contents
  6. About the Author
  7. About The Technical Reviewer
  8. Acknowledgments
  9. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Downloading the Code
    4. Contacting the Author
  10. CHAPTER 1: SQL Query Performance tuning
    1. The Performance-Tuning Process
      1. The Core Process
      2. Iterating the Process
    2. Performance vs. Price
      1. Performance Targets
      2. "Good Enough" Tuning
    3. Performance Baseline
    4. Where to Focus Efforts
    5. SQL Server Performance Killers
      1. Poor Indexing
      2. Inaccurate Statistics
      3. Excessive Blocking and Deadlocks
      4. Non-Set-Based Operations
      5. Poor Query Design
      6. Poor Database Design
      7. Excessive Fragmentation
      8. Nonreusable Execution Plans
      9. Poor Execution Plans
      10. Frequent Recompilation of Execution Plans
      11. Improper Use of Cursors
      12. Improper Configuration of the Database Log
      13. Excessive Use or Improper Configuration of tempdb
    6. Summary
  11. CHAPTER 2: System Performance Analysis
    1. Performance Monitor Tool
    2. Dynamic Management Views
    3. Hardware Resource Bottlenecks
      1. Identifying Bottlenecks
      2. Bottleneck Resolution
    4. Memory Bottleneck Analysis
      1. SQL Server Memory Management
      2. Available Bytes
      3. Pages/sec and Page Faults/sec Counters
      4. Buffer Cache Hit Ratio
      5. Page Life Expectancy
      6. Checkpoint Pages/sec
      7. Lazy writes/sec
      8. Memory Grants Pending
      9. Target Server Memory (KB) and Total Server Memory (KB)
    5. Memory Bottleneck Resolutions
      1. Optimizing Application Workload
      2. Allocating More Memory to SQL Server
      3. Increasing System Memory
      4. Changing from a 32-bit to a 64-bit Processor
      5. Enabling 3GB of Process Space
      6. Using Memory Beyond 4GB Within SQL Server
    6. Disk Bottleneck Analysis
      1. Disk Counters
      2. % Disk Time
      3. Current Disk Queue Length
      4. Disk Transfers/sec
      5. Disk Bytes/sec
      6. Avg. Disk Sec/Read and Avg. Disk Sec/Write
    7. Disk Bottleneck Resolutions
      1. Optimizing Application Workload
      2. Using a Faster Disk Drive
      3. Using a RAID Array
      4. Using a SAN System
      5. Aligning Disks Properly
      6. Using a Battery-Backed Controller Cache
      7. Adding System Memory
      8. Creating Multiple Files and Filegroups
      9. Placing the Table and Index on Separate Disks
      10. Saving Log Files to a Separate Physical Disk
      11. Partitioning Tables
    8. Processor Bottleneck Analysis
      1. % Processor Time
      2. % Privileged Time
      3. Processor Queue Length
      4. Context Switches/sec
      5. Batch Requests/sec
      6. SQL Compilations/sec
      7. SQL Recompilations/sec
    9. Processor Bottleneck Resolutions
      1. Optimizing Application Workload
      2. Eliminating Excessive Compiles/Recompiles
      3. Using More or Faster Processors
      4. Using a Large L2/L3 Cache
      5. Running More Efficient Controllers/Drivers
      6. Not Running Unnecessary Software
    10. Network Bottleneck Analysis
      1. Bytes Total/sec
      2. % Net Utilization
    11. Network Bottleneck Resolutions
      1. Optimizing Application Workload
      2. Adding Network Adapters
      3. Moderating and Avoiding Interruptions
    12. SQL Server Overall Performance
      1. Missing Indexes
      2. Database Blocking
      3. Nonreusable Execution Plans
      4. General Behavior
    13. Creating a Baseline
      1. Creating a Reusable List of Performance Counters
      2. Creating a Counter Log Using the List of Performance Counters
      3. Minimizing Performance Monitor Overhead
    14. System Behavior Analysis Against Baseline
    15. Summary
  12. CHAPTER 3: SQL Query Performance Analysis
    1. The SQL Profiler Tool
      1. Profiler Traces
      2. Events
      3. Data Columns
      4. Filters
      5. Trace Templates
      6. Trace Data
    2. Trace Automation
      1. Capturing a Trace Using the GUI
      2. Capturing a Trace Using Stored Procedures
    3. Combining Trace and Performance Monitor Output
    4. SQL Profiler Recommendations
      1. Limiting the Number of Events and Data Columns
      2. Discarding Start Events for Performance Analysis
      3. Limiting the Trace Output Size
      4. Avoiding Online Data Column Sorting
      5. Running Profiler Remotely
      6. Limiting the Use of Certain Events
    5. Query Performance Metrics Without Profiler
    6. Costly Queries
      1. Identifying Costly Queries
      2. Identifying Slow-Running Queries
    7. Execution Plans
      1. Analyzing a Query Execution Plan
      2. Identifying the Costly Steps in an Execution Plan
      3. Analyzing Index Effectiveness
      4. Analyzing Join Effectiveness
      5. Actual vs. Estimated Execution Plans
      6. Plan Cache
    8. Query Cost
      1. Client Statistics
      2. Execution Time
      3. STATISTICS IO
    9. Summary
  13. CHAPTER 4: Index Analysis
    1. What Is an Index?
      1. The Benefit of Indexes
      2. Index Overhead
    2. Index Design Recommendations
      1. Examine the WHERE Clause and Join Criteria Columns
      2. Use Narrow Indexes
      3. Examine Column Uniqueness
      4. Examine the Column Data Type
      5. Consider Column Order
      6. Consider the Type of Index
    3. Clustered Indexes
      1. Heap Tables
      2. Relationship with Nonclustered Indexes
      3. Clustered Index Recommendations
    4. Nonclustered Indexes
      1. Nonclustered Index Maintenance
      2. Defining the Bookmark Lookup
      3. Nonclustered Index Recommendations
    5. Clustered vs. Nonclustered Indexes
      1. Benefits of a Clustered Index over a Nonclustered Index
      2. Benefits of a Nonclustered Index over a Clustered Index
    6. Advanced Indexing Techniques
      1. Covering Indexes
      2. Index Intersections
      3. Index Joins
      4. Filtered Indexes
      5. Indexed Views
      6. Index Compression
    7. Special Index Types
      1. Full-Text
      2. Spatial
      3. XML
    8. Additional Characteristics of Indexes
      1. Different Column Sort Order
      2. Index on Computed Columns
      3. Index on BIT Data Type Columns
      4. CREATE INDEX Statement Processed As a Query
      5. Parallel Index Creation
      6. Online Index Creation
      7. Considering the Database Engine Tuning Advisor
    9. Summary
  14. CHAPTER 5: Database Engine Tuning Advisor
    1. Database Engine Tuning Advisor Mechanisms
    2. Database Engine Tuning Advisor Examples
      1. Tuning a Query
      2. Tuning a Trace Workload
    3. Database Engine Tuning Advisor Limitations
    4. Summary
  15. CHAPTER 6: Bookmark Lookup Analysis
    1. Purpose of Bookmark Lookups
    2. Drawbacks of Bookmark Lookups
    3. Analyzing the Cause of a Bookmark Lookup
    4. Resolving Bookmark Lookups
      1. Using a Clustered Index
      2. Using a Covering Index
      3. Using an Index Join
    5. Summary
  16. CHAPTER 7: Statistics Analysis
    1. The Role of Statistics in Query Optimization
    2. Statistics on an Indexed Column
      1. Benefits of Updated Statistics
      2. Drawbacks of Outdated Statistics
    3. Statistics on a Nonindexed Column
      1. Benefits of Statistics on a Nonindexed Column
      2. Drawback of Missing Statistics on a Nonindexed Column
    4. Analyzing Statistics
      1. Density
      2. Statistics on a Multicolumn Index
      3. Statistics on a Filtered Index
    5. Statistics Maintenance
      1. Automatic Maintenance
      2. Manual Maintenance
      3. Statistics Maintenance Status
    6. Analyzing the Effectiveness of Statistics for a Query
      1. Resolving a Missing Statistics Issue
      2. Resolving an Outdated Statistics Issue
    7. Recommendations
      1. Backward Compatibility of Statistics
      2. Auto Create Statistics
      3. Auto Update Statistics
      4. Automatic Update Statistics Asynchronously
      5. Amount of Sampling to Collect Statistics
    8. Summary
  17. CHAPTER 8: Fragmentation Analysis
    1. Causes of Fragmentation
      1. Page Split by an UPDATE Statement
      2. Page Split by an INSERT Statement
    2. Fragmentation Overhead
    3. Analyzing the Amount of Fragmentation
      1. Analyzing the Fragmentation of a Small Table
    4. Fragmentation Resolutions
      1. Dropping and Re-creating the Index
      2. Re-creating the Index with the DROP_EXISTING Clause
      3. Executing the ALTER INDEX REBUILD Statement
      4. Executing the ALTER INDEX REORGANIZE Statement
    5. Significance of the Fill Factor
    6. Automatic Maintenance
    7. Summary
  18. CHAPTER 9: Execution Plan Cache Analysis
    1. Execution Plan Generation
      1. Parser
      2. Algebrizer
      3. Optimization
    2. Execution Plan Caching
    3. Components of the Execution Plan
      1. Query Plan
      2. Execution Context
    4. Aging of the Execution Plan
    5. Analyzing the Execution Plan Cache
    6. Execution Plan Reuse
      1. Ad Hoc Workload
      2. Prepared Workload
      3. Plan Reusability of an Ad Hoc Workload
      4. Plan Reusability of a Prepared Workload
    7. Query Plan Hash and Query Hash
    8. Execution Plan Cache Recommendations
      1. Explicitly Parameterize Variable Parts of a Query
      2. Create Stored Procedures to Implement Business Functionality
      3. Code with sp_executesql to Avoid Stored Procedure Maintenance
      4. Implement the Prepare/Execute Model to Avoid Resending a Query String
      5. Avoid Ad Hoc Queries
      6. Prefer sp_executesql over EXECUTE for Dynamic Queries
      7. Parameterize Variable Parts of Queries with Care
      8. Do Not Allow Implicit Resolution of Objects in Queries
    9. Summary
  19. CHAPTER 10: Stored Procedure Recompilation
    1. Benefits and Drawbacks of Recompilation
    2. Identifying the Statement Causing Recompilation
    3. Analyzing Causes of Recompilation
      1. Schema or Bindings Changes
      2. Statistics Changes
      3. Deferred Object Resolution
      4. SET Options Changes
      5. Execution Plan Aging
      6. Explicit Call to sp_recompile
      7. Explicit Use of the RECOMPILE Clause
    4. Avoiding Recompilations
      1. Do Not Interleave DDL and DML Statements
      2. Avoiding Recompilations Caused by Statistics Change
      3. Using Table Variables
      4. Avoiding Changing SET Options Within a Stored Procedure
      5. Using OPTIMIZE FOR Query Hint
      6. Using Plan Guides
    5. Summary
  20. CHAPTER 11: Query Design Analysis
    1. Query Design Recommendations
    2. Operating on Small Result Sets
      1. Limit the Number of Columns in select_list
      2. Use Highly Selective WHERE Clauses
    3. Using Indexes Effectively
      1. Avoid Nonsargable Search Conditions
      2. Avoid Arithmetic Operators on the WHERE Clause Column
      3. Avoid Functions on the WHERE Clause Column
    4. Avoiding Optimizer Hints
      1. JOIN Hint
      2. INDEX Hints
    5. Using Domain and Referential Integrity
      1. NOT NULL Constraint
      2. Declarative Referential Integrity
    6. Avoiding Resource-Intensive Queries
      1. Avoid Data Type Conversion
      2. Use EXISTS over COUNT(*) to Verify Data Existence
      3. Use UNION ALL Instead of UNION
      4. Use Indexes for Aggregate and Sort Conditions
      5. Avoid Local Variables in a Batch Query
      6. Be Careful Naming Stored Procedures
    7. Reducing the Number of Network Round-Trips
      1. Execute Multiple Queries Together
      2. Use SET NOCOUNT
    8. Reducing the Transaction Cost
      1. Reduce Logging Overhead
      2. Reduce Lock Overhead
    9. Summary
  21. CHAPTER 12: Blocking Analysis
    1. Blocking Fundamentals
    2. Understanding Blocking
      1. Atomicity
      2. Consistency
      3. Isolation
      4. Durability
    3. Database Locks
      1. Lock Granularity
      2. Lock Escalation
      3. Lock Modes
      4. Lock Compatibility
    4. Isolation Levels
      1. Read Uncommitted
      2. Read Committed
      3. Repeatable Read
      4. Serializable
      5. Snapshot
    5. Effect of Indexes on Locking
      1. Effect of a Nonclustered Index
      2. Effect of a Clustered Index
      3. Effect of Indexes on the Serializable Isolation Level
    6. Capturing Blocking Information
      1. Capturing Blocking Information with SQL
      2. Profiler Trace and the Blocked Process Report Event
    7. Blocking Resolutions
      1. Optimize the Queries
      2. Decrease the Isolation Level
      3. Partition the Contended Data
      4. Covering Index on Contended Data
    8. Recommendations to Reduce Blocking
    9. Automation to Detect and Collect Blocking Information
    10. Summary
  22. CHAPTER 13: Deadlock Analysis
    1. Deadlock Fundamentals
      1. Choosing the Deadlock Victim
    2. Using Error Handling to Catch a Deadlock
    3. Deadlock Analysis
      1. Collecting Deadlock Information
      2. Analyzing the Deadlock
    4. Avoiding Deadlocks
      1. Accessing Resources in the Same Chronological Order
      2. Decreasing the Number of Resources Accessed
      3. Minimizing Lock Contention
    5. Summary
  23. CHAPTER 14: Cursor Cost Analysis
    1. Cursor Fundamentals
      1. Cursor Location
      2. Cursor Concurrency
      3. Cursor Types
    2. Cursor Cost Comparison
      1. Cost Comparison on Cursor Location
      2. Cost Comparison on Cursor Concurrency
      3. Cost Comparison on Cursor Type
    3. Default Result Set
      1. Benefits
      2. Drawbacks
    4. Analyzing SQL Server Overhead with Cursors
      1. Analyzing SQL Server Overhead with T-SQL Cursors
    5. Cursor Recommendations
    6. Summary
  24. CHAPTER 15: Database Workload Optimization
    1. Workload Optimization Fundamentals
    2. Workload Optimization Steps
      1. Sample Workload
    3. Capturing the Workload
    4. Analyzing the Workload
    5. Identifying the Costliest Query
    6. Determining the Baseline Resource Use of the Costliest Query
      1. Overall Resource Use
      2. Detailed Resource Use
    7. Analyzing and Optimizing External Factors
      1. Analyzing the Batch-Level Options Used by the Application
      2. Analyzing the Effectiveness of Statistics
      3. Analyzing the Need for Defragmentation
    8. Analyzing the Internal Behavior of the Costliest Query
      1. Analyzing the Query Execution Plan
      2. Identifying the Costly Steps in the Execution Plan
      3. Analyzing the Effectiveness of the Processing Strategy
    9. Optimizing the Costliest Query
      1. Modifying an Existing Index
      2. Analyzing the Application of a Join Hint
      3. Avoiding the Clustered Index Scan Operation
      4. Modifying the Procedure
    10. Analyzing the Effect on Database Workload
    11. Iterating Through Optimization Phases
    12. Summary
  25. CHAPTER 16: SQL Server Optimization Checklist
    1. Database Design
      1. Balancing Under- and Overnormalization
      2. Benefiting from Entity-Integrity Constraints
      3. Benefiting from Domain and Referential Integrity Constraints
      4. Adopting Index-Design Best Practices
      5. Avoiding the Use of the sp_ Prefix for Stored Procedure Names
      6. Minimizing the Use of Triggers
    2. Query Design
      1. Use the Command SET NOCOUNT ON
      2. Explicitly Define the Owner of an Object
      3. Avoid Nonsargable Search Conditions
      4. Avoid Arithmetic Operators on the WHERE Clause Column
      5. Avoid Optimizer Hints
      6. Stay Away from Nesting Views
      7. Ensure No Implicit Data Type Conversions
      8. Minimize Logging Overhead
      9. Adopt Best Practices for Reusing Execution Plans
      10. Adopt Best Practices for Database Transactions
      11. Eliminate or Reduce the Overhead of Database Cursors
    3. Configuration Settings
      1. Affinity Mask
      2. Memory Configuration Options
      3. Cost Threshold for Parallelism
      4. Max Degree of Parallelism
      5. Optimize for Ad Hoc Workloads
      6. Query Governor Cost Limit
      7. Fill Factor (%)
      8. Blocked Process Threshold
      9. Database File Layout
      10. Database Compression
    4. Database Administration
      1. Keep the Statistics Up-to-Date
      2. Maintain a Minimum Amount of Index Defragmentation
      3. Cycle the SQL Error Log File
      4. Avoid Automatic Database Functions Such As AUTO_CLOSE or AUTO_SHRINK
      5. Minimize the Overhead of SQL Tracing
    5. Database Backup
      1. Incremental and Transaction Log Backup Frequency
      2. Backup Distribution
      3. Backup Compression
    6. Summary
  26. Index

Product information

  • Title: SQL Server 2008 Query Performance Tuning Distilled
  • Author(s): Grant Fritchey, Sajal Dam
  • Release date: March 2009
  • Publisher(s): Apress
  • ISBN: 9781430219026