Sql Server - Troubleshooting Query Plan Quality Issues
Год выпуска: 2013
Производитель: Pluralsight
Сайт производителя:
http://pluralsight.com/training/Courses/TableOfContents/sqlserver-tqpq
Автор: Joe Sack
Продолжительность: 2h 20m
Тип раздаваемого материала: Видеоклипы
Язык: Английский
Описание:
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
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.
Содержание
Course Introduction
Course Introduction
Course Structure
Why Query Plan Quality Matters
Module Introduction
Which is the 'Good' Plan?
Cardinality Estimates
Costing and Plan Quality
Operator Cost (1)
Operator Cost (2)
Operator Cost (3)
Demo: Operator Cost
Operator Memory
Memory Operators
Under-estimates and Spills
Demo: Under-estimates and Spills
Over-estimates and Concurrency
Demo: Over-estimates and Concurrency
Impacted Query Optimizer Decisions
Excessive Resource Consumption
Identifying Query Plan Quality Issues
Module Introduction
Estimated Query Execution Plan
Actual Query Execution Plan
Capturing an Actual Plan
Demo: Capturing an Actual Plan
SQL Sentry Plan Explorer
Demo: SQL Sentry Plan Explorer
SQL Server 2012 Supplemental Information
Demo: Inaccurate Cardinality Estimate Event Capture
Demo: ConvertIssue Plan Attributes
Demo: Row Count Statistics in sys.dm_exec_query_stats
Query Plan Quality Patterns and Resolutions
Module Introduction
Before Jumping In...
Issue Prioritization
Missing or Stale Statistics (1)
Demo: Checking sys.databases
Demo: Checking sys.stats and sp_helpstats
Demo: Resolving NO_RECOMPUTE Issues
Missing or Stale Statistics (2)
Demo: Checking STATS_DATE
Demo: Manual Statistics Updates
Demo: Using Trace Flag 2371
Sampling Issues
Demo: Using DBCC SHOW_STATISTICS
Demo: Using sys.dm_db_stats_properties
Demo: Using FULLSCAN Manual Statistics Updates
Demo: Creating Filtered Statistics
Demo: Filtered Statistics Threshold Update Problem
Hidden Column Correlation
Demo: Hidden Column Correlation
Comparison of Intra-Table Columns
Demo: Comparison of Intra-Table Columns
Table Variable Usage
Demo: Table Variable Usage
Scalar and MSTV UDFs
Demo: MSTV UDFs
Parameter Sniffing
Demo: Parameter Sniffing
Implicit Data Type Conversion Issues
Complex Predicates
Demo: Complex Predicates
Query Complexity
Demo: Query Complexity
Hints
Demo: Hints
Distributed Queries
Query Optimizer Bugs
Course Summary
Файлы примеров: отсутствуют
Формат видео: WMV
Видео: wmv3, 1024x768, 15 fps, 1448kbps
Аудио: wma2, Stereo, 128kbps, 44.1kHz
Доп. информация: NO Exercise Files
