SQL Server Query Performance Tuning,Fourth Edition

SQL Server Query Performance Tuning,Fourth Edition

Released September 2014
Publisher(s): Apress
ISBN: 9781430267423

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

Queries not running fast enough? Wondering about the in-memory database features in 2014? Tired of phone calls from frustrated users? Grant Fritchey's book SQL Server Query Performance Tuning is the answer to your SQL Server query performance problems. The book is revised to cover the very latest in performance optimization features and techniques, especially including the newly-added, in-memory database features formerly known under the code name Project Hekaton. This book provides the tools you need to approach your queries with performance in mind.

SQL Server Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You’ll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You’ll learn to recognize bottlenecks and defuse them before the phone rings. You’ll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server Query Performance Tuning into practice today.

  • Covers the in-memory features from Project Hekaton
  • Helps establish performance baselines and monitor against them

    • Guides in troubleshooting and eliminating of bottlenecks that frustrate users

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Contents at a Glance
    5. Contents
    6. About the Author
    7. About the Technical Reviewer
    8. Acknowledgments
    9. Introduction
    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. Insufficient Indexing
        2. Inaccurate Statistics
        3. Improper Query Design
        4. Poorly Generated Execution Plans
        5. Excessive Blocking and Deadlocks
        6. Non-Set-Based Operations
        7. Inappropriate Database Design
        8. Excessive Fragmentation
        9. Nonreusable Execution Plans
        10. Frequent Recompilation of Queries
        11. Improper Use of Cursors
        12. Improper Configuration of the Database Transaction Log
        13. Excessive Use or Improper Configuration of tempdb
      6. Summary
    11. Chapter 2: Memory Performance Analysis
      1. Performance Monitor Tool
      2. Dynamic Management Objects
      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
        4. Paging File %Usage and Page File %Usage
        5. Buffer Cache Hit Ratio
        6. Page Life Expectancy
        7. Checkpoint Pages/Sec
        8. Lazy Writes/Sec
        9. Memory Grants Pending
        10. Target Server Memory (KB) and Total Server Memory (KB)
      5. Additional Memory Monitoring Tools
        1. DBCC MEMORYSTATUS
        2. Dynamic Management Objects
      6. Memory Bottleneck Resolutions
        1. Optimizing Application Workload
        2. Allocating More Memory to SQL Server
        3. Moving In-Memory Tables Back to Standard Storage
        4. Increasing System Memory
        5. Changing from a 32-Bit to a 64-Bit Processor
        6. Compressing Data
        7. Enabling 3GB of Process Address Space
        8. Addressing Fragmentation
      7. Summary
    12. Chapter 3: Disk Performance Analysis
      1. 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
      2. Additional I/O Monitoring Tools
        1. Sys.dm_io_virtual_file_stats
        2. Sys.dm_os_wait_stats
      3. Disk Bottleneck Resolutions
        1. Optimizing Application Workload
        2. Using a Faster I/O Path
        3. Using a RAID Array
        4. Using a SAN System
        5. Using Solid State Drives
        6. Aligning Disks Properly
        7. Adding System Memory
        8. Creating Multiple Files and Filegroups
        9. Moving the Log Files to a Separate Physical Disk
        10. Using Partitioned Tables
      4. Summary
    13. Chapter 4: CPU Performance Analysis
      1. 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
      2. Other Tools for Measuring CPU Performance
        1. Sys.dm_os_wait_stats
        2. Sys.dm_os_workers and Sys.dm_os_schedulers
      3. Processor Bottleneck Resolutions
        1. Optimizing Application Workload
        2. Eliminating Excessive Compiles/Recompiles
        3. Using More or Faster Processors
        4. Not Running Unnecessary Software
      4. Network Bottleneck Analysis
        1. Bytes Total/Sec
        2. % Net Utilization
      5. Network Bottleneck Resolutions
        1. Optimizing Application Workload
      6. SQL Server Overall Performance
        1. Missing Indexes
        2. Database Concurrency
        3. Nonreusable Execution Plans
        4. General Behavior
        5. User Connections
        6. Batch Requests/Sec
      7. Summary
    14. Chapter 5: Creating a Baseline
      1. Considerations for Monitoring Virtual and Hosted Machines
      2. Creating a Baseline
        1. Creating a Reusable List of Performance Counters
        2. Creating a Counter Log Using the List of Performance Counters
        3. Performance Monitor Considerations
        4. System Behavior Analysis Against Baseline
      3. Summary
    15. Chapter 6: Query Performance Metrics
      1. Extended Events
        1. Extended Events Sessions
        2. Global Fields
        3. Event Filters
        4. Event Fields
        5. Data Storage
        6. Finishing the Session
      2. Extended Events Automation
        1. Creating a Session Script Using the GUI
        2. Defining a Session Using T-SQL
      3. Extended Events Recommendations
        1. Set Max File Size Appropriately
        2. Avoid Debug Events
        3. Avoid Use of No_Event_Loss
      4. Other Methods for Query Performance Metrics
      5. Summary
    16. Chapter 7: Analyzing Query Performance
      1. Costly Queries
        1. Identifying Costly Queries
        2. Costly Queries with a Single Execution
        3. Costly Queries with Multiple Executions
        4. Identifying Slow-Running Queries
      2. 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. Hash Join
        6. Actual vs. Estimated Execution Plans
        7. Plan Cache
        8. Query Resource Cost
        9. Client Statistics
        10. Execution Time
        11. STATISTICS IO
      3. Summary
    17. Chapter 8: Index Architecture and Behavior
      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. When to Use a Clustered Index
        5. Poor Design Practices for a Clustered Index
      4. Nonclustered Indexes
        1. Nonclustered Index Maintenance
        2. Defining theLookup Operation
        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. Summary
    18. Chapter 9: Index Analysis
      1. Advanced Indexing Techniques
        1. Covering Indexes
        2. A Pseudoclustered Index
        3. Recommendations
        4. Index Intersections
        5. Index Joins
        6. Filtered Indexes
        7. Indexed Views
        8. Index Compression
        9. Columnstore Indexes
      2. Special Index Types
        1. Full-Text
        2. Spatial
        3. XML
      3. 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
      4. Summary
    19. Chapter 10: 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. Tuning from the Procedure Cache
      3. Database Engine Tuning Advisor Limitations
      4. Summary
    20. Chapter 11: Key Lookups and Solutions
      1. Purpose of Lookups
      2. Drawbacks of Lookups
      3. Analyzing the Cause of a Lookup
      4. Resolving Lookups
        1. Using a Clustered Index
        2. Using a Covering Index
        3. Using an Index Join
      5. Summary
    21. Chapter 12: Statistics, Data Distribution, and Cardinality
      1. The Role of Statistics in Query Optimization
        1. Statistics on an Indexed Column
        2. Benefits of Updated Statistics
        3. Drawbacks of Outdated Statistics
      2. Statistics on a Nonindexed Column
        1. Benefits of Statistics on a Nonindexed Column
        2. Drawback of Missing Statistics on a Nonindexed Column
      3. Analyzing Statistics
        1. Density
        2. Statistics on a Multicolumn Index
        3. Statistics on a Filtered Index
        4. Cardinality
      4. Statistics Maintenance
        1. Automatic Maintenance
        2. Auto Create Statistics
        3. Auto Update Statistics
        4. Auto Update Statistics Asynchronously
        5. Manual Maintenance
        6. Manage Statistics Settings
        7. Generate Statistics
      5. Statistics Maintenance Status
        1. Status of Auto Create Statistics
        2. Status of Auto Update Statistics
      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
    22. Chapter 13: Index Fragmentation
      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
      4. Analyzing the Fragmentation of a Small Table
      5. 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. Defragmentation and Partitions
      6. Significance of the Fill Factor
      7. Automatic Maintenance
      8. Summary
    23. Chapter 14: Execution Plan Generation
      1. Execution Plan Generation
        1. Parser
        2. Binding
        3. Optimization
        4. Execution Plan Caching
      2. Components of the Execution Plan
        1. Query Plan
        2. Execution Context
      3. Aging of the Execution Plan
      4. Summary
    24. Chapter 15: Execution Plan Cache Behavior
      1. Analyzing the Execution Plan Cache
      2. Execution Plan Reuse
      3. Ad Hoc Workload
        1. Prepared Workload
        2. Plan Reusability of an Ad Hoc Workload
        3. Plan Reusability of a Prepared Workload
      4. Query Plan Hash and Query Hash
      5. 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
      6. Summary
    25. Chapter 16: Parameter Sniffing
      1. Parameter Sniffing
        1. Bad Parameter Sniffing
        2. Mitigating Bad Parameter Sniffing
      2. Summary
    26. Chapter 17: Query 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 RECOMPILE
      4. Avoiding Recompilations
        1. Don’t Interleave DDL and DML Statements
        2. Avoiding Recompilations Caused by Statistics Change
        3. Using the KEEPFIXED PLAN Option
        4. Disable Auto Update Statistics on the Table
        5. Using Table Variables
        6. Avoiding Changing SET Options Within a Stored Procedure
        7. Using OPTIMIZE FOR Query Hint
        8. Using Plan Guides
      5. Summary
    27. Chapter 18: 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. Summary
    28. Chapter 19: Reduce Query Resource Use
      1. 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 When Naming Stored Procedures
      2. Reducing the Number of Network Round-Trips
        1. Execute Multiple Queries Together
        2. Use SET NOCOUNT
      3. Reducing the Transaction Cost
        1. Reduce Logging Overhead
        2. Reduce Lock Overhead
      4. Summary
    29. Chapter 20: Blocking and Blocked Processes
      1. Blocking Fundamentals
      2. Understanding Blocking
        1. Atomicity
        2. Consistency
        3. Isolation
        4. Durability
      3. Locks
        1. Lock Granularity
        2. Row-Level Lock
        3. Page-Level Lock
        4. Extent-Level Lock
        5. Heap or B-tree Lock
        6. Table-Level Lock
        7. Database-Level Lock
      4. Lock Operations and Modes
        1. Lock Escalation
        2. Lock Modes
        3. Exclusive (X) Mode
        4. Intent Shared (IS), Intent Exclusive (IX and Shared with Intent Exclusive (SIX) Modes
        5. Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes
        6. Bulk Update (BU) Mode
        7. Key-range Mode
        8. Lock Compatibility
      5. Isolation Levels
        1. Read Uncommitted
        2. Read Committed
        3. Repeatable Read
        4. Serializable
        5. Snapshot
      6. 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
      7. Capturing Blocking Information
        1. Capturing Blocking Information with SQL
        2. Extended Events and the blocked_process_report Event
      8. Blocking Resolutions
        1. Optimize the Queries
        2. Decrease the Isolation Level
      9. Partition the Contended Data
      10. Recommendations to Reduce Blocking
      11. Automation to Detect and Collect Blocking Information
      12. Summary
    30. Chapter 21: Causes and Solutions for Deadlocks
      1. Deadlock Fundamentals
        1. Choosing the Deadlock Victim
        2. Using Error Handling to Catch a Deadlock
      2. Deadlock Analysis
        1. Collecting Deadlock Information
        2. Analyzing the Deadlock
      3. Avoiding Deadlocks
        1. Accessing Resources in the Same Physical Order
        2. Decreasing the Number of Resources Accessed
        3. Minimizing Lock Contention
      4. Summary
    31. Chapter 22: Row-by-Row Processing
      1. Cursor Fundamentals
        1. Cursor Location
        2. Cursor Concurrency
        3. Cursor Types
        4. Cursor Cost Comparison
        5. Cost Comparison on Cursor Location
        6. Cost Comparison on Cursor Concurrency
        7. Read-Only
        8. Cost Comparison on Cursor Type
      2. Default Result Set
        1. Benefits
        2. Multiple Active Result Sets
        3. Drawbacks
      3. Cursor Overhead
        1. Analyzing Overhead with T-SQL Cursors
        2. Cursor Recommendations
      4. Summary
    32. Chapter 23: Memory-Optimized OLTP Tables and Procedures
      1. In-Memory OLTP Fundamentals
        1. System Requirements
        2. Basic Setup
        3. Create Tables
        4. In-Memory Indexes
        5. Hash Index
      2. Natively Compiled Stored Procedures
      3. Recommendations
        1. Baselines
        2. Correct Workload
        3. Memory Optimization Advisor
        4. Native Compilation Advisor
      4. Summary
    33. Chapter 24: Database Performance Testing
      1. Database Performance Testing
        1. A Repeatable Process
        2. Distributed Replay
      2. Capturing Data with the Server-Side Trace
      3. Distributed Replay for Database Testing
        1. Configuring the Client
        2. Running the Distributed Tests
      4. Conclusion
    34. Chapter 25: Database Workload Optimization
      1. Workload Optimization Fundamentals
        1. Workload Optimization Steps
        2. Sample Workload
      2. Capturing the Workload
      3. Analyzing the Workload
      4. Identifying the Costliest Query
        1. Determining the Baseline Resource Use of the Costliest Query
        2. Overall Resource Use
        3. Detailed Resource Use
      5. Analyzing and Optimizing External Factors
        1. Analyzing the Connection Options Used by the Application
        2. Analyzing the Effectiveness of Statistics
        3. Analyzing the Need for Defragmentation
        4. Analyzing the Internal Behavior of the Costliest Query
        5. Analyzing the Query Execution Plan
        6. Identifying the Costly Steps in the Execution Plan
        7. Analyzing the Processing Strategy
        8. Optimizing the Costliest Query
        9. Modifying the Code
        10. Fixing the Key Lookup Operation
        11. Tuning the Second Query
        12. Creating a Wrapper Procedure
      6. Analyzing the Effect on Database Workload
      7. Iterating Through Optimization Phases
      8. Summary
    35. Chapter 26: 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
        7. Consider Putting Tables into In-Memory Storage
      2. Configuration Settings
        1. Memory Configuration Options
        2. Cost Threshold for Parallelism
        3. Max Degree of Parallelism
        4. Optimize for Ad Hoc Workloads
        5. Blocked Process Threshold
        6. Database File Layout
        7. Database Compression
      3. Database Administration
        1. Keep the Statistics Up-to-Date
        2. Maintain a Minimum Amount of Index Defragmentation
        3. Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK
      4. Database Backup
        1. Incremental and Transaction Log Backup Frequency
        2. Backup Scheduling Distribution
        3. Backup Compression
      5. 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 Expressions 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
        12. Natively Compile Stored Procedures
      6. Summary
    36. Index

    Product information

    • Title: SQL Server Query Performance Tuning,Fourth Edition
    • Author(s):
    • Release date: September 2014
    • Publisher(s): Apress
    • ISBN: 9781430267423