Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 1“. Part 1 is 2 hours and 31 minutes and covers the following:
Module: Validating Assumptions
- Downstream Impact of Bad Assumptions
- Cardinality Estimation and Predicates
- Selectivity
- Query Cardinality Questions
- Cardinality Estimate Inputs
- Statistics
- Demo: DBCC SHOW_STATISTICS STAT_HEADER
- Demo: DBCC SHOW_STATISTICS DENSITY_VECTOR
- Demo: DBCC SHOW_STATISTICS HISTOGRAM
- Demo: Histogram Direct Step Hit
- Demo: Scaling the Estimate
- Demo: Histogram Intra-Step Hit
- Demo: Density Vector With an Unknown Runtime Value
- Demo: Parameter Sniffed Value Estimation
- Demo: Distinct Value Estimation
- Impact to Query Execution and Performance
- Identifying Bad Assumptions
- Demo: Viewing Estimated Vs. Actual Row Counts
- Demo: sys.dm_exec_query_profiles
- Demo: Root-level Skews via sys.dm_exec_query_stats
- What Variance is Problematic?
Module: Troubleshooting Bad Assumptions
- Model Assumptions
- Demo: Jagged Distributions
- Demo: Correlated Predicates
- Demo: Correlated Predicates in SQL Server 2014
- Demo: Correlated Join Predicates, Independent Filter Predicates
- Demo: Searching for Off-Histogram Values
- Demo: Searching for Off-Histogram Values in SQL Server 2014
- Troubleshooting Questions
- Demo: Missing Statistics
- Demo: Selectivity Guesses
- Demo: Stale Statistics
- Demo: Inadequate Sampling
- Demo: Parameter Sensitivity
- Demo: Table Variable Usage
- Demo: MSTVF Usage – Part
- Demo: Data Type Conversions
- Demo: Buried Predicates
Module: Tuning Imprecision Problems
- Demo: SELECT *
- Demo: Unnecessary Sorts
- Demo: Needless DISTINCT
- Demo: UNION Vs. UNION ALL
- Demo: Leading Wildcards
- Demo: Hidden Cartesian Products
- Demo: Data Type Conversion Issues
- Demo: Redundant Logic and Overlapping Object References
And part 2 will be published soon!