SQL Server: Common Query Tuning Problems and Solutions - Part 1
Learn to recognize and diagnose numerous types of SQL Server query performance problems. This course is perfect for developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onward.
What you'll learn
There are a wide variety of common performance problems that you will encounter when query tuning. Part 1 of this two-part course begins by showing how to validate query execution assumptions around statistics and cardinality in order to ensure you're troubleshooting the true root cause of a query performance issue. You'll then learn about common areas where bad assumptions can occur that negatively impact overall query performance, and about common problems and solutions related to being imprecise when querying a database. The course is very demo-centric, with lots of practical tips and tricks you'll be able to apply in your environment. This course is perfect for developers, DBAs, and anyone responsible for query performance tuning on SQL Server, from SQL Server 2005 onward.
Table of contents
- Module Introduction 1m
- Downstream Impact of Bad Assumptions 1m
- Cardinality Estimation and Predicates 1m
- Selectivity 1m
- Query Cardinality Questions 1m
- Cardinality Estimate Inputs 1m
- Statistics 1m
- Demo: DBCC SHOW_STATISTICS STAT_HEADER 3m
- Demo: DBCC SHOW_STATISTICS DENSITY_VECTOR 2m
- Demo: DBCC SHOW_STATISTICS HISTOGRAM 3m
- Demo: Histogram Direct Step Hit 2m
- Demo: Scaling the Estimate 4m
- Demo: Histogram Intra-Step Hit 3m
- Demo: Density Vector With an Unknown Runtime Value 4m
- Demo: Parameter Sniffed Value Estimation 4m
- Demo: Distinct Value Estimation 2m
- Impact to Query Execution and Performance 1m
- Identifying Bad Assumptions - 1 1m
- Demo: Viewing Estimated Vs. Actual Row Counts 4m
- Identifying Bad Assumptions - 2 1m
- Demo: sys.dm_exec_query_profiles 2m
- Demo: Root-level Skews via sys.dm_exec_query_stats 5m
- What Variance is Problematic? 1m
- Module Summary 1m
- Module Introduction 1m
- Model Assumptions 1m
- Demo: Jagged Distributions 6m
- Demo: Correlated Predicates 5m
- Demo: Correlated Predicates in SQL Server 2014 6m
- Demo: Correlated Join Predicates, Independent Filter Predicates 4m
- Demo: Searching for Off-Histogram Values 3m
- Demo: Searching for Off-Histogram Values in SQL Server 2014 4m
- Troubleshooting Questions - 1 1m
- Demo: Missing Statistics 3m
- Demo: Selectivity Guesses 4m
- Demo: Stale Statistics 4m
- Demo: Inadequate Sampling 6m
- Demo: Parameter Sensitivity 6m
- Demo: Table Variable Usage 2m
- Demo: MSTVF Usage - Part 1 4m
- Demo: MSTVF Usage - Part 2 3m
- Demo: MSTVF Usage - Part 3 1m
- Troubleshooting Questions - 2 2m
- Demo: Data Type Conversions 4m
- Demo: Buried Predicates - Part 1 2m
- Demo: Buried Predicates - Part 2 2m
- Module Summary 0m
- Module Introduction 1m
- Demo: SELECT * 3m
- Demo: Unnecessary Sorts 3m
- Demo: Needless DISTINCT 3m
- Demo: UNION Vs. UNION ALL 3m
- Demo: Leading Wildcards 3m
- Demo: Hidden Cartesian Products 2m
- Demo: Data Type Conversion Issues 4m
- Demo: Redundant Logic and Overlapping Object References 4m
- Module and Course Summary 1m
