SQL Server: Troubleshooting Query Plan Quality Issues
Learn how to identify, diagnose, and prevent problems where SQL Server chooses the incorrect query plan for your critical queries, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards
What you'll learn
There are many problems that can lower the performance of your workload and one of the most common is an incorrect query plan. Often the poor query plan is chosen because the cardinality estimate is wrong - the estimate by the query processor of how many table rows will be involved in the query. This course shows you how to recognize when the query processor has an incorrect estimate, along with explaining and showing a multitude of possible causes, plus how to fix them. The course starts with explaining why query plan quality is important, and then shows how to easily spot cardinality estimate issues from examining query plans. The majority of the course shows all the possible causes of cardinality estimates being incorrect along with how to fix them, and more than 25 demos to walk you through practical examples of the concepts and problems in the lectures. This course is perfect for developers, DBAs, and anyone responsible for performance tuning on SQL Server. The information in the course applies to all version from SQL Server 2005 onwards.
Table of contents
- Module Introduction 1m
- Which is the 'Good' Plan? 3m
- Cardinality Estimates 2m
- Costing and Plan Quality 1m
- Operator Cost (1) 1m
- Operator Cost (2) 1m
- Operator Cost (3) 2m
- Demo: Operator Cost 8m
- Operator Memory 1m
- Memory Operators 2m
- Under-estimates and Spills 1m
- Demo: Under-estimates and Spills 4m
- Over-estimates and Concurrency 1m
- Demo: Over-estimates and Concurrency 4m
- Impacted Query Optimizer Decisions 2m
- Excessive Resource Consumption 1m
- Module Introduction 1m
- Estimated Query Execution Plan 1m
- Actual Query Execution Plan 1m
- Capturing an Actual Plan 1m
- Demo: Capturing an Actual Plan 2m
- SQL Sentry Plan Explorer 1m
- Demo: SQL Sentry Plan Explorer 2m
- SQL Server 2012 Supplemental Information 2m
- Demo: Inaccurate Cardinality Estimate Event Capture 3m
- Demo: ConvertIssue Plan Attributes 2m
- Demo: Row Count Statistics in sys.dm_exec_query_stats 4m
- Module Introduction 1m
- Before Jumping In... 1m
- Issue Prioritization 1m
- Missing or Stale Statistics (1) 2m
- Demo: Checking sys.databases 1m
- Demo: Checking sys.stats and sp_helpstats 1m
- Demo: Resolving NO_RECOMPUTE Issues 5m
- Missing or Stale Statistics (2) 1m
- Demo: Checking STATS_DATE 2m
- Demo: Manual Statistics Updates 6m
- Demo: Using Trace Flag 2371 6m
- Sampling Issues 2m
- Demo: Using DBCC SHOW_STATISTICS 3m
- Demo: Using sys.dm_db_stats_properties 2m
- Demo: Using FULLSCAN Manual Statistics Updates 4m
- Demo: Creating Filtered Statistics 3m
- Demo: Filtered Statistics Threshold Update Problem 5m
- Hidden Column Correlation 1m
- Demo: Hidden Column Correlation 8m
- Comparison of Intra-Table Columns 1m
- Demo: Comparison of Intra-Table Columns 1m
- Table Variable Usage 1m
- Demo: Table Variable Usage 2m
- Scalar and MSTV UDFs 1m
- Demo: MSTV UDFs 2m
- Parameter Sniffing 2m
- Demo: Parameter Sniffing 3m
- Implicit Data Type Conversion Issues 1m
- Complex Predicates 1m
- Demo: Complex Predicates 2m
- Query Complexity 2m
- Demo: Query Complexity 3m
- Hints 1m
- Demo: Hints 3m
- Distributed Queries 1m
- Query Optimizer Bugs 1m
- Course Summary 1m
