دانلود کتاب SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

فهرست مطالب :

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 Use Appropriate Data Types Test EXISTS over COUNT(*) to Verify Data Existence Favor UNION ALL Over UNION Ensure Indexes Are Used for Aggregate and Sort Operations Be Cautious with Local Variables in a Batch Query Stored Procedure Names Actually Matter Reducing Network Overhead Where Possible Execute Multiple Queries in Sets Use SET NOCOUNT Techniques to Reduce Transaction Cost of a Query Reduce Logging Overhead Reduce Lock Overhead Mark the Database As READONLY Use Snapshot Isolation Prevent SELECT Statements from Requesting a Lock Summary Chapter 16: Blocking and Blocked Processes Blocking Fundamentals A Short Discussion of Terminology Introducing Blocking Transactions and ACID Properties Atomicity SET XACT_ABORT ON Explicit Rollback Consistency Isolation Durability Lock Types Row Locks Key Locks Page Locks Extent Locks Heap or B-Tree Locks Rowgroup Locks Table Locks Database Locks Lock Operations and Modes Lock Escalation Lock Modes Shared (S) Mode Update (U) Mode Exclusive (X) Mode Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) Modes Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes Bulk Update (BU) Mode Key-Range Mode Lock Compatibility Isolation Levels Read Uncommitted Read Committed Repeatable Read Serializable Snapshot Effect of Indexes on Locking Effect of a Nonclustered Index Effects of a Clustered Index Capturing Blocking Information Capturing Blocking Information Using T-SQL Extended Events and the blocked_process_report Event Recommendations to Reduce Blocking Summary Chapter 17: Causes and Solutions For Deadlocks Deadlock Fundamentals Choosing the Deadlock Victim Analyzing the Causes of Deadlocks Capturing Deadlock Information Trace Flag Extended Events Analyzing the Deadlock Graph Error Handling for Deadlocks Mechanisms to Prevent Deadlocks Access Resources in the Same Order Decrease the Amount of Resources Accessed Convert a Nonclustered Index to a Clustered Index Use a Covering Index Minimize Lock Contention Implement Row Versioning Decrease the Isolation Level Use Locking Hints Tune the Queries Summary Chapter 18: Row-by-Row Processing from Cursors and Other Causes Cursor Fundamentals Cursor Location Client-Side Cursors Server-Side Cursors Cursor Concurrency Read-Only Optimistic Scroll Locks Cursor Types Forward-Only Cursors Static Cursors Keyset-Driven Cursors Dynamic Cursors WHILE Loop Cursor Cost Comparison Cost Comparison Based on Location Client-Side Cursors Server-Side Cursors Cost Comparison Based on Concurrency Read-Only Optimistic Scroll Locks Cost Comparison Based on Cursor Type Forward-Only Cursors Fast-Forward-Only Cursor Static Cursors Keyset-Driven Cursors Dynamic Cursor Default Result Set Benefits Multiple Active Result Sets Drawbacks Cursor Overhead Cursor Recommendations Summary Chapter 19: Memory-Optimized OLTP Tables and Procedures In-Memory OLTP Fundamentals System Requirements Basic Setup Creating Tables In-Memory Indexes Hash Index Nonclustered Indexes Columnstore Index Statistics Maintenance Natively Compiled Stored Procedures Recommendations Baselines Correct Workload Memory Optimization Advisor Native Compilation Advisor Summary Chapter 20: Graph Databases Introduction to Graph Databases Querying Graph Data Shortest Path Performance Considerations of Graph Data Summary Chapter 21: Intelligent Query Processing Adaptive Query Processing Interleaved Execution Query Processing Feedback Memory Grants Cardinality Estimates Degree of Parallelism (DOP) Feedback Feedback Persistence Approximate Query Processing APPROX_COUNT_DISTINCT APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC Table Variable Deferred Compilation Scalar User-Defined Function Inlining Summary Chapter 22: Automated Tuning in Azure and SQL Server Automatic Plan Correction Tuning Recommendations Enabling Automatic Tuning Azure Portal SQL Server Automatic Tuning in Action Azure SQL Database Automatic Index Management Summary Chapter 23: A Query Tuning Methodology Database Design Use Entity-Integrity Constraints Maintain Domain and Referential Integrity Constraints Adopt Index-Design Best Practices Avoid the Use of the “sp_” Prefix for Stored Procedure Names Minimize the Use of Triggers Put Tables into In-Memory Storage Use Columnstore Indexes Take Advantage of Graph Storage Configuration Settings Memory Configuration Options Cost Threshold for Parallelism Max Degree of Parallelism Optimize for Ad Hoc Workloads Block Process Threshold Database Compression Database Administration Keep Statistics Up to Date Maintain a Minimum Amount of Index Fragmentation Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK Query Design Use the Command SET NOCOUNT ON Explicitly Define the Owner of an Object Avoid Non-sargable Search Conditions Avoid Arithmetic Expressions on Filter Clauses Avoid Optimizer Hints Stay Away from Nesting Views Ensure No Implicit Data Type Conversions Minimize Logging Overhead Adopt Best Practices for Reusing Execution Plans Caching Execution Plans Effectively Minimize Recompilation of Execution Plans Adopt Best Practices for Database Transactions Eliminate or Reduce the Overhead of Database Cursors Use Natively Compiled Stored Procedures Take Advantage of Columnstore for Analytical Queries Enable Query Store Summary Index