فیلم آموزش کوئری نویسی در اس کیو ال

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. Course Introduction 01:08 Course Scope 00:40 Course Structure 01:04 Validating Assumptions 00:47:00 Module Introduction 00:53 Downstream Impact of Bad Assumptions 01:16 Cardinality Estimation and Predicates 01:01 Selectivity 00:43 Query Cardinality Questions 00:54 Cardinality Estimate Inputs 00:39 Statistics 00:41 Demo: DBCC SHOW_STATISTICS STAT_HEADER 03:21 Demo: DBCC SHOW_STATISTICS DENSITY_VECTOR 01:46 Demo: DBCC SHOW_STATISTICS HISTOGRAM 03:19 Demo: Histogram Direct Step Hit 02:17 Demo: Scaling the Estimate 03:36 Demo: Histogram Intra-Step Hit 02:58 Demo: Density Vector With an Unknown Runtime Value 03:54 Demo: Parameter Sniffed Value Estimation 03:45 Demo: Distinct Value Estimation 01:42 Impact to Query Execution and Performance 00:41 Identifying Bad Assumptions - 1 00:46 Demo: Viewing Estimated Vs. Actual Row Counts 03:52 Identifying Bad Assumptions - 2 00:42 Demo: sys.dm_exec_query_profiles 01:45 Demo: Root-level Skews via sys.dm_exec_query_stats 04:50 What Variance is Problematic? 00:55 Module Summary 00:33 Troubleshooting Bad Assumptions 01:15:15 Module Introduction 00:42 Model Assumptions 01:22 Demo: Jagged Distributions 05:32 Demo: Correlated Predicates 05:20 Demo: Correlated Predicates in SQL Server 2014 06:03 Demo: Correlated Join Predicates, Independent Filter Predicates 04:20 Demo: Searching for Off-Histogram Values 03:19 Demo: Searching for Off-Histogram Values in SQL Server 2014 03:37 Troubleshooting Questions - 1 01:15 Demo: Missing Statistics 03:13 Demo: Selectivity Guesses 03:54 Demo: Stale Statistics 04:24 Demo: Inadequate Sampling 05:43 Demo: Parameter Sensitivity 05:31 Demo: Table Variable Usage 02:24 Demo: MSTVF Usage - Part 1 03:32 Demo: MSTVF Usage - Part 2 02:34 Demo: MSTVF Usage - Part 3 01:29 Troubleshooting Questions - 2 01:41 Demo: Data Type Conversions 04:15 Demo: Buried Predicates - Part 1 02:16 Demo: Buried Predicates - Part 2 02:11 Module Summary 00:26 Tuning Imprecision Problems 00:26:12 Module Introduction 00:55 Demo: SELECT * 03:26 Demo: Unnecessary Sorts 02:41 Demo: Needless DISTINCT 03:01 Demo: UNION Vs. UNION ALL 02:38 Demo: Leading Wildcards 03:27 Demo: Hidden Cartesian Products 01:55 Demo: Data Type Conversion Issues 03:41 Demo: Redundant Logic and Overlapping Object References 03:33 Module and Course Summary 00:50