Pluralsight - SQL Server: Common Query Tuning Problems and Solutions - Part 2

There are a wide variety of common performance problems that you will encounter when query tuning. Part 2 of this two-part course begins by covering common problems arising from writing and using inefficient Transact-SQL code. You'll then learn about query performance problems and associated query tuning solutions for situations where the query and/or design is inappropriate for the workload type. Finally, the course will cover a variety of more advanced query tuning problem and solutions. 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 in SQL Server, from SQL Server 2005 onward.

Course Introduction
Course Introduction
Course Structure

Troubleshooting Inefficiency
Module Introduction
Demo: One-Row-At-A-Time-Processing
Demo: The Forgotten Trigger
Demo: Scalar Function Inhibited Parallelism
Demo: Scalar Function Row by Row Execution
Demo: Too Few Rows Per Batch
Demo: Not Using TOP When You Can
Demo: Unnecessary Many-To-Many
Demo: Excessive Fragmentation and Scans
Module Summary

Tuning OLTP vs. DSS Patterns
Module Introduction
Defining OLTP and DSS
OLTP Characteristics
Demo: Stop-And-Go Operators
Demo: Finding Timeout-Prone Queries
Demo: Over-Indexing
Demo: OLTP and Parallelism
Demo: OLTP and Deep Trees
DSS Characteristics
Demo: Expanding the AdventureWorksDW2012 Database
Demo: Unwanted Serial Plans
Demo: Gatekeeper Row Issue - Part 1
Demo: Gatekeeper Row Issue - Part 2
Demo: Gatekeeper Row Issue - Part 3
Demo: Aggregation Queries and Columnstore Index Opportunities
Demo: Distinct Aggregation Issue
Module Summary

Advanced Query Tuning Patterns
Module Introduction
Demo: New-Feature Abuse
Demo: Recursive CTE Estimate Skews
Demo: Seeks, Scans, Predicates, and Filters - Part 1
Demo: Seeks, Scans, Predicates, and Filters - Part 2
Demo: Seeks, Scans, Predicates, and Filters - Part 3
Demo: Unmatched Indexes
Demo: Disused Filtered Statistics
Demo: Interpreting RetrievedFromCache
Demo: Amplification and Distinct Value Counts
Demo: Join Hints and Forced Order
Demo: CTEs vs. Derived Tables
Demo: One Procedure With Multiple Optional Input Parameters
Demo: Procedure Sub-Branching
Demo: Verifying Instance Workload Complexity
Module Summary
Course Summary