SQL Server 2022 Query performance tuning troubleshoot and optimize Query performance
Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated...
| Otros Autores: | |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
New York, New York :
Apress
[2022]
|
| Edición: | Sixth edition |
| Colección: | ITpro collection
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009707507606719 |
Tabla de Contenidos:
- Intro
- Table of Contents
- About the Author
- About the Technical Reviewer
- Acknowledgments
- Introduction
- Chapter 1: Query Performance Tuning
- The Query Performance Tuning Process
- Performance Issues
- A Repetitive Process
- Understanding What Defines "Good Enough"
- Establishing Comparison Points
- Most Likely Performance Issues
- Common Performance Issues
- Insufficient or Poor Indexes
- Inaccurate or Missing Statistics
- Bad T-SQL
- Problematic Execution Plans
- Excessive Blocking
- Deadlocks
- Non-Set-Based Operations
- Incorrect Database Design
- Poor Execution Plan Reuse
- Frequent Recompilation of Queries
- Summary
- Chapter 2: Execution Plan Generation and the Query Optimizer
- The Query Optimization Process
- Optimization Preparation
- Parsing
- Binding
- Optimization
- Simplification
- Trivial Plan Match
- Optimization Phases
- Generating Parallel Execution Plans
- Execution Plan Caching
- Aging of the Execution Plan
- Summary
- Chapter 3: Methods for Capturing Query Performance Metrics
- Methods to Capture Query Performance Metrics
- Include Client Statistics
- Connection Properties
- SET STATISTICS TIME/IO
- QueryTimeStats in the Execution Plan
- Trace Events (Profiler)
- Dynamic Management Views
- Actively Executing Queries
- Previously Executed Queries
- Query Store
- Extended Events
- Creating an Extended Events Session
- Adding and Configuring Events
- Adding Global Fields to Events
- Using Predicates with Events
- Optional Event Fields
- Defining Targets
- Using the event_file Target
- Using the histogram Target
- Working with Sessions
- Adding Causality Tracking
- Scripting Extended Events
- Live Data Explorer Window
- Filtering Live Data
- Aggregating Live Data
- General Recommendations for Using Extended Events
- Set Max File Size Appropriately.
- Avoid Debug Events
- Avoid the Use of No_Event_Loss
- Summary
- Chapter 4: Analyzing Query Behavior Using Execution Plans
- Estimated vs. Actual Execution Plans
- Capturing Execution Plans
- SQL Server Management Studio
- Dynamic Management Views
- Query Store
- Extended Events
- What Is Inside an Execution Plan
- Reading an Execution Plan
- What Do You Look for in an Execution Plan?
- First Operator
- Warnings
- Most Costly Operations
- Fat Pipes
- Extra Operators
- Scans
- Estimate vs. Actual
- After the Guideposts
- Tools That Assist You with Execution Plans
- SQL Server Management Studio
- Find Node
- Compare Plans
- Live Query Statistics
- Third Party
- Solar Winds Plan Explorer
- Supratimas
- Paste The Plan
- Summary
- Chapter 5: Statistics, Data Distribution, and Cardinality
- Statistics in the Query Optimization Process
- Statistics on Rowstore Indexed Columns
- Benefits of Updated Statistics
- Drawbacks of Outdated Statistics
- Statistics on Nonindexed Columns
- Benefits of Statistics on a Nonindexed Column
- Comparing Performance with Missing Statistics
- Analyzing Statistics
- Header
- Density
- Histogram
- Cardinality
- Statistics on a Multicolumn Index
- Statistics on a Filtered Index
- Controlling the Cardinality Estimator
- Statistics Maintenance
- Auto Create Statistics
- Auto Update Statistics
- Auto Update Statistics Asynchronously
- Manual Maintenance
- Manage Statistics Settings
- Create Statistics Manually
- Analyzing the Effectiveness of Statistics for a Query
- Resolving a Missing Statistics Issue
- Resolving an Outdated Statistics Issue
- Recommendations on Statistics
- Backward Compatibility of Statistics
- Auto Create Statistics
- Auto Update Statistics
- Automatic Update Statistics Asynchronously
- Amount of Sampling to Collect Statistics
- Summary.
- Chapter 6: Using Query Store for Query Performance and Execution Plans
- Query Store Function and Design
- Information Collected by Query Store
- Query Runtime Data
- Controlling Query Store
- Capture Mode
- Query Store Reporting
- Plan Forcing
- Forcing Query Hints
- Optimized Plan Forcing
- Query Store for Upgrades
- Summary
- Chapter 7: Execution Plan Cache Behavior
- Querying the Plan Cache
- Execution Plan Caching and Plan Reuse
- Ad Hoc Workload
- Optimize for Ad Hoc Workload
- Simple Parameterization
- Forced Parameterization
- Prepared Workload
- Stored Procedure
- Stored Procedure Compiled on Initial Execution
- Performance Benefits of Stored Procedures
- Nonperformance Benefits of Stored Procedures
- sp_executesql
- Prepare/Execute Model
- Query Hash and Query Plan Hash
- Execution Plan Cache Recommendations
- Explicitly Parameterize Values in Your Query
- Use Stored Procedures Where You Can
- Use sp_executesql As an Alternative to Stored Procedures
- Take Advantage of the Prepare/Execute Model
- Avoid Ad Hoc Queries
- Enable Optimize For Ad Hoc
- Summary
- Chapter 8: Query Recompilation
- Benefits and Drawbacks of Recompilation
- Identifying the Statement Being Recompiled
- Analyzing Causes of Recompilation
- Deferred Object Resolution
- Recompilation on a Table
- Recompilation on a Temporary Table
- Avoiding Recompiles
- Avoid Interleaving DDL and DML Statements
- Reduce Recompilation Caused by Statistics Changes
- Use the KEEPFIXED PLAN Hint
- Disable Automatic Statistics Maintenance on a Table
- Use Table Variables
- Use Temporary Tables Across Multiple Scopes
- Avoid Changing SET Options Within a Batch
- Controlling Recompile Results
- Plan Forcing
- Query Hints
- Plan Guides
- Hint Forcing
- Summary
- Chapter 9: Index Architecture
- What Is a Rowstore Index?.
- The Benefits of Indexes
- Index Overhead
- What Is a Columnstore Index?
- Columnstore Index Storage
- Index Design Recommendations
- Type of Query Processing Being Performed
- Determine Filtering Criteria
- Use Narrow Indexes
- Consider Selectivity of the Data
- Determine Data Type
- Consider Column Order
- Determine Data Storage
- Rowstore Index Behavior
- Clustered Indexes
- Heap Tables
- Relationships with Nonclustered Indexes
- Clustered Index Recommendations
- Create the Clustered Index First
- Keep Clustered Indexes Narrow
- Rebuild the Clustered Index in a Single Step
- Where Possible, Make the Clustered Index Unique
- When to Use a Clustered Index
- Accessing the Data Directly
- Retrieving Presorted Data
- Poor Design Practices for a Clustered Index
- Frequently Updated Columns
- Wide Keys
- Nonclustered Indexes
- Nonclustered Index Maintenance
- Defining the Lookup Operation
- Nonclustered Index Recommendations
- When to Use a Nonclustered Index
- When Not to Use a Nonclustered Index
- Columnstore Index Behavior
- Columnstore Recommendations
- Summary
- Chapter 10: Index Behaviors
- Covering Indexes
- A Pseudoclustered Index
- Recommendations
- Index Intersection
- Index Joins
- Filtered Indexes
- Indexed Views
- Benefit
- Overhead
- Usage Scenarios
- Index Compression
- Index Characteristics
- Different Column Sort Order
- Index on Computed Columns
- CREATE INDEX Statement Processed As a Query
- Parallel Index Creation
- Online Index Creation
- Considering the Database Engine Tuning Advisor
- OPTIMIZE_FOR_SEQUENTIAL_KEY
- Resumable Indexes and Constraints
- Special Index Types
- Full-Text
- Spatial
- XML
- Summary
- Chapter 11: Key Lookups and Solutions
- Purpose of Lookups
- Performance Issues Caused by Lookups
- Analysis of the Causes of Lookups.
- Techniques to Resolve Lookups
- Create a Clustered Index
- Use a Covering Index
- Take Advantage of Index Joins
- Summary
- Chapter 12: Dealing with Index Fragmentation
- Causes of Rowstore Fragmentation
- How Fragmentation Occurs in Rowstore Indexes
- Page Split from an UPDATE Statement
- Page Split by an INSERT Statement
- How Fragmentation Occurs in the Columnstore Indexes
- Fragmentation Overhead
- Rowstore Overhead
- Columnstore Overhead
- Analyzing the Amount of Fragmentation
- Analyzing the Fragmentation of a Small Table
- Fragmentation Resolutions
- Drop and Recreate the Index
- Recreating the Index with the DROP_EXISTING Clause
- Execute the ALTER INDEX REBUILD Command
- Execute the ALTER INDEX REORGANIZE Command
- Defragmentation and Partitions
- Significance of the Fill Factor
- Automatic Maintenance
- Summary
- Chapter 13: Parameter-Sensitive Queries: Causes and Solutions
- How Does Parameter Sniffing Work?
- Identifying Queries That Are Sensitive to Parameter Values
- Mechanisms for Addressing Plan-Sensitive Queries
- Disable Parameter Sniffing
- Local Variables
- Recompile
- OPTIMIZE FOR Query Hint
- Force Plan
- Parameter Sensitive Plan Optimization
- Summary
- Chapter 14: Query Design Analysis
- Query Design Recommendations
- Keep Your Result Sets Small
- Limit the Columns in Your SELECT List
- Filter Your Data Through a WHERE Clause
- Use Indexes Effectively
- Use Effective Search Conditions
- BETWEEN vs. IN/OR
- LIKE Condition
- !<
- Condition vs. >
- = Condition
- Avoid Operations on Columns
- Custom Scalar UDF
- Minimize Optimizer Hints
- JOIN Hint
- INDEX Hints
- Using Domain and Referential Integrity
- NOT NULL Constraint
- User-Defined Constraints
- Declarative Referential Integrity
- Summary
- Chapter 15: Reduce Query Resource Use.
- Avoiding Resource-Intensive Queries.
