Microsoft SQL Server 2014 Query Tuning & Optimization

Microsoft SQL Server 2014 Query Tuning & Optimization

by Benjamin Nevarez
Released October 2014
Publisher(s): McGraw-Hill
ISBN: 9780071829434

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

Optimize Microsoft SQL Server 2014 queries and applications

Microsoft SQL Server 2014 Query Tuning & Optimization is filled with ready-to-use techniques for creating high-performance queries and applications. The book describes the inner workings of the query processor so you can write better queries and provide the query processor with the quality information it needs to produce efficient execution plans. You’ll also get tips for troubleshooting underperforming queries. In-Memory OLTP (Hekaton), a key new feature of SQL Server 2014, is fully covered in this practical guide.

  • Understand how the query optimizer works
  • Troubleshoot queries using extended events, SQL trace, dynamic management views (DMVs), the data collector, and other tools
  • Work with query operators for data access, joins, aggregations, parallelism, and updates
  • Speed up queries and dramatically improve application performance by creating the right indexes
  • Understand statistics and how to detect and fix cardinality estimation errors
  • Maximize OLTP query performance using In-Memory OLTP (Hekaton) features, including memory-optimized tables and natively compiled stored procedures
  • Monitor and promote plan caching and reuse to improve application performance
  • Improve the performance of data warehouse queries using columnstore indexes
  • Handle query processor limitations with hints and other methods

Table of contents

  1. Cover Page
  2. Title Page
  3. About the Author
  4. Copyright Page
  5. Dedication
  6. Contents at a Glance
  7. Contents
  8. Acknowledgments
  9. Introduction
  10. Chapter 1 An Introduction to Query Tuning and Optimization
    1. Architecture
      1. Parsing and Binding
      2. Query Optimization
      3. Generating Candidate Execution Plans
      4. Assessing the Cost of Each Plan
      5. Query Execution and Plan Caching
    2. Execution Plans
      1. Graphical Plans
      2. XML
      3. Text Plans
      4. Additional Plan Properties
      5. Warnings on Execution Plans
      6. Getting Plans from a Trace or the Plan Cache
      7. Removing Plans from the Plan Cache
    3. SET STATISTICS TIME / 10
    4. Summary
  11. Chapter 2 Troubleshooting Queries
    1. DMVs and DMFs
      1. sys.dm_exec_requests and sys.dm_exec_sessions
      2. sys.dm_exec_query_stats
      3. Understanding statement_start_offset and statement_end_offset
      4. sql_handle and plan_handle
      5. query_hash and plan_hash
      6. Finding Expensive Queries
    2. SQL Trace
    3. Extended Events
      1. Mapping SQL Trace Events to Extended Events
      2. Creating a Session
    4. Data Collector
      1. Configuration
      2. Using the Data Collector
      3. Querying the Data Collector Tables
    5. Summary
  12. Chapter 3 The Query Optimizer
    1. Overview
    2. sys.dm_exec_query_optimizer_info
    3. Parsing and Binding
    4. Simplification
      1. Contradiction Detection
      2. Foreign Key Join Elimination
    5. Trivial Plan
    6. Transformation Rules
    7. The Memo
    8. Statistics
    9. Full Optimization
      1. Search 0
      2. Search 1
      3. Search 2
    10. Summary
  13. Chapter 4 Query Operators
    1. Data Access Operators
      1. Scans
      2. Seeks
      3. Bookmark Lookup
    2. Aggregations
      1. Sorting and Hashing
      2. Stream Aggregate
      3. Hash Aggregate
      4. Distinct Sort
    3. Joins
      1. Nested Loops Join
      2. Merge Join
      3. Hash Join
    4. Parallelism
      1. The Exchange Operator
      2. Limitations
    5. Updates
      1. Per-Row and Per-Index Plans
      2. Halloween Protection
    6. Summary
  14. Chapter 5 Indexes
    1. Introduction
    2. Creating Indexes
      1. Clustered Indexes vs. Heaps
      2. Clustered Index Key
      3. Covering Indexes
      4. Filtered Indexes
    3. Index Operations
    4. The Database Engine Tuning Advisor
      1. Tuning a Workload Using the Plan Cache
      2. Offload of Tuning Overhead to Test Server
    5. Missing Indexes
    6. Index Fragmentation
    7. Unused Indexes
    8. Summary
  15. Chapter 6 Statistics
    1. Statistics
      1. Creating and Updating Statistics
      2. Inspecting Statistics Objects
      3. Density
    2. Histogram
    3. The New Cardinality Estimator
      1. Examples
      2. Trace Flag 4137
    4. Cardinality Estimation Errors
    5. Incremental Statistics
    6. Statistics on Computed Columns
    7. Filtered Statistics
    8. Statistics on Ascending Keys
      1. Trace Flag 2389
    9. UPDATE STATISTICS with ROWCOUNT and PAGECOUNT
    10. Statistics on Linked Servers
    11. Statistics Maintenance
    12. Cost Estimation
    13. Summary
  16. Chapter 7 In-Memory OLTP aka Hekaton
    1. Architecture
    2. Tables and Indexes
      1. Creating Hekaton Tables
      2. Hash Indexes
      3. Range Indexes
      4. Examples
    3. Natively Compiled Stored Procedures
      1. Creating Natively Compiled Stored Procedures
      2. DLLs
    4. Limitations
    5. AMR Tool
    6. Summary
  17. Chapter 8 Plan Caching
    1. Batch Compilation and Recompilation
    2. Exploring the Plan Cache
      1. How to Remove Plans
    3. Parameterization
      1. Autoparameterization
      2. The Optimize for Ad Hoc Workloads Option
      3. Forced Parameterization
      4. Stored Procedures
    4. Parameter Sniffing
      1. Optimize for a Typical Parameter
      2. Optimize on Every Execution
      3. Local Variables and the OPTIMIZE FOR UNKNOWN Hint
      4. Disabling Parameter Sniffing
      5. Parameter Sniffing and SET Options That Affect Plan Reuse
    5. Summary
  18. Chapter 9 Data Warehouses
    1. Data Warehouses
    2. Star Join Query Optimization
    3. Columnstore Indexes
      1. Performance Benefits
      2. Batch Mode Processing
      3. Creating Columnstore Indexes
      4. Hints
    4. Summary
  19. Chapter 10 Query Processor Limitations and Hints
    1. Query Optimization Research
    2. Join Orders
    3. Break Down Complex Queries
      1. OR Logic in the WHERE Clause
      2. Joins on Aggregated Data Sets
    4. Hints
      1. When to Use Hints
      2. Types of Hints
      3. Joins
      4. Aggregations
      5. FORCE ORDER
      6. INDEX, FORCESCAN, and FORCESEEK Hints
      7. FAST N
      8. NOEXPAND and EXPAND VIEWS Hints
      9. Plan Guides
      10. USE PLAN
    5. Summary
  20. Appendix References
    1. White Papers
    2. Articles
    3. Research Papers
    4. Books
  21. Index

Product information

  • Title: Microsoft SQL Server 2014 Query Tuning & Optimization
  • Author(s): Benjamin Nevarez
  • Release date: October 2014
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071829434