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!