SQL Server: Analyzing Query Performance for Developers
This course will teach you everything you need to know as a developer to start making sense of query plans and improving query performance. Apart from developers, this course is also applicable to anyone who is responsible for SQL Server performance.
What you'll learn
Developers are sometimes charged with fixing performance issues, and they have no idea where to start. They've never seen a query plan, or when they look at one they're not sure what it really means. The developers know how long it takes for a query to execute, but they don't know how to see how much IO, CPU, or memory it uses. And sometimes they just create an index and that seems to fix the problem (even though they have no idea why!). In this course, SQL Server: Analyzing Query Performance for Developers, you'll learn about all the data that SQL Server generates when a query executes - it's more than just the query plan - and you'll learn where it exists and how to find it. First, you'll discover what the data represents and how to read a query plan. Next, you'll explore some of the query plan operators you'll see most often. Finally, you'll learn what information is included in the plan, which may not be immediately obvious, but can be extremely valuable when troubleshooting. When you're finished this course, you'll have the skills and knowledge to start examining and understanding query plans and making query changes for better performance!
Table of contents
- Introduction and Information About Queries 3m
- Demo: Viewing "Live" Query Execution Data 9m
- Finding Query Execution Data and Extended Events 3m
- Demo: Query Information from Extended Events 5m
- Dynamic Management Objects 1m
- Demo: Query Execution Data in DMOs 8m
- Query Store and Performance Monitor 3m
- Demo: Query Metrics From Performance Monitor 5m
- A Note About Estimated and Actual Plans and Module Summary 2m
- Introduction and What Do You Look for in a Plan? 2m
- Scans and Seeks 2m
- Demo: Data Access Operators 16m
- Nested Loop and Lookups 2m
- Demo: Nested Loops and Lookups 11m
- Merge Join and Sort 3m
- Demo: Merge Join and Sorts 18m
- Hash Join 2m
- Demo: Hash Join 10m
- Compute Scalar 1m
- Demo: Compute Scalar 6m
- Module Summary 1m
- Introduction and Input Parameters 2m
- Demo: Finding Input Parameters in a Plan 10m
- Trace Flags 1m
- Demo: Trace Flags in a Query Plan 7m
- Cardinality Estimator Version and Issues 2m
- Demo: Examining CE Version and Estimates 11m
- Operator Execution Statistics 1m
- Demo: Execution Statistics for Operators 7m
- Parallelism 4m
- Demo: Parallelism in Plans 8m
- Seek Predicate and Residual Predicate 2m
- Demo: Seek and Residual Predicates 10m
- Warnings 1m
- Demo: Generating Plan Warnings 8m
- Module and Course Summaries 3m
About the author
Erin Stellato is a Principal Consultant with SQLskills and a SQL Server MVP. She has worked as a SQL Server professional since 2003, spending 12 years of her career at two different software companies. Erin discovered a passion for all things data while completing her M.S. in Motor Control at the University of Michigan. As she entered the IT field, she discovered the power of relational databases. During her IT career, Erin has worked closely with customers, users and colleagues to support bus... more